# Thread: Solved: Extract number of digits/characters of a variable

1. ## Solved: Extract number of digits/characters of a variable

Hi,

I have VBa function which takes a varibale (integer); is it possible to extract number digits of this variable..

when I use Len function in VBA, it is returning wrong results:
For example for

123 has 3 digits
1234 has 4 digits
123456 has 6 digits
1234567 has 7 digits

[VBA]
Public Function Calc(ByVal AccountNo As Long) As String

Dim Len_accountNo As Integer
Len_accountNo = Len(AccountNo)

Calc = Len_accountNo
End Function

[/VBA]

Will be thankful for any response...  Reply With Quote

2. That function works fine, so give an example where you think it doesn't.  Reply With Quote

3. I think the len function in vba is returning the bytes.
I got following results

Text Result 1 4 22 4 333 4 444 4  Reply With Quote

4. Text Result
1 4
22 4
333 4
444 4

I should have got
Text Result
1 1
22 2
333 3
444 4  Reply With Quote

5. sorry formatting problem, not able to set spaces  Reply With Quote

6. this is simple len functions
so where u stucks???
& why u need to make its UDF as u r calling the same len function in UDF???  Reply With Quote

7. You could use:
[VBA]Public Function Calc(ByVal AccountNo As Long) As String

Dim Len_accountNo As Integer
Len_accountNo = Len(CStr(AccountNo))

Calc = Len_accountNo
End Function
[/VBA]

Regards,
Rory  Reply With Quote

8. I am getting

Text - Result
1 - 4
22 - 4
333 - 4
444 - 4
I should have got
Text - Result
1 - 1
22 - 2
333 - 3
444 - 4
I think Len function in VBA is returning no of bytes...  Reply With Quote

9. simplest one try this

[VBA]Public Function Calc(AccountNo As Variant)
Calc = Len(AccountNo)
End Function
[/VBA]

i dont know why u are defining as string  Reply With Quote

10. I am getting output 4 for all the test values such as

1
22
333
4444  Reply With Quote

11. thanks rory;
the length function works on string ( I think); so thats I am getting the right results when I convert the input into string..

Another question:
Is it possible to use excel function from vba? Originally Posted by rory
You could use:
[vba]Public Function Calc(ByVal AccountNo As Long) As String

Dim Len_accountNo As Integer
Len_accountNo = Len(CStr(AccountNo))

Calc = Len_accountNo
End Function
[/vba]
Regards,
Rory  Reply With Quote

12. Many of them, yes. You can use Application.Worksheetfunction.Sum syntax, or just Application.Sum
Regards,
Rory  Reply With Quote

13. thanks Rory,

I am not able use Repeat function;
please have a look at me code..

[VBA]Public Function Calc(ByVal accountNo As Long)

Dim Len_accountNo As Integer
Len_accountNo = Len(CStr(accountNo))

Calc = Rept("A", Len_accountNo)

End Function

[/VBA]  Reply With Quote

14. You can use the String function:
[VBA]Public Function Calc(ByVal accountNo As Long)

Dim Len_accountNo As Integer
Len_accountNo = Len(CStr(accountNo))

Calc = String(Len_accountNo, "A")

End Function[/VBA]

HTH
Rory  Reply With Quote

15. Hello Rory,
Many many thanks indeed, for helping me in resolving this problem...  Reply With Quote  Reply With Quote