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

[¤À¨É] ¾Ç²ßExcel VBAªº¤@¨Ç°ò¥»·§©À

[¤À¨É] ¾Ç²ßExcel VBAªº¤@¨Ç°ò¥»·§©À

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-10-30 09:22 ½s¿è

¬°¤F¾Ç²ßExcel¤¤ªº¥¨¶°¡A§Ú­Ì»Ý­n¥ýÁA¸Ñ¥H¤U¤@¨Ç°ò¥»·§©À¡C
¡@¡@1¡B¤u§@ï¡GWorkbooks¡BWorkbook¡BActiveWorkbook¡BThisWorkbook
¡@¡@Workbooks¶°¦X¥]§tExcel¤¤©Ò¦³·í«e¥´¶}ªºExcel¤u§@ï¡A¥ç§Y©Ò¦³¥´¶}ªºExcel¤å¥ó¡FWorkbook¹ïÀ³Workbooks¤¤ªº¦¨­û¡A§Y¨ä¤¤ªºExcelÀÉ¡FActiveWorkbook¥Nªí·í«e³B©ó¬¡°Êª¬ºAªº¤u§@ï¡A§Y·í«eÅã¥ÜªºExcelÀÉ¡FThisWorkbook¥Nªí¨ä¤¤¦³Visual Basic¥N½X¥¿¦b¹B¦æªº¤u§@ï¡C
¡@¡@¦b¨ãÅé¨Ï¥Î¤¤¥i¥ÎWorkbooks(index)¨Ó¤Þ¥ÎWorkbookª«¥ó¡A¨ä¤¤index¬°¤u§@ï¦WºÙ©Î½s¸¹¡F¦pWorkbooks(1)¡BWorkbooks("¦~«×³øªí.xls")¡C¦Ó½s¸¹«ö·Ó³Ð«Ø©Î¥´¶}¤u§@諸¶¶§Ç¨Ó½T©w¡A²Ä¤@­Ó¥´¶}ªº¤u§@ï½s¸¹¬°1¡A²Ä¤G­Ó¥´¶}ªº¤u§@אּ2¡K¡K¡C
¡@¡@2¡B¤u§@ªí¡GWorksheets¡BWorksheet¡BActiveSheet
¡@¡@Worksheets¶°¦X¥]§t¤u§@襤©Ò¦³ªº¤u§@ªí¡A§Y¤@­ÓExcelÀɤ¤ªº©Ò¦³¸ê®Æªí­¶¡F¦ÓWorksheet«h¥Nªí¨ä¤¤ªº¤@­Ó¤u§@ªí¡FActiveSheet¥Nªí·í«e³B©óªº¬¡°Êª¬ºA¤u§@ªí¡A§Y·í«eÅã¥Üªº¤@­Ó¤u§@ªí¡C
¡@¡@¥i¥ÎWorksheets(index)¨Ó¤Þ¥ÎWorksheetª«¥ó¡A¨ä¤¤index¬°¤u§@ªí¦WºÙ©Î¯Á¤Þ¸¹¡F¦pWorksheets(1)¡BWorksheets("²Ä¤@©u«×¸ê®Æ")¡C¤u§@ªí¯Á¤Þ¸¹ªí©ú¸Ó¤u§@ªí¦b¤u§@ªí¼ÐÅÒ¤¤ªº¦ì¸m¡G²Ä¤@­Ó¡]³Ì¥ªÃ䪺¡^¤u§@ªíªº¯Á¤Þ¸¹¬°1¡A³Ì«á¤@­Ó¡]³Ì¥kÃ䪺¡^¬°Worksheets.Count¡C»Ý­nª`·Nªº¬O¡G¦b¨Ï¥Î¹Lµ{¤¤Excel·|¦Û°Ê­«±Æ¤u§@ªí¯Á¤Þ¸¹¡A«O«ù«ö·Ó¨ä¦b¤u§@ªí¼ÐÅÒ¤¤ªº±q¥ª¦Ü¥k±Æ¦C¡A¤u§@ªíªº¯Á¤Þ¸¹»¼¼W¡C¦]¦¹¡A¥Ñ©ó¥i¯à¶i¦æªº¤u§@ªí²K¥[©Î§R°£¡A¤u§@ªí¯Á¤Þ¸¹¤£¤@©w©l²×«O«ù¤£ÅÜ¡C ¡@3¡B¹Ïªí¡GChart ¡BCharts¡BChartObject¡BChartObjects¡BActiveChart
¡@¡@Chart¥Nªí¤u§@襤ªº¹Ïªí¡C¸Ó¹Ïªí¬J¥i¬°´O¤J¦¡¹Ïªí¡]¥]§t¦bChartObject¤¤¡^¡A¤]¥i¬°¤@­Ó¤À¶}ªº¡]³æ¿Wªº¡^¹Ïªí¤u§@ªí¡C
¡@¡@Charts¥Nªí«ü©w¤u§@ï©Î¬¡°Ê¤u§@襤©Ò¦³¹Ïªí¤u§@ªíªº¶°¦X¡A¦ý¤£¥]¬A´O¤J¦¡¦b¤u§@ªí©Î¹ï¸Ü¤è¶ô½s¿èªí¤¤ªº¹Ïªí¡C¨Ï¥ÎCharts(index) ¥i¤Þ¥Î³æ­ÓChart¹Ïªí¡A¨ä¤¤index¬O¸Ó¹Ïªí¤u§@ªíªº¯Á¤Þ¸¹©Î¦WºÙ¡F¦pCharts(1)¡BCharts("¾P°â¹Ïªí")¡C¹Ïªí¤u§@ªíªº¯Á¤Þ¸¹ªí¥Ü¹Ïªí¤u§@ªí¦b¤u§@諸¤u§@ªí¼ÐÅÒÄæ¤Wªº¦ì¸m¡CCharts(1)¬O¤u§@襤²Ä¤@­Ó¡]³Ì¥ªÃ䪺¡^¹Ïªí¤u§@ªí¡FCharts(Charts.Count)¬°³Ì«á¤@­Ó¡]³Ì¥kÃ䪺¡^¹Ïªí¤u§@ªí¡C
¡@¡@ChartObject¥Nªí¤u§@ªí¤¤ªº´O¤J¦¡¹Ïªí¡A¨ä§@¥Î¬O§@¬°Chart¹ï¶Hªº®e¾¹¡C§Q¥ÎChartObject¥i¥H±±¨î¤u§@ªí¤W´O¤J¦¡¹Ïªíªº¥~Æ[©M¤Ø¤o¡C
¡@¡@ChartObjects¥Nªí«ü©wªº¹Ïªí¤u§@ªí¡B¹ï¸Ü¤è¶ô½s¿èªí©Î¤u§@ªí¤W©Ò¦³´O¤J¦¡¹Ïªíªº¶°¦X¡C¥i¥ÑChartObjects(index)¤Þ¥Î³æ­ÓChartObject¡A¨ä¤¤index¬°´O¤J¦¡¹Ïªíªº½s¸¹©Î¦WºÙ¡C¦pWorksheets("Sheet1").ChartObjects(1)¡BWorksheets("sheet1").ChartObjects("chart1")¤À§O¹ïÀ³"Sheet1"¤u§@ªí¤¤ªº²Ä¤@­Ó´O¤J¦¡¹Ïªí¡B¥H¤Î¦W¬°"Chart1"ªº´O¤J¦¡¹Ïªí¡C
¡@¡@ActiveChart¥i¥H¤Þ¥Î¬¡°Êª¬ºA¤Uªº¹Ïªí¡A¤£½×¸Ó¹Ïªí¬O¹Ïªí¤u§@ªí¡A©Î´O¤J¦¡¹Ïªí¡C¦Ó¹ï©ó¹Ïªí¤u§@ªí¬°¬¡°Ê¤u§@ªí®É¡AÁÙ¥i¥H³q¹LActiveSheetÄݩʤޥΤ§¡C
¡@¡@4¡B³æ®æ¡GCells¡BActiveCell¡BRange¡BAreas
¡@¡@Cells(row,column)¥Nªí³æ­Ó³æ®æ¡A¨ä¤¤row¬°¦æ¸¹¡Acolumn¬°¦C¸¹¡C¦p¥i¥H¥ÎCells(1,1)¡BCells(10,4)¨Ó¤Þ¥Î"A1"¡B"D10" ³æ®æ¡CActiveCell¥Nªí¬¡°Ê¤u§@ªíªº¬¡°Ê³æ®æ¡A©Î«ü©w¤u§@ªíªº¬¡°Ê³æ®æ¡C
¡@¡@Range¥Nªí¤u§@ªí¤¤ªº¬Y¤@³æ®æ¡B¬Y¤@¦æ¡B¬Y¤@¦C¡B¬Y¤@¿ï©w°Ï°ì¡]¸Ó¿ï©w°Ï°ì¥i¥]§t¤@­Ó©Î­Y¤z³sÄò³æ®æ°Ï°ì¡^©ÎªÌ¬Y¤@¤Tºû°Ï°ì¡C
¡@¡@¥i¥ÎRange(arg)¨Ó¤Þ¥Î³æ®æ©Î³æ®æ°Ï°ì¡A¨ä¤¤arg¥i¬°³æ®æ¸¹¡B³æ®æ¸¹½d³ò¡B³æ®æ°Ï°ì¦WºÙ¡C¦pRange("A5")¡BRange("A1:H8")¡BRange("Criteria")¡CÁöµM¥i¥ÎRange("A1")ªð¦^³æ®æA1¡A¦ý¥ÎCells§ó¤è«K¡A¦]¬°¦¹®É¥i¥ÎÅܼƫü©w¦æ©M¦C¡C
¡@¥i±NRange»PCellsµ²¦X°_¨Ó¨Ï¥Î¡A¦pRange(Cells(1,1),Cells(10,10))¥Nªí³æ®æ°Ï°ì"A1:J10"¡F¦Óexpression.Cells(row,column)ªð¦^³æ®æ°Ï°ì¤¤ªº¤@³¡¤À¡A¨ä¤¤expression¬Oªð¦^Rangeªº¹Bºâ¦¡¡Arow©Mcolumn¬°¬Û¹ï©ó¸Ó°Ï°ìªº¥ª¤W¨¤°¾²¾¶q¡C¦p¥ÑRange("C5:C10").Cells(1,1)¤Þ¥Î³æ®æC5¡C
¡@¡@Areas¬°¿ï©w°Ï°ì¤ºªº³sÄò³æ®æ¶ôªº¶°¦X¡A¨ä¦¨­û¬ORange¹ï¶H¡C¦Ó¨ä¤¤ªº¨C­ÓRangeª«¥ó¥Nªí¿ï©w°Ï°ì¤º»P¨ä¥L³¡¤À¬Û¤ÀÂ÷ªº¤@­Ó³sÄò³æ®æ¶ô¡C¬Y¨Ç¾Þ§@¤£¯à¦b¿ï©w°Ï°ì¤ºªº¦h­Ó³æ®æ¶ô¤W¦P®É°õ¦æ¡F¥²¶·¦b¿ï©w°Ï°ì¤ºªº³æ®æ¶ô¼ÆAreas.Count¤W°j°é¡A¹ï¨C­Ó³æ¿Wªº³æ®æ¶ô¤À§O°õ¦æ¸Ó¾Þ§@¡C¦¹®É¡A¥i¥ÎAreas(index)±q¶°¦X¤¤ªð¦^³æ­ÓRangeª«¥ó¡A¨ä¤¤index¬°³æ®æ¶ô½s¸¹¡F¦pAreas(1)¡C
¡@¡@5¡B ¦æ»P¦C¡GRows¡BColumns¡BRow¡BColumn
¡@¡@Rows¡BColumns¤À§O¥Nªí¬¡°Ê¤u§@ªí¡B³æ®æ°Ï°ì½d³òRange¡B«ü©w¤u§@ªí¤¤ªº©Ò¦³¦æ¼Æ¡B¦C¼Æ¡C¹ï©ó¤@­Ó¦h¿ï³æ®æ°Ï°ì½d³òRangeªºRows¡BColumns¡A¥uªð¦^¸Ó½d³ò¤¤²Ä¤@­Ó°Ï°ìªº¦æ¼Æ¡B¦C¼Æ¡C¨Ò¦p¡A¦pªGRangeª«¥ó¦³¨â­Ó°Ï°ì¡]areas¡^A1:B2©MC3:D4¡ARows.Countªð¦^2¦Ó¤£¬O4¡C
¡@¡@¥i³q¹LRows(¦æ¸¹)¡BColumns(¦C¸¹)¨Ó¤Þ¥Î¬ÛÀ³ªº¦æ»P¦C¡F¦pRows(3)¡BColumns(4)¤À§O¹ïÀ³²Ä¤T¦æ¡BD¦C¡C
§Q¥ÎRows¡BColumn¥i¥HÀò±o°Ï°ì¤¤²Ä¤@¶ôªº²Ä¤@¦æ¦æ¸¹¡B²Ä¤@¦C¦C¸¹¡A©Ò±o­È§¡¥H¤Q¶i¦ì¼Æ¦rªí¥Ü¡C

¦^´_ 1# Hsieh


    ¤µ¤Ñ­è¶i¤J¦¹½×¾Â, ¤wµn¿ý¬°·|­ûo ¦b¤@¨Ç©«¤¤±`¬Ý¨ì±zºë±m¤Î¸Ô²Óªº¦^ÂÐ, ¤×¨ä¦¹½g±N¤@¨Ç excel vba ªº°ò¥»Æ[©À§@¤@¾ã²z¤Î»¡©ú, ¬Û«H«Ü¦h¤H·|¨ü¯q¨}¦ho
    ¦b¦¹¦³¤@­Ó¨p¤Hªº°ÝÃD·Q½Ð±Ð, ¤£ª¾±z¬O§_¯àµ¹¤©À°¦£?
    §Ú¦bÀÉ®× a.xlsm ¤¤ a-sheet ¤w«Ø¥ß¤@­Ó vba macro ¬° a-macro,
       1. ·í§Ú¦b¥t¤@­ÓÀÉ®× b.xlsm ¤¤ªº b-macro ¤¤­n¨Ï¥Î¨ì a.xlsm ¤¤ªº a-macro ®É­n¦p¦ó¶}±Ò, °õ¦æ¤ÎÃö³¬?
       2. ­Y§Ú¤w¸g±N a-macro ¥Ñ a.xlsm ¤¤¶×¥X¦¨¤@­Ó a.bas ÀÉ®×®É, §Ú¦p¦ó¥Ñ b-macro ©I¥s¶}±Ò, °õ¦æ¤ÎÃö³¬?
    ½Ðµ¹¤©¸Ñµª, ¤£¬Æ·P¿Eo Thanks!
James

TOP

¦^´_ 2# James
  1. Sub bb()
  2. Workbooks.Open "E:\aa.xls" '¶}±Ò¬¡­¶Ã¯
  3. Application.Run "aa.xls!aaa" '°õ¦æ¥¨¶°
  4. End Sub
  5. Sub kk()
  6. With ActiveWorkbook.VBProject.VBComponents.Import("E:\a.bas") '¶×¤J
  7. Application.Run "aaa" '°õ¦æ
  8. Application.VBE.ActiveVBProject.VBComponents.Remove Application.VBE.ActiveVBProject.VBComponents(.Name) '§R°£
  9. End With
  10. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¨Ï¥Îvba¤]¦³´X­Ó¤ë
¦ý¬O¥\¤OÁÙ¤£°÷²`
¦Ó¥B¬Ý¨ì²³®a°ª¤âªºµo¤å
¯u¬OÅå¹Ä³s³s

«Ü¦n©_¤j®a¬O«ç»ò¾Çªº
·|°Oµ{¦¡½X¶Ü?
§Ú³£¬O±N¤@¨Çµ{¦¡½X½Æ»s°_¨Ó
¤ñ¸û½ÆÂøªº´N¦b±q¸Ì­±´M§ä
²³æªº´Nª½±µ¥´¥X¨Ó
³o¼Ëªº¤è¦¡¬O¹ïªº¶Ü?
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

·PÁªO¥D´£¨Ñ¸Ñ¨M¤è®×, Åý§Ú¹ï VBA ¦bµ{¦¡¶¡ªº©I¥s¦³§ó²`ªº¤F¸Ño
¤é¬Q¤]¬Ý¤F§Aªº©« "Ãö©óDDE°ÊºA³sµ²°ÝÃD", ¸Õ¤F¤@¤U¨F¤j¤jªº°ÝÃD, ÁÙÆZ¦nª±ªº, ©Î³\¥H«á¥i°Ñ¦Ò¨Ï¥Îo
James

TOP

½Ð°Ýª©¥D¡G1.­Y¨Ï¥ÎEXCEL DDE+VBA ¬ö¿ý´Á³f¸ê®ÆÀ³¸Ó­n¦³­þ¨Ç±ø¥ó?  
               2.­Y¨Ï¥Î (KGI¥þ²y³qI3_Betaª©)+excel2007ªºDDE¼Æ¾Ú¨C5¤ÀÄÁ¦Û°Ê°O¿ý¤@µ§¸ê®ÆÀ³¸Ó¦p¦ó¼g?(§Ú¨S¦³VBAªº°ò¦)¤£¦n·N«ä¡C  ÁÂÁ¡C
·s¤â

TOP

wearnksªº°ÝÃD¤]¬O§Ú·Qª¾¹Dªº ¡I

TOP

¬Ý¤Fª©¥Dªº¤À¨É, Åý§Ú¹ï©ó excel vba ¦³§ó²`¤Jªº¤F¸Ñ,§Ú¯uªº»Ý­n¦h¥[¥Î¨Ç¹ê°È¨Ó¥[²`¦L¶H¤F!!^^

TOP

¹ï©óH¤j sub kk()¦³ÂI°ÝÃD
§Ú·Ó¤@¦¸¿ì
¦ýcompiler»¡remove¨º¦æ¤Þ¼Æ¤£¬°¿ï¾Ü©Ê(not optional)
°±¤Fmacro
¤£ª¾¦ó¸Ñ?

TOP

[img]

Uploaded with ImageShack.us[/img]
¦³ÂI°ÝÃD,³o¬O3¼ÓH¤jªºµ{¦¡,¥[¤Ö¤Ö­×§ï
¦³¨â­Óexcel file,¤@¦W¬°a.xls,¤º¦³¤@­Ó¥ssub a()ªºµ{¦¡,°õ¦æ¤@­Ó²³æªºmsgbox°T®§,§Ú§â¥¦¶×¥X¦Üa.bas,¥´ºâ¥Ñb.xls¤¤ªºsub c()¥s¥X¸Óa.bas,¥i¬Oµ{¦¡Ãa¤F. ¤£ª¾¦p¦ó¬O¦n
¤£¤ÓÀ´¬ÛÃö§Þ¥©,§Æ±æ«ü¥¿
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD