3

I've been using the built-in Format() function successfully in Excel VBA for years. However, I have just come across a situation that would appear to be a bug. When the input value is Empty (Variant), Format appears to change its value to Null !! I have no quibble with the output value (empty string), but it should not change the input argument. The behaviour does not seem to depend on the nature of the optional format control string, and even happens when that string is omitted. Can anyone else reproduce this behaviour?

Dim x, y
x = Empty
Debug.Print TypeName(x), TypeName(y), IsEmpty(x)  'Empty, Empty, True
y = Format(x, "0")
Debug.Print TypeName(x), TypeName(y), IsNull(x)   'Null, String, True
Improve this question
3
  • 1
    Debug.Print x now returns Null (on my end (Win10 MS365 both 64bit)). So you're asking: "How on earth did x become Null when I didn't do anything to it, or did I?"? Commented 10 hours ago
  • From learn.microsoft.com/en-us/office/vba/language/reference/… Empty Used as a Variant subtype that indicates an uninitialized variable value. So, I guess that has a lot to do with the data type Variant where a lot of implicit conversions are ongoing. Commented 9 hours ago
  • this is really not a user friendly behaviour, but since you are aware it, maybe can live with it. Commented 8 hours ago

2 Answers 2

Reset to default
2

x is passed by reference to Format. To avoid that, wrap it in parentheses:

Dim x, y

Debug.Print TypeName(x), TypeName(y), IsEmpty(x), IsNull(x)     ' Empty, Empty, True, False

y = Format((x), "0")
Debug.Print TypeName(x), TypeName(y), IsEmpty(x), IsNull(x)     ' Empty, String, True, False

y = Format(x, "0")
Debug.Print TypeName(x), TypeName(y), IsEmpty(x), IsNull(x)     ' Null, String, False, True
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

While this fixes the problem, it is not normal behaviour. Format accepts its argument byref to avoid copying of potentially long data, not to be able to modify it. It is a bug that it does.
0

I reproduced the behavior and suspect the cause is that x and y are declared as Variant. The Format documentation never states that the input is passed ByVal, so the argument is likely passed ByRef. During execution, Format implicitly casts the value, and because of ByRef, x gets changed.

A simple helper function that takes the argument ByVal avoids this issue and works as expected.

Public Function SafeFormat(ByVal v As Variant, Optional ByVal fmt As String = vbNullString) As String
    SafeFormat = Format$(v, fmt)
End Function

And if you declare the variable as a basic data type (e.g., Integer, Double, String or even date), the issue does not occur.

    Dim x As Date, y As Date
    x = Empty  ' Be aware: Implict conversion in this line now
    Debug.Print TypeName(x), TypeName(y), IsEmpty(x)
    y = Format(x, "0")
    Debug.Print TypeName(x), TypeName(y), IsNull(x)

This works fine as well.

    Dim x , y 
    x = Empty
    Debug.Print TypeName(x), TypeName(y), IsEmpty(x)
    y = SafeFormat(x, "0")
    Debug.Print TypeName(x), TypeName(y), IsNull(x)

PS If you want to force a copy or an explicit conversion without a helper function, use Format(CVar(x), "0") instead which documents the intent more clearly.

PPS I am not saying that one can really assign Empty to a basic data type. The basic variable types all have a default value and VBA does a lot of implcit conversions. The conversions might be misleading.

Further reading

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications

https://learn.microsoft.com/en-us/office/vba/language/reference/keywords-visual-basic-for-applications (Description Empty)

Empty Used as a Variant subtype that indicates an uninitialized variable value.

Improve this answer

4 Comments

And if you declare the variable as a basic data type (e.g., Integer, Double, String or even date), the issue does not occur - the correct behaviour does not occur either. You cannot set x As Date to Empty like you are doing in your example. It gets converted to x = 0, which results in 30/12/1899.
I can set x= Empty, it works with no error. It's just the same as x=Cdate(Empty), which is #00:00:00#. Conversion of Variant to Date. Anyway, what is the correct behaviour?
Yes, it works with no error, because it is implicitly converted to x = 0 like I said. The correct behaviour is the one intended by the developer, which is unlikely to output 30/12/1899 in case the variable is empty, so the suggestion to fix the original problem by declaring Dim x As Date will result in an unexpected change of output. You are seeing #00:00:00# because that is the format you are outputting it it, the full contents is #1899-12-30 00:00:00#.
The initial value of a Date variable is 00:00:00 (i.e., 1899-12-30 00:00:00). Assigning x = Empty doesn’t really change it because Empty is implicitly converted to the Date default (0). I’m not claiming this fixes the behavior — just suggesting that for basic data types Format probably won’t alter the input, since no additional implicit conversion occurs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.