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