Re: Still filename problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Grace (Whooshbopbang4_at_adelphia.net)
Date: 06/13/04 

Date: Sun, 13 Jun 2004 08:26:25 -0700

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" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-1B6028.22112312062004@msnews.microsoft.com...
> 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 <VK2dnUpBeIOYUFbdRVn-ug@adelphia.com>,
> "Grace" <Whooshbopbang4@adelphia.net> 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

Relevant Pages

  • Err.Raise + Call Stack + UserForm
    ... its way back to the top-most subroutine. ... Public Sub Class_Initialize ... On Error GoTo ErrorHandler ... Dim lErrNum As Long ...
    (microsoft.public.word.vba.general)
  • RE: Message appear while Access is thinking/working
    ... OpenMyMessage subroutine. ... I have a form that contains 3 controls, a label, and two ... Optional DisplayStatus As Boolean = False) ... Public Sub MyMessageStatus ...
    (microsoft.public.access.formscoding)
  • Efficient use of sheet "codenames"
    ... Suppose I define the following subroutine in one of my VBA modules: ... invoiceDate = Sheet1.Range.Value ... Public Sub DoCalculations ... The revised routine above will accept a sheet codename in string ...
    (microsoft.public.excel.programming)
  • Re: problems passing form object to a subroutine
    ... the subroutine is public and is a separate module, i don't know if this has ... >> I would like to make a subroutine that I can pass the form object to ... >> Public Sub SetOrderLineFields(frm As Variant) ...
    (microsoft.public.access.formscoding)
  • RE: Absolutely Crazy Error
    ... you must explicitly declare each variable's type. ... 'Set the percentage label ... frm.InsideHeight = WaitMessageHeight ... Public Sub Increment_Wait_Percentage ...
    (microsoft.public.access.formscoding)