ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¸óÀɮס]¤u§@ï¡^¨Ï¥Î¨Ï¥Î¦WºÙ

¸óÀɮס]¤u§@ï¡^¨Ï¥Î¨Ï¥Î¦WºÙ

¥»©«³Ì«á¥Ñ et5323 ©ó 2010-10-3 10:41 ½s¿è

a.xlsªºSheet1ùØ©w¸q¦³¦WºÙ¡¨name¡¨,¦b¨ä³B©ó¡§¥´¶}¡¨ªºª¬ºA¤U:
1.¦bb.xlsªº³æ®æùؤޥΡG=¡¦a.xls¡¦!name
2.¥N½XùؤޥÎ:
Sub test()
Msgbox workbooks(¡§a.xls¡¨).sheets(¡§Sheet1¡¨).[name]
End sub

¥H¤U¤º®e来¦Ûhttp://www.cpearson.com/excel/hidden.htm¡G

                                                             Excel¤¤ÁôÂõ۪º¦WºÙªÅ¶¡

    ³o­Ó³QÁôÂ꺦WºÙªÅ¶¡¬O¤@­ÓÄÝ©ó·í«eExcel¹ê¨Òªº°O¾ÐÅé°Ï°ì¡A¸Ó°Ï°ì¸ü¤J¶µDLLs(¡§XLLS¡¨)¯à¦sÀxÁ{®Éªº¦WºÙ¡C¨Ï¥Î³o­Ó°Ï°ì¡A§Y¨Ï¨S¦³¥i§Q¥Îªº§»¤u§@ªí¥i¥Î¡AXLLs¤]¯à©w¸q¦WºÙ¡C
    ÁôÂõ۪º¦WºÙ¾Þ§@°ò©óXLM¨ç¼ÆSET.NAME¡BGET.NAME©MEVALUATE¡A¦Ó¥B·í¦bXLM§»¤u§@ªí¤¤¨Ï¥Î®É¡ASET.NAME©w¸q¤@­Ó¼Ð·Çªº¤u§@ªí¯Å¦WºÙ¡A¦P®É¡A·í¥¦¦bXLL¤¤³Q½Õ¥Î®É¡A³Ð«Ø¤@­ÓÀ³¥Îµ{¦¡¯Å¦WºÙ¨Ã¥B¦sÀx¦b¤@­Ó³QÁôÂ꺰ϰ줤¡C
    ©w¸q¦b³QÁôÂðϰ쪺¦WºÙ¦³¤@¨Ç¯S®íªº¥\¯à¡A³o¨Ï±o¥¦­Ì»P¼Ð·Ç¤u§@ï¦WºÙ¦³«Ü¤jªº¤£¦P¡C³o¨Ç¥\¯à±N¦b«á­±´y­z¡C
(1) ³Ð«Ø¤@­ÓÁôÂ꺦WºÙ
¤U­±ªº»y¥y³Ð«Ø¤@­Ó¥]§t¦r¦ê¡§OK¡¨ªº¦W¬°TestªºÁôÂæWºÙ¡G
Application.ExecuteExcel4Macro "SET.NAME(""Test"",""OK"")"
(2) Àò¨ú¤@­ÓÁôÂæWºÙ©Ò¥Nªíªº¤º®e
¬°¤FÀò¨ú¦WºÙTest©Ò¥Nªíªº¤º®e¡A¨Ï¥Î¤U­±ªº¥N½X¡G
TestVal = Application.ExecuteExcel4Macro("Test")
ª`·N¡A¥u¨Ï¥Î¦WºÙ¥»¨­§@¬°ExecuteExcel4Macroªº°Ñ¼Æ¡C
(3) §R°£¤@­ÓÁôÂ꺦WºÙ
¬°¤F§R°£¦WºÙTest¡A¨Ï¥Î¤U­±ªº»y¥y¡G
Application.ExecuteExcel4Macro "SET.NAME(""Test"")"
ª`·N¡A©¿²¤¤FSET.NAME¤¤ªº²Ä¤G­Ó°Ñ¼Æ¡C
     ³QÁôÂ꺦WºÙªÅ¶¡ªº¯S¼x
¦b³QÁôÂ꺦WºÙªÅ¶¡¤¤©Ò©w¸qªº¦WºÙªº¥D­n¯S¼x¬O¡G¥¦­Ì¤£ÄÝ©ó¥ô¦ó¤u§@ï¡A¦ÓÄÝ©óÀ³¥Îµ{¦¡¥»¨­¡A³o·N¨ýµÛ¡G
(1)¦bExcel¤¤ªº¥ô¦ó¦a¤è¥i¥Hª½±µ³X°Ý³o¨Ç¦WºÙ¡CµL½×¦b­þ­Ó¤u§@襤³Ð«Ø¤F³oºØ¦WºÙ¡A¦b¥ô¦ó¤u§@襤ªº¥ô¦óVBA¼Ò²Õ¡B¤u§@ªí©Î¥¨¶°¤u§@ªí(©M¥ô¦óDLL¸ü¤J¶µ)¤¤ª½±µ³£¯àª½±µÅª¨ú©M­×§ï¥¦­Ì¡C
(2) ¥¦­Ìªº¡§¥Í¦s®É¶¡¡¨»P·í«eExcel·|¸Ü¤@­P
    ¦pªG¦b¤u§@ïWbk1.xls¤¤ªº¬Y­ÓVBA¼Ò²Õ¤¤³Ð«Ø¤F¤@­Ó¦WºÙ¡AµM«áÃö³¬¤F³o­Ó¤u§@ï¡A¨º»ò¸Ó¦WºÙ¤´µM¦sÀx¦b³QÁôÂ꺩R¦WªÅ¶¡¤¤¡C¦pªG±µµÛ¥´¶}¥t¤@­Ó¤u§@ïWbk2.xls¡A¨º»ò³o­Ó¤u§@諸VBA¹Lµ{¤´¯àÀò¨ú©M­×§ï­è¤~¦bWbk1.xls¤¤©Ò³Ð«Øªº¦WºÙ¡C¦b¨S¦³³Q¥ô¦óVBA¸ü¤J¶µ©Ò­­¨îªº±¡ªp¤U¡A¦b³QÁôÂ꺦WºÙªÅ¶¡¤¤©Ò©w¸qªº¦WºÙ¯à§@¬°¥Ã¤[ªº¡§¤½¦@Åܼơ¨³X°Ý¡C
    ¹ï©ó³o¨ÇÄÝ©óÀ³¥Îµ{¦¡ªºÁôÂ꺦WºÙ¡AÃö³¬©Ò¦³¤u§@ï©M¸ü¤J¶µ¤£·|¾P·´¥¦­Ì¡C³q¹L¹ïSET.NAME(¨S¦³²Ä¤G­Ó°Ñ¼Æ)©ú½Tªº½Õ¥Î©ÎªÌ°h¥X¨Ã­«±ÒExcel¡A¤~¯à¾P·´¥¦­Ì¡C¦b³oºØ±¡ªp¤U¡A³o¨Ç¦WºÙ¯à§@¬°¤@ºØExcelªºÀô¹ÒÅܼƨӨϥΡC
(3) ¥¦­Ì¬O¡§§¹¥þÁôÂõ۪º¡¨
     ·í¤@­Ó¨ü«OÅ@ªº¸ü¤J¶µ¨Ï¥Î³o­ÓÁôÂ꺦WºÙªÅ¶¡®É¡A·sªº¦WºÙ¤£¯à³Q¥ô¦ó¨ä¥LªºVBA¼Ò²ÕŪ¨ú¡A¤]¤£¯à³Q¥Î¤áŪ¨ú¡A°£«D¥L­Ìª¾¹D¦WºÙªÅ¶¡ªº¨­¥÷ÃҮѡC¨S¦³¤èªk¥h¡§¦C¥X¡¨³Q©w¸q¦bÁôÂéR¦WªÅ¶¡¤¤ªº¦WºÙ¡C
    ³o¨Ç¦WºÙµL¶·»P¼Ð·ÇªºÁôÂæWºÙ(§Y±N¤u§@ï©Î¤u§@ªí¦WºÙªº.VisibleÄݩʳ]¸m¬°False)¬Û²V²c¡C¼Ð·Çªº¤u§@ï¯Åªº¦WºÙ¨M¤£·|¯u¥¿ªº³QÁôÂáA¦]¬°¥¦­Ì¯à³q¹L¨Ï¥ÎApplication.Names¶°¦Xªº¥ô¦óVBA¹Lµ{¨ÓÀò¨ú©M­×§ï¡A¦p¤U­±ªº¥N½X©Ò¥Ü¡G
Dim CName As Name
  For Each CName In Workbooks("Wbks1.xls").Names
    If CName.Hidden Then
        MsgBox CName.Name & " deleted"
        CName.Delete
    End If
  Next CName
     ¤W­±ªº¥N½X¦b¤u§@ïWbks1.xls©Ò¦³ÁôÂ꺦WºÙ¤¤°j°é¨Ã§R°£¥¦­Ì¡A¦ý¬O³o¨Ç¥N½X¤£¯àµo²{¦sÀx¦b³QÁôÂ꺩R¦WªÅ¶¡¤¤ªº¦WºÙ¡A¦]¬°³o¨Ç¦WºÙ¤£ÄÝ©óApplication.Names¶°¦X¡A¦]¦¹¡A¥¦­Ì³Q«OÅ@¥H¤Ï¹ï¥ô¦ó´c·Nªº³X°Ý©Î­×§ï¡C
¥Ü¨Ò
     ¤U­±ªº¥N½Xºt¥Ü¤F¦b¨ü«OÅ@ªºVBA¸ü¤J¶µ¤¤ÁôÂõ۪º©R¦WªÅ¶¡ªº¥i¯àªº¥Î³~¡C
    ¥¦­­¨î¥Î¤á¦b¬Û¦PªºExcel·|¸Ü¤¤°õ¦æ¸ü¤J¶µ¥D¹Lµ{¶W¹L3¦¸¡C¤¹³\³Ñ¾l°õ¦æ¦¸¼Æªº­p¼Æ¾¹¨S¦³¦sÀx¦b¼Ò²Õ¯ÅªºÅܼƤ¤¡A¤]¨S¦³¦sÀx¦b¨Ì¿à¸ü¤J¶µªº¦WºÙ¤¤¡A¦Ó¬O¦sÀx¦bÁôÂ꺩R¦WªÅ¶¡ùØ¡C³q¹L±Æ°£¤F¶Ç²Î¤èªkªº¤U¦C¯ÊÂI¡AÁôÂ꺩R¦WªÅ¶¡ªý¤î¤F¥Î¤á¯à°÷¤¤¤î«OÅ@¡C
(1) ¹³©Ò¦³ÅܼƤ@¼Ë¡A¦sÀx¦bVBA¤¤ªº­p¼Æ¾¹ÅܼƯà¦bVBE¤¤¤â°Ê²M°£¡C
(2) ¦P¼Ëªº¤è¦¡¡A¥ô¦ó¥~³¡¹Lµ{³q¹L¦b¸ü¤J¶µªºNames¶°¦X¤¤°j°é¡A³£¥i¥HŪ¨ú¡B­×§ï©M¥i¯à§R°£¸ü¤J¶µ¤¤©Ò¦³ÁôÂ꺩Υ¼ÁôÂ꺤u§@ï¦WºÙ¡C
    ¦ý¬O¡AÁôÂ꺩R¦WªÅ¶¡ÁקK¤F³o¨Ç¦MÀI¡C¥¦¤]¤ñ¨Ï¥Î°ò©óÀô¹Ò¦r¦êªº¹ê¨Ò¡B¼È¦sÀɮשεù¥U¶i¤Jµ¥¤èªk§ó²³æ¡A¦Ó¥BÁôÂ꺩R¦WªÅ¶¡¬O¥Ã¤[ªº¡A¥Î¤á¯àÃö³¬©M­«·s¥´¶}¸Ó¤u§@ï¦ÓµL¶·­«·s³]¸m³o­Ó­p¼Æ¾¹¡C
    ¦b³o­Ó¥N½X¤¤¡A¨ç¼ÆSetHName¡BGetHName©MDelHName¥i¥H³Ð«Ø¡BÀò¨ú©M§R°£ÁôÂ꺦WºÙ¡A¦Ó¤£»Ý­nª½±µ¨Ï¥Î¤¾ªøªºApplication.ExecuteExcel4Macro¤èªk¡C
¥N½X²M³æ¦p¤U¡G
Sub Main()
  Application.EnableCancelKey = xlDisabled
  Dim Count
  Count = GetHName("TswbkCount")
  If IsError(Count) Then
    SetHName "TswbkCount", 3
  ElseIf Count = 1 Then
    MsgBox "Macro disabled. You must restart Excel.", vbInformation
  Else
    SetHName "TswbkCount", Count - 1
  End If
End Sub
Sub SetHName(Name As String, Value)
  Application.ExecuteExcel4Macro _
    "SET.NAME(""" & Name & """," & Value & ")"
End Sub
Function GetHName(Name As String)
  GetHName = Application.ExecuteExcel4Macro(Name)
End Function
Sub DelHName(Name As String)
  Application.ExecuteExcel4Macro "SET.NAME(""" & Name & """)"
End Sub
2

µû¤À¤H¼Æ

    • HUNGCHILIN: ³o½g¼gªº¤£¿ù ´I­ô§Ú们¤W¦¸´ú¸Õªº¬O ...ª÷¿ú + 2
    • Hsieh: ¶}²´¬É¤F¡A¾Ç¨ì·sªF¦èª÷¿ú + 2

¥»©«³Ì«á¥Ñ sklo ©ó 2011-1-18 13:26 ½s¿è

¬Ý¨£³o­Ó¼ÐÃD¡A¥u·Q¨ìindirect¨ç¼Æ¡A¼Ó¥D©ÒÁ¿ªº¤èªk¹ï§Ú¨Ó»¡¤Ó½ÆÂø¡C

TOP

³oºØÀ³¸Ó²¾Âà¨ì "¶i¬ÒÀ³¥Î°Ï"
©ñ¦b "¤@¯ë°Ï" «Ü¦h¤H·|¶Ì²´

TOP

³o­Ó°ÝÃD¤§©Ò¥H©ñ¦b¤@¯ë°Ï°Q½×¡A©Î³\¤j®aı±o«Ü¯Ç´e
¨ä¹ê¡A©w¸q¦WºÙ¦bEXCELªº¨Ï¥Î¤W¡A¥\¯à«D±`­«­n
¥L¹ï¤½¦¡¤Þ¥Î¦³µÛ±K¤£¥i¤ÀªºÃö«Y
©Ò¥H¡A¾Ç²ßEXCEL¹ï©ó©w¸q¦WºÙªº»{ÃѬO¤£¥i©Î¯Êªº
¤@¯ë¨Ï¥ÎªÌ¥u­nÃöª`¨ì¤å³¹¶}ÀY
a.xlsªºSheet1ùØ©w¸q¦³¦WºÙ¡¨name¡¨,¦b¨ä³B©ó¡§¥´¶}¡¨ªºª¬ºA¤U:
1.¦bb.xlsªº³æ®æùؤޥΡG=¡¦a.xls¡¦!name

ª¾¹D¦p¦ó¸ó¬¡­¶Ã¯¤Þ¥Î©w¸q¦WºÙ§Y¥i
µ¥¨ì±z»Ý­n¶i¤@¨B²`¤J¬ã¨s©w¸q¦WºÙµ²ºc®É¡A¦A¦^¨Ó²Ó²Ó«~¨ý¥»¤å
¨º®É±z¤w¸g¨ã³Æ¤@¨Ç°ò¥»·§©À«á¡A±z´Nª¾¹D¥»¤å¬O¦p¦¹ºëªö¤F
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ª©¤j¦n:
§Ú§â¤U­±º|±¼ªº¦r¦ê³s±µ½X¸É¤W"&"
§Æ±æ¹ï¤j®a¦³À°§U~^^
MsgBox CName.Name  & " deleted"

Application.ExecuteExcel4Macro _
    "SET.NAME("""  & Name & """," & Value & ")"

Application.ExecuteExcel4Macro "SET.NAME("""  & Name & """)"
¶}¤ß¾Ç²ß,¾Ç²ß«Ü¶}¤ß

TOP

¬Ý°_¨Ó©w¸q¦WºÙ¦n¹³¬O³Ì°ò¦ªºªF¦è~ÁÂÁÂ¥ýªñ¸Ñ´b

TOP

¬Ý¨Ó±o¦n¦n¾Ç²ß,¯u¬O²`·P¨ü¯q

TOP

¦nÃø³á¡A¯uªº¬Ý¤£À´³á

TOP

«ÜÃø§r..
¦ý§Ú³£·Q¤F¸Ñ¦h¨Ç©w¸q¦WºÙ..
§Úª¾¹D«Ü¦³¥Î.. ¦ý§Ú¤£ª¾¹D¦³¦h¦³¥Î..
¥~­±¤S¾Ç¤£¨ì...
­n«ç¼Ë¤~¥i¥H¦h¾Ç¦³Ãö©w¸q¦WºÙ©O...
><

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD