Bill,

I don't think you can do that. But using a public variable is easy, and achieves the same thing, if
you only set the value once per macro run.

HTH,
Bernie
MS Excel MVP


"Bill Martin" wrote in message ...
I have VBA where I want to define a cell address that I use throughout the macro, as a constant up
at the top. This makes the code neater, but more importantly makes it easier to make global
changes without forgetting something, and in conjunction with "Explicit" makes it harder to mistype
something. A routine programming practice.

What I have used up to now, and which works well, is something like:

CONST TargetCell As String = "Sheet3!A1"

The thing is that I'm now using this particular macro from various places. As a result, I'd like
to set the CONST based on the active sheet name when the macro is fist called. After setting the
CONST it would not change of course.

I've tried variations on:

CONST TargetCell As String = ActiveSheet.Name & "!A1"

VBA apparently does not like to base a constant value on something that may change later and
refuses to cooperate. After a lot of futzing around I ended up defining it as a string variable
rather than a constant and VBA allows that.

Just as a matter of programming practice though I'd rather use a constant. Is there some way to
persuade VBA to create a constant based on the value of a variable at the moment the constant is
created?

Thanks...

Bill