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.

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.

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

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.

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.

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.


"Tom Ogilvy" wrote:

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.

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.

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

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


"Tom Ogilvy" wrote:

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.

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

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

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.




"Tom Ogilvy" wrote:

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

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


"Tom Ogilvy" wrote:

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.

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.


"Tushar Mehta" wrote:

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

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

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

--
Regards,
Tom Ogilvy

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

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

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

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




"Tom Ogilvy" wrote:

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

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


"Tom Ogilvy" wrote:

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.

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

In article ,
says...
Why do you want to use anything but thisworkbook.name at all?

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
------------------------------------------------------
Function myOptFile() As String : myOptFile = ThisWorkbook.Name : End Function

Thanks again.



"monir" wrote:

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.