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

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

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 VBProjectSet VBProj = ThisWorkbook.VBProject VBComponent Dim VBComp As VBComponentSet VBComp = ThisWorkbook.VBProject.VBComponents("Module1") CodeModule Dim VBCodeMod As CodeModuleSet 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").
Adding A Module To A Workbook
The procedure below will add a new module named "NewModule" to ThisWorkbook.
Sub AddModule()Dim VBComp As VBComponentSet VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)VBComp.Name = "NewModule"Application.Visible = TrueEnd 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 Application.Visible = True returns focus back to the Excel application.
Deleting A Module From A Workbook
The procedure below will delete the module named "NewModule" from ThisWorkbook.
Sub DeleteModule()Dim VBComp As VBComponentSet VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")ThisWorkbook.VBProject.VBComponents.Remove VBCompEnd Sub
You cannot delete the ThisWorkbook object module, or a sheet object module, or a chart object module.

Adding A Procedure To A Module
The procedure below will add a new procedure called "MyNewProcedure" to the module named "NewModule" in ThisWorkbook.
Sub AddProcedure()Dim VBCodeMod As CodeModuleDim LineNum As LongSet VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModuleWith VBCodeMod LineNum = .CountOfLines + 1 .InsertLines LineNum, _"Sub MyNewProcedure()" & Chr(13) & _" Msgbox ""Here is the new procedure"" " & Chr(13) & _"End Sub"End WithApplication.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
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"
Creating An Event Procedure
The CodeModule object has a method called CreateEventProc that you can use to create an event procedure in and class module, a sheet object module, or the ThisWorkbook object 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 LongWith 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.
Sub DeleteProcedure()Dim VBCodeMod As CodeModuleDim StartLine As LongDim HowManyLines As LongSet VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModuleWith VBCodeMod StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc) HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc) .DeleteLines StartLine, HowManyLinesEnd WithEnd Sub
Deleting All Code From A Module
The procedure below will delete all code from a module name "NewModule".
Sub DeleteAllCodeInModule()Dim VBCodeMod As CodeModuleDim StartLine As LongDim HowManyLines As LongSet VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModuleWith VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLinesEnd WithEnd 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.
Sub ListModules()Dim VBComp As VBComponentDim Msg As StringFor Each VBComp In ThisWorkbook.VBProject.VBComponents Msg = Msg & VBComp.Name & " Type: " & CompTypeToName(VBComp) & Chr(13)Next VBCompMsgBox MsgEnd Sub
Function CompTypeToName(VBComp As VBComponent) As StringSelect 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 ElseEnd SelectEnd 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 CodeModuleDim StartLine As LongDim Msg As StringDim ProcName As StringSet VBCodeMod = ThisWorkbook.VBProject.VBComponents("SaveModule").CodeModuleWith 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) LoopEnd WithMsgBox MsgEnd 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.VBComponentDim Sfx As StringFor 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 IfNext VBCompEnd 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.VBComponentDim VBComps As VBIDE.VBComponentsSet VBComps = ActiveWorkbook.VBProject.VBComponentsFor 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 SelectNext VBCompEnd 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 StringWith Workbooks("Book2") FName = .Path & "\code.txt" .VBProject.VBComponents("Module1").Export FNameEnd WithWorkbooks("book1").VBProject.VBComponents.Import FNameEnd 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 StringDim VBComp As VBIDE.VBComponentWith 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 VBCompEnd WithEnd 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 BooleanOn Error Resume NextModuleExists = Len( _ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0End Function
Function ProcedureExists(ProcedureName As String, _ ModuleName As String) As BooleanOn Error Resume NextIf ModuleExists(ModuleName) = True Then ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _ .CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0End IfEnd Function
Renaming Code Modules
You can rename VBA's code modules with code like ThisWorkbook.VBProject.VBComponents("Module1").Name = "NewModule"
This code will work with any VBComponent, including the built-in components such as the sheet modules and the ThisWorkbook module:
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "MyWorkbook"
Eliminating Screen FlickeringWhen you use code to write code, the VBA Editor displays itself. Broadly speaking, this is undesirable. You can reduce this to a flicker by using code like
Application.VBE.MainWindow.Visible = False'' your code to add code'Application.VBE.MainWindow.Visible = True
This will close the VBA Editor, but you will still see the editor appear momentarily and then hide itself. To prevent this screen flickering, you need to use the LockWindowUpdate API function. Put the following function declares at the top of your code module, before and outside of any procedures.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal ClassName As String, ByVal WindowName As String) As LongPrivate Declare Function LockWindowUpdate Lib "user32" _ (ByVal hWndLock As Long) As Long
Then, in your code, use code like the following:
Dim VBEHwnd As LongOn Error Goto ErrH:Application.VBE.MainWindow.Visible = FalseVBEHwnd = FindWindow("wndclass_desked_gsk", _ Application.VBE.MainWindow.Caption)If VBEHwnd Then LockWindowUpdate VBEHwndEnd If'' your code to write code'Application.VBE.MainWindow.Visible = FalseErrH:LockWindowUpdate 0&
You may still see the title bar of Excel momentarily dim, but the VBA Editor will not be visible at all. If you already have error handling code in your procedure that writes the VBA code, you want to be sure to call LockWindowUpdate 0&.The code above will work in Excel 2000 and later. It has not been tested in Excel97.