How to add code module to Excel programmatically

[deko]

I need to add a code module to Excel from Access via automation.

I've looked at these 2 sites for help:

http://www.j-walk.com/ss/excel/tips/tip96.htm
http://www.cpearson.com/excel/vbe.htm

For starters, I'm trying to simply add a module to an Excel workbook from a
standard Excel module.

The code below returns this:

This workbook has 6 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed

Is there something wrong with the way I'm instantiating newmod?
Any guidance would be appreciated.
Thanks in advance.

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) >= 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & vbpCt & " modules."
End If
End If
Exit_Here:
Exit Function
HandleErr:
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
End Function

 

[Bob Phillips]

I tried this from Excel, and after renaming the variable vbpCpt to intCpt,
it ran fine for me.

If running this from Excel, you need to set a variable object to the Excel
app, and qualify each Excel object with that app object. Not sure if that
applies equally to VBE objects, never tried it from another app, but try it
anyways.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

I tried this from Excel, and after renaming the variable vbpCpt to intCpt,

it ran fine for me.

What did you try? There was no variable named vbpCpt in my code.

If running this from Excel, you need to set a variable object to the Excel
app, and qualify each Excel object with that app object.

Do you mean "if running this from Access"... ??

Not sure if that applies equally to VBE objects,
never tried it from another app, but try it anyways.

hmmm... do I need to instantiate VBE objects differently?

 

[Bob Phillips]

What did you try? There was no variable named vbpCpt in my code.

Okay, so I got the name slightlyu wrong. This is the code, it should have
been vbpCt

Debug.Print "This workbook has " & vbpCt & " modules."

Do you mean "if running this from Access"... ??

Or an other automation client.

hmmm... do I need to instantiate VBE objects differently?

Don't know as I said I have never done it, but I would expect so.

 

[deko]

Ah, I see...

Debug.Print "This workbook has " & vbpCt & " modules."

Yes, that was a mistake in my code... should be intCt

Still, the problem I had was instantiating newmod. You say that code
instantiated newmod for you?

I have that code in a standard module named "Module1". I tried putting the
code in "ThisWorkbook" as well as "Sheet1" but then the code would not run
(from the immediate window using ?AddModuleToExcel).

I tried the below revised code can got this:

This workbook has 5 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed
Error Number 91: Object variable or With block variable not set
This workbook has 5 modules.

What I'm expecting to see is this:

This workbook has 5 modules.
This workbook has 6 modules.

And then, of course, see an additional module in the project from the IDE.
The next step would be finding a way to get the actual code in the module,
and also getting the code to run via automation (as you mentioned, this
should simply be a matter of creating an object to hold reference to the
Excel application object and using something like xlapp.vbp = Active
Workbook.VBProject).

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) >= 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
'Set newmod =
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
End If
End If
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function

 

[deko]

Got it working. I put the below code in a standard module named "Module1"
and run it from the immediate window:

?AddMod
This workbook has 6 modules.
This workbook has 7 modules.

This KB helped: http://support.microsoft.com/?kbid=245801

The key was setting a reference to Microsoft Visual Basic for Applications
Extensibility.

But this raises a new question: Can I programmatically set the reference?
My code prompts the user to change security settings if necessary, which
gets around one potential hurdle, but how do I set the required reference?

Public Function AddMod()
On Error GoTo HandleErr
Dim vbcoms As VBComponents
Dim vbp As Object
Dim objMod As Object
Dim intCt As Integer
If Val(Application.Version) >= 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
Set vbcoms = Application.VBE.ActiveVBProject.VBComponents
intCt = vbcoms.Count
Debug.Print "This workbook has " & intCt & " modules."
Set objMod = vbcoms.Add(vbext_ct_StdModule)
objMod.Name = "MyNewModule"
intCt = vbcoms.Count
Debug.Print "This workbook has " & intCt & " modules."
End If
End If
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function
'http://www.j-walk.com/ss/excel/tips/tip96.htm
'http://www.cpearson.com/excel/vbe.htm
'http://support.microsoft.com/?kbid=245801

 

[deko]

Third time's a charm...

The below code adds a standard module to Book1.xls from Access via
automation. This assumes that "Trust access to Visual Basic Project" is
checked on the Trusted Publishers tab of the Security Dialog in Excel
(accessed from Tools >> Macro >> Security). I've omitted the code that
alerts the user with a message box if this is not the case.

The reference I was worried about (Microsoft Visual Basic for Applications
Extensibility) I don't need in the Excel workbook - I just set it in Access
(which I have control over). Now if I can just figure out how to get my
code into the module...

Public Function AddMod()
On Error GoTo HandleErr

Dim xlapp As Excel.Application
Dim strXlsPath As String
Dim strXlsFile As String
Dim intCt As Integer

strXlsPath = "C:\Book1.xls"
strXlsFile = "Book1.xls"
Set xlapp = CreateObject("Excel.Application")
xlapp.Workbooks.Open (strXlsPath)
intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count
Debug.Print strXlsFile & " has " & intCt & " modules."
xlapp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count
Debug.Print strXlsFile & " has " & intCt & " modules."

Exit_Here:
xlapp.Workbooks(strXlsFile).Save
xlapp.Workbooks(strXlsFile).Close
xlapp.Quit
Set xlapp = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function

 

[Bob Phillips]

You don't need to set a reference to that library, that is early binding, it
is just as simple with late binding. Just change this line

Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)

to

Set newmod = vbp.VBComponents.Add(1)

To add code, try something like

'----------------------------------------------------------------
Sub AddModuleProc()
'----------------------------------------------------------------
Dim StartLine As Long
Dim cLines As Long

With ActiveWorkbook.VBProject.VBComponents("Module2").CodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"Sub myProc()" & Chr(13) & _
" Msgbox ""myProc installed"" " & Chr(13) & _
"End Sub"
End With
End Sub

adapted to your code of course.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

You don't need to set a reference to that library, that is early binding,
it

is just as simple with late binding. Just change this line

Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)

to

Set newmod = vbp.VBComponents.Add(1)

Yes, but I have control over the Access app. I'm not sure there's any
advantage to late binding in this case. I like to use early binding where I
can - it's like getting something for free. Although references can be a
pain in the neck, to be sure.

As for adding code, I figured out how to add code to a standard module (see
below). But what I need to do is put a button on worksheet that launches a
user form with some controls with which users can run various "what if"
scenarios - by changing formulas, adding highlighting, etc. So the user
form will have (among other things) an input box for users to enter a
'control value' which sets a ceiling, max variance, etc. All this code will
make for a very long string to insert. And how would I add the user form?

The other thing that could be tricky is that I will be creating an unknown
number of worksheets - which I will not know the names until Access inserts
them into the workbook.

Am I getting too ambitious with automation here?

I appreciate any thoughts you care to offer.

[Access code to add Excel code]
Private Sub AddModule(xlapp As Excel.Application)
On Error GoTo HandleErr
Dim str1 As String
Dim lngLn As Long
xlapp.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule).Name = _
"AdvancedSettings"
lngLn = xlapp.VBE.ActiveVBProject.VBComponents("AdvancedSettings") _
.CodeModule.CountOfLines + 1
xlapp.VBE.ActiveVBProject.VBComponents("AdvancedSettings") _
.CodeModule.InsertLines lngLn, _
"Private Sub MyNewProcedure()" & Chr(13) & _
" Msgbox" & Chr(34) & "Here is the new procedure" & Chr(34) & _
Chr(13) & "End Sub"
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
str1 = "Error Number " & Err.Number & _
" [basWorksheets.AddModule]: " & Err.Description
Call basHandler.Logger(str1)
End Select
Resume Exit_Here
End Sub

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

Yes, but I have control over the Access app. I'm not sure there's any
advantage to late binding in this case. I like to use early binding where I
can - it's like getting something for free. Although references can be a
pain in the neck, to be sure.

Which is why I suggested late binding, it overcomes those problems.

As for adding code, I figured out how to add code to a standard module (see
below). But what I need to do is put a button on worksheet that launches a
user form with some controls with which users can run various "what if"
scenarios - by changing formulas, adding highlighting, etc. So the user
form will have (among other things) an input box for users to enter a
'control value' which sets a ceiling, max variance, etc. All this code will
make for a very long string to insert. And how would I add the user form?

Why not just create a template workbook with a userform.

To add the button is easy

'-----------------------------------------------------------------
Sub CreateControlButton()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "Userform1.Show"

End With

End Sub

The other thing that could be tricky is that I will be creating an unknown
number of worksheets - which I will not know the names until Access inserts
them into the workbook.

With xlApp
.SheetsInNewWorkbook = 4
.Workbooks.Add
For i = 1 To .Activeworkbook.Worksheets.Count
'etc
Next i
End With

 

[deko]

Why not just create a template workbook with a userform.

And do things the easy way??!! Unfortunately, that would require users to
do something differently, and politics being as they are, that's not an
option.

The thing I'm concerned about is having to add code to every worksheet -
that would require looping through all the worksheets and inserting a lot of
redundant code, would it not? On the other hand, inserting a module sounds
simple enough, but can that one module get a button on each worksheet in the
workbook?

 

[Bob Phillips]

And do things the easy way??!! Unfortunately, that would require users to
do something differently, and politics being as they are, that's not an
option.

No, I'm thinking nothing to do with the users, you can open that template in
VBA, so you keep control.

The thing I'm concerned about is having to add code to every worksheet -
that would require looping through all the worksheets and inserting a lot of
redundant code, would it not? On the other hand, inserting a module sounds
simple enough, but can that one module get a button on each worksheet in the
workbook?

Adding a module is simple, so is inserting procs in those nodules, but
remember, each sheet has a class module automatically created with it that
can be used. In addition, you can add workbook events that can apply to all
sheets.

One procedure can create buttons on each worksheet, but why not create a
toolbar button instead, which can test what the activesheet is at runtime?

 

[deko]

No, I'm thinking nothing to do with the users, you can open that template
in

VBA, so you keep control.

Sounds interesting. But the Access app inserts new worksheets into an
existing workbook which is selected but the user at runtime.

Adding a module is simple, so is inserting process in those nodules, but
remember, each sheet has a class module automatically created with it that
can be used.

So I'm not constrained to use the worksheet module to get a button on the
sheet? That was my concern - i.e. inserting redundant code into 255
modules.

In addition, you can add workbook events that can apply to all
sheets.
hmmm...

One procedure can create buttons on each worksheet, but why not create a
toolbar button instead, which can test what the activesheet is at runtime?

A toolbar? Now that sounds like a winner. I'll look into it.

Thanks for your comments.