Left and Len not working

[moishy]

Hello to All,

I came across a weird issue, with the following statement

Left(r$, Len(r$) - 1)

It should remove a character from the right (which it does) the problem is that it also removes a character from the left.
Any ideas on what caused the problem and how to remedy it will be greatly appreciated!!!

 

[gemma-the-husky]

it should not do what you said. can you give an example?

 

[moishy]

Here you go:

Option Compare Database

Function GetGimatrics$(ByVal Num%)


    Dim r$
    Dim Digit%

    r$ = ""

    If Num% >= 1000 Then
        r$ = GetGimatrics$(Num% \ 1000)
        Num = Num Mod 1000
    End If

    If Num% >= 900 Then r$ = r$ + "zzw"

    If Num% >= 500 And Num% < 900 Then
        r$ = r$ + "z"
        r$ = r$ + Chr$(Asc("w") + (Num \ 100 - 5))
    End If

    If Num >= 100 And Num < 500 Then
        r$ = r$ + Chr$(Asc("w") + (Num \ 100 - 1))
    End If

    Digit = (Num Mod 100) \ 10
    If Digit Then
        Select Case Digit
            Case 1: r$ = r$ + "j"
            Case 2: r$ = r$ + "k"
            Case 3: r$ = r$ + "l"
            Case 4: r$ = r$ + "m"
            Case 5 To 7: r$ = r$ + Chr$(Asc("n") + Digit - 5)
            Case 8: r$ = r$ + "o"
            Case 9: r$ = r$ + "p"
        End Select
    End If

    Digit = (Num Mod 10)
    If Digit Then r$ = r$ + Chr$(Asc("a") + Digit - 1)

    If Len(r$) >= 2 Then
        r$ = Mid(r$, 1, Len(r$) - 1) & Chr(34) & Mid(r$, Len(r$), 1)
    End If

' This line should only remove the first letter
' But in reallity it removes the first and last
        r$ = Left(r$, Len(r$) - 1)

    GetGimatrics = r$

End Function

 

[vbaInet]

And have you tested what r$ contains before the "faulty" code line?

 

[moishy]

yes, I don't understand but it only holds one chr.

 

[vbaInet]

Replace that line of code with this:

' This line should only remove the first letter
' But in reallity it removes the first and last
        Debug.Print "before: " & r$
        r$ = Left(r$, Len(r$) - 1)
        Debug.Print "after: " & r$

And tell me what you see in the Immediate Window.

I tested it and there's nothing wrong with Left() or Len().

 

[gemma-the-husky]

I am not sure exactly what your code is doing - but maybe one of the steps is generating a non-printing character (eg a backspace) which might produce strange effects.

I would put a breakpoint in, and step through your sub a line at time - or put in a load of msgboxes so you can trace what is happening

 

[moishy]

VbaInet,

This is what I get;

?Test(5770)
before: zy"p
after: zy"
zy"

 

[vbaInet]

So there's nothing wrong. What were you expecting to get?

 

[moishy]

You're right in English it works but if I replace the English letters in the above code (post #3) with their equivalent in a Hebrew (right to left orientation) it removes both the first and the last letters.
I'm totally confused since if I add your debug print lines to the original code when I run the code this is what I get in the immediate window:

?GetGimatrics$(5772)
before: ה
after: 
before: תשע"ב
after: תשע"
תשע"

it should be:
before: התשע"ב ' (that is what shows if I comment  the r$ = Left(r$, Len(r$) - 1) line)
after: תשע"ב

here is the original Function (same as one in post #3, only hebrew letters are used instead of English ones:

Function GetGimatrics$(ByVal Num%)

    Dim r$
    Dim Digit%

    r$ = ""

    If Num% >= 1000 Then
        r$ = GetGimatrics$(Num% \ 1000)
        Num = Num Mod 1000
    End If

    If Num% >= 900 Then r$ = r$ + "תתק"

    If Num% >= 500 And Num% < 900 Then
        r$ = r$ + "ת"
        r$ = r$ + Chr$(Asc("ק") + (Num \ 100 - 5))
    End If

    If Num >= 100 And Num < 500 Then
        r$ = r$ + Chr$(Asc("ק") + (Num \ 100 - 1))
    End If

    Digit = (Num Mod 100) \ 10
    If Digit Then
        Select Case Digit 
            Case 1: r$ = r$ + "י"
            Case 2: r$ = r$ + "כ"
            Case 3: r$ = r$ + "ל"
            Case 4: r$ = r$ + "מ"
            Case 5 To 7: r$ = r$ + Chr$(Asc("נ") + Digit - 5)
            Case 8: r$ = r$ + "פ"
            Case 9: r$ = r$ + "צ"
        End Select
    End If

    Digit = (Num Mod 10)
    If Digit Then r$ = r$ + Chr$(Asc("א") + Digit - 1)

    If Len(r$) >= 2 Then
        r$ = Mid(r$, 1, Len(r$) - 1) & Chr(34) & Mid(r$, Len(r$), 1)
    End If

        Debug.Print "before: " & r$
        r$ = Left(r$, Len(r$) - 1)
        Debug.Print "after: " & r$

    GetGimatrics = r$

End Function

 

[gemma-the-husky]

so it would not surprise me if "left" does not do what you expect, in a right-to-left language

the windows locale settings may affect the way the vba function works.

try replacing left with right, and see what happens.

 

[vbaInet]

so it would not surprise me if "left" does not do what you expect, in a right-to-left language

Yes it must be the Reading Order. It's interesting how it reverses some chars then chops off the last character.

before: תשע"ב
after: תשע"

 

[moishy]

Thank you both I solved the mystery!

 

[vbaInet]

What did you do?

 

[moishy]

Added the following:

    If Num% >= 1000 Then
        If Len(r$) >= 2 Then
            r$ = Mid(r$, 1, Len(r$) - 1) & Chr(34) & Mid(r$, Len(r$), 1)
        End If
    End If
    
    If Len(r$) > 2 Then
        r$ = Right(r$, Len(r$) - 1)
    End If

Instead of:

If Len(r$) >= 2 Then         
    r$ = Mid(r$, 1, Len(r$) - 1) & Chr(34) & Mid(r$, Len(r$), 1)     
End If       
   
Debug.Print "before: " & r$         
r$ = Left(r$, Len(r$) - 1)         
Debug.Print "after: " & r$

 

[vbaInet]

I don't know how that impacts on your function but I would advise you give that some thorough testing. Remember, the problem wasn't to do with your function but was to do with the way Left() and Len() were interpreted by your Locale settings.

 

[moishy]

It had nothing to do with the Locale settings, I used Left() instead of Right (), and the problem I was having with Len() was due to the fact that after the function is called it calls itself again so it really was removing the last chr twice (the first time around the variable is only one chr) once for each call.

 

[vbaInet]

Now, I didn't even notice the recursive part. Good eye moishy.