Your use of subroutines in your answer confuses me. I guess good VBA
programmers use them a lot. But after reading the VBA help section you
mentioned, I assume that all I need to do is something like:

Dim PublicstrSHORT_MGR_NAME as string

and that should make that variable available to all modules. Is that right?
Do I have to declare it in each module or just any module? If just one
module, does it have to be in Module 1, in order to be available in Module 2
and not vice versa (or are module names just names)?

In any event, I tried what I've suggested above and it didn't help. It
still is not recognized in Module 2. By the way the help section indicated
there should be a space after Public but when I tried that, the compiler
gave me syntax errors.

Please help, anyone!

Thanks,
Grace

"JE McGimpsey" wrote in message
...
The answer to your second question is easy, and you should look up
"Understanding Scope and Visibility" in XL/VBA Help. Variables declared
within a module are only visible within the module:

Public Sub foo()
Dim vTest As Variant
vTest = "test"
bar
End Sub

Public Sub bar()
MsgBox vTest
End Sub

In this case, bar() will display an empty message box. Incidentally,
this is a superb reason to put "Option Explicit" at the top of your
modules (choose Preference/Editor in the VBE, and check the Require
variable declaration checkbox to make this automatic). If you'd had
that, you would have gotten a compile error - variable not declared in
bar().

To pass variables on, you could declare them globally (at the top of the
module), which is a widely used technique, but one that sometimes gets
beginning coders in trouble when two procedures operate on the same
global.

Dim vTest As Variant

Public Sub foo()
vTest = "test"
bar
End Sub

Public Sub bar()
MsgBox vTest
End Sub

Or you could explicitly pass them as an argument:

Public Sub foo()
Dim vTest As Variant
vTest = "test"
bar vTest
End Sub

Public Sub bar(ByRef vTest2 As Variant)
MsgBox vTest
End Sub

this time, bar() will display "test" in the message box.


In article ,
"Grace" wrote:

I am sorry to repost this but it looks like too much time is passed and
the
solution never came. I have two statements that involve a string called
"strMGR_SHORT_NAME that are having problems. In the first case, the
statement is

fname = StrMGR_SHORT_NAME & Sheets("INPUTS").Range("B45").Value &
Sheets("INPUTS").Range("E11").Value & ".xls"

The compiler error message I am getting says "object variable or with
block
variable not set." I am pretty sure this statement had been working
but
somehow now is not. I have had it show me that variable, which is
entered
thru an Input Box and it is what it's supposed to be. But the command
is
not working. Does this error message tell anyone anything?

Then, this subroutine calls another subroutine where I use a similar
statement to try to open a file. In this case, the statement that is
crashing is:

myFilename = StrMGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value &
"SUMPRF" & ".L00"

It was not an XLS file and that caused some confusion but it now seems
evident that the real problem is that this same strMGR_SHORT_NAME is now
blank and so is not automatically being "seen' by the called subroutine.
So, the solution for this seems to be merely to pass the result of the
INPUTBox along and so the question is, shouldn't a calling subroutine
pass
its definitions along to a subroutine it calls? And, if not, how do I
get
it to do so?

Thanks,
Grace