Constant expression ?

[Westbury]

Within a macro I have this line

Const strFileName = Range("B22").Value

When I try to run the macro it gives a warning "constant expression required". Can someone help me with an explanation / solution please?

 

[Norie]

Range("B22").Value is not a constant, Range("B22") it's a reference to a range and it's value, i.e. Value property, can change.

 

[DanteAmor]

By declaring a constant, you can assign a meaningful name to a value. You use the Const statement to declare a constant and set its value. After a constant is declared, it cannot be modified or assigned a new value.

Reference:

Declaring constants (VBA)

Office VBA reference topic

docs.microsoft.com

It means that you cannot put a cell, since the content of the cell can change, so you must put a fixed data, such as "hello" or a number like 54.

Simply use:
strFileName = Range("B22").Value

 

[Rick Rothstein]

It is my understanding that all constants are replaced by their values before the code is physically compiled into executable form. In essence, this part of the process acts like a word processor modifying a text file by replacing small pieces of text with other small pieces of text to produce a modified text file (the "final" code)... only then is the code (text file) compiled into executable form.

 

[Norie]

Rick

That may be the case for languages that are compiled but VBA is interpreted.

 

[mikerickson]

VBA says that Range("B22").Value is not constant, not because the value in the cell can change, but because Range is a function, not a constant.
Similarly Const myVal As String = UCase("cat") would get the same error.

A work-around (that requires no change in the other coding) would be to cast strFileName as a function

Public Function strFileName() as String
    strFileName =  Range("B22").Value
End Function

Although I would further qualify the range.

 

[Rick Rothstein]

Rick

That may be the case for languages that are compiled but VBA is interpreted.

According to Wikipedia (Visual Basic for Applications - Wikipedia)... "Code written in VBA is compiled to Microsoft P-Code (pseudo-code), a proprietary intermediate language, which the host applications (Access, Excel, Word, Outlook, and PowerPoint) store as a separate stream in COM Structured Storage files (e.g., .doc or .xls) independent of the document streams. The intermediate code is then executed by a virtual machine (hosted by the host application)." It is during the compiling into P-Code that I believe the constant names are replaced by their values (text replacing text)... it's during execution by the hosted virtual machine where I believe the line-by-line interpretation takes place.

 

[Westbury]

Guys,

That started an unexpected debate! I've gone off to re-consider my code. Thanks for your comments and please consider this enquiry closed.

Geoff

 

[DanteAmor]