Public Variables Declaration - VBA Excel

[monirg]

Please advise:

1. If a variable is declared above all procedures in one module, then it is available to all procedures in that module. (correct ?)

2. If a variable at one module level is declared as Public, then it is available to all procedures in all modules in the entire workbook. (correct ?)

3. Now, does it matter where you assign a value to a Public variable ?
....3.a) Can you assign the value in a w/s event ? (Y / N)
....3.b) Can you assign the value in a w/b event ? (Y / N)
....3.c) Can you assign the value in a standard module ? (Y / N)
....3.d) It doesn't really matter where you assign a value to a Public variable

4. For those who are familiar with FORTRAN, is there a similarity between the FORTRAN (blank or named) COMMON data Block and the VBA Public Variables Declaration ?

Thank you.

 

[monirg]

Hi;

A follow-up to my earlier question.

...Dim myOptFile As String

is declared at the top of a module which has 10 macros.

Where should I assign the value of variable "myOptFile" ??

If I assign "myOptFile" in macro4 and run macro7, I get the "Run-time error '13'!!

Thank you.

 

[Norie]

Can you post your actual code?

 

[shades]

Please advise:

1. If a variable is declared above all procedures in one module, then it is available to all procedures in that module. (correct ?)

Yes

2. If a variable at one module level is declared as Public, then it is available to all procedures in all modules in the entire workbook. (correct ?)

Yes

....3.d) It doesn't really matter where you assign a value to a Public variable

Just a note on declaring variables:

As John Walkenbach notes, "In general, local variables are the most efficient because VBA frees up the memory they use when the procedure ends." (Power Programming, p. 188)

 

[Zack Barresse]

Also, passing variables into different routines may be able to be accomplished in other ways as well. Some related discussion ...

http://www.vbaexpress.com/forum/showthread.php?t=1508

http://www.vbaexpress.com/forum/showthread.php?t=837

Shades: That last comment looks familiar.

 

[shades]

Yep, you can only teach an old dog a new trick once a year.

 

[monirg]

Norie: The actual code is quite long, but here is the layout of 2 procedures out of 10 in Module1.

Sub macro1()
............................
....Dim myOptFile As String
...................................
....myOptFile = "Test.xls"
....Call myMacro(myOptFile)
..................................
End Sub
------------------------------------------------------------------------------
Sub macro3()
............................
....Dim myOptFile As String
...................................
...................................
....myOptFile = "Test.xls"
....Call myMacro(myOptFile)
..................................
..................................
End Sub
------------------------------------------------------------------------------
Sub mymacro(OptFile)
...........................................
....Windows(OptFile).Activate
...........................................
End Sub
------------------------------------------------------------------------------

The above works fine!!!!

I moved the declaration Dim myOptFile As String to the top of the module, and deleted it from the individual macros. I left myOptFile = "Test.xls" in macro3 and deleted same from macro1. Nothing else was changed. So, the modified layout looks like:

....Dim myOptFile As String
------------------------------------------------------------------------------
Sub macro1()
...................................
....Call myMacro(myOptFile)
..................................
End Sub
------------------------------------------------------------------------------
Sub macro3()
...................................
...................................
....myOptFile = "Test.xls"
....Call myMacro(myOptFile)
..................................
..................................
End Sub
------------------------------------------------------------------------------
Sub mymacro(OptFile)
...........................................
....Windows(OptFile).Activate
...........................................
End Sub
------------------------------------------------------------------------------

Tried to run macro1(). I got "Run-time error '13': Type mismatch", and OptFile in the Windows statement highlighted (as shown above).

Shades: Thank you. Very helpful.

firefytr: Thank you. Will review the links.

Your suggestions would be greatly appreciated.

 

[Zack Barresse]

Try declaring what (name)type you are going to be using, such as ...

Sub mymacro (OptFile As String)
...........................................
    On Error Resume Next 'used if OptFile is not open.
    Windows(OptFile).Activate
    'optional if file is not already open
    Workbooks.Open "C:\Your path here\" & OptFile
...........................................
End Sub

 

[monirg]

firefytr;

The full path & file name, as suggested in your sample code, would be different each time I "Save As" the file.

A module-level declaration:
....Const myOptFile As String = "Test.xls"
and deleting all the relevant local declarations appears to be the solution! The program works fine (so far!).

A nice enhancement would be if I could somehow use ThisWorkbook.Name instead of "Test.xls" in the above module-level declaration.

Any suggestions ?

 

[just_jon]

Public FName As String
Public FPath As String

Sub foo()

FName = "Foobar!"
MsgBox FName
Call foo1

End Sub

Sub foo1()

MsgBox FName
FName = "More " & FName
FPath = ThisWorkbook.Path
Call foo2

End Sub

Sub foo2()

MsgBox FName
FName = ThisWorkbook.Name
MsgBox FName
MsgBox FPath

End Sub