Running code from a different file (VBA / Excel 2000 / SP3)

Topic

New Reply

Good day all
I have a two part problem. Firstly, the following code resides in Procedure B which is Called from Procedure A, and attempts to run code from a workbook that is open, but not ThisWorkbook. The same external procedure is run twice. First successfully with a string of text, then unsuccessfully with a variable that reproduces the same string. On the second attempt to run the external procedure, the program exits Procedure B and falls back to Procedure A with no indication that it has processed the external procedure. The external procedure is little more than a MsgBox.
Please folks; where am I going wrong?

Dim mysub As String
    Application.Run "ISS_GDC1.xls!v1About.About"
    mysub = """" & "ISS_GDC1.xls!v1About.About" & """"
    Application.Run mysub

In the event that it is possible to call an external procedure with a variable; Can arguments be passed in this manner? Can these arguments include arrays? Are there any Gotchas involved?

As ever,

Reply | Quote

Replies

The general syntax for adding arguments, as illustrated in the attached, is to tack them on, separated by commas. Are there gotchas? I’m sure there are, but I’m not aware of any collection of tips on this. (Many past threads, of course, but not a compilation.) I think it is at least worth popping up the online help for the .Run method (or any other method you’re trying for the first time) and reading through the discussion to see if there are limitations designed in that could affect your application. If you try something that seems as though it should work and it doesn’t work, then we can try that out and see if we can find the problem.

Reply | Quote

Incidentally, I get the same results for these; the macro is in a global add-in, which may be while I can omit the workbook name in my example:

Sub RunExternal()
Application.Run “wdFileSaveAs”, xlWorksheet
End Sub

Sub RunExternal()
Dim strMacro As String, strArgument As String
strMacro = “wdFileSaveAs”
strArgument = CStr(xlWorksheet)
Application.Run strMacro, strArgument
End Sub

But it does not work to try to pass a single string containing both the macro name and the first argument:

Sub RunExternal()
Dim strMacro As String, strArgument As String
strMacro = “wdFileSaveAs”
strArgument = CStr(xlWorksheet)
Application.Run strMacro & “, ” & strArgument ‘FAILS!
End Sub

Hope this helps.

Reply | Quote

Thank you Jefferson
As a self taught VBA user I’ve had a terrible teacher. But with the type of help provided by folk such as you and other “Lounge Regulars”; it’s all beginning to make a lot more sense.

Reply | Quote

Thank you Jefferson
As a self taught VBA user I’ve had a terrible teacher. But with the type of help provided by folk such as you and other “Lounge Regulars”; it’s all beginning to make a lot more sense.

Reply | Quote

Incidentally, I get the same results for these; the macro is in a global add-in, which may be while I can omit the workbook name in my example:

Sub RunExternal()
Application.Run “wdFileSaveAs”, xlWorksheet
End Sub

Sub RunExternal()
Dim strMacro As String, strArgument As String
strMacro = “wdFileSaveAs”
strArgument = CStr(xlWorksheet)
Application.Run strMacro, strArgument
End Sub

But it does not work to try to pass a single string containing both the macro name and the first argument:

Sub RunExternal()
Dim strMacro As String, strArgument As String
strMacro = “wdFileSaveAs”
strArgument = CStr(xlWorksheet)
Application.Run strMacro & “, ” & strArgument ‘FAILS!
End Sub

Hope this helps.

Reply | Quote

This should work:

Dim mysub As String, MyArg1 As String, MyArg2 as Integer
    Application.Run "ISS_GDC1.xls!v1About.About"
    mysub = "ISS_GDC1.xls!v1About.About"
    MyArg1 = "Argument1"
    MyArg2 = 1
    Application.Run mysub, MyArg1, MyArg2

Reply | Quote

Thank you Legare
My error was in attempting to include quotes as part of the text string. I was more tired than I thought.

Reply | Quote

Thank you Legare
My error was in attempting to include quotes as part of the text string. I was more tired than I thought.

Reply | Quote