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

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

Bernie Deitrick wrote:
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

----------------------

Ok, thanks. I'll go ahead and use the variable. Some programming languages run
a bit more efficiently with a constant than a variable, but I must admit that
isn't really a factor in this case. It's more just my own mule headedness at issue.

Thanks...

Bill

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?

No, there isn't. A constant means just that ... a value that is KNOWN at the
time you write the code, and that will not change when the code runs.

CONST statements can include a limited number of "calculations", such as
adding together two other constants, etc, because the compiler can do a small
amount of arithmetic. But the compiler cannot do things like call functions in
libraries or execute a call to Excel's object model to determine the active
sheet. And the active sheet is not known until the code is actually running.

Myrna Larson wrote:
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?


No, there isn't. A constant means just that ... a value that is KNOWN at the
time you write the code, and that will not change when the code runs.

CONST statements can include a limited number of "calculations", such as
adding together two other constants, etc, because the compiler can do a small
amount of arithmetic. But the compiler cannot do things like call functions in
libraries or execute a call to Excel's object model to determine the active
sheet. And the active sheet is not known until the code is actually running.

---------------------

Ok -- I can live with that. I just didn't want to overlook some obvious
solution and the opportunity to learn a new technique.

Thanks for the info...

Bill