Assign Value to Module Level Variable

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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
 
Back
Top