Constant expression ?

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
77
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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:

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,501
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Rick

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
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

Code:
Public Function strFileName() as String
    strFileName =  Range("B22").Value
End Function
Although I would further qualify the range.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,501
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Jun 7, 2009
Messages
77
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
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top