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

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 © 2020 vBulletin Solutions Inc. All rights reserved.