Assign Value to Module Level Variable

[Guest]

There are 8 VBA regular macros (macro1 to macro8) in a single module. Each
macro includes the following 3 statements:
..............................................
.....Dim myOptFile As String 'statement # 1
.....myOptFile = "Test.xls" 'statement # 2
.....Call myMacro (myOptFile) 'statement # 3
.............................................

All macros work fine.

Now, for easy maintenance, I moved the procedure declaration statement # 1
to the module Declarations section at the top, and deleted same from the 8
macros.
Then I left the assignment statement # 2 in one macro (macro1) and deleted
same from the other macros.

When I tried to run macro3(), I got "Run-time error '13': type mismatch",
with reference to myOptFile in the call statement.

Isn't that how one makes a variable visible to all procedures in a module ?
Declare the variable at the top, and assign it in any single procedure.

Thank you.

 

[Myrna Larson]

Isn't that how one makes a variable visible to all procedures in a module ?

Declare the variable at the top, and assign it in any single procedure.

Yes, it is. But then you typically don't use the variable as an argument in
any Sub definition line, and you don't use it in the Call statements that call
that sub. i.e. myMacro would look like this

Sub myMacro()

End Sub

and the call statements look like this:

Call myMacro

But then you should also modify each of the 8 macros to be sure that the
module-level variable has in fact been set and isn't an empty string.

 

[Nigel]

Could you not assign the file name to a Constant ?

Const myOptFile = "Test.xls"

 

[Gareth Roberts]

Depending on what you're doing with myOptFile you might want to declare it
as a string

Const myOptFile as string = "Test.xls"

Otherwise it's still technically a variant (as I understand) it and that
occasionally throws up type mismatch problems.

HTH,
Gareth

 

[Tushar Mehta]

You've discovered one of the many reasons why global variables should
be avoided. If I were in your shoes, in this case, I would use a
single global constant.

Const myOptFile as String = "Test.xls"
sub macro1
Call myMacro (myOptFile)
...
sub macro2
call myMacro (myOptFile)
...

--
Regards,

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

 

[ross]

yeah, you've got a bit mixed up.

try putting in a brake point at the line which cause the error. i bet
the variable
myOptFile, is = "", i.e. null. that's whey your gettthing the type
mismatch - your trying to save/open a workbook?

make sure you set the string befor it's used in macro 3.

good luck

Ross
www.mehtodsinexcel.co.uk

 

[Tom Ogilvy]

Might want to correct your sig:
"ross"

http://www.methodsinexcel.co.uk/

rather than

www.mehtodsinexcel.co.uk

 

[Guest]

Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level declaration:
.....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to) change the
string in the module Declaratios section!!

Why can't I use instead:
.....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is highlighted)

Thanks again.

 

[Tushar Mehta]

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is highlighted)

As the error message says, it's not a constant! ;-)

What I do is create a 'initialization' routine that is called first
thing out. It decides whether it has already been executed or not. If
not, it initializes the system.

sub Initialize(byref myOptFile as string)
static InitializationDone as boolean, _
InitializedFilename as string
if initializationdone then
myOptFile=InitializedFilename
'...other one-time-established values
else
InitializationDone=true
InitializedFilename=thisworkbook.name
'...establish other one-time-initialized values
end if
end sub
sub macro1
dim myOptfile as string
initialize myOptFile
'...other stuff
sub macro2
dim myOptfile as string
initialize myOptFile
'...other stuff

--
Regards,

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

 

[Chip Pearson]

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
.....Public Sub Workbook_Open()
.........myOptFile = ThisWorkbook.Name
.....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.

 

[Tom Ogilvy]

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why don't you
just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy

Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.

 

[Guest]

Tom;

Couple of points. First; my idea of using ThisWorkbook.Name is that I don't
have to change the myOptFile declaration each time I "Save As" the file.

Second; yes, there is a Private Sub Workbook_Open () event procedure in
ThisWorkbook module.

Chip in his response suggested to initialize the value of myOptFile in a
Public Sub Workbook_Open () event.
I can see clearly now the conflict between the Private and Public open
events!!

Perhaps, I should delete the Private Sub Workbook_Open () event and copy its
code to the Public Sub Workbook_Open () procedure. It might work, but it
might also cause a lot of headache !! The w/b has over 100 macros,
functions, events, private and public procedures, etc.

Is there an alternative to the event:
.....Public Sub Workbook_Open()
.........myOptFile = ThisWorkbook.Name
.....End Sub
so that I may leave everything else unchanged ?

Thank you.

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why don't you
just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy

Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.

 

[Guest]

Tushar;

Excellent suggestion! Will try it shortly.

Is it allowed to use:
.....Const myOptFile As String = "Test.xls"
in the module Declarations section, and at the same time declare locally:
.....Dim myOptFile As String

Chip has suggested to initialize myOptFile in a Public Sub Workbook_Open()
event. There is a conflict, however, between an existing Private w/b Open
event and the suggested Public w/b Open event! Please review my replies to
Chip and Tom.

Thank you.

 

[Tom Ogilvy]

Any where you can use myOptFile, use ThisWorkbook.Name instead and you
won't need myOptfile

There should be one procedure named Workbook_Open.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of code.

--
Regards,
Tom Ogilvy

Tom;

Couple of points. First; my idea of using ThisWorkbook.Name is that I don't
have to change the myOptFile declaration each time I "Save As" the file.

Second; yes, there is a Private Sub Workbook_Open () event procedure in
ThisWorkbook module.

Chip in his response suggested to initialize the value of myOptFile in a
Public Sub Workbook_Open () event.
I can see clearly now the conflict between the Private and Public open
events!!

Perhaps, I should delete the Private Sub Workbook_Open () event and copy its
code to the Public Sub Workbook_Open () procedure. It might work, but it
might also cause a lot of headache !! The w/b has over 100 macros,
functions, events, private and public procedures, etc.

Is there an alternative to the event:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub
so that I may leave everything else unchanged ?

Thank you.

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why don't you
just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy

Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.



:

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.

 

[Tushar Mehta]

Not that this helps the OP, but this is a classic case where I miss the
ability to create compile-time definitions (a la C or other languages).

Using thisworkbook.name masks the intent behind the token. If one
could
Define myOptFile=Thisworkbook.name: End Define
it makes it easier to understand and maintain the code.

Given that this is VBA, I would consider a one line function:
function myOptFile() as String:myOptFile=ThisWorkbook.name:End function

Might burn a few more CPU cycles but it sure would save a major
headache in the future.

A similar technique is also very useful to export constants from a
class module. public const ... is not allowed in a class module. So,
I use
Property Get cX() As Integer: cX = 1: End Property

with no corresponding Property Let

--
Regards,

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

 

[Guest]

Tom;

Since other work books may or may not be opened by other modules in the same
work book, it is much safer to limit the use of ThisWorkbook.Name to this
module in this work book.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of code.

Just to make sure I understand your suggestion. Are you suggesting that I
put the statement:
.....myOptFile = ThisWorkbook.Name
in the Private Sub Workbook_Open ()
and delete the Public Sub Workbook_Open () event ??

Keep in mind that at the module level there is the declaration:
.....Public myOptFile As String

Thanks again.

Any where you can use myOptFile, use ThisWorkbook.Name instead and you
won't need myOptfile

There should be one procedure named Workbook_Open.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of code.

--
Regards,
Tom Ogilvy

Tom;

Couple of points. First; my idea of using ThisWorkbook.Name is that I don't
have to change the myOptFile declaration each time I "Save As" the file.

Second; yes, there is a Private Sub Workbook_Open () event procedure in
ThisWorkbook module.

Chip in his response suggested to initialize the value of myOptFile in a
Public Sub Workbook_Open () event.
I can see clearly now the conflict between the Private and Public open
events!!

Perhaps, I should delete the Private Sub Workbook_Open () event and copy its
code to the Public Sub Workbook_Open () procedure. It might work, but it
might also cause a lot of headache !! The w/b has over 100 macros,
functions, events, private and public procedures, etc.

Is there an alternative to the event:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub
so that I may leave everything else unchanged ?

Thank you.

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why don't you
just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy


Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.



:

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.

 

[Guest]

Tushar;

Are there restrictions on where you put the one line function ? (I never
used one line functions before!!), and
Would you still have the module-level declaration:
.....Dim myOptFile As String ? (I suppose you would!!).

Thank you for your tremendous help.

 

[Tom Ogilvy]

Why do you want to use anything but thisworkbook.name at all?

 

[Tom Ogilvy]

Guess you don't understand that thisworkbook is a built in defined
"constant" that will refer to the workbook running the code no matter which
module it is used it - it can be used in all modules and all procedures. If
you want to duplicate that constant with another constant for whatever
reason, go ahead, but in my opinion your are duplicationg what already
exists and would cause none of the problems you are having.

Activeworkbook is another story, but we aren't talking about activeworkbook.

--
Regards,
Tom Ogilvy

Tom;

Since other work books may or may not be opened by other modules in the same
work book, it is much safer to limit the use of ThisWorkbook.Name to this
module in this work book.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of

code.

Just to make sure I understand your suggestion. Are you suggesting that I
put the statement:
....myOptFile = ThisWorkbook.Name
in the Private Sub Workbook_Open ()
and delete the Public Sub Workbook_Open () event ??

Keep in mind that at the module level there is the declaration:
....Public myOptFile As String

Thanks again.

Any where you can use myOptFile, use ThisWorkbook.Name instead and you
won't need myOptfile

There should be one procedure named Workbook_Open.

If you still want to use your variable, put your lines of code in the
original workbook_Open and delete the one that has only your lines of code.

--
Regards,
Tom Ogilvy

Tom;

Couple of points. First; my idea of using ThisWorkbook.Name is that I don't
have to change the myOptFile declaration each time I "Save As" the file.

Second; yes, there is a Private Sub Workbook_Open () event procedure in
ThisWorkbook module.

Chip in his response suggested to initialize the value of myOptFile in a
Public Sub Workbook_Open () event.
I can see clearly now the conflict between the Private and Public open
events!!

Perhaps, I should delete the Private Sub Workbook_Open () event and

copy
its

code to the Public Sub Workbook_Open () procedure. It might work, but it
might also cause a lot of headache !! The w/b has over 100 macros,
functions, events, private and public procedures, etc.

Is there an alternative to the event:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub
so that I may leave everything else unchanged ?

Thank you.


:

That means you have two procedures nameed Workbook_Open in that module.
Normally, when you first open the thisworkbook module, excel creates a
workbook_Open event for you. Apparently you created your own after that.

Also, if your going to use ThisWorkbook.Name in a variable, why

don't
you

just skip the middleman and refer to it directly in your code.

--
Regards,
Tom Ogilvy


Chip;

Thank you for your suggestion. In a regular module Declaration section, I
declare:
....Public myOptFile As String
and I added in ThisWorkbook module:
....Public Sub Workbook_Open()
........myOptFile = ThisWorkbook.Name
....End Sub

The following error appeares when I open the file:
"Compile error: Ambiguous name detected: workbook_open"
and Public Sub Workbook_Open() is highlighted.



:

Code must execute within a procedure -- code cannot exist outside
a procedure. You could declare a module level Public variable
(not a constant) and initialize its value in the workbook's Open
procedure.

'[in a regular code module]
Public myOptFile As String
'[in the ThisWorkbook code module]
Public Sub Workbook_Open()
myOptFile = ThisWorkbook.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Myrna::ross::Nigel::Gareth::Tushar;

Thank you all for your helpful responses. The modul-level
declaration:
....Const myOptFile As String = "Test.xls"
does solve the problem.

If I save the file as Test5.xls, then I've to (remember to)
change the
string in the module Declaratios section!!

Why can't I use instead:
....Const myOptFile As String = ThisWorkbook.Name
(Compile error: Constant expression required, and .Name is
highlighted)

Thanks again.