Solved: Running Module from Other Workbook

[magelan]

Hi All,

I have two workbooks, Workbook A and Workbook B.

Workbook A has a bunch of code..and at a certain point in time, I want to call up workbook B.

Here's what I have

bookB is a Workbook that is the currently open Workbook B
Book A has some code that looks like such...

Application.Run ("bookB.xlsm!runMyCode")

However, I want to replace the "bookB.xlsm" with a variable... is this possible?

Can i just replace the whole thing with a string?


dim myString as string
mystring = "bookB.xlsm!runMyCode,arg1,arg2,etc"
application.run(mystring)

[Bob Phillips]

Did you try

dim myString as string
Dim myBook As String
myBook = "bookB.xlsm"
mystring = myBook & "!runMyCode,arg1,arg2,etc"
application.run(mystring)

[magelan]

So i've tried it with the string now...

in Book A i've got...

Sub startSpecialRules()
Dim myString As String
myString = ruleBook.Name & "!RulesMain.runMyRules,stateNum,stateList,inputBook"
Application.Run (myString)
End Sub

this code will run as long as I dont have any arguments.Having it just be rulebook.Name & "!RulesMain.runMyRules" works, but if i add arguments to it, it breaks.

This is BookB

from within a module called RulesMain.,..
Sub runMyRules(stateNum As Integer, stateList() As String, inputBook As Workbook)
MsgBox "Hi" & "StateNum: " & stateNum
End Sub

[Kenneth Hobs]

You need to assign the values for the parameter names that you set before using them in a Run.

Note the limitation of the Run command from the help:
The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.


So, rather than passing a workbook object for example, change your routine to use a string for the workbook object and create the object in your run routine.




Public Sub Main()
Dim FunctionName As String, Result As Double
FunctionName = "Fun2" ' Selected function
Result = Application.Run("'" & ThisWorkbook.FullName & "'!Module1." & FunctionName)
MsgBox Result
End Sub

Public Function Fun1() As Double
Fun1 = 1
End Function

Public Function Fun2() As Double
Fun2 = 2
End Function

Public Function Fun3() As Double
Fun3 = 3
End Function

[snb]

In Book2

Sub tst4()
Application.Run "Book1!Sheet1.tst", "new"
End Sub


in Book1


Sub tst(c00)
MsgBox c00
End Sub

[magelan]

In Book2

Sub tst4()
Application.Run "Book1!Sheet1.tst", "new"
End Sub

in Book1


Sub tst(c00)
MsgBox c00
End Sub


Changed mine to...


Dim myString As String
Dim bookName As String
bookName = inputBook.Name
myString = ruleBook.Name & "!RulesMain.runMyRules"
Application.Run myString, stateNum, stateList, inputBook.Name


It turns out you are DEFINITELY NOT Supposed to put the ENTIRE run command in the string, otherwise it literally looks for a macro called "MacroName,Arg1,Arg2,Arg3" instead of a macro called MacroName with arguments arg1,arg2...

Just putting those commas in there and separating out the variables totally fixed it [as well as the hint to convert workbook to a name. I can reference it by the name easy enough]

Thanks!
[cant find the solved button.... if someone wants to mark it ]

[snb]

or

- you need the name of the file (not it's fullname)
- you need the codename of the sheet (not it's name)
- concatenate the workbookname to the sheetname with a exclamationmark
- concatenate the sheet's codename to the macroname with a dot
- separate each argument by a comma

Sub tst()
Application.Run Workbooks(3).Name & "!" & Sheets("Blad1").CodeName & ".tst", "nieuw"
End Sub

[GTO]

You need to assign the values for the parameter names that you set before using them in a Run.

Note the limitation of the Run command from the help:

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to
values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the
Run method.

So, rather than passing a workbook object for example, change your routine to use a string for the workbook object and create
the object in your run routine.


@Kenneth Hobs:

Hi Kenneth :-)

Humbly and of course respectfully, I hope I may take that bit to task. I do not believe the help topic is quite accurate.
In short - arguments passed by Application.Run do appear to be passed by value; but the bit about objects being passed are
actually converted and we are only passing the current return of the object's Value Property appear to be BS.

By example (zip attached, but just in case attachments are lost in the future):

Five workbooks, named: 'Child01.xls', 'Child 01.xls', 'Child 02.xls', 'Child 03.xls', and 'Parent.xls'.

NOTES: (1)'Child01.xls' is not used in the below, but simply suggested, to show that the single apostrophes can be included
when not needed; thus - a good "default" method of string building. (2)'Child01.xls' and 'Child 01.xls' would be identical
in having a value in Sheet1.Cells(2,1). (3)All workbooks would be default 97-2003 types, that is - three sheets with both
Worksheet Names and CodeNames of 'Sheet1', 'Sheet2' and 'Sheet3'.

In Parent.xls:

A Standard Module, named: Module1
Option Explicit

Sub example()
Dim wb As Workbook
Dim wks As Worksheet
Dim dblValReturned As Double
Dim sWBName As String
Dim sArg As String
Dim fNeed2CloseWB As Boolean

'// Clear from any previous run. //
Sheet1.Range("A2").Clear

sWBName = "Child01.xls"

'// See if we need to open (and close) the workbook (wb), or just use it if already //
'// open. //
If WB_ExistsInCollection(sWBName) Then
Set wb = Workbooks(sWBName)
Else
If Not Dir(ThisWorkbook.Path & "\" & sWBName) = vbNullString Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & sWBName, , True)
fNeed2CloseWB = True
Else
Exit Sub
End If
End If

'// Get our base string for providing the procedure to run. //
sArg = "'" & wb.Name & "'!"

'// Tack in the name of the procedure to our base string, and include an object (in //
'// this case, a worksheet) to be passed. Here is where it gets neat, as we are //
'// not actually passing a copy of the object's Value property, but a copy of the //
'// the pointer to the object. Thus - changes to the object's propert(y|ies) CAN //
'// actually be propagated back to the object. That is to say, we are not passing //
'// pointer back-and-forth, but irregardless, a copy of the pointer will do the same//
'// thing, as the object's properties are still accessible. Please note as further //
'// evidence (using the word 'evidence' a bit loosely), that we are calling a Sub //
'// procedure, and still getting the value plunked back into ThisWorkbook's Sheet1. //
Application.Run sArg & "GetValue", Sheet1

If fNeed2CloseWB Then
wb.Close False
fNeed2CloseWB = False
End If

'========================================================================== =
' Round Two

sWBName = "Child 02.xls"

If WB_ExistsInCollection(sWBName) Then
Set wb = Workbooks(sWBName)
Else
If Not Dir(ThisWorkbook.Path & "\" & sWBName) = vbNullString Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & sWBName, , True)
fNeed2CloseWB = True
Else
Exit Sub
End If
End If

sArg = "'" & wb.Name & "'!"
'// Now we pass the sheet as an object, and a value. Note that the called //
'// procedure's parameters do not seem to matter, as to whether the passed arg is //
'// "supposed" to be by value or ref. //
Application.Run sArg & "GetValue2", Sheet1, 2

If fNeed2CloseWB Then
wb.Close False
fNeed2CloseWB = False
End If

'========================================================================== =
' Final Round (Okay, a short fight I admit)

sWBName = "Child 03.xls"

If WB_ExistsInCollection(sWBName) Then
Set wb = Workbooks(sWBName)
Else
If Not Dir(ThisWorkbook.Path & "\" & sWBName) = vbNullString Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & sWBName, , True)
fNeed2CloseWB = True
Else
Exit Sub
End If
End If

sArg = "'" & wb.Name & "'!"

'// Pass a copy of the pointer to ThisWorkbook, and this time, actually return a //
'// value as is probably more "traditional", by calling a Function //
dblValReturned = Application.Run(sArg & "PassWorkbook", ThisWorkbook)

If fNeed2CloseWB Then
wb.Close False
fNeed2CloseWB = False
End If

MsgBox dblValReturned

End Sub

Function WB_ExistsInCollection(WBName As String) As Boolean
On Error GoTo ReturnFalse
WB_ExistsInCollection = WBName = Workbooks(WBName).Name
Exit Function
ReturnFalse: WB_ExistsInCollection = False
End Function

In Child 01.xls (or Child01.xls):

A Standard Module, named: Module1

Option Explicit

Public Sub GetValue(ByVal wks As Worksheet)
wks.Cells(2, 1).Value = Sheet1.Cells(2, 1).Value
End Sub

In Child 01 (or Child01), Sheet1, A2, enter a value of '4'.

Note that we passed ByVal, actually a copy of the pointer I believe.

In Child 02.xls:

A Standard Module, named: Module1

Option Explicit

Public Sub GetValue2(ByVal wks As Worksheet, ByRef Multiplier As Long)
MsgBox (wks.Cells(2, 1).Value * Sheet1.Cells(2, 1).Value) * Multiplier
End Sub

In Child 02, Sheet1, A2, enter the value: 25

Note that we are supposedly demanding ByRef in the second parameter, but nothing falls over.

In Child 03.xls:

A Standard Module, named: Module1

Option Explicit
Option Private Module

Private Function PassWorkbook(ByRef wb As Workbook) As Double
Dim wks As Worksheet
Dim shtSheet1 As Worksheet

For Each wks In wb.Worksheets
If wks.CodeName = "Sheet3" Then
Set shtSheet1 = wks
Exit For
End If
Next

If Not shtSheet1 Is Nothing Then
PassWorkbook = shtSheet1.Range("A1").Value * 3.333
End If
End Function

In 'Child 03.xls', Sheet3 <---Note: Sheet3 this time, enter the value: 3.333

Note that whilst we are calling a Function this time, we are calling a Private Function, residing in a Private Module, in
another wb. Regardless, Application.Run will get to it :-) Further - we "demanded" ByRef in the parameter, but nothing
falls over, and in my opinion, we certainly show that the object (workbook in this case) is fully accessible, as we are now
looping through worksheets until we find the right one. As to where/how the called procedure asking for the passed arg as by
reference does not fall over, I am currently believing (admittedly in a most superstitious manner) is on the part of Run, but
I have no idea how.

Anyways, I think that you will find this interesting, so here are some links that I was able to recall:

http://www.tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_method.shtml

http://www.tushar-mehta.com/excel/vba/xl%20objects%20and%20procedures%20with%20arguments.htm

http://support.microsoft.com/kb/555159

http://support.microsoft.com/default.aspx?scid=kb;en-us;244075

Mark

[snb]

@GTO

I used your 'parent.xls' and 'child01.xls'

The macro I ran in Parent.xls is:


Sub M_snb_example()
c00 = "Child01.xls"
ThisWorkbook.Sheets("sheet1").Cells(2, 1).ClearContents
If IsError(Evaluate("'[child01.xls]sheet1'!A1")) Then Workbooks.Open ThisWorkbook.Path & "\" & c00
Application.Run c00 & "!GetValue", Sheet1
Application.Run c00 & "!GetValue", Sheet1.Name
'========================================================================== =

Application.Run c00 & "!GetValue1", Sheet1, 2
Application.Run c00 & "!GetValue1", Sheet1.Name, 8

'========================================================================== =
MsgBox Application.Run(c00 & "!PassWorkbook", ThisWorkbook)
MsgBox Application.Run(c00 & "!PassWorkbook", ThisWorkbook.Name)
End Sub

What I called in macromodule 1 in Child01.xls:

Sub GetValue(wks)
If TypeName(wks) = "String" Then
Sheets(wks).Cells(2, 1).Value = Sheet1.Cells(2, 1).Value
Else
wks.Cells(2, 1).Value = Sheet1.Cells(2, 1).Value
End If
End Sub

Sub GetValue1(wks, Multiplier)
If TypeName(wks) = "String" Then
MsgBox Sheets(wks).Cells(2, 1).Value * Sheet1.Cells(2, 1).Value * Multiplier
Else
MsgBox wks.Cells(2, 1).Value * Sheet1.Cells(2, 1).Value * Multiplier
End If
End Sub

Private Function PassWorkbook(wb)
PassWorkbook = 0
If TypeName(wb) = "String" Then
For Each sh In Workbooks(wb).Sheets
If sh.CodeName = "Sheet3" Then PassWorkbook = sh.Range("A1").Value * 3.333
Next
Else
For Each sh In wb.Sheets
If sh.CodeName = "Sheet3" Then PassWorkbook = sh.Range("A1").Value * 200
Next
End If
End Function