Null Delimited with Octothorpes and Null Propagation Returns "Null" (3 Viewers)

[riktek]

Perhaps this is new, or just new to me, but I just learned that a string that concatenates a Null Variant variable between octothorpes using Null propagation (by use of the + operator) does not return Null but rather the string "Null" (without the quotation marks).

This can be handy but I just can't figure out why that would be. An hour with Google was fruitless.

Here's the function:

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
    
    varTest = Null
    varReturn = "#" + varTest + "#"
    
    TestNullVariant = varReturn
End Function

 

[Josef P.]

TestNullVariant should return Null (not the string Null).

Simple test:

Dim x As Variant
x = TestNullVariant()
If IsNull(x) Then
   Debug.Print "x is null .. as expected"
Else
   Debug.Print "x is not null .. ???"
End If

 

[GPGeorge]

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
    
    varTest = Null
    varReturn = "#" + varTest + "#"
    
    TestNullVariant = varReturn
    Debug.Print TestNullVariant
    Debug.Print IsNull(TestNullVariant)
    
End Function

Immediate Window Output:

Null
True

 

[Gasman]

I do not think so?, as using len() one still gets Null not the length of a string as Null

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
    
    varTest = Null
    Debug.Print Len(varTest)
    varReturn = "#" + varTest + "#"
    Debug.Print Len(varReturn)
    TestNullVariant = varReturn
End Function

 

[MajP]

I do not think so, simply test your own function.

Public Sub Test()
  Debug.Print "Variable type: " & vartype(TestNullVariant()) & " and is null: " & IsNull(TestNullVariant)
  'vartype of 1 = vbnull
End Sub

This returns

variable type: 1 and is null: True

Both show it is real Null not a string Null

 

[riktek]

I've been tinkering a bit. If concatenated with &, Null returns; if concatenated with +, "Null" returns.

Expanding the original function a bit and adding a test function (with the Immediate pane output commented by line) illustrates my observation.

What's interesting is that the test function evaluates the parameter as Null AND prints/returns "Null". Curiouser and curiouser.

Here are the revised functions:

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
 
    varTest = Null                                  'Output to Immediate pane:
    varReturn = "#" + varTest + "#"
    Debug.Print varReturn                           'Null [String]
    Debug.Print "varReturn = >" + varReturn + "<"   'Null [String]
    Debug.Print "varReturn = >" & varReturn & "<"   'varReturn = ><
 
    TestNullVariant = varReturn                     'Null [String]
End Function    'TestNullVariant()

Function TestTheTest(varThis As Variant) As Variant
    If (IsNull(varThis)) Then                       'Output to Immediate pane:
        Debug.Print "varThis is Null."              'varThis is Null.
    Else
        Debug.Print "varThis = >" & varThis & "<"   '[no output]
    End If
    Debug.Print varThis                             'Null [String]
    TestTheTest = varThis                           'Null [String]
End Function    'TestTheTest()

The call is

TestTheTest(TestNullVariant)

 

[Josef P.]

Are you possibly interpreting the output via Debug.Print as a return value?
Debug.Print always creates a string and converts Null to the string Null. A date is also displayed as a formatted date string.
However, this is only the representation of the value as a string.

 

[theDBguy]

I've been tinkering a bit. If concatenated with &, Null returns; if concatenated with +, "Null" returns.

Expanding the original function a bit and adding a test function (with the Immediate pane output commented by line) illustrates my observation.

What's interesting is that the test function evaluates the parameter as Null AND prints/returns "Null". Curiouser and curiouser.

Here are the revised functions:

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant

    varTest = Null                                  'Output to Immediate pane:
    varReturn = "#" + varTest + "#"
    Debug.Print varReturn                           'Null [String]
    Debug.Print "varReturn = >" + varReturn + "<"   'Null [String]
    Debug.Print "varReturn = >" & varReturn & "<"   'varReturn = ><

    TestNullVariant = varReturn                     'Null [String]
End Function    'TestNullVariant()

Function TestTheTest(varThis As Variant) As Variant
    If (IsNull(varThis)) Then                       'Output to Immediate pane:
        Debug.Print "varThis is Null."              'varThis is Null.
    Else
        Debug.Print "varThis = >" & varThis & "<"   '[no output]
    End If
    Debug.Print varThis                             'Null [String]
    TestTheTest = varThis                           'Null [String]
End Function    'TestTheTest()

The call is

TestTheTest(TestNullVariant)

Here's my results...

 

[MajP]

I've been tinkering a bit. If concatenated with &, Null returns; if concatenated with +, "Null" returns.

That is just a convention of the Immediate window and proves nothing. Push the same results to a textbox instead. You will get a blank row for Null not a visual depiction of Null.

 

[The_Doc_Man]

The problem is that you are using octothorpes in a context where they are "datatype hints" and thus you are formatting "# null #" which will become a null DATE. That's why the octothorpes disappear.

 

[riktek]

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
   
    varTest = Null
    varReturn = "#" + varTest + "#"
   
    TestNullVariant = varReturn
    Debug.Print TestNullVariant
    Debug.Print IsNull(TestNullVariant)
   
End Function

Immediate Window Output:

Null
True

George, this illustrates and confirms the behavior of my "TestTheTest" function, which is that the return both evaluates as Null and prints the string.

 

[GPGeorge]

I think

George, this illustrates and confirms the behavior of my "TestTheTest" function, which is that the return both evaluates as Null and prints the string.

I think what it prints is that the variant evaluates to Null, not that it evaluates to a string of "Null". Further testing, such as getting the Len() of the variant should confirm that.

 

[riktek]

Are you possibly interpreting the output via Debug.Print as a return value?
Debug.Print always creates a string and converts Null to the string Null. A date is also displayed as a formatted date string.
However, this is only the representation of the value as a string.

Actually, I've been tinkering further and this (inconsistent output to memory and the Immediate window) seems exactly to be what is occurring.

I tested a variety of means to obtain or assign the string output, without success. Given the input I described, the VBE, as compiler / command interpreter / shell consistently outputs Null to memory and the string "Null" to the Immediate window. In all other cases of Null, the VBE prints nothing to the Immediate window.

So, it turns out the syntax I described doesn't return the string "Null" after all, it just tricks the VBE into outputting it to the Immediate window. I can't imagine a reason for such inconsistent output, and many reasons to avoid it, so I presume it is unintentional even if an arcane explanation exists.

 

[theDBguy]

Actually, I've been tinkering further and this (inconsistent output to memory and the Immediate window) seems exactly to be what is occurring.

I tested a variety of means to obtain or assign the string output, without success. Given the input I described, the VBE, as compiler / command interpreter / shell consistently outputs Null to memory and the string "Null" to the Immediate window. In all other cases of Null, the VBE prints nothing to the Immediate window.

So, it turns out the syntax I described doesn't return the string "Null" after all, it just tricks the VBE into outputting it to the Immediate window. I can't imagine a reason for such inconsistent output, and many reasons to avoid it, so I presume it is unintentional even if an arcane explanation exists.

Not sure I understand the original idea, but just did a quick test with the following result.

Were you expecting nothing to show up in the result?

 

[riktek]

I think

I think what it prints is that the variant evaluates to Null, not that it evaluates to a string of "Null". Further testing, such as getting the Len() of the variant should confirm that.

Len() (and LenB()) are two tests I hadn't attempted. I tried them just now and both return Null, which one would expect for a Null input but not for a string.

This said, I tried a number of other things all related to evaluation or type conversion in one form or another, so you may be onto something in suggesting that the syntax triggers evaluation somehow. Evaluation is somewhat of a dark art in VBA, it seems, more often done implicitly but also triggered by delimiters.

So, parentheses can be used to evaluate an assignment statement, e.g., `x = y` as a Boolean expression, e.g., `If (x = y)`. Quotation marks force evaluation of strings. Square brackets are shorthand for the Excel Evaluate method,, apparently the hidden _Evaluate method of many Access objects, and, I believe, other procedures with DISPID = 5. Eval() seems like an implementation of a Win32 Evaluate method. Percentage signs (%) force evaluation of environment variables.

Octothorpes are expressly shorthand for DateValue() according to its documentation but the function accepts only a String argument. This tells me the function may be a wrapper for a Win32 function that does more and is what octothorpes actually call because of the behavior when octothorpes delimit a Null Variant variable. For dates, DateValue() is essentially the inverse of Format(), which does both type conversion and formatting. DateValue() probably does its own conversion and formatting, printing according to the localization settings while returning a Double. Its parent function, when called with octothorpe delimiters, may in the same fashion print the string "Null" while returning Null itself.

 

[The_Doc_Man]

I looked in the MS VBA Language Spec (version dated 24 Apr 2014) and unfortunately it doesn't specify a mechanism for constant evaluation - which is what would happen for # Null # as an input sequence. It merely states how the implied/explicit number would be interpreted. There is no mention of an implied function call. I suppose constant evaluation could be treated as an overloaded function call implicitly.

 

[gemma-the-husky]

Why does it all matter anyway?
What are you doing with this construct?

 

[riktek]

Why does it all matter anyway?
What are you doing with this construct?

Very good questions, actually.

I was just framing out two or three generic upstream helper functions to do some small things (mostly relating to delimitation) to facilitate dynamic construction of SQL statements and criteria expressions when the behavior manifested.

I've exploited other undocumented behavior I have observed to great effect and so am keen to find other instances. This one seems increasingly like a coding error in the compiler, or a quirk of the DateValue() parent function that octothorpes (as a shortcut) call, so is probably worthless, but others have been far more deliberate and well-implemented and thus exploitable. The VBA devs dropped breadcrumbs all over and I've just learned to be alert to them.

 

[riktek]

I looked in the MS VBA Language Spec (version dated 24 Apr 2014) and unfortunately it doesn't specify a mechanism for constant evaluation - which is what would happen for # Null # as an input sequence. It merely states how the implied/explicit number would be interpreted. There is no mention of an implied function call. I suppose constant evaluation could be treated as an overloaded function call implicitly.

I'm not certain that Null is a constant but leaving that aside, I think George was on to something by suggesting this behavior is the consequence of an evaluation. Delimiters trigger evaluation in VBA and Windows, and in Bash, for that matter. Octothorpes trigger evaluation of date literals in strings., i.e., DateValue() (according to its documentation0. The [MS-VBAL] can be profoundly revealing but ultimately it's implemented with MFC or other Win32 classes. DateValue() won't take a Null argument but the function its shortcuts (i.e., octothorpes) call, will. This tells me that the behavior probably is a consequence of how DateValue() implements the parent W32 function that octothorpes call.

 

[gemma-the-husky]

Perhaps this is new, or just new to me, but I just learned that a string that concatenates a Null Variant variable between octothorpes using Null propagation (by use of the + operator) does not return Null but rather the string "Null" (without the quotation marks).

This can be handy but I just can't figure out why that would be. An hour with Google was fruitless.

Here's the function:

Function TestNullVariant() As Variant
    Dim varReturn As Variant
    Dim varTest As Variant
   
    varTest = Null
    varReturn = "#" + varTest + "#"
   
    TestNullVariant = varReturn
End Function

Out of interest what would happen if you used a different variable type, other than a variant, as variant is very much a special case.

Normally that expression would return a date with a valid string in vartest. Would it return a string if everything was of type string?