Assign Value to Module Level Variable

[Guest]

Here are the two (simplest!) versions that work:

A) variable myOptFile declared / assigned at the module-level

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

B) variable myOptFile assigned to ThisWorkbook.Name

.....Public myOptFile As String
------------------------------------------------------
Sub macro1 ()
....................................
.....Call myMacro(myOptFile)
...................................
End Sub
------------------------------------------------------
Sub myMacro (OptFile)
............................................
.....Windows( OptFile ).Activate
............................................
End Sub
------------------------------------------------------
Private Sub Workbook_Open()
............................................
.....myOptFile = ThisWorkbook.Name
............................................
End Sub


Thank you all for your tremendous help.

 

[Tushar Mehta]

It may not matter in this discussion, but thisworkbook.name might be
used in different contexts, for example to locate an add-in and to
support persistence. Future maintenance would have to differentiate
between the two uses. Defining (or using a function or a variable)
makes the distinction clear.

A long time ago I worked on a program used by a bank. 12 was used for
months of year and for items per screen. The bank wanted the latter
changed. Guess how long it took to wade through the 3,000+ line
program trying to figure out which 12 was months-per-year and which was
items-per-screen?

The original developer could have made life so much easier by using
Months-per-year=12 and Items-per-screen=12. In fact, maintenance would
have become trivial.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Tushar's suggestion of using a function to define the variable myOptFile also
works fine with no side effects of type mismatch or the need for Public
declaration or w/b Open event:

C) variable myOptFile assigned to ThisWorkbook.Name in a function

Sub macro1 ()
....................................
.....Call myMacro(myOptFile)
...................................
End Sub
------------------------------------------------------
Sub myMacro (OptFile)
............................................
.....Windows( OptFile ).Activate
............................................
End Sub