Passing values to new app

[BrianG]

I have some public variables defined in an Excel97 macro which I would
like to pass to a different macro. They are "strTempFileName" and
"strTempFilePath". I use the following code to to start the new macro:

Application.OnTime.Now, "MacroFile2.xls!Module1"

Is there a way to pass the values to the new macro when I start it?

BrianG

 

[Bob Phillips]

Better to make the Onetime macro a macro in the same workbook, and then use
Application.Run from within this macro to fire the macro in the other
workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Brian,

Rather than use OnTime, use Run. This allows you to pass up to 29
parameters. E.g,

Application.Run "'Book3.xls'!AAA", "this is a test"

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[BrianG]

Thanks for the help! Two questions though...
1) Will Application.Run close the first workbook(I'm thinking that's why
I used .OnTime.Now)? Part of the purpose of the 2nd workbook macro is
to delete the macros and vb components in the first wookbook.
2)I'm confused as to whether I can pass the variable name as the
parameter of if I need to pass the value. Would this work...

Sub Main()
Dim strTempFileName as String
Dim strTempFilePath as String
strTempFileName = ActiveWorkbook.Name
strTempFilePath = Environ("temp")
..
..
..
Application.Run "'MacroFile2.xls'!Module1", strTempFileName_
,strTempFilePath
End Sub

In MacroFile2.xls
Sub Module1(strTempFileName as String, strTempFilePath as_ String)
..
..
..
End Sub