Why do Len and VBA.Len return different results?

Question

When I run the following macro:

Sub try()
    Dim num As Integer
    num = 123
    MsgBox Len(num)
    MsgBox VBA.Len(num)
End Sub

The first Msgbox displays 2 and the second Msgbox displays 3. If I remove the first line which says Dim num As Integer, both MsgBoxes display 3.

Can anyone explain why?

Answer 1

Len and LenB are not just ordinary functions, they are keywords, and as such are found on the list of VBA keywords (although LenB is only mentioned after you click through to Len).
Mid would be another example of such keyword disguised as function, whereas e.g. Left isn't on the list at all, because that is just an ordinary function.

It has to be a keyword because one of its jobs is to perform the compile-time task of determining the storage size of a variable. E.g. with private user-defined types, an ordinary function could not do that at all:

Private Type foo
    a As Long
    b As String
End Type

Sub TestLens()
    Dim f As foo
  
    MsgBox Len(f)     ' OK
    MsgBox VBA.Len(f) ' Compile time error
End Sub

The fact that the object browser brings you to VBA.Len when you press Shift+F2 on that Len(f) is both correct and misleading.
The Len(f) here does not actually call the VBA.Len function that determines the string size, it simply cannot do that because it would require coercing a private struct into a Variant. Instead it calculates the size of foo at compile time and substitutes the result as a literal constant into the executable.

In your example the Len(num) calculates the compile-time size of variable num (which is 2) and substitutes the constant 2 into the resulting object code, whereas VBA.Len(num) packs the value of num into a Variant and passes that variant to VBA.Len where it's further converted to string "123" and the length of that is returned.