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,418
    Provided Answers: 7
    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 09:20. Reason: Spelling
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •