Why are Len and VBA.Len returning different results?
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 one Msgbox
displays 2
and the second Msgbox
displays 3
. If I remove the first line Dim num As Integer
,, is displayed in both boxes 3
. Can someone explain why?
source to share
This has to do with how VB stores integers and how the function Len()
handles arguments that are not strings.
When a data type that is not a string is passed to a function Len()
, it returns the nominal number of bytes used to store the data (VB uses 2 bytes to store an integer). See the documentation for the functionLen
.
The function Len()
will automatically add a variant variable (which is created by assigning a value to the variable without declaring it first) as a string. The reclaim does not change because the storage allocation changes (although it does; the variants require 16 bytes of storage space, minimum). Since an implicitly declared variable is actually a variant of a variant, VB will automatically change its type depending on the situation. It Len()
expects a string in this case , so VB makes the variable a string.
You can use Msgbox Len(Cstr(num))
to convert an integer variable as a string before passing it to a function Len
if you intend to return the number of characters in your integer value.
source to share