Executing VBA code from a string in another workbook

Question

I want to execute VBA code, where the code itself is built from a string at runtime

I already found out how to do this via this existing SO answer: How to run a string as a command in VBA

For ease of reference, the solution is as follows:

Sub Testing()
    StringExecute "MsgBox" & """" & "Job Done!" & """"
End Sub

Sub StringExecute(s As String)
    Dim vbComp As Object
    Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    vbComp.CodeModule.AddFromString "Sub foo()" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.Name & ".foo"
    ThisWorkbook.VBProject.VBComponents.Remove vbComp
End Sub

In a nutshell, it creates a new module within the same workbook; writes a sub according to the specified string; executes the sub; and then removes the module - all within the same runtime process. This works well.

However what if I want to perform the operation on any other workbook than ThisWorkbook? I'm trying the following modified code:

Option Explicit
Dim NewWB As Workbook

Sub Testing()
    Set NewWB = Workbooks.Add
    StringExecute "MsgBox" & """" & "Job Done!" & """"
End Sub

Sub StringExecute(s As String)
    Dim vbComp As Object
    Set vbComp = NewWB.VBProject.VBComponents.Add(1) ' Note the use of NewWB
    vbComp.CodeModule.AddFromString "Sub foo()" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.Name & ".foo" ' <-- Error on this line
    NewWB.VBProject.VBComponents.Remove vbComp
End Sub

As you can see, this code is identical but for the replacement of ThisWorkbook with NewWB. However it causes an error popup at the Application.Run line:

Run-time error '1004': Cannot run the macro 'Module1.foo'. The macro may not be available in this workbook or all macros may be disabled.

Does anyone know why this wouldn't work with a new workbook? I already tried adding a reference to VBA Extensibility to the NewWB via code after creating it (NewWB.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 0) - however this did not help.

`Application.Run` will be looking for the module in the workbook creating the new workbook. Try `application.run newwb.name & "!foo` — Nathan_Sav, Mar 21 '18 at 10:59

score 2 · Accepted Answer · edited Apr 02 '18 at 09:37

2

Application.Run will be looking for the module in the workbook creating the new workbook. Try (not fully tested) something along these lines:

application.run newwb.name & "!foo"

edited Apr 02 '18 at 09:37

Shadow The GPT Wizard

66,030
26
140
208

answered Mar 21 '18 at 11:01

Nathan_Sav

8,466
2
13
20

Thanks. You posted at the same time as me! If you add a close quote to your answer, I'll accept it. – Chris Melville Mar 21 '18 at 11:04
1

@Chris quote has been added. – Shadow The GPT Wizard Apr 02 '18 at 09:38

score 0 · Answer 2 · answered Mar 21 '18 at 11:02

Would you believe it - after 18 hours of scratching my head over this, I post a SO question and then within 5 minutes I realise the answer myself.

The problem was with the syntax of Application.Run. Apparently, it can be used in the format [ModuleName].[ProcedureName] when running a procedure in the calling workbook; but to specify another workbook, the syntax should be [WorkbookName]![ProcedureName].

So all I had to do was change the line to read:

Application.Run NewWB.Name & "!foo"

I thought that instead of deleting my question I should just answer it :)

Executing VBA code from a string in another workbook

2 Answers2