Adding Module through code

Question

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?

Looks like you can do this: Application.VBE.ActiveVBProject.VBComponents.Import ("c:\MyFolder\MyFileName.bas") ... — Dan
– Dan, Commented Mar 4, 2013 at 8:54
Thanks for the answer, appreciated. Although I ended up creating a template file since that was easier (for me) to create, test and debug. — Rasmus Remmer Bielidt
– Rasmus Remmer Bielidt, Commented Mar 4, 2013 at 13:04

TYale · Accepted Answer · 2013-03-07 16:17:38Z

1

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:

The data containing workbook
- This book has no macro functionality
A template workbook containing the necessary macros
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.

answered Mar 7, 2013 at 16:17

TYale

1141 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rasmus Remmer Bielidt Over a year ago

That is exactly what I ended up doing. I had to program some events that would be executed on the imported data's worksheets. I put those in a template macro-enabled workbook and copy the import data into a copy of this template before the data is cleaned up.

Bruce Gavin · Accepted Answer · 2024-06-28 00:14:05Z

 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

Collectives™ on Stack Overflow

Adding Module through code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related