Programmatically construct a constant's name and return its value (1 Viewer)

riktek

Member
Local time
Today, 15:42
Joined
Dec 15, 2023
Messages
135
I would like to programmatically construct a constant's name and return its value, but am stuck.

Eval() seems the most logical approach but it chokes, as follows.

At the top of a standard module, I do, e.g.:

Code:
Public Const conFooBar As String = "conFooBarValue"

Then, in the same module a public function attempts to return a constant's value with the call GetConstantValue("Foo") and Eval() can't find the constant's name even though it is module-level and therefore in scope, and doubly so because it is declared Public. E.g.:

Code:
Function GetConstantValue(strStem As String) As Variant

    Dim strConstantName As String
    Dim strReturn As String
    
    strConstantName = "con" & strStem & "Bar"
    
'    strReturn = Eval(strConstantName)               'Throws 2482, can't find name 'conFooBar'.
'    strReturn = Eval("con" & strStem & "Bar")       'Throws same.
'    strReturn = Eval("conFooBar")                   'Throws same.
'    strReturn = Eval(conFooBar)                     'Throws same, but for 'conFooBarValue'.  Odd.
    
    GetConstantValue = strReturn

End Function    'GetConstantValue()

I understand Eval() cannot read local variables, i.e., those declared in the calling procedure. The first two failures above seem to illustrate this for a local variable and a parameter. The third attempt is the most puzzling because the same error occurs with no local variable reference. Finally, Eval() takes the constant name directly and not as a string and errors similarly with the constant's value. In the latter case, I presume Eval() gets the constant's value and attempts to evaluate it.

To be clear, the question is how to get the constant's value from the string being passed, not necessarily about getting Eval() working although that would work if it's possible.

Any thoughts would be most appreciated.
 
This level of indirection is not supported by VBA, it being a very simple language, comparatively.
You *may* be able to do something with RtlMoveMemory API and VarPtr, but my initial tests all resulted in Access crashing.
 
Why you should use constants?
Use a function to return the value:

SQL:
Public Function conFooBar() As String: conFooBar = "conFooBarValue": End Function
Public Function conBazBar() As String: conBazBar = "conFooBarValue1": End Function

Public Function GetConstantValue(strStem As String) As Variant
    Dim strFunctionName As String
    strFunctionName = "con" & strStem & "Bar"
    GetConstantValue = Eval(strFunctionName & "()")
End Function

Then if you try it, the value can be returned.
Code:
? GetConstantValue("Foo")
conFooBarValue
 
Last edited:
Or instead of a bunch of functions you can have one function with select case:
SQL:
Public Function ConstValue(strName As String) As String
    Select Case strName
        Case "conFooBar": ConstValue = "conFooBarValue"
        Case "conFoo2Bar": ConstValue = "conFooBarValue2"
        Case "conFoo3Bar": ConstValue = "conFooBarValue3"
        Case Else: ConstValue = "#Unknown#"
    End Select
End Function

Public Function GetConstantValue(strStem As String) As Variant
    Dim strFunctionName As String
    strFunctionName = "con" & strStem & "Bar"
    GetConstantValue = ConstValue(strFunctionName)
End Function

The following returns your constant values.
? GetConstantValue("Foo")
? GetConstantValue("Foo2")
.....

You can also use a Dictionary to keep the values, or if it was me, I would keep the values in a table and a Dlookup will get the value.
 
Last edited:
just put it in a Table (tblConsVariables)

ConsName (Short Text)
ConsValue (Short Text)

if the Value of the Constant is Numeric just convert the string to numeric, ie Val(ConstValue & "")

here is a demo db for you to check out.
 

Attachments

Last edited:
This level of indirection is not supported by VBA, it being a very simple language, comparatively.
You *may* be able to do something with RtlMoveMemory API and VarPtr, but my initial tests all resulted in Access crashing.
I hadn't thought of it that way. Probably because this doesn't seem to require more than the thinnest and least removed degree of indirection to accomplish, or even qualify as such.

I'm surprised at some level that this might be so because of how the function is defined and described - give it a string that looks like an expression and Eval() will return its value. Clearly, I'm providing an entirely legal argument, judging by the documentation. Not only legal, but that is the simplest possible expression to evaluate, a constant. That isn't a heavy lift. One would think the function would be agnostic about how and from where it got its argument so long as it was a string, and the string is a valid expression, if only because that is what its documentation states. That is, that the first evaluation it would perform would be to evaluate the argument as provided into a string it otherwise could consume. Perhaps I've spent too much time with Bash but this seems like elementary expansion and substitution. This doesn't involve a criteria expression but as picky as things consuming or constructing them are, they aren't picky this way, whatever "this way" is.

On the other hand, this is consistent with prior experiences with Eval() that have left me with the impression that it is unknowably picky about its argument. I've never gotten it to work when, by all indications, it should. I'm beginning to think the problem is that the function's code must be overhauled to conform the function with its documentation.

I won't be holding my breath but while we're waiting, what is at work and what, if anything, is possible to understand about what Eval() will accept as an argument? Clearly, I'm missing something, and not because I can't read. We have established that its parameter is defined as a string that evaluates as an expression, and also that undocumented exceptions to this definition exist for (a) strings; (b) concatenated strings; (c) local and module-level string variables; and (d) the return value of string functions, in all cases that do actually evaluate as expressions otherwise, such that the function will throw an exception in each case. One wonders what is left, and how one is to know.

I am genuinely curious, and thanks for responding.
 
To be clear, the question is how to get the constant's value from the string being passed, [...]
I'm afraid that is not possible. The documentation of Expressions in Access is misleading in regard to Constants. Microsoft uses that term but they should have used the term Literal instead. An expression cannot include VBA constants but only literal constants.
 
just put it in a Table (tblConsVariables)

ConsName (Short Text)
ConsValue (Short Text)

if the Value of the Constant is Numeric just convert the string to numeric, ie Val(ConstValue & "")

here is a demo db for you to check out.
They're string constants, actually, GUIDs.
 
I'm afraid that is not possible. The documentation of Expressions in Access is misleading in regard to Constants. Microsoft uses that term but they should have used the term Literal instead. An expression cannot include VBA constants but only literal constants.
Having read that documentation just now, I wouldn't necessarily say "misleading." "Silent" might be more descriptive. "False," "wrong," and "incorrect" also would work nicely. Regardless, any might well be amplified with the adverbs "woefully" or "profoundly."

To the point, it's good to know it can't work but the distinction is lost on me.

By "VBA constant," do you mean (a) a constant declared and set using the Const statement; (b) a member of a type or enum created in VBA; (c) an application constant defined and compiled as an enumeration, class, or module into an Automation library that the VBE references?

Regardless, and besides, what exactly is a literal constant, what distinguishes it from a "VBA constant" (which of course is the epitome of literal), and how does one create and set the value of a literal constant (because the concept wouldn't exist if neither were possible).

Not to be obtuse, and thanks.
 
Last edited:
Or instead of a bunch of functions you can have one function with select case:
Thanks for both replies. Your code is illegible in my browser (I think there may be site settings for this) and it's late, but I'll work on getting something readable and revert.
 
GUID? as in you want to create Object from GUID?
here the same demo db, with Clipboard GUID ("{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
if you select this record, it will show you the Text content of the clipboard (if there is).
 

Attachments

By "VBA constant," do you mean (a) a constant declared and set using the Const statement; (b) a member of a type or enum created in VBA; (c) an application constant defined and compiled as an enumeration, class, or module into an Automation library that the VBE references?
All of those.

Regardless, and besides, what exactly is a literal constant, what distinguishes it from a "VBA constant" (which of course is the epitome of literal), and how does one create and set the value of a literal constant (because the concept wouldn't exist if neither were possible).
A literal (constant) is a value itself written hardcoded into source code or into an expression.

Numeric literal (constant): 999
String literal (constant): "ABC"
Date literal (constant): #1/1/2025#

Here they are used in an expression:
X = Iif(Now() > #1/1/2025#, 999, "ABC")

PS: Here is Microsoft's definition of literals in the context of VB.Net: Constant and Literal Data Types (Visual Basic)
The concept and definition of Literal is universal to all programming languages.
 
Last edited:
What is the purpose that you are trying to achieve? The only thing I can guess is that you want to set the value once like a constant and not allow your code to reassign. If that is the case could you make a single class with all your constants and do something like.

Code:
Private pCon1 As Variant
Private bCon1IsSet As Boolean

' a set of class variables for each constant
Private pCon2 as Variant
private bCon2IsSet as boolean

Public Property Get Con1() As Variant
    Con1 = pCon1
End Property

Public Property Let Con1(ByVal vNewValue As Variant)
    If Not bCon1IsSet Then
        pCon1 = vNewValue
        bCon1IsSet = True
    Else
        Debug.Print "Error: This variable can only be set once."
        ' Optionally, you could raise an error:
        ' Err.Raise vbObjectError + 1000, "ClassConstants", "Variable already set."
    End If

' Repeat for other 'constants'

End Property
 
I think I would update the class so you have run time "setable" constants and design time read only.
1. Provides intellisense for coding
2. Can set a "constant" only once and throws error if you try a second time.
3. throws and error if "constant" not set and try to access

Code:
Private Dict_Constants As Object

'------------------------------------------- Setable constants -----------------------------------------------------------------
Public Property Get ApplicationName() As String
   If Dict_Constants.exists("ApplicationName") Then
      ApplicationName = Dict_Constants("ApplicationName")
   Else
     Err.Raise 2025, "Get Application Name", "Constant not set"
   End If
End Property

Public Property Let ApplicationName(ByVal sNewValue As String)
  If Not Dict_Constants.exists("ApplicationName") Then
    SetConstant "ApplicationName", sNewValue
  Else
    Err.Raise 2026, "Let Application Name", "Constant Name already set"
  End If
End Property

Public Property Get DefaultDate() As Date
  If Dict_Constants.exists("DefaultDate") Then
      DefaultDate = Dict_Constants("DefaultDate")
   Else
     Err.Raise 2025, "Get Default Date", "Constant not set"
   End If
End Property

Public Property Let DefaultDate(ByVal dtNewValue As Date)
  If Not Dict_Constants.exists("DefaultDate") Then
    SetConstant "DefaultDate", sNewValue
  Else
    Err.Raise 2026, "Let DefaultDate", "Constant Name already set"
  End If
End Property

'---------------------------------------------------- Read Only Constant -------------------------------------------------
Public Property Get DefaultRecordsToDisplay() As Variant
  DefaultRecordsToDisplay = 10
End Property

'---------------------------------------------------------- Set Constant ------------------------------------------------------
Private Sub SetConstant(ConstantName As String, ConstantValue As Variant)
  Dict_Constants.Add ConstantName, ConstantValue
End Sub
Private Sub Class_Initialize()
  Set Dict_Constants = CreateObject("Scripting.Dictionary")
End Sub
ErrorAppName.png



NotSet.png
 
Regardless, and besides, what exactly is a literal constant, what distinguishes it from a "VBA constant" (which of course is the epitome of literal), and how does one create and set the value of a literal constant (because the concept wouldn't exist if neither were possible).

Some of what I provide in your answer comes from the Microsoft VBA Language Reference dated 2014, self-identified as v20140424. Reading the actual document might be more than you want to do, but that VBA Language Reference IS downloadable in PDF format if you feel you just HAVE to torture yourself. (No accounting for some tastes).

Regarding literal constants and declared constants, the rules are in the VBA Language Reference, section 3.3 Lexical Tokens.

Paraphrasing several pages of Backus-Naur notation: A token is a sequence of text not broken apart by anything that belongs to a separator-class character. Spaces, most math operators, tabs, ampersand, dot, comma, exclamation point, end-of-line characters, and a few other special characters are separators, usually because those characters have active meaning in expressions. Some characters are delimiter tokens that enclose or encapsulate other tokens according to particular rules (e.g. the double-quote character around strings.)

A literal constant is any single sequence of printing characters that contains no separators as noted above. In simple terms, 0123 can be a literal INTEGER-class constant, but 01 23 cannot (because the embedded space is a separator.) A properly quoted sequence of printing characters enclosed by either paired double quotes or, in certain contexts, paired single quotes, is a literal STRING-class constant.

Valid identifers are defined by being string/digit sequences with no separator and no embedded enclosing characters. The language reference DOES contain exceptional rules for some languages that have non-Latin character sets, and the biggest difference is merely the added "special" characters that can appear in identifiers outside of the range of A-Z, a-z, 0-9, and the characters $ and _ (underscore). A valid constant is declared by providing an identifier, possibly modified by such things as type or scope tokens - PUBLIC, PRIVATE, LONG, CURRENCY, etc followed by an equals sign and a valid literal constant or constant expression to provide the value. A constant expression is an expression containing only literal values. It IS possible to involve math in the constant's defining expression but only using literals.

Your post's "Title" question implies that you are attempting to establish a late-binding value association. In the VBA Language reference, see sections 5.6.2 Expression Evaluation and 5.6.16.1 Constant Expressions. This section is rather lengthy in the VBA Language Reference, because it becomes deeply involved with Backus-Naur definitions for several classes of expression that include properties, values, objects, and literals.

Remember that VBA involves a formal compilation step to produce pseudo-code of the "instruction, address, address..." style similar to (though not necessarily identical to) the style of Assembly Language. The VBE7.DLL file (reference-library file) contains the emulator that embodies VBA code execution. When you installed Access, the installation process selected whatever library best fit your machine. In a way, installation loads the DLL that is the app level equivalent of the Windows Hardware Abstraction Layer that allows older versions of Windows to run on various older machines. (Let's not talk about Win11 in polite company.)

When your compiler references a variable OR a named constant (both of which are technically objects, albeit very simple objects), you use the name of that object and the name is called an IDENTIFIER. The identifier represents the object in your code expressions. The compiler's symbol table provides the address (or offset, depending on context) for the instruction to find the variable. In common VBA terms, variable identifiers are EARLY-BOUND i.e. defined long before you attempt to actually execute the instructions. Code able to self-reference AT RUN-TIME would have to use LATE-binding methods - which would require a symbol table available for searching at run-time.

In collection objects, you DO have the ability to search at run-time for a named singular object. All Office collections including Dictionaries and user-defined collections and Access-intrinsic collections (and Excel, Word, Power Point, and traditional Outlook, too). However, VBA DOES NOT have a "variable" collection with a run-time searchable name table. (Or if it does, we cannot see it, even if the debugger CAN see it.)

Therefore, your problem is the lack of visibility for the compiler's symbol table. Note that if you convert an .ACCDB file to.ACCDE, this symbol table does not exist after that conversion. It is removed along with the literal text of your instructions (or at least, I think it goes away.)
 
I hadn't thought of it that way. Probably because this doesn't seem to require more than the thinnest and least removed degree of indirection to accomplish, or even qualify as such.

I'm surprised at some level that this might be so because of how the function is defined and described - give it a string that looks like an expression and Eval() will return its value. Clearly, I'm providing an entirely legal argument, judging by the documentation. Not only legal, but that is the simplest possible expression to evaluate, a constant. That isn't a heavy lift. One would think the function would be agnostic about how and from where it got its argument so long as it was a string, and the string is a valid expression, if only because that is what its documentation states. That is, that the first evaluation it would perform would be to evaluate the argument as provided into a string it otherwise could consume. Perhaps I've spent too much time with Bash but this seems like elementary expansion and substitution. This doesn't involve a criteria expression but as picky as things consuming or constructing them are, they aren't picky this way, whatever "this way" is.

On the other hand, this is consistent with prior experiences with Eval() that have left me with the impression that it is unknowably picky about its argument. I've never gotten it to work when, by all indications, it should. I'm beginning to think the problem is that the function's code must be overhauled to conform the function with its documentation.

I won't be holding my breath but while we're waiting, what is at work and what, if anything, is possible to understand about what Eval() will accept as an argument? Clearly, I'm missing something, and not because I can't read. We have established that its parameter is defined as a string that evaluates as an expression, and also that undocumented exceptions to this definition exist for (a) strings; (b) concatenated strings; (c) local and module-level string variables; and (d) the return value of string functions, in all cases that do actually evaluate as expressions otherwise, such that the function will throw an exception in each case. One wonders what is left, and how one is to know.

I am genuinely curious, and thanks for responding.
Constants are converted to literal values before execution so don't exist for eval to calculate.
Const One as Long = 1
Const Two as Long = 2

If myValue = ONE + TWO then

is converted to

If myValue = 3 then
 

Users who are viewing this thread

Back
Top Bottom