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.