Consulting

Results 1 to 16 of 16

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

  1. #1

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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    That function works fine, so give an example where you think it doesn't.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

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

  5. #5
    sorry formatting problem, not able to set spaces

  6. #6
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    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???

  7. #7
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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

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

  9. #9
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    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

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

    1
    22
    333
    4444

  11. #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?



    Quote 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

  12. #12
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Many of them, yes. You can use Application.Worksheetfunction.Sum syntax, or just Application.Sum
    Regards,
    Rory

  13. #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]

  14. #14
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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

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

  16. #16
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Glad to help!
    Rory

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •