PDA

View Full Version : Solved: Extract number of digits/characters of a variable



surya prakash
07-24-2007, 01:07 AM
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




Public Function Calc(ByVal AccountNo As Long) As String

Dim Len_accountNo As Integer
Len_accountNo = Len(AccountNo)

Calc = Len_accountNo
End Function




Will be thankful for any response...

Bob Phillips
07-24-2007, 01:09 AM
That function works fine, so give an example where you think it doesn't.

surya prakash
07-24-2007, 01:12 AM
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
07-24-2007, 01:13 AM
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
07-24-2007, 01:14 AM
sorry formatting problem, not able to set spaces

anandbohra
07-24-2007, 01:19 AM
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
07-24-2007, 01:19 AM
You could use:
Public Function Calc(ByVal AccountNo As Long) As String

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

Calc = Len_accountNo
End Function


Regards,
Rory

surya prakash
07-24-2007, 01:21 AM
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
07-24-2007, 01:25 AM
simplest one try this

Public Function Calc(AccountNo As Variant)
Calc = Len(AccountNo)
End Function



i dont know why u are defining as string

surya prakash
07-24-2007, 01:32 AM
I am getting output 4 for all the test values such as

1
22
333
4444

surya prakash
07-24-2007, 01:35 AM
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:
Public Function Calc(ByVal AccountNo As Long) As String

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

Calc = Len_accountNo
End Function

Regards,
Rory

rory
07-24-2007, 01:45 AM
Many of them, yes. You can use Application.Worksheetfunction.Sum syntax, or just Application.Sum
Regards,
Rory

surya prakash
07-24-2007, 01:48 AM
thanks Rory,

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

Public Function Calc(ByVal accountNo As Long)

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

Calc = Rept("A", Len_accountNo)


End Function

rory
07-24-2007, 02:05 AM
You can use the String function:
Public Function Calc(ByVal accountNo As Long)

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

Calc = String(Len_accountNo, "A")


End Function

HTH
Rory

surya prakash
07-24-2007, 03:03 AM
Hello Rory,
Many many thanks indeed, for helping me in resolving this problem...

rory
07-24-2007, 03:12 AM
Glad to help!
Rory