转贴：英文 VBE编程

killl

Programming To The Visual Basic Editor （http://www.cpearson.com/excel/vbe.htm）

Visual Basic Editor (VBE) is the tool used to create, modify, and maintain Visual Basic For Applications (VBA) procedures and modules in MS Office applications.   VBA gives you the ability to modify workbooks and worksheets through VBA, as if you were going through the Excel interface.  VBA also allows you to modify VBA components and code modules, as if you were going through the VBE interface.    This page applies only to Excel97 and above.  It does not apply to Excel95 or previous versions.  

This pages describes a few of the objects, methods, and properties of the VBE that you can manipulate from VBA.  In Excel97, these objects, methods, and properties are not described in the normal VBA help files.  You need to open the file called VEENOB3.hlp.  This file many not have been installed on your system when you installed the VBA help files and Office97.  You can find it in the MoreHelp folder on your Excel or Office CD.  You many want to have a macro, assigned to a menu item or a shortcut key to easily display this file.

Visual Basic Editor (VBE vb编辑器)是用以建立、修改和维护MS Office 应用中Visual Basic For Applications (VBA)程序及模块的工具。VBA提供你在excel界面下用vba更改工作簿及工作表的能力。VBA也允许你修改通过VBE界面修改VBA组成和代码模块。

Sub ShowVBEHelp()
Shell "c:\windows\winhelp.exe veenob3.hlp", vbNormalFocus
End Sub
In Excel 2000 and 2002, these topics are included in the standard VBA help files.  

Before using these procedures, you’ll need to set a reference in VBA to the VBA Extensibility library.  In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library.   This enables VBA to find the definitions of these objects.  If you are using Excel97, this library will appear in the References list without a version number:  "Microsoft Visual Basic For Applications Extensibility".  If you are using Excel 2000 or 2002, it will appear with a version number: "Microsoft Visual Basic For Applications Extensibility 5.3".  It is very important that you reference the proper library.  If you reference the wrong library, you will receive "Type Mismatch" errors.  If you don’t reference the extensibility library at all, you will receive "User Defined Type Not Defined Error".

For  information about programming the menus in the VBE, see the Adding Menus To The VBA Editor page.

在使用该程序前，你需要设定在VBA中引用VBA Extensibility library。在vba编辑器工具菜单中选择引用项目，选择"Microsoft Visual Basic For Applications Extensibility"库。它可以使VBA找到对象的定义。如果你用的是Excel97，该库在引用列表中没有版本号："Microsoft Visual Basic For Applications Extensibility"。如果你用的是Excel 2000 or 2002，它显示的版本号为："Microsoft Visual Basic For Applications Extensibility 5.3"。你引用适当的库是非常重要的。当你引用错误的库，你将遇到“类型错误搭配”。如果你根本没引用扩展库，你会遇到“未定义的用户自定义类型”。

关于VBE中菜单编程的资料，可参看“VBA中添加菜单”页

Note: An additional level of security was added in Excel 2002.  To manipulate the VBA Project objects as described here, you’ll have to change your security setting.  Go to the Tools menu, choose Macros, then Security. Click the "Trusted Sources" tab, and put a check next to the "Trust access to Visual Basic Project".

注意：在 Excel 2002中增添附加安全级别。此处讲述如何操作VBA工程对象，你可以改变你的安全设置。在工具菜单中选择宏—安全。点击“信任来源（可靠发行商）”标签，然后点击下一步“信任对于Visual Basic 项目的访问”。

NOTE: In all versions of Excel, the VBProject must not be protected.  If it is, these procedures will fail.  In Excel 2002, you must have "Trust Access To Visual Basic Project" enabled.  To enable this setting, go to the Tools menu in Excel, choose Macros, Security, then the "Trusted Sources" tab, and put a check next to "Trust Access To Visual Basic Project".  Otherwise, you will get errors.

Also, you may get unpredictable results if you attempt to modify a code module’s code from that same module.  That is, having code in Module1 modify the contents of Module1.  I recommend that you do not do this.

注意：在所有Excel版本中，VBProject必须不能有保护。如果这样会使程序失败。在Excel 2002中，你必须激活“信任对于Visual Basic 项目的访问”。在工具菜单中选择宏—安全。点击“信任来源（可靠发行商）”标签，然后点击下一步“信任对于Visual Basic 项目的访问”。否则你肯定出错。（译者按：使用vbproject的范例会出现：错误1004，方法“vbproject”作用于“_workbook”对象时失败。从有office2000后就一直没有解决，4年了，终于看到了答案）

同样，如果你试图用相同的模块代码修改代码模块你将有可能遇到未知的结果。就像用模块1的代码修改模块1的内容。我建议你不要这样做。

VBE Objects

We’ll be using three of these objects in our code:  

VBProject                This is the entire set of VBA modules and references associated with a workbook.  

VBComponent       This is the individual component within a VBProject.  For example, a UserForm and a standard code module are each a VBComponent.  The VBComponents collection contains each existing VBComponent object.  

CodeModule            This object represents the actual code contained in a VBComponent.  For example, when you enter code into Module1, you’re entering code into the CodeModule object of the VBComponent named "Module1".  

We’ll be programmatically "navigating" to these components through the Workbook object.  You can also get to these components by going through the Application.VBE object path, but we won’t be doing this.  

VBE 对象

我们在自己的代码中可以使用以下三个对象：

VBProject  这是 VBA 模块的整体设置引用workbook的关联
VBComponent  这是VBProject中的独立部件。比如，用户窗体和标准代码模块都是各自的VBComponent。VBComponents 集合当前每个VBComponent对象的内容。
CodeModule 此对象为当前 VBComponent中的代码内容。比如，当你在模块1中加入代码，你加入的代码在 VBComponent 的代码模块对象中称为“模块1”。

There are various types of VBComponents, identified by the Type property of the VBComponent object.  

  
         Type Constant                         Description
vbext_ct_ClassModule                      This is a class module, used to create your own objects.  We won’t be using these here.
vbext_ct_Document                          This is the component for a worksheet, chart sheet, or ThisWorkbook.
vbext_ct_MSForm                             This is the component for a UserForm.
vbext_ct_StdModule                         This is the component for a standard code module.  Most of our procedures will work with these components.

procedure  n.程序, 手续  

VBComponents有多种类型，定义 VBComponent 对象属性的类别
类常量                                       描述
vbext_ct_ClassModule                    类模块，用以创建自己的对象。我们不能在此处使用
vbext_ct_Document                       由 worksheet, chart sheet, or ThisWorkbook构成
vbext_ct_MSForm                         由 UserForm（用户窗体）构成        
vbext_ct_StdModule                      由标准代码模块构成。我们多数程序由此部分工作

Getting A Reference To An Object

The first step in programming to the VBE is to get a reference to object you need to work with.  

VBProject                
Dim VBProj As VBProject
Set VBProj = ThisWorkbook.VBProject

VBComponent                
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("Module1")

CodeModule                
Dim VBCodeMod As CodeModule
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

In all of the examples in this page, we’ll be working with the ThisWorkbook object -- working with the VBA components in the workbook which contains the code.  Of course, you can work with any open workbook, by using ActiveWorkbook or Workbooks("SomeBook.xls").

获得对象参考
第一步通过程序在VBEzhong 获取你需要的对象参考

此页的所有范例我们对ThisWorkbook对象进行操作—工作簿包含的代码中的VBA components进行操作 。当然，你能使用ActiveWorkbook 或 Workbooks("SomeBook.xls")打开工作簿操作。

Adding A Module To A Workbook  
The procedure below will add a new module named "NewModule" to ThisWorkbook.

对工作簿添加模块
以下程序为在ThisWorkbook添加一个名为"NewModule"的新模块。

Sub AddModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"
Application.Visible = True
End Sub

When you run this code from Excel while the VBE is open, you will be taken to the new module’s code module, and the macro will terminate.  When you run this code while the VBE is not open, your Excel application will be visible, but will not have focus.  The  statement returns focus back to the Excel application.
当你在EXCEL打开VBE时运行此代码，你将获得新模块的代码模块，并终止宏。当你在EXCEL未打开VBE时运行此代码，你的EXCEL应用程序可见但是无法获得焦点。代码段返回焦点给EXCEL应用程序。

Deleting A Module From A Workbook

The procedure below will delete the module named "NewModule" from ThisWorkbook.  

对工作簿删除模块
以下程序为在ThisWorkbook删除一个名为"NewModule"的新模块。

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

You cannot delete the ThisWorkbook code module, or a sheet code module, or a chart code module.

你不能删除ThisWorkbook或工作表或图表代码模块。

Adding A Procedure To A Module

The procedure below will add a new procedure called "MyNewProcedure" to the module named "NewModule" in ThisWorkbook.
添加程序到模块
以下程序将添加名为"MyNewProcedure"的新程序到ThisWorkbook中名为"NewModule"的模块。

Sub AddProcedure()

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Sub MyNewProcedure()" & Chr(13) & _
" Msgbox ""Here is the new procedure"" " & Chr(13) & _
"End Sub"
End With

Application.Run "MyNewProcedure"

End Sub

Pay attention to the way in which the .InsertLines method is called.  The entire procedure is passed as one argument -- a string with embedded Chr(13) characters for the line breaks.  The code statement
注意这种称为 .InsertLines 模块的方法。所有的程序是通过类是一种概念——插入Chr(13)字符的字符串作行终止。此代码表达为

Application.Run "MyNewProcedure"

will run the new procedure.  You must use Application.Run rather than calling the procedure directly in order to prevent compile-time errors.  This method will work only if you are adding code to another code module.  If you are adding code a the same code module, you must use an Application.OnTime method, so that control is returned to Excel, and the module can be recompiled and reloaded.  Using Application.OnTime may have some synchronizations problems, so you should avoid calling a procedure that you’ve just added to the same code module without allowing all VBA procedures to come to an end.  
运行新程序。你必须
Application.OnTime Now,"NewProcedureName"

  

[此贴子已经被taller于2007-1-15 9:22:19编辑过]

killl

Creating An Event Procedure

The CodeModule object has a method called CreateEventProc that you can use to create an event procedure in class module, a sheet module, or the ThisWorkbook module. The advantage of CreateEventProc over InsertLines is that CreateEventProc will automatically insert the complete procedure declaration, including all of the correct parameters. CreateEventProc returns the line number on which the procedure begins, so once you’ve called CreateEventProc , add one to the result and use this with InsertLines to insert the body of the event procedure. For example, the code below creates a Workbook_Open procedure containing a Msgbox statement in the ThisWorkbook module of the Active Workbook.

创建事件代码 Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule StartLine = .CreateEventProc("Open", "Workbook") + 1 .InsertLines StartLine, _ "Msgbox ""Hello World"",vbOkOnly" End With

Deleting A Procedure From A Module

The procedure below will delete the procedure called "MyNewProcedure" from the module named "NewModule" in ThisWorkbook.

从删除中删除程序 以下程序将从ThisWorkbook的名为"NewModule"的模块中删除名为"MyNewProcedure"的程序。

Sub DeleteProcedure()

Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule With VBCodeMod StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc) HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc) .DeleteLines StartLine, HowManyLines End With

End Sub

Deleting All Code From A Module

The procedure below will delete all code from a module name "NewModule".

删除模块中所有代码 以下程序将从名为"NewModule"的模块中删除所有代码。

Sub DeleteAllCodeInModule() Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With

End Sub

Listing All Modules In A Workbook

The procedure below will list, in a message box, all of the modules in ThisWorkbook. It uses a function called CompTypeToName to get a string describing the type of module. The function CompTypeToName is listed below. 列表Workbook中的所有模块 以下程序将通过message box列表Workbook中的所有模块。

Sub ListModules()

Dim VBComp As VBComponent Dim Msg As String

For Each VBComp In ThisWorkbook.VBProject.VBComponents Msg = Msg & VBComp.Name & " Type: " & CompTypeToName(VBComp) & Chr(13) Next VBComp MsgBox Msg

End Sub

Function CompTypeToName(VBComp As VBComponent) As String

Select Case VBComp.Type Case vbext_ct_ActiveXDesigner CompTypeToName = "ActiveX Designer" Case vbext_ct_ClassModule CompTypeToName = "Class Module" Case vbext_ct_Document CompTypeToName = "Document" Case vbext_ct_MSForm CompTypeToName = "MS Form" Case vbext_ct_StdModule CompTypeToName = "Standard Module" Case Else End Select

End Function Listing All Procedures In A Module

The procedure below will list, in a message box, all of the procedures in a standard code module called "SaveModule" in ThisWorkbook. Procedures are listed in the order in which they appear in the CodeModule object.

Sub ListProcedures()

Dim VBCodeMod As CodeModule Dim StartLine As Long Dim Msg As String Dim ProcName As String

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("SaveModule").CodeModule With VBCodeMod StartLine = .CountOfDeclarationLines + 1 Do Until StartLine >= .CountOfLines Msg = Msg & .ProcOfLine(StartLine, vbext_pk_Proc) & Chr(13) StartLine = StartLine + _ .ProcCountLines(.ProcOfLine(StartLine, _ vbext_pk_Proc), vbext_pk_Proc) Loop End With MsgBox Msg

End Sub Also see Code Modules And Code Names for more information about the CodeName property of VBComponents.

Exporting All Modules In A Project

The procedure below will list export all of the modules in a workbook to text files. It will save the files in the same folder as the workbook. This can be useful for saving a backup copy of your VBA, or for transferring VBA code from one project to another.

输出项目中的所有模块

Sub ExportAllVBA() Dim VBComp As VBIDE.VBComponent Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.VBComponents Select Case VBComp.Type Case vbext_ct_ClassModule, vbext_ct_Document Sfx = ".cls" Case vbext_ct_MSForm Sfx = ".frm" Case vbext_ct_StdModule Sfx = ".bas" Case Else Sfx = "" End Select If Sfx <> "" Then VBComp.Export _ Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx End If Next VBComp End Sub Deleting All VBA Code In A Project

The procedure below will delete all the VBA code in a project. You should use this procedure with care, as it will permanently delete the code. Standard modules, user forms, and class modules will be removed, and code within the ThisWorkbook module and the sheet modules will be deleted. You may want to export the VBA code, using the procedure above, before deleting the VBA code.

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp

End Sub

Copying Modules Between Projects

There isn’t a single method to copy modules from one VBProject to another. Instead, you have to export the module from one project, and then import it into another. The following procedure will copy Module1 from Book2 to Book1.

复制项目间的模块

Sub CopyOneModule()

Dim FName As String With Workbooks("Book2") FName = .Path & "\code.txt" .VBProject.VBComponents("Module1").Export FName End With Workbooks("book1").VBProject.VBComponents.Import FName

End Sub

Just change "Module1" to the name of the module you want to copy. If you want to copy all modules (except the ThisWorkbook and Sheet modules), you can use the following code.

Sub CopyAllModules()

Dim FName As String Dim VBComp As VBIDE.VBComponent

With Workbooks("Book2") FName = .Path & "\code.txt" If Dir(FName) <> "" Then Kill FName End If For Each VBComp In .VBProject.VBComponents If VBComp.Type <> vbext_ct_Document Then VBComp.Export FName Workbooks("book1").VBProject.VBComponents.Import FName Kill FName End If Next VBComp End With

End Sub

Testing Existence Of A Module Or Procedure

You can use the VBA Extensibility tools to determine whether a module exists, or a procedure exists in a module.

测试具体模块或程序

Function ModuleExists(ModuleName As String) As Boolean On Error Resume Next ModuleExists = Len( _ ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0 End Function

Function ProcedureExists(ProcedureName As String, _ ModuleName As String) As Boolean On Error Resume Next If ModuleExists(ModuleName) = True Then ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _ .CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0 End If End Function

yanjun_lyg

感谢killl，很好的文章

lbx19820727

在excel中，能用这个解决哪些问题？

walterlai

感谢killl，很好的文章

esnake

非常好，太感谢了，这样的文章沉下去太可惜了！

konggs

很不错。

可惜没有整理出来，看不出所以然。

killl

http://www.cpearson.com/excel/vbe.htm

转载自：http://www.cpearson.com/excel/vbe.htm

asat

看不太懂~不过支持一下~`