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

¹F¤HÁ`µ²ªºVBAºë¨åªº""¤K""Ãþ¦Ê±ø»y¥yª©

¹F¤HÁ`µ²ªºVBAºë¨åªº""¤K""Ãþ¦Ê±ø»y¥yª©

¤@¡G©w»s¼Ò¶ô¦æ¬°

(1) Option Explicit '±j¨î¹ï¼Ò¶ô¤º©Ò¦³Åܶq¶i¦æÁn©ú
Option Private Module '¼Ð°O¼Ò¶ô¬°¨p¦³¡A¶È¹ï¦P¤@¤uµ{¤¤¨ä¥¦¼Ò¶ô¦³¥Î¡A¦b§»¹ï¸Ü®Ø¤¤¤£Åã¥Ü
       Option Compare Text '¦r²Å¦ê¤£°Ï¤À¤j¤p¼g
       Option Base 1 '«ü©w¼Æ²Õªº²Ä¤@­Ó¤U¼Ð¬°1
(2) On Error Resume Next '©¿²¤¿ù»~Ä~Äò°õ¦æVBA¥N½X,ÁקK¥X²{¿ù»~®ø®§
(3) On Error GoTo ErrorHandler '·í¿ù»~µo¥Í®É¸õÂà¨ì¹Lµ{¤¤ªº¬Y­Ó¦ì¸m
(4) On Error GoTo 0 '«ì´_¥¿±`ªº¿ù»~´£¥Ü
(5) Application.DisplayAlerts=False '¦bµ{§Ç°õ¦æ¹Lµ{¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
(6) Application.ScreenUpdating=False 'Ãö³¬«Ì¹õ¨ê·s
   Application.ScreenUpdating=True '¥´¶}«Ì¹õ¨ê·s
(7) Application.Enable.CancelKey=xlDisabled '¸T¥ÎCtrl+Break¤¤¤î§»¹B¦æªº¥\¯à

¤G¡G¾Þ§@¤u§@ï

(8) Workbooks.Add() '³Ð«Ø¤@­Ó·sªº¤u§@ï
(9) Workbooks(¡ubook1.xls¡v).Activate '¿E¬¡¦W¬°book1ªº¤u§@ï
(10) ThisWorkbook.Save '«O¦s¤u§@ï
(11) ThisWorkbook.close 'Ãö³¬·í«e¤u§@ï
(12) ActiveWorkbook.Sheets.Count 'Àò¨ú¬¡°Ê¤u§@Á¡¤¤¤u§@ªí¼Æ
(13) ActiveWorkbook.name  'ªð¦^¬¡°Ê¤u§@Á¡ªº¦WºÙ
(14) ThisWorkbook.Name ¡yªð¦^·í«e¤u§@ï¦WºÙ
    ThisWorkbook.FullName ¡yªð¦^·í«e¤u§@ï¸ô®|©M¦WºÙ
(15) ActiveWindow.EnableResize=False ¡y¸T¤î½Õ¾ã¬¡°Ê¤u§@諸¤j¤p
(16) Application.Window.Arrange xlArrangeStyleTiled ¡y±N¤u§@ï¥H¥­¾Q¤è¦¡±Æ¦C
(17) ActiveWorkbook.WindowState=xlMaximized ¡y±N·í«e¤u§@ï³Ì¤j¤Æ

¤T¡G¾Þ§@¤u§@ªí

(18) ActiveSheet.UsedRange.Rows.Count ¡y·í«e¤u§@ªí¤¤¤w¨Ï¥Îªº¦æ¼Æ
(19) Rows.Count ¡yÀò¨ú¤u§@ªíªº¦æ¼Æ(µù¡G¦Ò¼{¦V«e­Ý®e©Ê)
(20) Sheets(Sheet1).Name= ¡uSum¡v '±NSheet1©R¦W¬°Sum
(21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) '²K¥[¤@­Ó·s¤u§@ªí¦b²Ä¤@¤u§@ªí«e
(22) ActiveSheet.Move After:=ActiveWorkbook. _
Sheets(ActiveWorkbook.Sheets.Count) '±N·í«e¤u§@ªí²¾¦Ü¤u§@ªíªº³Ì«á
(23) Worksheets(Array(¡usheet1¡v,¡vsheet2¡v)).Select '¦P®É¿ï¾Ü¤u§@ªí1©M¤u§@ªí2
(24) Sheets(¡usheet1¡v).Delete©Î Sheets(1).Delete '§R°£¤u§@ªí1
(25) ActiveWorkbook.Sheets(i).Name 'Àò¨ú¤u§@ªíiªº¦WºÙ
(26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '¤Á´«¤u§@ªí¤¤ªººô®æ½uÅã¥Ü¡A³oºØ¤èªk¤]¥i¥H¥Î¦b¨ä¥¦¤è­±¶i¦æ¬Û¤¬¤Á´«¡A§Y¬Û·í©ó¶}Ãö«ö¶s
(27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings  ¡y¤Á´«¤u§@ªí¤¤ªº¦æ¦CÃä®ØÅã¥Ü
(28) ActiveSheet.UsedRange.FormatConditions.Delete ¡y§R°£·í«e¤u§@ªí¤¤©Ò¦³ªº±ø¥ó®æ¦¡
(29) Cells.Hyperlinks.Delete ¡y¨ú®ø·í«e¤u§@ªí©Ò¦³¶WÃì±µ
(30) ActiveSheet.PageSetup.Orientation=xlLandscape
©ÎActiveSheet.PageSetup.Orientation=2 '±N­¶­±³]¸m§ó§ï¬°¾î¦V
(31) ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName ¡y¦b­¶­±³]¸mªºªí§À¤¤¿é¤J¤å¥ó¸ô®|
ActiveSheet.PageSetup.LeftFooter=Application.UserName ¡y±N¥Î¤á¦W©ñ¸m¦b¬¡°Ê¤u§@ªíªº­¶¸}

¥|¡G³æ¤¸®æ/³æ¤¸®æ°Ï°ì

(32) ActiveCell.CurrentRegion.Select
©ÎRange(ActiveCell.End(xlUp),ActiveCell.End(xlDown)).Select
'¿ï¾Ü·í«e¬¡°Ê³æ¤¸®æ©Ò¥]§tªº½d³ò¡A¤W¤U¥ª¥kµLªÅ¦æ
(33) Cells.Select ¡y¿ï©w·í«e¤u§@ªíªº©Ò¦³³æ¤¸®æ
(34) Range(¡uA1¡v).ClearContents '²M°£¬¡°Ê¤u§@ªí¤W³æ¤¸®æA1¤¤ªº¤º®e
Selection.ClearContents '²M°£¿ï©w°Ï°ì¤º®e
Range(¡uA1:D4¡v).Clear '¹ý©³²M°£A1¦ÜD4³æ¤¸®æ°Ï°ìªº¤º®e¡A¥]¬A®æ¦¡
(35) Cells.Clear '²M°£¤u§@ªí¤¤©Ò¦³³æ¤¸®æªº¤º®e
(36) ActiveCell.Offset(1,0).Select '¬¡°Ê³æ¤¸®æ¤U²¾¤@¦æ¡A¦P²z¡A¥i¤U²¾¤@¦C
(37) Range(¡uA1¡v).Offset(ColumnOffset:=1)©ÎRange(¡uA1¡v).Offset(,1) ¡y°¾²¾¤@¦C
Range(¡uA1¡v).Offset(Rowoffset:=-1)©ÎRange(¡uA1¡v).Offset(-1) ¡y¦V¤W°¾²¾¤@¦æ
(38) Range(¡uA1¡v).Copy Range(¡uB1¡v) '½Æ»s³æ¤¸®æA1¡AÖ߶K¨ì³æ¤¸®æB1¤¤
Range(¡uA1:D8¡v).Copy Range(¡uF1¡v) '±N³æ¤¸®æ°Ï°ì½Æ»s¨ì³æ¤¸®æF1¶}©lªº°Ï°ì¤¤
Range(¡uA1:D8¡v).Cut Range(¡uF1¡v) '°Å¤Á³æ¤¸®æ°Ï°ìA1¦ÜD8¡A½Æ»s¨ì³æ¤¸®æF1¶}©lªº°Ï°ì¤¤
Range(¡uA1¡v).CurrentRegion.Copy Sheets(¡uSheet2¡v).Range(¡uA1¡v) '½Æ»s¥]§tA1ªº³æ¤¸®æ°Ï°ì¨ì¤u§@ªí2¤¤¥HA1°_©lªº³æ¤¸®æ°Ï°ì¤¤
µù¡GCurrentRegionÄݩʵ¥»ù©ó©w¦ì©R¥O¡A¥Ñ¤@­Ó¯x§Î³æ¤¸®æ¶ô²Õ¦¨¡A©P³ò¬O¤@­Ó©Î¦h­ÓªÅ¦æ©Î¦C
(39) ActiveWindow.RangeSelection.Value=XX '±N­ÈXX¿é¤J¨ì©Ò¿ï³æ¤¸®æ°Ï°ì¤¤
(40) ActiveWindow.RangeSelection.Count '¬¡°Êµ¡¤f¤¤¿ï¾Üªº³æ¤¸®æ¼Æ
(41) Selection.Count '·í«e¿ï¤¤°Ï°ìªº³æ¤¸®æ¼Æ
(42) GetAddress=Replace(Hyperlinkcell.Hyperlinks(1).Address,mailto:,¡v¡v) ¡yªð¦^³æ¤¸®æ¤¤¶W¯ÅÃì±µªº¦a§}¨Ã½á­È
(43) TextColor=Range(¡uA1¡v).Font.ColorIndex ¡yÀˬd³æ¤¸®æA1ªº¤å¥»ÃC¦â¨Ãªð¦^ÃC¦â¯Á¤Þ
Range(¡uA1¡v).Interior.ColorIndex ¡yÀò¨ú³æ¤¸®æA1­I´º¦â
(44) cells.count ¡yªð¦^·í«e¤u§@ªíªº³æ¤¸®æ¼Æ
(45) Selection.Range(¡uE4¡v).Select ¡y¿E¬¡·í«e¬¡°Ê³æ¤¸®æ¤U¤è3¦æ¡A¦V¥k4¦Cªº³æ¤¸®æ
(46) Cells.Item(5,¡vC¡v) ¡y¤Þ³æ¤¸®æC5
    Cells.Item(5,3) ¡y¤Þ³æ¤¸®æC5
(47) Range(¡uA1¡v).Offset(RowOffset:=4,ColumnOffset:=5)
©Î Range(¡uA1¡v).Offset(4,5) ¡y«ü©w³æ¤¸®æF5
(48) Range(¡uB3¡v).Resize(RowSize:=11,ColumnSize:=3)
Rnage(¡uB3¡v).Resize(11,3) ¡y³Ð«ØB3¡GD13°Ï°ì
(49) Range(¡uData¡v).Resize(,2) ¡y±NData°Ï°ìÂX¥R2¦C
(50) Union(Range(¡uData1¡v),Range(¡uData2¡v)) ¡y±NData1©MData2°Ï°ì³s±µ
(51) Intersect(Range(¡uData1¡v),Range(¡uData2¡v)) ¡yªð¦^Data1©MData2°Ï°ìªº¥æ¤e°Ï°ì
(52) Range(¡uData¡v).Count ¡y³æ¤¸®æ°Ï°ìData¤¤ªº³æ¤¸®æ¼Æ
    Range(¡uData¡v). Columns.Count ¡y³æ¤¸®æ°Ï°ìData¤¤ªº¦C¼Æ
    Range(¡uData¡v). Rows.Count ¡y³æ¤¸®æ°Ï°ìData¤¤ªº¦æ¼Æ
(53) Selection.Columns.Count ¡y·í«e¿ï¤¤ªº³æ¤¸®æ°Ï°ì¤¤ªº¦C¼Æ
Selection.Rows.Count ¡y·í«e¿ï¤¤ªº³æ¤¸®æ°Ï°ì¤¤ªº¦æ¼Æ
(54) Selection.Areas.Count ¡y¿ï¤¤ªº³æ¤¸®æ°Ï°ì©Ò¥]§tªº°Ï°ì¼Æ
(55) ActiveSheet.UsedRange.Row  ¡yÀò¨ú³æ¤¸®æ°Ï°ì¤¤¨Ï¥Îªº²Ä¤@¦æªº¦æ¸¹
(56) Rng.Column ¡yÀò¨ú³æ¤¸®æ°Ï°ìRng¥ª¤W¨¤³æ¤¸®æ©Ò¦b¦C½s¸¹
(57) ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions) ¡y¦b¬¡°Ê¤u§@ªí¤¤ªð¦^©Ò¦³²Å¦X±ø¥ó®æ¦¡³]¸mªº°Ï°ì
(58) Range(¡uA1¡v).AutoFilter Field:=3,VisibleDropDown:=False ¡yÃö³¬¥Ñ©ó°õ¦æ¦Û°Ê¿z¿ï©R¥O²£¥Íªº²Ä3­Ó¦r¬qªº¤U©Ô¦Cªí¦WºÙ
(59) Range(¡uA1¡GC3¡v).Name=¡ucomputer¡v ¡y©R¦WA1¡GC3°Ï°ì¬°computer
©ÎRange(¡uD1¡GE6¡v).Name=¡uSheet1!book¡v ¡y©R¦W§½³¡Åܶq¡A§YSheet1¤W°Ï°ìD1¡GE6¬°book
©Î Names(¡ucomputer¡v).Name=¡urobot¡v ¡y±N°Ï°ìcomputer­«©R¦W¬°robot
(60) Names(¡ubook¡v).Delete ¡y§R°£¦WºÙ
(61) Names.Add Name:=¡uContentList¡v¡A_
RefersTo:=¡u=OFFSET(Sheet1!A2,0,0,COUNTA(Sheet2!$A:$A))¡v ¡y°ÊºA©R¦W¦C
(62) Names.Add Name:=¡uCompany¡v,RefersTo:=¡uCompanyCar¡v ¡y©R¦W¦r²Å¦êCompanyCar
(63) Names.Add Name:=¡uTotal¡v,RefersTo:=123456 ¡y±N¼Æ¦r123456©R¦W¬°Total¡Cª`·N¼Æ¦r¤£¯à¥[¤Þ¸¹¡A§_«h´N¬O©R¦W¦r²Å¦ê¤F¡C
(64) Names.Add Name:=¡uMyArray¡v,RefersTo:=ArrayNum ¡y±N¼Æ²ÕArrayNum©R¦W¬°MyArray¡C
(65) Names.Add Name:=¡uProduceNum¡v,RefersTo:=¡u=$B$1¡v,Visible:=False ¡y±N¦WºÙÁôÂÃ
(66) ActiveWorkbook.Names(¡uCom¡v).Name ¡yªð¦^¦WºÙ¦r²Å¦ê
¯qÁ¨à

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-14 14:51 ½s¿è

¤­¡G¤½¦¡»P¨ç¼Æ

(67) Application.WorksheetFunction.IsNumber(¡uA1¡v) '¨Ï¥Î¤u§@ªí¨ç¼ÆÀˬdA1³æ¤¸®æ¤¤ªº¼Æ¾Ú¬O§_¬°¼Æ¦r
(68) Range(¡uA:A¡v).Find(Application.WorksheetFunction.Max(Range(¡uA:A¡v))).Activate
'¿E¬¡³æ¤¸®æ°Ï°ìA¦C¤¤³Ì¤j­Èªº³æ¤¸®æ
(69) Cells(8,8).FormulaArray=¡u=SUM(R2C[-1]:R[-1]C[-1]*R2C:R[-1]C)¡v ¡y¦b³æ¤¸®æ¤¤¿é¤J¼Æ²Õ¤½¦¡¡Cª`·N¥²¶·¨Ï¥ÎR1C1¼Ë¦¡ªºªí¹F¦¡
¹Ïªí
(70) ActiveSheet.ChartObjects.Count 'Àò¨ú·í«e¤u§@ªí¤¤¹Ïªíªº­Ó¼Æ
(71) ActiveSheet.ChartObjects(¡uChart1¡v).Select ¡y¿ï¤¤·í«e¤u§@ªí¤¤¹ÏªíChart1
(72) ActiveSheet.ChartObjects(¡uChart1¡v).Activate
    ActiveChart.ChartArea.Select ¡y¿ï¤¤·í«e¹Ïªí°Ï°ì
(73) WorkSheets(¡uSheet1¡v).ChartObjects(¡uChart2¡v).Chart. _
ChartArea.Interior.ColorIndex=2 ¡y§ó§ï¤u§@ªí¤¤¹Ïªíªº¹Ïªí°ÏªºÃC¦â
(74) Sheets(¡uChart2¡v).ChartArea.Interior.ColorIndex=2 ¡y§ó§ï¹Ïªí¤u§@ªí¤¤¹Ïªí°ÏªºÃC¦â
(75) Charts.Add ¡y²K¥[·sªº¹Ïªí¤u§@ªí
(76) ActiveChart.SetSourceData Source:=Sheets(¡uSheet1¡v).Range(¡uA1:D5¡v), _
PlotBy:=xlColumns ¡y«ü©w¹Ïªí¼Æ¾Ú·½¨Ã«ö¦C±Æ¦C
(77) ActiveChart.Location Where:=xlLocationAsNewSheet ¡y·s¹Ïªí§@¬°·s¹Ïªí¤u§@ªí
(78) ActiveChart.PlotArea.Interior.ColorIndex=xlNone ¡y±Nø¹Ï°ÏÃC¦âÅܬ°¥Õ¦â
(79) WorkSheets(¡uSheet1¡v).ChartObjects(1).Chart. _
Export FileName:=¡uC¡GMyChart.gif¡v,FilterName:=¡uGIF¡v ¡y±N¹Ïªí1¾É¥X¨ìC½L¤W¨Ã©R¦W¬°MyChart.gif

¤»¡Gµ¡Å骺¾Þ§@©R¥O

(80) MsgBox ¡uHello!¡v '®ø®§®Ø¤¤Åã¥Ü®ø®§Hello
(81) Ans=MsgBox(¡uContinue?¡v,vbYesNo) '¦b®ø®§®Ø¤¤ÂIÀ»¡u¬O¡v«ö¶s¡A«hAns­È¬°vbYes¡FÂIÀ»¡u§_¡v«ö¶s¡A«hAns­È¬°vbNo¡C
If MsgBox(¡uContinue?¡v,vbYesNo)vbYes Then Exit Sub 'ªð¦^­È¤£¬°¡u¬O¡v¡A«h°h¥X
(82) Config=vbYesNo+vbQuestion+vbDefaultButton2 '¨Ï¥Î±`¶qªº²Õ¦X¡A½á­È²ÕConfigÅܶq¡A¨Ã³]¸m²Ä¤G­Ó«ö¶s¬°¯Ê¬Ù«ö¶s
(83) MsgBox ¡uThis is the first line.¡v & vbNewLine & ¡uSecond line.¡v '¦b®ø®§®Ø¤¤±j¨î´«¦æ¡A¥i¥ÎvbCrLf¥N´ÀvbNewLine¡C
(84) MsgBox "the average is :"&Format(Application.WorksheetFunction.Average(Selection),"#,##0.00"),vbInformation, "selection count average" & Chr(13) 'À³¥Î¤u§@ªí¨ç¼Æªð

¦^©Ò¿ï°Ï°ìªº¥­§¡­È¨Ã«ö«ü©w®æ¦¡Åã¥Ü
(85) Userform1.Show ¡yÅã¥Ü¥Î¤áµ¡Åé
(86) Load Userform1 ¡y¥[¸ü¤@­Ó¥Î¤áµ¡Åé,¦ý¸Óµ¡Åé³B©óÁôÂꬺA
(87) Userform1.Hide ¡yÁôÂåΤᵡÅé
(88) Unload Userform1 ©Î Unload Me ¡y¨ø¸ü¥Î¤áµ¡Åé
(89) (¹Ï¹³±±¥ó).Picture=LoadPicture(¡u¹Ï¹³¸ô®|¡v) ¡y¦b¥Î¤áµ¡Å餤Åã¥Ü¹Ï§Î
(90) UserForm1.Show 0 ©Î UserForm1.Show vbModeless ¡y±Nµ¡Åé³]¸m¬°µL¼Ò¦¡ª¬ºA
(91) Me.Height=Int(0.88*ActiveWindow.Height) ¡yµ¡Åé°ª«×¬°·í«e¬¡°Êµ¡¤f°ª«×ªº0.88
   Me.Width=Int(0.88*ActiveWindow.Width) ¡yµ¡Åé¼e«×¬°·í«e¬¡°Êµ¡¤f°ª«×ªº0.88
¨Æ¥ó
(92) Application.EnableEvents=False '¸T¥Î©Ò¦³¨Æ¥ó
        Application.EnableEvents=True '±Ò¥Î©Ò¦³¨Æ¥ó
    µù¡G¤£¾A¥Î©ó¥Î¤áµ¡Åé±±¥óIJµoªº¨Æ¥ó

¤C¡G¹ï¶H¾Þ§@

(93) Set ExcelSheet = CreateObject("Excel.Sheet") ¡y³Ð«Ø¤@­ÓExcel¤u§@ªí¹ï¶H
ExcelSheet.Application.Visible = True '³]¸m Application ¹ï¶H¨Ï Excel ¥i¨£
ExcelSheet.Application.Cells(1, 1).Value = "Data" '¦bªí®æªº²Ä¤@­Ó³æ¤¸¤¤¿é¤J¤å¥»
ExcelSheet.SaveAs "C:\TEST.XLS" '±N¸Óªí®æ«O¦s¨ìC:\test.xls ¥Ø¿ý
ExcelSheet.Application.Quit 'Ãö³¬ Excel
Set ExcelSheet = Nothing 'ÄÀ©ñ¸Ó¹ï¶HÅܶq
(94) ¡yÁn©ú¨Ã³Ð«Ø¤@­ÓExcel¹ï¶H¤Þ¥Î
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
(95) ¡y³Ð«Ø¨Ã¶Ç»¼¤@­Ó Excel.Application ¹ï¶Hªº¤Þ¥Î
Call MySub (CreateObject("Excel.Application"))
(96) Set d = CreateObject(Scripting.Dictionary) ¡y³Ð«Ø¤@­Ó Dictionary ¹ï¶HÅܶq
(97) d.Add "a", "Athens" '¬°¹ï¶HÅܶq²K¥[ÃöÁä¦r©M±ø¥Ø

¤K¡G¨ä¥L

(98) Application.OnKey ¡u^I¡v,¡vmacro¡v '³]¸mCtrl+IÁ䬰macro¹Lµ{ªº§Ö±¶Áä
(99) Application.CutCopyMode=False ¡y°h¥X°Å¤Á/½Æ»s¼Ò¦¡
(100) Application.Volatile True 'µL½×¦ó®É¤u§@ªí¤¤¥ô·N³æ¤¸®æ­«·s­pºâ¡A³£·|±j¨î­pºâ¸Ó¨ç¼Æ
Application.Volatile False '¥u¦³¦b¸Ó¨ç¼Æªº¤@­Ó©Î¦h­Ó°Ñ¼Æµo¥Í§ïÅܮɡA¤~·|­«·s­pºâ¸Ó¨ç¼Æ
¯qÁ¨à

TOP

Sheets(¡usheet1¡v).Delete
¡u¡v<---- ­Ó²Å¸¹¦p¦ó¿é¤J?? ¬O¦P " " ¶Ü??³o­Ó¸ê®Æ¦n¹³¬O¹ï©¤ªº??

TOP

¦^´_ 4# chen_cook
¬O§r¡A¨Ì¤¤µØ¥ÁªG¾Ëªk³W©w¬O¤j³°¦a°Ï
ª½±µ"Âà(Ác)¶K"¹L¨Ó¡A
¥¦­Ìªº»yªk¸ò§Ú­Ì¦³¤£¦P  sheets("¤u§@ªí1") sheet1  ==>"¤u§@ªí1"
¦]¬°VBA¤¤§Ú¹J¨ì§xÃø¡A¦Aºô¤WSEARCHı±o¤£¿ù¶K¹L¨Óªº
¯qÁ¨à

TOP

¥»©«³Ì«á¥Ñ oobird ©ó 2012-4-15 11:26 ½s¿è

§âµü·J§ï¤@¤U

¤@¡G©w»s¼Ò²Õ¦æ¬°

(1) Option Explicit '±j¨î¹ï¼Ò²Õ¤º©Ò¦³Åܼƶi¦æÁn©ú
Option Private Module '¼Ð°O¼Ò²Õ¬°¨p¦³¡A¶È¹ï¦P¤@±M®×¤¤¨ä¥¦¼Ò²Õ¦³¥Î¡A¦b¥¨¶°¹ï¸Ü®Ø¤¤¤£Åã¥Ü
       Option Compare Text '¦r²Å¦ê¤£°Ï¤À¤j¤p¼g
       Option Base 1 '«ü©w°}¦Cªº²Ä¤@­Ó¤U¼Ð¬°1
(2) On Error Resume Next '©¿²¤¿ù»~Ä~Äò°õ¦CVBA¥N½X,ÁקK¥X²{¿ù»~«H®§
(3) On Error GoTo ErrorHandler '·í¿ù»~µo¥Í®É¸õÂà¨ìµ{§Ç¤¤ªº¬Y­Ó¦ì¸m
(4) On Error GoTo 0 '«ì´_¥¿±`ªº¿ù»~´£¥Ü
(5) Application.DisplayAlerts=False '¦bµ{§Ç°õ¦Cµ{§Ç¤¤¨Ï¥X²{ªºÄµ§i®Ø¤£Åã¥Ü
(6) Application.ScreenUpdating=False 'Ãö³¬¼ü¹õ§ó·s
   Application.ScreenUpdating=True '¥´¶}¼ü¹õ§ó·s
(7) Application.Enable.CancelKey=xlDisabled '¸T¥ÎCtrl+Break¤¤¤î¥¨¶°¹B¦æªº¥\¯à

¤G¡G¾Þ§@¬¡­¶Ã¯

(8) Workbooks.Add() '³Ð«Ø¤@­Ó·sªº¬¡­¶Ã¯
(9) Workbooks("book1.xls").Activate '¿ï¤¤¦W¬°book1ªº¬¡­¶Ã¯
(10) ThisWorkbook.Save '«O¦s¬¡­¶Ã¯
(11) ThisWorkbook.close 'Ãö³¬·í«e¬¡­¶Ã¯
(12) ActiveWorkbook.Sheets.Count 'Àò¨ú¬¡°Ê¬¡­¶Ã¯¤¤¤u§@ªí¼Æ
(13) ActiveWorkbook.name  '¨ú±o¬¡°Ê¬¡­¶Ã¯ªº¦WºÙ
(14) ThisWorkbook.Name '¨ú±o·í«e¬¡­¶Ã¯¦WºÙ
    ThisWorkbook.FullName '¨ú±o·í«e¬¡­¶Ã¯¸ô®|©M¦WºÙ
(15) ActiveWindow.EnableResize=False '¸T¤î½Õ¾ã¬¡°Ê¬¡­¶Ã¯ªº¤j¤p
(16) Application.Window.Arrange xlArrangeStyleTiled '±N¬¡­¶Ã¯¥H¨Ã±Æ¤è¦¡±ÆÄæ
(17) ActiveWorkbook.WindowState=xlMaximized '±N·í«e¬¡­¶Ã¯³Ì¤j¤Æ

¤T¡G¾Þ§@¤u§@ªí

(18) ActiveSheet.UsedRange.Rows.Count '·í«e¤u§@ªí¤¤¤w¨Ï¥Îªº¦C¼Æ
(19) Rows.Count 'Àò¨ú¤u§@ªíªºÄæ¼Æ(µù¡G¦Ò¼{¦V«e­Ý®e©Ê)
(20) Sheets(Sheet1).Name= "Sum" '±NSheet1©R¦W¬°Sum
(21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) '²K¥[¤@­Ó·s¤u§@ªí¦b²Ä¤@¤u§@ªí«e
(22) ActiveSheet.Move After:=ActiveWorkbook. _
Sheets(ActiveWorkbook.Sheets.Count) '±N·í«e¤u§@ªí²¾¦Ü¤u§@ªíªº³Ì«á
(23) Worksheets(Array("sheet1","sheet2")).Select '¦P®É¿ï¾Ü¤u§@ªí1©M¤u§@ªí2
(24) Sheets("sheet1").Delete©Î Sheets(1).Delete '§R°£¤u§@ªí1
(25) ActiveWorkbook.Sheets(i).Name 'Àò¨ú¤u§@ªíiªº¦WºÙ
(26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '¤Á´«¤u§@ªí¤¤ªººô®æ½uÅã¥Ü¡A³oºØ¤èªk¤]¥i¥H¥Î¦b¨ä¥¦¤è­±¶i¦C¬Û¤¬¤Á´«¡A§Y¬Û·í©ó¶}Ãö«ö¶s
(27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings  '¤Á´«¤u§@ªí¤¤ªºÄæ¦CÃä®ØÅã¥Ü
(28) ActiveSheet.UsedRange.FormatConditions.Delete '§R°£·í«e¤u§@ªí¤¤©Ò¦³ªº±ø¥ó®æ¦¡
(29) Cells.Hyperlinks.Delete '¨ú®ø·í«e¤u§@ªí©Ò¦³¶W³sµ²
(30) ActiveSheet.PageSetup.Orientation=xlLandscape
©ÎActiveSheet.PageSetup.Orientation=2 '±N­¶­±³]¸m§ó§ï¬°¾î¦V
(31) ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName '¦b­¶­±³]¸mªºªí§À¤¤¿é¤J¤å¥ó¸ô®|
ActiveSheet.PageSetup.LeftFooter=Application.UserName '±N¥Î¤á¦W©ñ¸m¦b¬¡°Ê¤u§@ªíªº­¶§À

¥|¡GÀx¦s®æ/Àx¦s®æ°Ï°ì

(32) ActiveCell.CurrentRegion.Select
©ÎRange(ActiveCell.End(xlUp),ActiveCell.End(xlDown)).Select
¿ï¾Ü·í«e¬¡°ÊÀx¦s®æ©Ò¥]§tªº½d³ò¡A¤W¤U¥ª¥kµLªÅ¦C
(33) Cells.Select '¿ï©w·í«e¤u§@ªíªº©Ò¦³Àx¦s®æ
(34) Range("A1").ClearContents '²M°£¬¡°Ê¤u§@ªí¤WÀx¦s®æA1¤¤ªº¤º®e
Selection.ClearContents '²M°£¿ï©w°Ï°ì¤º®e
Range("A1:D4").Clear '¹ý©³²M°£A1¦ÜD4Àx¦s®æ°Ï°ìªº¤º®e¡A¥]¬A®æ¦¡
(35) Cells.Clear '²M°£¤u§@ªí¤¤©Ò¦³Àx¦s®æªº¤º®e
(36) ActiveCell.Offset(1,0).Select '¬¡°ÊÀx¦s®æ¤U²¾¤@¦C¡A¦P²z¡A¥i¤U²¾¤@Äæ
(37) Range("A1").Offset(ColumnOffset:=1)©ÎRange("A1").Offset(,1) '°¾²¾¤@Äæ
Range("A1").Offset(Rowoffset:=-1)©ÎRange("A1").Offset(-1) '¦V¤W°¾²¾¤@¦C
(38) Range("A1").Copy Range("B1") '½Æ»sÀx¦s®æA1¡AÖ߶K¨ìÀx¦s®æB1¤¤
Range("A1:D8").Copy Range("F1") '±NÀx¦s®æ°Ï°ì½Æ»s¨ìÀx¦s®æF1¶}©lªº°Ï°ì¤¤
Range("A1:D8").Cut Range("F1") '°Å¤ÁÀx¦s®æ°Ï°ìA1¦ÜD8¡A½Æ»s¨ìÀx¦s®æF1¶}©lªº°Ï°ì¤¤
Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1") '½Æ»s¥]§tA1ªºÀx¦s®æ°Ï°ì¨ì¤u§@ªí2¤¤¥HA1°_©lªºÀx¦s®æ°Ï°ì¤¤
µù¡GCurrentRegionÄݩʬ۹ï©ó"½s¿è~¨ì"©R¥O¡A¥Ñ¤@­Ó¯x§ÎÀx¦s®æ¶ô²Õ¦¨¡A©P³ò¬O¤@­Ó©Î¦h­ÓªÅ¦C©ÎÄæ
(39) ActiveWindow.RangeSelection.Value=XX '±N­ÈXX¿é¤J¨ì©Ò¿ïÀx¦s®æ°Ï°ì¤¤
(40) ActiveWindow.RangeSelection.Count '¬¡°Êµ¡¤f¤¤¿ï¾ÜªºÀx¦s®æ¼Æ
(41) Selection.Count '·í«e¿ï¤¤°Ï°ìªºÀx¦s®æ¼Æ
(42) GetAddress=Replace(Hyperlinkcell.Hyperlinks(1).Address,mailto:,"") '¨ú±oÀx¦s®æ¤¤¶W¯ÅÃì±µªº¦a§}¨Ã½á­È
(43) TextColor=Range("A1").Font.ColorIndex 'ÀˬdÀx¦s®æA1ªº¤å¥»ÃC¦â¨Ã¨ú±oÃC¦â¯Á¤Þ
Range("A1").Interior.ColorIndex 'Àò¨úÀx¦s®æA1­I´º¦â
(44) cells.count '¨ú±o·í«e¤u§@ªíªºÀx¦s®æ¼Æ
(45) Selection.Range("E4").Select '¿ï¤¤·í«e¬¡°ÊÀx¦s®æ¤U¤è3¦C¡A¦V¥k4Ä檺Àx¦s®æ
(46) Cells.Item(5,"C") '¤ÞÀx¦s®æC5
    Cells.Item(5,3) '¤ÞÀx¦s®æC5
(47) Range("A1").Offset(RowOffset:=4,ColumnOffset:=5)
©Î Range("A1").Offset(4,5) '«ü©wÀx¦s®æF5
(48) Range("B3").Resize(RowSize:=11,ColumnSize:=3)
Rnage("B3").Resize(11,3) '³Ð«ØB3¡GD13°Ï°ì
(49) Range("Data").Resize(,2) '±NData°Ï°ìÂX¥R2Äæ
(50) Union(Range("Data1"),Range("Data2")) '±NData1©MData2°Ï°ì³s±µ
(51) Intersect(Range("Data1"),Range("Data2")) '¨ú±oData1©MData2°Ï°ìªº¥æ¤e°Ï°ì
(52) Range("Data").Count 'Àx¦s®æ°Ï°ìData¤¤ªºÀx¦s®æ¼Æ
    Range("Data"). Columns.Count 'Àx¦s®æ°Ï°ìData¤¤ªºÄæ¼Æ
    Range("Data"). Rows.Count 'Àx¦s®æ°Ï°ìData¤¤ªº¦C¼Æ
(53) Selection.Columns.Count '·í«e¿ï¤¤ªºÀx¦s®æ°Ï°ì¤¤ªºÄæ¼Æ
Selection.Rows.Count '·í«e¿ï¤¤ªºÀx¦s®æ°Ï°ì¤¤ªº¦C¼Æ
(54) Selection.Areas.Count '¿ï¤¤ªºÀx¦s®æ°Ï°ì©Ò¥]§tªº°Ï°ì¼Æ
(55) ActiveSheet.UsedRange.Row  'Àò¨úÀx¦s®æ°Ï°ì¤¤¨Ï¥Îªº²Ä¤@¦Cªº¦C¸¹
(56) Rng.Column 'Àò¨úÀx¦s®æ°Ï°ìRng¥ª¤W¨¤Àx¦s®æ©Ò¦bÄæ½s¸¹
(57) ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions) '¦b¬¡°Ê¤u§@ªí¤¤¨ú±o©Ò¦³²Å¦X±ø¥ó®æ¦¡³]¸mªº°Ï°ì
(58) Range("A1").AutoFilter Field:=3,VisibleDropDown:=False 'Ãö³¬¥Ñ©ó°õ¦C¦Û°Ê¿z¿ï©R¥O²£¥Íªº²Ä3­Ó¦r¬qªº¤U©ÔÄæªí¦WºÙ
(59) Range("A1¡GC3").Name="computer" '©R¦WA1¡GC3°Ï°ì¬°computer
©ÎRange("D1¡GE6").Name="Sheet1!book" '©R¦W§½³¡ÅܼơA§YSheet1¤W°Ï°ìD1¡GE6¬°book
©Î Names("computer").Name="robot" '±N°Ï°ìcomputer­«©R¦W¬°robot
(60) Names("book").Delete '§R°£¦WºÙ
(61) Names.Add Name:="ContentList"¡A_
RefersTo:="=OFFSET(Sheet1!A2,0,0,COUNTA(Sheet2!$A:$A))" '°ÊºA©R¦WÄæ
(62) Names.Add Name:="Company",RefersTo:="CompanyCar" '©R¦W¦r²Å¦êCompanyCar
(63) Names.Add Name:="Total",RefersTo:=123456 '±N¼Æ¦r123456©R¦W¬°Total¡Cª`·N¼Æ¦r¤£¯à¥[¤Þ¸¹¡A§_«h´N¬O©R¦W¦r²Å¦ê¤F¡C
(64) Names.Add Name:="MyArray",RefersTo:=ArrayNum '±N°}¦CArrayNum©R¦W¬°MyArray¡C
(65) Names.Add Name:="ProduceNum",RefersTo:="=$B$1",Visible:=False '±N¦WºÙÁôÂÃ
(66) ActiveWorkbook.Names("Com").Name '¨ú±o¦WºÙ¦r²Å¦ê


¤­¡G¤½¦¡»P¨ç¼Æ

(67) Application.WorksheetFunction.IsNumber("A1") '¨Ï¥Î¤u§@ªí¨ç¼ÆÀˬdA1Àx¦s®æ¤¤ªº¸ê®Æ¬O§_¬°¼Æ¦r
(68) Range("A:A").Find(Application.WorksheetFunction.Max(Range("A:A"))).Activate
¿ï¤¤Àx¦s®æ°Ï°ìAÄ椤³Ì¤j­ÈªºÀx¦s®æ
(69) Cells(8,8).FormulaArray="=SUM(R2C[-1]:R[-1]C[-1]*R2C:R[-1]C)" '¦bÀx¦s®æ¤¤¿é¤J°}¦C¤½¦¡¡Cª`·N¥²¶·¨Ï¥ÎR1C1¼Ë¦¡ªºªí¹F¦¡
¹Ïªí
(70) ActiveSheet.ChartObjects.Count 'Àò¨ú·í«e¤u§@ªí¤¤¹Ïªíªº­Ó¼Æ
(71) ActiveSheet.ChartObjects("Chart1").Select '¿ï¤¤·í«e¤u§@ªí¤¤¹ÏªíChart1
(72) ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.ChartArea.Select '¿ï¤¤·í«e¹Ïªí°Ï°ì
(73) WorkSheets("Sheet1").ChartObjects("Chart2").Chart. _
ChartArea.Interior.ColorIndex=2 '§ó§ï¤u§@ªí¤¤¹Ïªíªº¹Ïªí°ÏªºÃC¦â
(74) Sheets("Chart2").ChartArea.Interior.ColorIndex=2 '§ó§ï¹Ïªí¤u§@ªí¤¤¹Ïªí°ÏªºÃC¦â
(75) Charts.Add '²K¥[·sªº¹Ïªí¤u§@ªí
(76) ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), _
PlotBy:=xlColumns '«ü©w¹Ïªí¸ê®Æ·½¨Ã«öÄæ±ÆÄæ
(77) ActiveChart.Location Where:=xlLocationAsNewSheet '·s¹Ïªí§@¬°·s¹Ïªí¤u§@ªí
(78) ActiveChart.PlotArea.Interior.ColorIndex=xlNone '±Nø¹Ï°ÏÃC¦âÅܬ°¥Õ¦â
(79) WorkSheets("Sheet1").ChartObjects(1).Chart. _
Export FileName:="C¡GMyChart.gif",FilterName:="GIF" '±N¹Ïªí1¾É¥X¨ìCºÐ¤W¨Ã©R¦W¬°MyChart.gif

¤»¡Gµ¡Å骺¾Þ§@©R¥O

(80) MsgBox "Hello!" '«H®§®Ø¤¤Åã¥Ü«H®§Hello
(81) Ans=MsgBox("Continue?",vbYesNo) '¦b«H®§®Ø¤¤ÂIÀ»"¬O"«ö¶s¡A«hAns­È¬°vbYes¡FÂIÀ»"§_"«ö¶s¡A«hAns­È¬°vbNo¡C
If MsgBox("Continue?",vbYesNo)vbYes Then Exit Sub '¨ú±o­È¤£¬°"¬O"¡A«h°h¥X
(82) Config=vbYesNo+vbQuestion+vbDefaultButton2 '¨Ï¥Î±`¶qªº²Õ¦X¡A½á­È²ÕConfigÅܼơA¨Ã³]¸m²Ä¤G­Ó«ö¶s¬°¹w³]«ö¶s
(83) MsgBox "This is the first line." & vbNewLine & "Second line." '¦b«H®§®Ø¤¤±j¨î´«¦C¡A¥i¥ÎvbCrLf¥N´ÀvbNewLine¡C
(84) MsgBox "the average is :"&Format(Application.WorksheetFunction.Average(Selection),"#,##0.00"),vbInformation, "selection count average" & Chr(13) 'À³¥Î¤u§@ªí¨ç¼Æªð

¦^©Ò¿ï°Ï°ìªº¥­§¡­È¨Ã«ö«ü©w®æ¦¡Åã¥Ü
(85) Userform1.Show 'Åã¥Ü¥Î¤áµ¡Åé
(86) Load Userform1 '¥[¸ü¤@­Ó¥Î¤áµ¡Åé,¦ý¸Óµ¡Åé³B©óÁôÂꬺA
(87) Userform1.Hide 'ÁôÂåΤᵡÅé
(88) Unload Userform1 ©Î Unload Me '¨ø¸ü¥Î¤áµ¡Åé
(89) (¹Ï¹³±±¥ó).Picture=LoadPicture("¹Ï¹³¸ô®|") '¦b¥Î¤áµ¡Å餤Åã¥Ü¹Ï§Î
(90) UserForm1.Show 0 ©Î UserForm1.Show vbModeless '±Nµ¡Åé³]¸m¬°¤£¿W¥e¼Ò¦¡ª¬ºA
(91) Me.Height=Int(0.88*ActiveWindow.Height) 'µ¡Åé°ª«×¬°·í«e¬¡°Êµ¡¤f°ª«×ªº0.88
   Me.Width=Int(0.88*ActiveWindow.Width) 'µ¡Åé¼e«×¬°·í«e¬¡°Êµ¡¤f°ª«×ªº0.88
¨Æ¥ó
(92) Application.EnableEvents=False '¸T¥Î©Ò¦³¨Æ¥ó
        Application.EnableEvents=True '±Ò¥Î©Ò¦³¨Æ¥ó
    µù¡G¤£¾A¥Î©ó¥Î¤áµ¡Åé±±¥óIJµoªº¨Æ¥ó

¤C¡Gª«¥ó¾Þ§@

(93) Set ExcelSheet = CreateObject("Excel.Sheet") '³Ð«Ø¤@­ÓExcel¤u§@ªíª«¥ó
ExcelSheet.Application.Visible = True '³]¸m Application ª«¥ó¨Ï Excel ¥i¨£
ExcelSheet.Application.Cells(1, 1).Value = "Data" '¦bªí®æªº²Ä¤@­Ó³æ¤¸¤¤¿é¤J¤å¥»
ExcelSheet.SaveAs "C:\TEST.XLS" '±N¸Óªí®æ«O¦s¨ìC:\test.xls ¥Ø¿ý
ExcelSheet.Application.Quit 'Ãö³¬ Excel
Set ExcelSheet = Nothing 'ÄÀ©ñ¸Óª«¥óÅܼÆ
(94) 'Án©ú¨Ã³Ð«Ø¤@­ÓExcelª«¥ó¤Þ¥Î
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
(95) '³Ð«Ø¨Ã¶Ç»¼¤@­Ó Excel.Application ª«¥óªº¤Þ¥Î
Call MySub (CreateObject("Excel.Application"))
(96) Set d = CreateObject(Scripting.Dictionary) '³Ð«Ø¤@­Ó Dictionary ª«¥óÅܼÆ
(97) d.Add "a", "Athens" '¬°ª«¥óÅܼƲK¥[ÃöÁä¦r©M±ø¥Ø

¤K¡G¨ä¥L

(98) Application.OnKey "^I","macro" '³]¸mCtrl+IÁ䬰macroµ{§Çªº§Ö±¶Áä
(99) Application.CutCopyMode=False '°h¥X°Å¤Á/½Æ»s¼Ò¦¡
(100) Application.Volatile True 'µL½×¦ó®É¤u§@ªí¤¤¥ô·NÀx¦s®æ­«·s­pºâ¡A³£·|±j¨î­pºâ¸Ó¨ç¼Æ
Application.Volatile False '¥u¦³¦b¸Ó¨ç¼Æªº¤@­Ó©Î¦h­Ó¤Þ¼Æµo¥Í§ïÅܮɡA¤~·|­«·s­pºâ¸Ó¨ç¼Æ

TOP

VBA »y¥y¶° 400 ¥y.pdf (439.05 KB)

TOP

ÁÂÁª©¥D­Ìªº¤À¨É

TOP

¾Ç²ßVBA«D±`¦³¥Î¸ê®Æ
·PÁ¤À¨É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 6# c_c_lai «z!³o­Ó¸ê®Æ«D±`¦n¥Î,¤è«K¬d¸ß,ÁÂÁ¤À¨É~~
peter460191

TOP

µ¹§A«ö­ÓÆg¡A«Ü¹ê¥Î!!

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD