Manifest Constants in VBA

  • Thread starter Thread starter Bill Martin
  • Start date Start date
B

Bill Martin

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
 
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?
Click to expand...

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 said:
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.
Click to expand...

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

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
 
Bernie said:
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
Click to expand...

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

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
 
Back
Top