Excel中各种VBA写法.docx
《Excel中各种VBA写法.docx》由会员分享，可在线阅读，更多相关《Excel中各种VBA写法.docx（28页珍藏版）》请在冰点文库上搜索。
Excel中各种VBA写法
Excel中各种VBA写法
工作簿对象事件一览表
当工作簿更改、工作簿中的任何工作表更改、加载宏更改或数据透视表更改时，将引发工作簿事件。
工作簿上的事件在默认情况下是可用的。
若要查看工作簿的事件过程，请用鼠标右键单击处于还原状态或最小化状态的工作簿窗口标题栏，再单击快捷菜单上的“查看代码”。
在“过程”下拉列表框选择事件名称。
事件说明
Activate激活工作薄时
AddinInstall当工作簿作为加载宏安装时
AddinUninstall工作簿作为加载宏卸载时
BeforeClose关闭工作薄前
BeforePrint打印工作薄（或其中任何容）之前
BeforeSave保存工作薄前
Deactivate工作簿从活动状态转为非活动状态时
NewSheet在工作簿中新建工作表时
Open打开工作簿时
PivotTableCloseConnection在数据透视表关闭与其数据源的连接之后
PivotTableOpenConnection在数据透视表打开与其数据源的连接之后
SheetActivate激活任何一表时
SheetBeforeDoubleClick双击任何工作表时
SheetBeforeRightClick鼠标右键单击任一工作表时
SheetCalculate工作表重新计算时
SheetChange更改工作表中的单元格时
SheetDeactivate任一工作表由活动状态转为非活动状态时
SheetFollowHyperlink单击MicrosoftExcel中的任意超时
SheetPivotTableUpdate数据透视表的工作表更新之后
SheetSelectionChange工作簿中的数据透视表更新之后
WindowActivate工作簿的窗口激活时
WindowDeactivate工作簿的窗口变为非活动状态时
WindowResize工作簿窗口调整大小时
工作表事件一览表
工作表上的事件在默认情况下是可用的。
若要查看工作表的事件过程，请用鼠标右键单击工作表标签，再单击快捷菜单上的“查看代码”。
在“过程”下拉列表框选择事件名称。
事件说明
Activate激活工作表时
Deactivate工作表从活动状态转为非活动状态时
BeforeDoubleClick双击工作表前
BeforeRightClick右键单击工作表时
Calculate对工作表进行重新计算之后
Change更改工作表中的单元格，或外部引起单元格时
FollowHyperlink单击工作表上的任意超时
PivotTableUpdate在工作簿中的数据透视表更新之后
SelectionChange工作表上的选定区域发生改变时
再贴一堆代码
例为设置密码窗口
（1）
IfApplication.InputBox（"请输入密码：
"）=1234Then
[A1]=1'密码正确时执行
Else:
MsgBox"密码错误，即将退出！
"'此行与第2行共同设置密码
EndIf
本示例为设置密码窗口
（1）
X=MsgBox（"是否真的要结帐？
",vbYesNo）
IfX=vbYesThen
Close
本示例为设置工作表密码
ActiveSheet.ProtectPassword:
=641112'保护工作表并设置密码
ActiveSheet.UnprotectPassword:
=641112'撤消工作表保护并取消密码
'本示例关闭除正在运行本示例的工作簿以外的其他所有工作簿，并保存其更改容
。
ForEachwInWorkbooks
Ifw.NameThisWorkbook.NameThen
w.CloseSaveChanges:
=True
EndIf
Nextw
'每次打开工作簿时，本示例都最大化MicrosoftExcel窗口。
Application.WindowState=xlMaximized
'本示例显示活动工作表的名称。
MsgBox"Thenameoftheactivesheetis"&ActiveSheet.Name
'本示例保存当前活动工作簿的副本。
ActiveWorkbook.SaveCopyAs"C:
\TEMP\XXXX.XLS"
'下述过程激活工作簿中的第四工作表。
Sheets（4）.Activate
'下述过程激活工作簿中的第1工作表。
Worksheets
（1）.Activate
'本示例通过将Saved属性设为True来关闭包含本段代码的工作簿，并放弃对该
工作簿的任何更改。
ThisWorkbook.Saved=True
ThisWorkbook.Close
'本示例对自动重新计算功能进行设置，使MicrosoftExcel不对第一工作表自
动进行重新计算。
Worksheets
（1）.EnableCalculation=False
'下述过程打开C盘上名为MyFolder的文件夹中的MyBook.xls工作簿。
Workbooks.Open（"C:
\MyFolder\MyBook.xls"）
'本示例显示活动工作簿中工作表sheet1上单元格A1中的值。
MsgBoxWorksheets（"Sheet1"）.Range（"A1"）.Value
本示例显示活动工作簿中每个工作表的名称
ForEachwsInWorksheets
MsgBoxws.Name
Nextws
本示例向活动工作簿添加新工作表,并设置该工作表的名称?
SetNewSheet=Worksheets.Add
NewSheet.Name="currentBudget"
本示例将新建的工作表移到工作簿的末尾
'PrivateSubWorkbook_NewSheet（ByValShAsObject）
Sh.MoveAfter:
=Sheets（Sheets.Count）
EndSub
本示例将新建工作表移到工作簿的末尾
'PrivateSubApp_WorkbookNewSheet（ByValWbAsWorkbook,_
ByValShAsObject）
Sh.MoveAfter:
=Wb.Sheets（Wb.Sheets.Count）
EndSub
本示例新建一工作表，然后在第一列中列出活动工作簿中的所有工作表的名称。
SetNewSheet=Sheets.Add（Type:
=xlWorksheet）
Fori=1ToSheets.Count
NewSheet.Cells（i,1）.Value=Sheets（i）.Name
Nexti
本示例将第十行移到窗口的最上面?
Worksheets（"Sheet1"）.Activate
ActiveWindow.ScrollRow=10
当计算工作簿中的任何工作表时，本示例对第一工作表的A1:
A100区域进行排序
。
'PrivateSubWorkbook_SheetCalculate（ByValShAsObject）
WithWorksheets
（1）
.Range（"a1:
a100"）.SortKey1:
=.Range（"a1"）
EndWith
EndSub
本示例显示工作表Sheet1的打印预览。
Worksheets（"Sheet1"）.PrintPreview
本示例保存当前活动工作簿?
ActiveWorkbook.Save
本示例保存所有打开的工作簿，然后关闭MicrosoftExcel。
ForEachwInApplication.Workbooks
w.Save
Nextw
Application.Quit
下例在活动工作簿的第一工作表前面添加两新的工作表?
Worksheets.AddCount:
=2,Before:
=Sheets
（1）
本示例设置15秒后运行my_Procedure过程，从现在开始计时。
Application.OnTimeNow+TimeValue（"00:
00:
15"）,"my_Procedure"
本示例设置my_Procedure在下午5点开始运行。
Application.OnTimeTimeValue（"17:
00:
00"）,"my_Procedure"
本示例撤消前一个示例对OnTime的设置。
Application.OnTimeEarliestTime:
=TimeValue（"17:
00:
00"）,_
Procedure:
="my_Procedure",Schedule:
=False
每当工作表重新计算时，本示例就调整A列到F列的宽度。
'PrivateSubWorksheet_Calculate（）
Columns（"A:
F"）.AutoFit
EndSub
本示例使活动工作簿中的计算仅使用显示的数字精度。
ActiveWorkbook.PrecisionAsDisplayed=True
本示例将工作表Sheet1上的A1:
G37区域剪下，并放入剪贴板。
Worksheets（"Sheet1"）.Range（"A1:
G37"）.Cut
Calculate方法
计算所有打开的工作簿、工作簿中的一特定的工作表或者工作表中指定区域的单元
格，如下表所示：
'要计算'依照本示例
所有打开的工作簿'Application.Calculate（或只是Calculate
）
指定工作表'计算指定工作表Sheet1Worksheets
（"Sheet1"）.Calculate
指定区域'Worksheets
（1）.Rows
（2）.Calculate
本示例对自动重新计算功能进行设置，使MicrosoftExcel不对第一工作表自动
进行重新计算。
Worksheets
（1）.EnableCalculation=False
本示例计算Sheet1已用区域中A列、B列和C列的公式。
Worksheets（"Sheet1"）.UsedRange.Columns（"A:
C"）.Calculate
本示例更新当前活动工作簿中的所有?
ActiveWorkbook.UpdateLinkName:
=ActiveWorkbook.LinkSources
本示例设置第一工作表的滚动区域?
Worksheets
（1）.ScrollArea="a1:
f10"
本示例新建一个工作簿，提示用户输入文件名，然后保存该工作簿。
SetNewBook=Workbooks.Add
Do
fName=Application.GetSaveAsFilename
LoopUntilfNameFalse
NewBook.SaveAsFilename:
=fName
本示例打开Analysis.xls工作簿，然后运行Auto_Open宏。
Workbooks.Open"ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacrosxlAutoOpen
本示例对活动工作簿运行Auto_Close宏，然后关闭该工作簿。
WithActiveWorkbook
.RunAutoMacrosxlAutoClose
.Close
EndWith
在本示例中，MicrosoftExcel向用户显示活动工作簿的路径和文件名称。
'SubUseCanonical（）
Displaythefullpathtouser.
MsgBoxActiveWorkbook.FullNameURLEncoded
EndSub
本示例显示当前工作簿的路径及文件名（假定尚未保存此工作簿）。
MsgBoxActiveWorkbook.FullName
本示例关闭Book1.xls，并放弃所有对此工作簿的更改。
Workbooks（"BOOK1.XLS"）.CloseSaveChanges:
=False
本示例关闭所有打开的工作簿。
如果某个打开的工作簿有改变，MicrosoftExcel
将显示询问是否保存更改的对话框和相应提示。
Workbooks.Close
本示例在打印之前对当前活动工作簿的所有工作表重新计算?
'PrivateSubWorkbook_BeforePrint（CancelAsBoolean）
ForEachwkInWorksheets
wk.Calculate
Next
EndSub
本示例对查询表一中的第一列数据进行汇总，并在数据区域下方显示第一列数据的总
和。
Setc1=Sheets（"sheet1"）.QueryTables
（1）.ResultRange.Columns
（1）
c1.Name="Column1"
c1.End（xlDown）.Offset（2,0）.Formula="=sum（Column1）"
本示例取消活动工作簿中的所有更改?
ActiveWorkbook.RejectAllChanges
本示例在商业问题中使用规划求解函数，以使总利润达到最大值。
SolverSave函数
将当前问题保存到活动工作表上的某一区域。
Worksheets（"Sheet1"）.Activate
SolverReset
SolverOptionsPrecision:
=0.001
SolverOKSetCell:
=Range（"TotalProfit"）,_
MaxMinVal:
=1,_
ByChange:
=Range（"C4:
E6"）
SolverAddCellRef:
=Range（"F4:
F6"）,_
Relation:
=1,_
FormulaText:
=100
SolverAddCellRef:
=Range（"C4:
E6"）,_
Relation:
=3,_
FormulaText:
=0
SolverAddCellRef:
=Range（"C4:
E6"）,_
Relation:
=4
SolverSolveUserFinish:
=False
SolverSaveSaveArea:
=Range（"A33"）
本示例隐藏Chart1、Chart3和Chart5。
Charts（Array（"Chart1","Chart3","Chart5"））.Visible=False
当激活工作表时，本示例对A1:
A10区域进行排序。
'PrivateSubWorksheet_Activate（）
Range（"a1:
a10"）.SortKey1:
=Range（"a1"）,order:
=xlAscending
EndSub
本示例更改MicrosoftExcel。
ActiveWorkbook.ChangeLink"c:
\excel\book1.xls",_
"c:
\excel\book2.xls",xlExcelLinks
本示例启用受保护的工作表上的自动筛选箭头?
ActiveSheet.EnableAutoFilter=True
ActiveSheet.Protectcontents:
=True,userInterfaceOnly:
=True
本示例将活动工作簿设为只读?
ActiveWorkbook.ChangeFileAccessMode:
=xlReadOnly
本示例使共享工作簿每三分钟自动更新一次?
ActiveWorkbook.AutoUpdateFrequency=3
下述Sub过程清除活动工作簿中Sheet1上的所有单元格的容。
'SubClearSheet（）
Worksheets（"Sheet1"）.Cells.ClearContents
EndSub
本示例对所有工作簿都关闭滚动条?
Application.DisplayScrollBars=False
如果具有密码保护的工作簿的文件属性没有加密，则本示例设置指定工作簿的密码加
密选项。
'SubSetPasswordOptions（）
WithActiveWorkbook
If.PasswordEncryptionProvider"MicrosoftRSASChannel
CryptographicProvider"Then
.SetPasswordEncryptionOptions_
PasswordEncryptionProvider:
="MicrosoftRSASChannel
CryptographicProvider",_
PasswordEncryptionAlgorithm:
="RC4",_
PasswordEncryptionKeyLength:
=56,_
PasswordEncryptionFileProperties:
=True
EndIf
EndWith
EndSub
在本示例中，如果活动工作簿不能进行写保护，那么MicrosoftExcel设置字符串
密码以作为活动工作簿的写密码。
'SubUseWritePassword（）
DimstrPasswordAsString
strPassword="secret"
'Setpasswordtoastringifallowed.
IfActiveWorkbook.WriteReserved=FalseThen
ActiveWorkbook.WritePassword=strPassword
EndIf
EndSub
在本示例中，MicrosoftExcel打开名为Password.xls的工作簿，设置它的密码
，然后关闭该工作簿。
本示例假定名为Password.xls的文件位于C:
\驱动器上。
'SubUsePassword（）
DimwkbOneAsWorkbook
SetwkbOne=Application.Workbooks.Open（"C:
\Password.xls"）
wkbOne.Password="secret"
wkbOne.Close
'注意Password属性可读并返回“********”。
EndSub
本示例将Book1.xls的当前窗口更改为显示公式。
Workbooks（"BOOK1.XLS"）.Worksheets（"Sheet1"）.Activate
ActiveWindow.DisplayFormulas=True
'本示例接受活动工作簿中的所有更改?
ActiveWorkbook.AcceptAllChanges
本示例显示活动工作簿的路径和名称
SubUseCanonical（）
MsgBox'消息框
[b7]=ActiveWorkbook.FullName'当前工作簿
[b8]=ActiveWorkbook.FullNameURLEncoded'活动工作簿
EndSub
本示例显示MicrosoftExcel启动文件夹的完整路径。
MsgBoxApplication.StartupPath
本示例显示活动工作簿中每个工作表的名称。
ForEachwsInWorksheets
MsgBoxws.Name
Nextws
本示例关闭除正在运行本示例的工作簿以外的其他所有工作簿，并保存其更改容。
ForEachwInWorkbooks
Ifw.NameThisWorkbook.NameThen
w.Closesavechanges:
=True
EndIf
Nextw
Activate事件
激活一个工作簿、工作表、图表或嵌入图表时产生此事件。
当激活工作表时，本示例对A1:
A10区域进行排序。
PrivateSubWorksheet_Activate（）
Range（"a1:
a10"）.SortKey1:
=Range（"a1"）,order:
=xlAscending
EndSub
Calculate事件
对于Worksheet对象，在对工作表进行重新计算之后产生此事件
每当工作表重新计算时，本示例就调整A列到F列的宽度。
PrivateSubWorksheet_Calculate（）
Columns（"A:
F"）.AutoFit
EndSub
BeforeDoubleClick事件
应用于Worksheet对象的Activate方法。
当双击某工作表时产生此事件，此事件先于默认的双击操作。
PrivateSubexpression_BeforeDoubleClick（ByValTargetAsRange,Cancel
AsBoolean）
expression引用在类模块中带有事件声明的Worksheet类型对象的变量。
Target必需。
双击发生时最靠近鼠标指针的单元格。
Cancel可选。
当事件发生时为False。
如果事件过程将该参数设为True，则该
过程执行完之后将不进行默认的双击操作。
BeforeRightClick事件
应用于Worksheet对象的Activate方法。
当用鼠标右键单击某工作表时产生此事件，此事件先于默认的右键单击操作。
PrivateSubexpression_BeforeRightClick（ByValTargetAsRange,Cancel
AsBoolean）
expression引用在类模块中带有事件声明的Worksheet类型对象的变量。
Target必需。
右键单击发生时最靠近鼠标指针的单元格。
Cancel可选。
当事件发生时为False。
如果该事件过程将本参数设为True，则
该过程执行结束之后不进行默认的右键单击操作。
Change事件
当用户更改工作表中的单元格，或外部引起单元格的更改时产生此事件。
PrivateSubWorksheet_Change（ByValTargetAsRange）
Target更改的区域。
可以是多个单元格。
说明
重新计算引起的单元格更改不触发本事件。
可使用Calculate事件俘获工作表重新
升级会员 