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

[¤À¨É] VBA»y¥y¶°200¥y (2-1)

[¤À¨É] VBA»y¥y¶°200¥y (2-1)

¥»©«³Ì«á¥Ñ la1975 ©ó 2014-1-22 14:13 ½s¿è

VBA»y¥y¶°200¥y

VBA»y¥y¶°
(²Ä2¿è)

*******************************************************
©w»s¼Ò¶ô¦æ¬°
(101) Err.Clear ¡y²M°£µ{§Ç¹B¦æ¹Lµ{¤¤©Ò¦³ªº¿ù»~
*******************************************************
¤u§@ï
(102) ThisWorkbook.BuiltinDocumentProperties(¡uLast Save Time¡v)
©ÎApplication.Caller.Parent.Parent.BuiltinDocumentProperties(¡uLast Save Time¡v) ¡yªð¦^¤W¦¸«O¦s¤u§@諸¤é´Á©M®É¶¡
(103) ThisWorkbook.BuiltinDocumentProperties("Last Print Date")
©ÎApplication.Caller.Parent.Parent.BuiltinDocumentProperties(¡uLast Print Date¡v) ¡yªð¦^¤W¦¸¥´¦L©Î¹wÄý¤u§@諸¤é´Á©M®É¶¡
(104) Workbooks.Close ¡yÃö³¬©Ò¦³¥´¶}ªº¤u§@ï
(105) ActiveWorkbook.LinkSources(xlExcelLinks)(1) ¡yªð¦^·í«e¤u§@襤ªº²Ä¤@±øÃì±µ
(106) ActiveWorkbook.CodeName
ThisWorkbook.CodeName  ¡yªð¦^¤u§@ï¥N½Xªº¦WºÙ
(107) ActiveWorkbook.FileFormat
ThisWorkbook.FileFormat  ¡yªð¦^·í«e¤u§@ï¤å¥ó®æ¦¡¥N½X
(108) ThisWorkbook.Path
ActiveWorkbook.Path ¡yªð¦^·í«e¤u§@諸¸ô®|(ª`:­Y¤u§@良«O¦s,«h¬°ªÅ)
(109) ThisWorkbook.ReadOnly
   ActiveWorkbook.ReadOnly ¡yªð¦^·í«e¤u§@諸Ū/¼g­È(¬°False)
(110) ThisWorkbook.Saved
ActiveWorkbook.Saved ¡yªð¦^¤u§@諸¦sÀx­È(­Y¤w«O¦s«h¬°False)
(111) Application.Visible = False ¡yÁôÂäu§@ï
   Application.Visible = True ¡yÅã¥Ü¤u§@ï
   ª`:¥i»P¥Î¤áµ¡Åé°t¦X¨Ï¥Î,§Y¦b¥´¶}¤u§@ï®É±N¤u§@ïÁôÂÃ,¥uÅã¥Ü¥Î¤áµ¡Åé.¥i³]¸m±±¨î«ö¶s±±¨î¤u§@ï¥i¨£
*******************************************************
¤u§@ªí
(112) ActiveSheet.Columns("B").Insert ¡y¦bA¦C¥k°¼´¡¤J¦C¡A§Y´¡¤JB¦C
ActiveSheet.Columns("E").Cut
  ActiveSheet.Columns("B").Insert  ¡y¥H¤W¨â¥y±NE¦C¼Æ¾Ú²¾¦ÜB¦C¡A­ìB¦C¤Î¥H«áªº¼Æ¾Ú¬ÛÀ³«á²¾
ActiveSheet.Columns("B").Cut
  ActiveSheet.Columns("E").Insert ¡y¥H¤W¨â¥y±NB¦C¼Æ¾Ú²¾¦ÜD¦C¡A­ìC¦C©MD¦C¼Æ¾Ú¬ÛÀ³¥ª²¾¤@¦C
(113) ActiveSheet.Calculate ¡y­pºâ·í«e¤u§@ªí
(114) ThisWorkbook.Worksheets(¡usheet1¡v).Visible=xlSheetHidden ¡y¥¿±`ÁôÂäu§@ªí¡A¦P¦bExcelµæ³æ¤¤¿ï¾Ü¡u®æ¦¡¡X¡X¤u§@ªí¡X¡XÁôÂáv¾Þ§@¤@¼Ë
ThisWorkbook.Worksheets(¡usheet1¡v).Visible=xlSheetVeryHidden ¡yÁôÂäu§@ªí¡A¤£¯à³q¹L¦bExcelµæ³æ¤¤¿ï¾Ü¡u®æ¦¡¡X¡X¤u§@ªí¡X¡X¨ú®øÁôÂáv¨Ó­«·sÅã¥Ü¤u§@ªí
ThisWorkbook.Worksheets(¡usheet1¡v).Visible=xlSheetVisible ¡yÅã¥Ü³QÁôÂ꺤u§@ªí
(115) ThisWorkbook.Sheets(1).ProtectContents ¡yÀˬd¤u§@ªí¬O§_¨ü¨ì«OÅ@
(116) ThisWorkbook.Worksheets.Add Count:=2, _
Before:=ThisWorkbook.Worksheets(2)
©Î ThisWorkbook.Workshees.Add ThisWorkbook.Worksheets(2), , 2  ¡y¦b²Ä¤G­Ó¤u§@ªí¤§«e²K¥[¨â­Ó·sªº¤u§@ªí
(117) ThisWorkbook.Worksheets(3).Copy ¡y½Æ»s¤@­Ó¤u§@ªí¨ì·sªº¤u§@ï
(118) ThisWorkbook.Worksheets(3).Copy ThisWorkbook.Worksheets(2) ¡y½Æ»s²Ä¤T­Ó¤u§@ªí¨ì²Ä¤G­Ó¤u§@ªí¤§«e
(119) ThisWorkbook.ActiveSheet.Columns.ColumnWidth = 20 ¡y§ïÅܤu§@ªíªº¦C¼e¬°20
  ThisWorkbook.ActiveSheet.Columns.ColumnWidth = _
ThisWorkbook.ActiveSheet.StandardWidth ¡y±N¤u§@ªíªº¦C¼e«ì´_¬°¼Ð·Ç­È
ThisWorkbook.ActiveSheet.Columns(1).ColumnWidth = 20 ¡y§ïÅܤu§@ªí¦C1ªº¼e«×¬°20
(120) ThisWorkbook.ActiveSheet.Rows.RowHeight = 10 ¡y§ïÅܤu§@ªíªº¦æ°ª¬°10
  ThisWorkbook.ActiveSheet.Rows.RowHeight = _
ThisWorkbook.ActiveSheet.StandardHeight ¡y±N¤u§@ªíªº¦æ°ª«ì´_¬°¼Ð·Ç­È
ThisWorkbook.ActiveSheet.Rows(1).RowHeight = 10 ¡y§ïÅܤu§@ªíªº¦æ1ªº°ª«×­È³]¸m¬°10
(121) ThisWorkbook.Worksheets(1).Activate ¡y·í«e¤u§@襤ªº²Ä¤@­Ó¤u§@ªí³Q¿E¬¡
(122) ThisWorkbook.Worksheets("Sheet1").Rows(1).Font.Bold = True ¡y³]¸m¤u§@ªíSheet1¤¤ªº¦æ1¼Æ¾Ú¬°²ÊÅé
(123) ThisWorkbook.Worksheets("Sheet1").Rows(1).Hidden = True ¡y±N¤u§@ªíSheet1¤¤ªº¦æ1ÁôÂÃ
ActiveCell.EntireRow.Hidden = True ¡y±N·í«e¤u§@ªí¤¤¬¡°Ê³æ¤¸®æ©Ò¦bªº¦æÁôÂÃ
µù¡G¦P¼Ë¥i¥Î©ó¦C¡C
(124) ActiveSheet.Range(¡uA:A¡v).EntireColumn.AutoFit ¡y¦Û°Ê½Õ¾ã·í«e¤u§@ªíA¦C¦C¼e
(125) ActiveSheet.Cells.SpecialCells(xlCellTypeConstants,xlTextValues) ¡y¿ï¤¤·í«e¤u§@ªí¤¤±`¶q©M¤å¥»³æ¤¸®æ
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants,xlErrors+xlTextValues) ¡y¿ï¤¤·í«e¤u§@ªí¤¤±`¶q©M¤å¥»¤Î¿ù»~­È³æ¤¸®æ
*******************************************************
¤½¦¡»P¨ç¼Æ
(126) Application.MacroOptions Macro:=¡vSumPro¡v,Category:=4 ¡y±N¦Û©w¸qªºSumPro¨ç¼Æ«ü©wµ¹Excel¤¤ªº¡u²Î­p¨ç¼Æ¡vÃþ§O
(127) Application.MacroOptions Macro:=¡vSumPro¡v, _
Description:=¡vFirst Sum,then Product¡v ¡y¬°¦Û©w¸q¨ç¼ÆSumPro¶i¦æ¤F¥\¯à»¡©ú
(128) Application.WorksheetFunction.CountA(Range(¡uA:A¡v))+1 ¡yÀò¨úA¦Cªº¤U¤@­ÓªÅ³æ¤¸®æ
(129) WorksheetFunction.CountA(Cell.EntireColumn) ¡yªð¦^¸Ó³æ¤¸®æ©Ò¦b¦C«DªÅ³æ¤¸®æªº¼Æ¶q
WorksheetFunction.CountA(Cell.EntireRow) ¡yªð¦^¸Ó³æ¤¸®æ©Ò¦b¦æ«DªÅ³æ¤¸®æªº¼Æ¶q
(130) WorksheetFunction.CountA(Cells) ¡yªð¦^¤u§@ªí¤¤«DªÅ³æ¤¸®æ¼Æ¶q
(131) ActiveSheet.Range(¡uA20:D20¡v).Formula=¡u=Sum(R[-19]C:R[-1]C¡v¡z¹ïA¦C¦ÜD¦C«e19­Ó¼Æ­È¨D©M
*******************************************************
¹Ïªí
(132) ActiveWindow.Visible=False
©Î ActiveChart.Deselect ¡y¨Ï¹Ïªí³B©ó«D¬¡°Êª¬ºA
(133) TypeName(Selection)=¡vChart¡v ¡y­Y¿ï¤¤ªº¬°¹Ïªí¡A«h¸Ó»y¥y¬°¯u¡A§_«h¬°°²
(134) ActiveSheet.ChartObjects.Delete ¡y§R°£¤u§@ªí¤W©Ò¦³ªºChartObject¹ï¶H
  ActiveWorkbook.Charts.Delete ¡y§R°£·í«e¤u§@襤©Ò¦³ªº¹Ïªí¤u§@ªí
*******************************************************
µ¡Åé©M±±¥ó
(135) UserForms.Add(MyForm).Show ¡y²K¥[¥Î¤áµ¡ÅéMyForm¨ÃÅã¥Ü
(136)TextName.SetFocus ¡y³]¸m¤å¥»®ØÀò¨ú¿é¤JµJÂI
(137) SpinButton1.Value=0 ¡y±N¼Æ­È½Õ¸`¶s±±¥óªº­È§ï¬°0
(138) TextBox1.Text=SpinButton1.Value ¡y±N¼Æ­È½Õ¸`¶s±±¥óªº­È½á­Èµ¹¤å¥»®Ø±±¥ó
   SpinButton1.value="/Val(TextBox1.Text)" ¡y±N¤å¥»®Ø±±¥ó­È½áµ¹¼Æ­È½Õ¸`¶s±±¥ó
   CStr(SpinButton1.Value)=TextBox1.Text ¡y¼Æ­È½Õ¸`¶s±±¥ó©M¤å¥»®Ø±±¥ó¬Û¤ñ¸û
(139) UserForm1.Controls.Count ¡yÅã¥Üµ¡ÅéUserForm1¤Wªº±±¥ó¼Æ¥Ø
(140) ListBox1.AddItem ¡uCommand1¡v ¡y¦b¦Cªí®Ø¤¤²K¥[Command1
(141) ListBox1.ListIndex ¡yªð¦^¦Cªí®Ø¤¤±ø¥Øªº­È¡A­Y¬°-1¡A«hªí©ú¥¼¿ï¤¤¥ô¦ó¦Cªí®Ø¤¤ªº±ø¥Ø
(142) RefEdit1.Text ¡yªð¦^¥Nªí³æ¤¸®æ°Ï°ì¦a§}ªº¤å¥»¦r²Å¦ê
   RefEdit1.Text=ActiveWindow.RangeSelection.Address ¡yªì©l¤ÆRefEdit±±¥óÅã¥Ü·í«e©Ò¿ï³æ¤¸®æ°Ï°ì
   Set FirstCell=Range(RefEdit1.Text).Range(¡uA1¡v) ¡y³]¸m¬Y³æ¤¸®æ°Ï°ì¥ª¤W¨¤³æ¤¸®æ
(143) Application.OnTime Now + TimeValue("00:00:15"), "myProcedure" ¡yµ¥«Ý15¬í«á¹B¦æmyProcedure¹Lµ{
(144) ActiveWindow.ScrollColumn=ScrollBarColumns.Value ¡y±Nºu°Ê±ø±±¥óªº­È½á­Èµ¹ActiveWindow¹ï¶HªºScrollColumnÄÝ©Ê
ActiveWindow.ScrollRow=ScrollBarRows.Value ¡y±Nºu°Ê±ø±±¥óªº­È½á­Èµ¹ActiveWindow¹ï¶HªºScrollRowÄÝ©Ê
(145) UserForm1.ListBox1.AddItem Sheets(¡uSheet1¡v).Cells(1,1) ¡y±N³æ¤¸®æA1¤¤ªº¼Æ¾Ú²K¥[¨ì¦Cªí®Ø¤¤
ListBox1.List=Product ¡y±N¤@­Ó¦W¬°Product¼Æ²Õªº­È²K¥[¨ìListBox1¤¤
ListBox1.RowSource=¡vSheet2!SumP¡v ¡y¨Ï¥Î¤u§@ªíSheet2¤¤ªºSumP°Ï°ìªº­È¶ñ¥R¦Cªí®Ø
(146) ListBox1.Selected(0) ¡y¿ï¤¤¦Cªí®Ø¤¤ªº²Ä¤@­Ó±ø¥Ø(µù¡G·í¦Cªí®Ø¤¹³\¤@¦¸¿ï¤¤¦h­Ó±ø¥Ø®É¡A¥²¶·¨Ï¥ÎSelectedÄÝ©Ê)
(147) ListBox1.RemoveItem ListBox1.ListIndex ¡y²¾°£¦Cªí®Ø¤¤¿ï¤¤ªº±ø¥Ø
*******************************************************

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD