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.

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.

On Sat, 19 Mar 2005 18:53:02 -0800, "monir"
wrote:

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.

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

Might want to correct your sig:
"ross"

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

rather than

www.mehtodsinexcel.co.uk

--
Regards,
Tom Ogilvy

"ross" wrote in message
om...
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

Could you not assign the file name to a Constant ?

Const myOptFile = "Test.xls"



--
Cheers
Nigel



"monir" wrote in message
...
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.

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

"monir" wrote in message
...
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.

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

In article ,
says...
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::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.

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



"monir" wrote in message
...
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.

Chip;

Thank you for your suggestion. In a regular module Declaration section, I
decla
....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.



"Chip Pearson" wrote:

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



"monir" wrote in message
...
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.

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


"monir" wrote in message
...
Chip;

Thank you for your suggestion. In a regular module Declaration section, I
decla
....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.



"Chip Pearson" wrote:

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



"monir" wrote in message
...
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.

In article ,
says...
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

In article ,
says...
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;

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.



"Tushar Mehta" wrote:

In article ,
says...
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

In article ,
says...
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.