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???
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
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
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.