Passing values to new app
 

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Passing values to new app
 

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)

"BrianG" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Passing values to new app
 

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" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Passing values to new app
 

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

--
BrianG


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!