CODEWITHSUNDEEP
excelvba
Rasmus Remmer Bielidt
Rasmus Remmer Bielidt

Reputation: 157

Adding Module through code

I have a master workbook containing a macro which opens another workbook containing a demand forecast. The workbook opened through the macro is downloaded from a customer portal and is all new every day without possibility of editing it beforehand.

The macro then loops through the information and creates new readable and more intuitive worksheets. However, on a few of these worksheets I would like to add some event-driven code to give tooltips when mousing over or selecting cells.

Is there any possibility (without installing add-ons from vanilla Excel 2010) to add code to worksheet objects created during a macro?

The layout of the processed workbook is more or less static, so I was wondering if I should create a template file and then copy the input into it. That would allow me to have coded the events before data is added. Is this the best possibility?

Upvotes: 3

Views: 4174

Answers (2)

Bruce Gavin
Bruce Gavin

Reputation: 387

 I have need to create modules by VBA for Excel.
 This is my library module that accepts a module name.
 It queries the user for overwrite, if the module already exists.

 Public Function LibAddCodeModule(ByVal valModuleName As String) As Boolean
 Dim colComponents As vbComponents
 Dim objCodeModule As VBComponent
 Dim objComponent As VBComponent
 Dim objProject As VBProject

 ' ----------
 ' initialize
 ' ----------
 LibAddCodeModule = False
 If (valModuleName = "") Then Exit Function
 Set colComponents = Application.VBE.ActiveVBProject.vbComponents
 Set objProject = ThisWorkbook.VBProject

 ' ---------------------------
 ' remove previous code module
 ' ---------------------------
 For Each objComponent In ThisWorkbook.VBProject.vbComponents
     If (StrComp(objComponent.Name, valModuleName, vbTextCompare) = 0) Then
         If MsgBox("Do you want to replace " & valModuleName, vbYesNo) = vbNo Then
            Exit Function
            End If
        ThisWorkbook.VBProject.vbComponents.Remove objProject.vbComponents(valModuleName)
        Exit For
        End If
     Next

 ' ----------------------
 ' create new code module
 ' ----------------------
 Set objCodeModule = colComponents.Add(vbext_ct_StdModule)
 objCodeModule.Name = valModuleName
 LibAddCodeModule = True
 End Function

Upvotes: 0

TYale
TYale

Reputation: 114

As Dan pointed out, you could use Application.VBE.ActiveVBProject to programmatically add code modules to a workbook. But doing this requires more lenient Macro security settings (which are set to untrusted, by default) and this is not recommended.

When I have to do something similar, I use three workbooks:

  1. The data containing workbook
    • This book has no macro functionality
  2. A template workbook containing the necessary macros
  3. A macro enabled workbook to facilitate the transition.

Use workbook #3 to open workbook #1 and copy its data into workbook #2. Save a copy of workbook #2 and close it. Repeat this process as necessary.

It isn't the prettiest solution, but it keeps your code modular.

Upvotes: 1

Related Questions