I am attempting to pass a variable value from one module to another module in
another worksheet.

I first collect the values based on user input in macro 1and then call
macro2 from my "PERSONAL.xls" file to continue processing. Unfortunately, I
need to capture some of the data which was used in the first macro and pass
it along to the 2nd macro.

I've declared the variables "up top" as follows:

Option Explicit
Public xlfile_drive As String
Public period_end As String
Public open_file As String
Public open_file_name As String
Public continueprompt As String

Sub Create_Return_File(xlfile_drive, period_end, open_file, open_file_name,
continueprompt)



periodend = InputBox("Enter reporting month-end in this sample format:
2007-Aug")
xlfile_drive = "T:\mktg\star keystone reporting\Keystone\" & periodend &
"\"
ChDir xlfile_drive
open_file_name = "ROR Series ATC and Models Report-" & Right(periodend,
3) & "-" & Mid(periodend, 3, 2) & ".xls"
open_file = xlfile_drive & open_file_name
....

Further down, I then call the 2nd macro as follows:

Call Add_EPL_Data
....
Public Sub Add_EPL_Data(open_file_name, open_file)
Application.Run "'Personal.xls'!Add_EPL_Returns"
End Sub

Still, the macro is not working and is giving me the error message:

Compile error -- variable not defined.

I had thought, however, that defining it at the global level outside the
subprocedure would resolve this problem?

First off, you have a scoping problem. When you put names on the Sub line,
you are defining those variables for that sub. since they have the same
names as the public variables, instide that sub you reference the local ones
and in other subs you reference the public global ones. This will obviously
cause you problems.

Second, you define the sub "Add_EPL_Data" to have 2 parameters but you don't
pass anything when you call it.

Third, how is Add_EPL_Data defined in personals? Does it have parameters?
If it is defined the same as this one, then remove the local funcation.

When you call "Create_Return_File" do you pass in the parameters?

I suspect what you want is something like this:
Option Explicit
Public xlfile_drive As String
Public period_end As String
Public open_file As String
Public open_file_name As String
Public continueprompt As String

Sub Create_Return_File
dim xlfile_drive as String
dim period_end as String
dim open_file as String
dim open_file_name as String
dim continueprompt as String



periodend = InputBox("Enter reporting month-end in this sample format:
2007-Aug")
xlfile_drive = "T:\mktg\star keystone reporting\Keystone\" & periodend &
"\"
ChDir xlfile_drive
open_file_name = "ROR Series ATC and Models Report-" & Right(periodend,
3) & "-" & Mid(periodend, 3, 2) & ".xls"
open_file = xlfile_drive & open_file_name
....

Further down, I then call the 2nd macro as follows:

Call Add_EPL_Data open_file_name, open_file
....

Peter Richardson

"Eric_G" wrote:

I am attempting to pass a variable value from one module to another module in
another worksheet.

I first collect the values based on user input in macro 1and then call
macro2 from my "PERSONAL.xls" file to continue processing. Unfortunately, I
need to capture some of the data which was used in the first macro and pass
it along to the 2nd macro.

I've declared the variables "up top" as follows:

Option Explicit
Public xlfile_drive As String
Public period_end As String
Public open_file As String
Public open_file_name As String
Public continueprompt As String

Sub Create_Return_File(xlfile_drive, period_end, open_file, open_file_name,
continueprompt)



periodend = InputBox("Enter reporting month-end in this sample format:
2007-Aug")
xlfile_drive = "T:\mktg\star keystone reporting\Keystone\" & periodend &
"\"
ChDir xlfile_drive
open_file_name = "ROR Series ATC and Models Report-" & Right(periodend,
3) & "-" & Mid(periodend, 3, 2) & ".xls"
open_file = xlfile_drive & open_file_name
...

Further down, I then call the 2nd macro as follows:

Call Add_EPL_Data
...
Public Sub Add_EPL_Data(open_file_name, open_file)
Application.Run "'Personal.xls'!Add_EPL_Returns"
End Sub

Still, the macro is not working and is giving me the error message:

Compile error -- variable not defined.

I had thought, however, that defining it at the global level outside the
subprocedure would resolve this problem?

I haven't examined the workings of your code yet, but you are getting
a Variable Not Defined error because you have a variable named wrong,
which is not allowed when using Option Explicit. You declared
variable period_end with an underscore, but in your code you are using
periodend with no underscore.
Eric_G wrote:
I am attempting to pass a variable value from one module to another module in
another worksheet.

I first collect the values based on user input in macro 1and then call
macro2 from my "PERSONAL.xls" file to continue processing. Unfortunately, I
need to capture some of the data which was used in the first macro and pass
it along to the 2nd macro.

I've declared the variables "up top" as follows:

Option Explicit
Public xlfile_drive As String
Public period_end As String
Public open_file As String
Public open_file_name As String
Public continueprompt As String

Sub Create_Return_File(xlfile_drive, period_end, open_file, open_file_name,
continueprompt)



periodend = InputBox("Enter reporting month-end in this sample format:
2007-Aug")
xlfile_drive = "T:\mktg\star keystone reporting\Keystone\" & periodend &
"\"
ChDir xlfile_drive
open_file_name = "ROR Series ATC and Models Report-" & Right(periodend,
3) & "-" & Mid(periodend, 3, 2) & ".xls"
open_file = xlfile_drive & open_file_name
...

Further down, I then call the 2nd macro as follows:

Call Add_EPL_Data
...
Public Sub Add_EPL_Data(open_file_name, open_file)
Application.Run "'Personal.xls'!Add_EPL_Returns"
End Sub

Still, the macro is not working and is giving me the error message:

Compile error -- variable not defined.

I had thought, however, that defining it at the global level outside the
subprocedure would resolve this problem?

nice catch. I missed that part of the problem

"JW" wrote:

I haven't examined the workings of your code yet, but you are getting
a Variable Not Defined error because you have a variable named wrong,
which is not allowed when using Option Explicit. You declared
variable period_end with an underscore, but in your code you are using
periodend with no underscore.
Eric_G wrote:
I am attempting to pass a variable value from one module to another module in
another worksheet.

I first collect the values based on user input in macro 1and then call
macro2 from my "PERSONAL.xls" file to continue processing. Unfortunately, I
need to capture some of the data which was used in the first macro and pass
it along to the 2nd macro.

I've declared the variables "up top" as follows:

Option Explicit
Public xlfile_drive As String
Public period_end As String
Public open_file As String
Public open_file_name As String
Public continueprompt As String

Sub Create_Return_File(xlfile_drive, period_end, open_file, open_file_name,
continueprompt)



periodend = InputBox("Enter reporting month-end in this sample format:
2007-Aug")
xlfile_drive = "T:\mktg\star keystone reporting\Keystone\" & periodend &
"\"
ChDir xlfile_drive
open_file_name = "ROR Series ATC and Models Report-" & Right(periodend,
3) & "-" & Mid(periodend, 3, 2) & ".xls"
open_file = xlfile_drive & open_file_name
...

Further down, I then call the 2nd macro as follows:

Call Add_EPL_Data
...
Public Sub Add_EPL_Data(open_file_name, open_file)
Application.Run "'Personal.xls'!Add_EPL_Returns"
End Sub

Still, the macro is not working and is giving me the error message:

Compile error -- variable not defined.

I had thought, however, that defining it at the global level outside the
subprocedure would resolve this problem?