Erlandsen Data Consulting

With the macro below you can copy a module from one workbook to another: Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook) ‘ requires a reference to the Microsoft Visual Basic Extensibility library ‘ copies a module from one workbook to another ‘ example: CopyModule Workbooks(“Book1.xls”), “Module1″, Workbooks(“Book2.xls”) Dim strFolder As String, strTempFile [...]

In Excel versions prior to 2003 you could access your VBProjects using code, as long as the VBProjects was unprotected. In Excel 2003 or later, access to the VBProjects using code is restricted by default. If you want to use VBA code to alter the contents of a VBProject you have to tell the application [...]

When you want to delete all macros from a workbook or document you can use the macro below. The procedure can be used in both Excel and Word without any editing. It also assumes that you in newer versions of Office has allowed code to access your VBProjects. Sub RemoveAllMacros(objDocument As Object) ‘ deletes all [...]

In Office 2000 the built-in help window has an annoying habit of trying to "help" you by floating back and forth on the screen while you work. To disable this behavior you can make a change to the Registry: Navigate to HKEY_CURRENT_USERSoftwareMicrosoftOffice9.0CommonHelpViewer Change the value for the key IsFloating to 1. If the key IsFloating [...]

It’s not possible to delete all kinds of modules, you can’t delete the codemodules for worksheets, charts and ThisWorkbook. In these modules you have to delete the content instead of the module itself: Sub DeleteModuleContent(ByVal wb As Workbook, ByVal DeleteModuleName As String) ‘ requires a reference to the Microsoft Visual Basic Extensibility library ‘ deletes [...]

When you want to delete a module by code, you can use the macro below: Sub DeleteVBComponent(ByVal wb As Workbook, ByVal CompName As String) ‘ requires a reference to the Microsoft Visual Basic Extensibility library ‘ deletes the vbcomponent named CompName from wb Application.DisplayAlerts = False On Error Resume Next ‘ ignores any errors wb.VBProject.VBComponents.Remove [...]

If you don’t want to add a complete module, you can add just the missing procedures to an existing module by using the macro below. It adds the content of a text file to an existing module: Sub ImportModuleCode(ByVal wb As Workbook, ByVal ModuleName As String, ByVal ImportFromFile As String) ‘ requires a reference to [...]

You can add code to a module without using a separate text file that contains the code. The macro below shows how this can be done. The macro must be customized to contain to the code you want to add: Sub AddProcedureCode(wb As Workbook, strModuleName As String) ‘ requires a reference to the Microsoft Visual [...]

With the macro below you can create a new module in a workbook: Sub CreateNewModule(ByVal wb As Workbook, ByVal ModuleTypeIndex As Integer, ByVal NewModuleName As String) ‘ requires a reference to the Microsoft Visual Basic Extensibility library ‘ creates a new module of ModuleTypeIndex ‘ (1=standard module, 2=userform, 3=class module) in wb ‘ renames the [...]

With the macro below you can delete an existing procedure from a module: Sub DeleteProcedureCode(ByVal wb As Workbook, _ ByVal DeleteFromModuleName As String, ByVal ProcedureName As String) ‘ requires a reference to the Microsoft Visual Basic Extensibility library ‘ deletes ProcedureName from DeleteFromModuleName in wb Dim VBCM As CodeModule, ProcStartLine As Long, ProcLineCount As Long [...]