Programmatically construct a constant's name and return its value

[riktek]

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

In Bash, this is called expansion.

What you describe explains the failure. Eval() jumps the gun by expanding its argument before it executes. Wow.

To me, this reveals a profound and fundamental design flaw of Eval() in that its very purpose would seem to depend on deferring expansion of the expression string passed to it. It's been designed to fail. I'm floored.

I've found no sources describing execution, expansion, or substitution in VBA and precious little about evaluation, although parentheses seem to force evaluation at some point. If you know of any, I'd be keen to know of them. VBA lacks a counterpart to the Bash Manual, as I've noted elsewhere. The VBAL is no substitute, indirect at best, and reads like a post hoc rationalization.

 

[riktek]

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.)

Thanks. That's quite helpful and explains or confirms much.

I actually have parsed the VBAL on a number of occasions over the years and so am familiar with it. In fact, I believe I have several editions of it in my reference directory. It's minimally descriptive and can't hold a candle to the Bash Manual, so I've found it of little practical use. I'm not above a revisit, though, and certainly not intimidated by it. I don't recall it addressing evaluation or execution at all but look forward to a pleasant surprise. @RonPaii 's response appears to spill the beans on Eval(), however, with defective evaluation order being the culprit.

If by "late-binding value association", you mean perform an ordinary lookup, I confess.

Whatever it takes, the very purpose of the Eval() function would seem to depend on deferring expansion of its argument. Of course it requires its own symbol table, or even its own instance of the "compiler"/ expansion engine: It's an evaluation engine and that means evaluating its argument itself. Job One is isolating that argument and preventing compiler action on it. Job Two is compiling / expanding / evaluating the argument independently at runtime. If doing that is too hard, or the developers are too lazy, too bad. Get back to work and conform the function to its own documentation. Until then, by abdicating expansion of its argument to its own compiler, the function will, by design, fail as it has here when passed a legal argument.

The explanation is quite clarifying and helpful. Unfortunately, for all of us, what it describes is a defect.

 

[The_Doc_Man]

I've found no sources describing execution, expansion, or substitution in VBA

In the languages I've seen that support symbol substitution, the closest VBA equivalent is concatenation. I am fairly certain that the problem isn't in creating a string by concatenation or by using the REPLACE function. You absolutely can do either of those things to alter a string.

The problem is that if you want to see a value corresponding to a variable named by the result of concatenation, there is no place to look for that variable. There is no searchable collection. Scripting languages that get dynamically interpreted CAN do that. I've done it many times. But... compiled languages are more limited.

In short, it is not that you can't manipulate a string at run-time. It is that you can't use that manipulated string to resolve to an address so that you can dissect the variable at that address.

VBA supports object variables that are merely address pointers to an object, but it doesn't support generic address variables and it doesn't support math on object variables (that are just addresses). That is, you can't manipulate elements of an array using an address pointer that you directly increment by data-element size to get to the next variable. The C equivalent to (pointer-variable++) syntax doesn't exist in VBA.

There is also the issue that for Access VBA code, that is always in an event procedure. All local variables are on the user-mode program stack. All static and public variables (and public constants) exist but their locations are not available. I specifically asked Google Gemini this question:

Does Access VBA have a searchable variable symbol table?

The answer I got was this:

Access VBA does not provide a direct, exposed "searchable variable symbol table" that you can query at runtime to list all variables and their current values. While VBA does manage an internal symbol table for its own compilation and execution, this table is not directly accessible to developers through built-in functions or objects.

This is the ultimate reason that you can't generate a value by synthesizing a variable name even if the name is valid. And the answer I got is of course subject to the possibility that Gemini got it wrong. But it explained its answer in an unequivocal way that showed it correctly interpreted the question. Therefore, regarding dynamic variable name creation? Not going to work for VBA.

I will further clarify that you CAN search for named objects in collections that hold those objects. You CAN synthesize names for situations like record-set.Fields( "X" & CStr(number)) or table-def.Fields(string-variable). You just cannot search for variables because you can't see the program symbol table.

 

[riktek]

Perhps this approach helps you @riktek:

https://nolongerset.com/better-global-constants/

Thanks for that. I've read it before and actually do exactly what it describes for a slightly different purpose. Mike uses a predeclared class module as the primary container but I've found a standard module will perform identically and is a bit more robust.

I also actually had a close look at this angle in particular. It's relevant because the constants are public, so the same IntelliSense is available for them. Unfortunately, at least so far, this comes down to having the identifier that the code constructs evaluated as such, so as to return the value. That, unfortunately, is the same problem I'm facing with Eval().

 

[riktek]

In short, it is not that you can't manipulate a string at run-time. It is that you can't use that manipulated string to resolve to an address so that you can dissect the variable at that address.

Yes, exactly. That's the design failure.

My point is that Eval() would not fail if it instead were implemented essentially as a subclassed compiler to exclusively evaluate its argument.

 

[The_Doc_Man]

That's the design failure.

VBA is not the only language that disallows dynamic, wide-open symbolic references. Interpretive scripts might be able to make such references, but compiled VBA cannot. Quite a few languages disallow this ability.

If you are familiar with Goedel's Completeness Theorem and Alan Turing's theories on computability, both of which were still being studied hard in the 1960s-1980s, you would see that by blocking off certain types of dynamic program self-reference through code, you make the programs avoid certain issues that would make their computability status unknowable. That issue of determining computability was a very big problem sixty years ago. It is why languages like Pascal, P/L-1G, FORTRAN, and Ada don't allow dynamic symbol creation.

Any time that you have a strongly typed language with a lot of run-time error checking, that language places a shell around your program to assure that if you have an error, it is detected. I recall the rush I got from the first time one of my U.S. Navy programs compiled correctly under P/L-1G, a language for which two strings are ALWAYS different if they have different string lengths, even if the longer string has blanks or nulls in the longer portion. Two NULL strings of different lengths are different. Variable references have to be an exact match in size for a comparison to even be allowed. You do LOTS of typecasts in P/L-1G or Ada.

By contrast, the first versions of the C language were designed using macros on a PDP-11 running some RSX-11 variant. Type-checking and close analysis of syntax were still to be defined because assembler macros don't recognize data types. From a linguistic viewpoint, early versions of C were abominations when considered in the context of a program being "correct" or "complete" - because such programs could not be formally proved to be either.

You call it a design failure - but if the design was meant to assure program stability and facilitate analysis of program computability, then you are incorrectly characterizing this feature. It is there to protect you from your own mistakes. Pascal, for example, was developed by Niklaus Wirth, who was quoted as saying that 80% of all program flaws originate from data layout or implementation errors. It should be no surprise, then, that his "baby" (Pascal) was a strict-data-typing language environment.

 

[riktek]

You call it a design failure - but if the design was meant to assure program stability and facilitate analysis of program computability, then you are incorrectly characterizing this feature.

That's a big "if," actually.

The reason? Several but I'll start with criteria expressions, which, when required and for all their pickiness, are isolated from expansion before being submitted at runtime for expansion and submission.

So, Eval() fails because either (a) its argument isn't required to be, and isn't handled as, a criteria expression; or (b) it fails in evaluating a criteria expression. E.g.,

That both arguments execute, and identically, is astonishing. Either the first instead should return its string literal >1+1< consistent with compiler expansion or the second shouldn't even execute. Instead, it tries to do all things and so fails, predictably and by design, with an entire class of arguments.

Contributing to this is that expansion isn't done iteratively, and so isn't consistently performed as specified, even though it could be. E.g.,

View attachment 121719
The first and second are as we might hope and expect. The trouble here is that the third doesn't return 2. That is, the inner parentheses properly evaluate the string as its literal but the outer parentheses don't evaluate the literal. In fact, the outer parentheses don't even operate[!]. They should, and could without prompting the Second Coming of Turing.

That is, we invoke evaluation (expansion, actually) with ( unless we don't. Better to get this right by doing it every time.

Finally, from a higher-level functional perspective, which we might otherwise call a product marketing perspective, it is astonishing to see published such an audaciously named and documented function if it is incapable of a local lookup, evaluating a string as an identifier, or even evaluating an identifier in the first place. It is, or at least aspires to be, a precedence function. One would expect the specification for such a function to begin with these capabilities.

Much of this is a kvetch but impossibility fails as an excuse for Eval()'s design flaw as far as I can tell. There would seem to be ways to conform Eval() with its documentation that VBA or other shells already implement without difficulty.

I will hasten to add that, even if the two inconsistencies I describe don't fix Eval()'s design flaw, not only does that design flaw remain a design flaw, but also those inconsistencies are design flaws in themselves.

It's important and worthwhile to worry over these design flaws to discern their source, triggers, and behavior so we can learn when and how we can avoid them and our vulnerability if we do not, so this has been a good and valuable discussion. Just as important, though, is not to be shy about calling those design flaws what they are.

 

[cheekybuddha]

That both arguments execute, and identically, is astonishing.

You didn't try:

?Eval("1"+"1")
 11

 

[riktek]

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.

Philipp, I discovered this morning that Eval() also fails with references to local functions.

So, starting with a standard module named "stdBas":

Option Compare Database
Option Explicit

Public Const MyConstant As String = "ConstantString"

Function MyFunction() As String
    MyFunction = "FunctionString"
End Function

We then see that Eval() fails with error 2482, unable to find differing things, as follows:

?Eval("MyFunction")

Fails to find 'MyFunction'

?Eval(MyFunction)
?Eval(stdBas.MyFunction)
?Eval([stdBas].[MyFunction])

All fail to find 'FunctionString'. So, it evaluates the function, gets its value, but fails to return that value.

?Eval("stdBas.MyFunction")
?Eval("[stdBas].[MyFunction]")

Both fail tofind 'stdBas'.

However inexplicable these results may be, they are consistent with similar references to the module's constant.

You wrote above that the trouble is that an expression cannot contain a constant. This demonstration suggests that the difficulty is broader because it involves a function.

I believe we now can say that the difficulty is in evaluating identifiers, or at least some identifiers. I believe the demonstration here establishes that the difficulty exists at least with identifiers of the current module and its members. Perhaps the difficulty is even broader. Further tests will tell.

I'm quite curious what you think. Are these observations consistent with your understanding? Can we say yet with any certainty the extent of this difficulty or is further testing necessary?

 

[riktek]

You didn't try:

?Eval("1"+"1")
 11

Oh, God. Numeric strings and an ambiguous operator to boot.

The result is actually correct. The question is whether it's numeric or a string. It should be a string and this should be numeric:

Eval(("1"+"1"))

but

(should <> is)

 

[sonic8]

Code: ?Eval("MyFunction")
Fails to find 'MyFunction'

You need to add the parenthesis:
? Eval("MyFunction()")

Code: ?Eval(MyFunction)
?Eval(stdBas.MyFunction)
?Eval([stdBas].[MyFunction])
All fail to find 'FunctionString'. So, it evaluates the function, gets its value, but fails to return that value.

Sorry, I can't follow to your conclusion.
Unfortunately, Eval cannot resolve qualified names. So. you must omit the module name and if you got multiple functions of the same name in different modules, you got a problem.

 

[riktek]

Or instead of a bunch of functions you can have one function with select case:

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.

I finally got a readable presentation of your code and thanks for the ideas.

Yes, all those things will work.

This is just one step in a refactoring exercise that I describe in my response to @MajP 's comment. The data will migrate to a table in time but that has some dependencies and for now the data source is the constants. The current job had been to replace many functions, each hardwired for a constant, with a single parameterized function. My approach had contemplated what was effectively a lookup on the constants as the data source pending the data migration but that turns out to be too hard for Access and VBA. We'll still need the function eventually, and for it to do table lookups, but we don't need it now.

 

[The_Doc_Man]

That both arguments execute, and identically, is astonishing.

Or not. I now refer to the VBA Language Specification v20140424, 5.6.9.3 Arithmetic Operators and 5.6.9.3.2 + Operator which present tables of compatible data types and their eventual data type after "LET COERCION" - the practice of converting different-sized data types to a data type that holds everything.

The order of evaluation for Eval(1+1) is that VBA sees a function (doesn't care what it is quite yet) AND it sees an actual argument based on a literal constant expression. VBA knows constant expressions. So it evaluates 1+1 BEFORE calling Eval() and gets 2, after which it executes Eval(2). And of course, you get back 2 for that case. This action is based on the general rule that you evaluate parenthetical expressions from the inside out, and in that context, 1+1 is a parenthetical expression.

For Eval("1+1") you have a string expression as a simple actual argument, and since it is a quoted constant string, VBA just passes in the literal string. Eval("1+1") gets executed and the evaluation code uses LET COERCION to see if that string could have been a text-based constant numeric expression, which in this case it very well could be. Therefore, in this quoted case, Eval() uses LET COERCION to convert the constant string (because that is what "1+1" is) to get 1+1 (NOT strings after two coercions) and then computes 2, which it returns. They get the same results, which really ISN'T that astonishing.

But THEN... There is the case of Eval("1" + "1") which uses the ambiguous "+" sign, which for strings CAN mean "concatenate." You have an expression inside parentheses with a viable operator and two numeric-constant strings. Most important... an expression with two strings separated by an operator that works on strings. So the concatenation occurs first (before Eval is called) because VBA does constant expressions from inner parentheses first. "1"+"1" concatenates to "11", after which Eval("11") is what gets executed. Eval() checks whether that is a digit string - which it IS - and 11 gets returned.

You originally wanted to use EVAL on an argument like Eval( "con" & strStem & "Bar" ) - which according to the rules, would be processed by VBA to do the concatenation BEFORE calling Eval(). So if strStem is "Foo" you would be calling Eval( "conFooBar" ) - and you cannot find a value for that. The link below is to Microsoft Support's article on Eval().

Eval Function - Microsoft Support

support.microsoft.com

In this article, there are discussions regarding evaluation of strings.

The stringexpr argument must evaluate to a string or numeric value; it can't evaluate to a Microsoft Access object.

I can't get a closed definition of where CONST values are stored, other than that they are stored within the compiled code. Part of the problem is that Access is not open source, so we don't know where things actually get stored. Given that VBA is compiled to pseudo-code, it is possible that the constant ISN'T treated as a variable after all. Asking Google on a far-out hunch, it says that the Access VBA special "addressof()" function doesn't necessarily work on constants because the compiler optimizes use of constants by embedding them into code. The constant might not exist as a separate variable at all.

Your problem, @riktek, is that you are casting your expectations on someone else's language developed about 30 years ago during a time when issues of computability were considered paramount. There is nothing wrong with VBA. What is wrong is that you are blindly expecting languages to do what you want when in fact they ALWAYS do what their authors wanted. I'm trying to not be harsh here, but you are basically making faulty assumptions on what VBA will do and I can't seem to convey that to you.

 

[riktek]

You need to add the parenthesis

[facepalm] Of course. I stand corrected, and thanks.

Eval cannot resolve qualified names

Good to know about that. I'm careful with scope so don't usually require them but things happen.

 

[riktek]

Your problem, @riktek, is that you are casting your expectations on someone else's language

I take the point and understand why you might think that. Actually, I'm casting desperately for understanding for want of documentation providing it. My only expectations are those that someone else's documentation articulate. When someone else's language deviates from someone else's documentation, that's on them.

Otherwise, a constant isn't a Microsoft Access object and isn't even a member of any class in its library. My guess is it's a VBE built-in. All the quote says is that Eval() won't return a TextBox or Form.

I of course understand the implemented expansion algorithm but the design defects remain design defects. Let coercion reads like a post hoc rationalization for bad design decisions unwilling to be redone. Call it something fancy so we can look down our noses if anyone calls us on it. The function remains ambiguous and unpredictable, in part for want of a parameter requirement sufficiently rigorous to distinguish strings for concatenation from expressions for evaluation, and in part for want of a means to reliably and consistently specify expansion and precedence. It also remains designed to fail with an entire class of legal arguments by exposing its argument to the compiler before runtime. It got to ambiguous and defective on its own without any help from me. I'm just calling balls and strikes.

 

[The_Doc_Man]

Let coercion reads like a post hoc rationalization for bad design decisions unwilling to be redone.

Though it has many names (including "variable promotion" and "implicit coercion"), LET COERCION exists in many languages that relax strong-type rules. For example, regular BASIC does it. FORTRAN does it. Some versions of C do it (though in that case it is "implementation dependent" i.e. manufacturer's choice.) Languages like Ada or P/L-1G do not allow it because of their strict adherence to explicit and firm data types.

You keep on calling symbol-table isolation a design defect - but it isn't. It is a "protect the crazy user who wants to make self-referential, crash-prone code" behavior. Compiled languages are very different from scripted languages in terms of what they can do and how they do it.

You keep on coming back to BASH, but that is a Bourne (Again) SHell scripting language. Compiled languages have to put data addresses in the instruction they are compiling, which is the earliest of early binding. Address assignment is a compile-time action for compiled code. Scripted languages aren't compiled but rather are interpreted on the fly including address resolution during execution of a looped line. Expression component addresses are re-evaluated in each step of any loops. It is the extreme of late binding.

The difference in performance has true-compiled code fastest (i.e. VB6, which DOES compile to machine code), then pseudo-code (VBA), and finally scripting (BASH or VBS) slowest. You can verify that assuming you have VB6, VBA, and VBS available on the same machine for benchmarking. Your arguments tell me that you don't fully appreciate the technicle differences between compiled and interpreted code. Otherwise you would appreciate the difficulty of the actions that true compilers have to perform - and the order in which they MUST be performed.