Solved: Extract number of digits/characters of a variable

[surya prakash]

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...

[Bob Phillips]

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

[surya prakash]

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

[surya prakash]

Text Result
1 4
22 4
333 4
444 4


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

[surya prakash]

sorry formatting problem, not able to set spaces

[anandbohra]

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???

[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

[surya prakash]

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...

[anandbohra]

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

[surya prakash]

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

1
22
333
4444

[surya prakash]

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?

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

[rory]

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

[surya prakash]

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]

[rory]

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

[surya prakash]

Hello Rory,
Many many thanks indeed, for helping me in resolving this problem...

[rory]

Glad to help!
Rory