Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: VBA-Problem with Len() Function

    Hello to all i have this piece of code in a worsheet where Sheet1 has nine rows.
    HTML Code:
    Sub ContaRegistos()
    	Dim UltimaLinha As Long  
      With Sheet1
            UltimaLinha = (.Cells(.Rows.Count, "A").End(xlUp).Row) - 1
      End With
    	TotRegistos = UltimaLinha
    Print #intFNumber,  String(14 - Len(TotRegistos), "0")
    End SUb
    I don't understand why but String(14 - Len(TotRegistos), "0") is printing 12 (twelve) zeroes because Len(UltimaLinha) is returning 2 (two). How can this be if Sheet1 has only nine rows???
    Thanks in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does the function string() do?

    Ive never come accross it before
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Posts
    65
    It does the same thing as REPT Excel function, but with arguments in the opposite order:

    STRING(<number of times to repeat>,<string to repeat>)

    Thanks.
    Octavio

  4. #4
    Join Date
    Jun 2007
    Posts
    65
    I realize now that i posted this question in the wrong forum. Can someone please move it to Excel Forum?
    Thanks.
    Octavio

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. This is an Excel problem, not an Access problem. More generally, it is a VB/VBA problem.

    2. The Len() function returns the length of a variable, however:
    a. if the variable is a String, Len() returns the number of characters in that string:
    Code:
    Sub LenString()
    
        Dim str As String
        str = "abc"
        Debug.Print Len(str) ' --> returns 3
        
    End Sub
    b. This is also true when the variable is a Variant of subtype String:
    Code:
    Sub LenVarString()
    
        Dim str As Variant
        str = "abc"
        Debug.Print Len(str) ' --> returns 3
        
    End Sub
    c. In any other cases, except Variants, Len() is acting as the sizeof() operator in C, it returns the number of bytes used to store the variable:
    Code:
    Sub LenInteger()
    
        Dim intVariable As Integer
        intVariable = 21345
        Debug.Print Len(intVariable) ' --> returns 2
        
    End Sub
    Code:
    Sub LenLong()
    
        Dim lngVariable As Long
        lngVariable = 21345
        Debug.Print Len(lngVariable) ' --> returns 4
        
    End Sub
    Code:
    Sub LenSingle()
    
        Dim sngVariable As Single
        sngVariable = 21345.6789
        Debug.Print Len(sngVariable) ' --> returns 4
        
    End Sub
    Code:
    Sub LenDouble()
    
        Dim dblVariable As Double
        dblVariable = 21345.6789
        Debug.Print Len(dblVariable) ' --> returns 8
        
    End Sub
    d. Curiously when the variable is a Variant of a numeric subtype, Len() returns the number of bytes needed to represent the string corresponding to the number, i.e. an implicit conversion of the variable to a String subtype occurs:
    Code:
    Sub LenVarNumeric()
    
        Const c1 As Integer = 12345
        Const c2 As Long = 1234567890
        Const c3 As Single = 123.456
        Dim var As Variant
        var = c1
        Debug.Print Len(var) ' --> Returns 5 !!!
        var = c2
        Debug.Print Len(var) ' --> Returns 10 !!!
        var = c3
        Debug.Print Len(var) ' --> Returns 7 !!!
        
    End Sub
    3. @healdem: The String(Count, Char) function returns a string containing Count times the Char caracter.
    Have a nice day!

  6. #6
    Join Date
    Jun 2007
    Posts
    65
    Thanks a lot! I learned a lot with your explanation. Can you give me an idea of how can i solve this problem?
    Im my case, my variable is returning the integer 8.
    In the Immediate window:
    var = 8
    ? len(var)

    Returns 1

    Why is it returning 2 in my code?

    Thanks a lot once again.
    Octavio

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What precisely do you want to be returned by String(14 - Len(TotRegistos), "0")? Or if you prefer, what do you expect to be written to the file opened with the handle #intFNumber?
    Have a nice day!

  8. #8
    Join Date
    Jun 2007
    Posts
    65
    Hi, and thank you for helping.
    As TotRegistos variable holds the integer 8 String(14 - Len(TotRegistos), "0") should print 13 (thirteen) zeroes on the opened file. But it is printing only 12 (twelve) because Len(TotRegistos) is returning 2 (two) instead of 1 (one).
    Something must be wrong because this is clear as mud...
    Octavio

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. As the variable TotRegistos is not explicitly declared, you cannot assert that is contains an Integer value:
    As TotRegistos variable holds the integer 8
    2. You could try forcing an explicit conversion:
    Code:
    TotRegistos = CStr(UltimaLinha)
    3. You could also declare TotRegistos as String and rely on an implicit conversion:
    Code:
    Dim TotRegistos As String
    TotRegistos = UltimaLinha
    You could prevent this kind of problem by using the compiler directive:
    Code:
    Option Explicit
    in the Declaration section of the module. This would force you to declare any variable before using it.
    Have a nice day!

  10. #10
    Join Date
    Jun 2007
    Posts
    65
    The code i posted is just part of all code i'm using. I'm declaring the variable type as integer and also using Option Explicit
    I also tried your sugestion:
    HTML Code:
    TotRegistos = CStr(UltimaLinha)
    but it is not working. It still returns 2 instead of 1.
    I believe i'll have to carefully check (again) my code to see what is causing the problem...
    Thanks a lot for helping me.
    Octavio

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by oteixeira View Post
    The code i posted is just part of all code i'm using. I'm declaring the variable type as integer and also using Option Explicit
    It still returns 2 instead of 1.
    If TotRegistos is declared as integer, it's normal that Len(TotRegistos) returns 2:
    c. In any other cases, except Variants, Len() is acting as the sizeof() operator in C, it returns the number of bytes used to store the variable:
    Code:
    Sub LenInteger()
    
        Dim intVariable As Integer
        intVariable = 21345
        Debug.Print Len(intVariable) ' --> returns 2
        
    End Sub
    Have a nice day!

  12. #12
    Join Date
    Jun 2007
    Posts
    65
    I executed your code in the Immediate Window of VBA and:
    HTML Code:
    intv = 31254
    debug.Print len(intv)
     5 
    I do not have much more hair left...

  13. #13
    Join Date
    Jun 2007
    Posts
    65
    Just to let you know how i solved the problem:

    Print #intFNumber, String(14 - Len(Mid(TotRegistos, 1, 1000)), "0")

    I'm a bit reluctant to use this but could not find another solution...Variable TotRegistos holds, in fact, the integer 8...I confirmed and reconfirmed it...

  14. #14
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    He some code that I use to put leading char


    Code:
    Function EDITXT(ThisText, Leng, Txt, Leading)
        If Len(ThisText) >= Leng Then
            ThisText = Mid(ThisText, 1, Leng)
        Else
        End If
        If Leading Then
            EDITXT = String(Leng - Len(ThisText), Txt) & ThisText
        Else
            EDITXT = ThisText & String(Leng - Len(ThisText), Txt)
        End If
        If Len(EDITXT) <> Leng Then Stop ' never had it stop here
    End Function
    aaa = EDITXT("123", 7, "0", True)

    aaa = 0000123

    or

    aaa = EDITXT("123", 7, "0", False)

    aaa = 1230000

    or

    aaa = EDITXT("123", 7, "#", True)

    aaa = ####123

    open execl and then do a alt+f11 insert a module

    add the function

    now go back to excel (cells) and you be able to call the Function in a cell
    Last edited by myle; 03-06-13 at 08:20. Reason: Spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  15. #15
    Join Date
    Jun 2007
    Posts
    65
    That is a nice one! Thanks.
    Octavio

Posting Permissions

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