Len function in excel vba is not giving the expected result

Question

In my code I'm using the len() function to get the length of the data content assigned to a variable. And here I found some strange behavior - but see my code first and then my annotations / questions.

Dim Array(1 To 3) As Integer

' some code

Array(1)=345

' some code

If len(Array(1)) = 3 Then
    ' do this (here is the expected code bunch)
Else
    ' do this (and here the unexpected code bunch)
End If

Google guided me to MS len()-function and here I read the first sentence with an "or" and (for me) no further explanation, which is a little confusing to me...

(Len function) Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

On a second Google recommended site I found the very helpful hint that

VBA Len Function can count the number of characters in variables declared as strings or variants
If VBA Len is used with an integer, long, single or double then VBA Len is going to count the number of bytes needed to store the variable

Okay, but how can I get the length of the data content if the variable is Integer - like in my code snippet?

Here I found in these links (Link_1, Link_2) the suggestion of conversion by using TEXT() and CStr() - the second link recommends CStr() because of some drawbacks of TEXT().

Now my Question(s):

Is it a proper way to use CStr() to solve my issue?
And on the other hand an additional question arises because of my research: How can I get the amount of used bytes from variables declared as strings or variants?

Thanks a lot for your help in advance!

FunThomas · Accepted Answer · 2022-10-10T11:54:49.010

When you use Len on a string, it will give you the length of that string. Type in the immediate window:

? Len("123")
3
? Len("ABCDE")
5

Note that there are some characters that are stored as 2 bytes, see https://stackoverflow.com/a/55418901/7599798, so to be precise, Len will return the number of bytes needed for that string.

When you use Len on a non-string variable (your array contains integers), it will return the number of bytes used by this variable. An integer needs 2 bytes (ok, technically it uses 4 bytes nowadays, but that's a different story). A Long will return 4, a Double 8 - no matter what value is stored in those variables.

It seems you want to check if the number contains 3 digits: Either convert the number using CStr into a String:

 If Len(CStr(Array(1))) = 3 Then

Or (what seems to be more reasonable to me), check the value directly

 If Array(1) >= 100 And Array(1) <= 999 Then

Len function in excel vba is not giving the expected result

1 Answers1