ªð¦^¦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Á¨à

·P谢¤À¨É¡A¦¬ÂÃ备¥Î¤¤¡A¤ä«ù±zªº§V¤O¡C

TOP

ªñ´Á¦b¾Ç²ßVBA
·PÁª©¥D¤À¨É
«D±`¹ê¥Î¸ê®Æ

TOP

¦^´_ 11# cchent

      ³o¨Ç¾ã²z«D±`¦³½ì¤]«Ü¹ê¥Î

TOP

VBA«D±`¦³¥Î¸ê®Æ
·PÁª©¥D¤À¨É

TOP

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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD