[RESOLVED] VBA automatically adds a leading zero to a number stored as text

[Chrissy]

I have an Excel spreadsheet where the user enters a code and then clicks a button. The macro takes the code and looks it up in the database to return data. The issue I am having is that the code being entered is .16225 and VBA is adding a leading zero so the query is returning "Not Found". I don't understand why it is adding the leading zero. The cell is formatted as Text and when using in my query I am using Format(). Below is the snippet of code along with a visual of how it is being interpreted.

    
    Call rsInit(rsData)
    rsData.Source = ("SELECT * FROM bank WHERE scode = '" & Format(Cells(xRow, xCol)) & "'")
    rsData.Open

    If rsData.EOF Then
        MsgBox Format(Cells(xRow, xCol)) & " is not a valid bank code in Yardi.", vbOKOnly, "Invalid Bank Code"
        GoTo WrongCode
    End If

Image showing the cell is formatted as "Text"





Image showing the error message and how it is being interpreted by VBA.



How do I get rid of the leading zero? And it's not as easy as coding to remove a leading zero. There could in fact be valid codes that do begin with a zero.

Thank you for any help you can offer!
Chrissy

[Chrissy]

I am answering my own question. Apparently if I use Cstr instead of Format, that works. Strange... never had this issue before!

[wqweto]

Strange... never had this issue before!

It's always the best to know how things work, usually means to find the root cause of any problem you meet.

What you have here is a String as a result of Cells(xRow, xCol) invocation. Then you call Format with this String as a parameter but Format expects a number so your String (in a Variant parameter) is cast to Double at some point inside Format code, then this Double is formatted and the result returned by Format is a String.

You "fixed" your code by substituting Format for CStr so now you call CStr on a String just to get a String. This is called no-op from "no operation" like "nothing happens here" and usually is completely redundant and dead code should be quickly removed during code reviews.

The moral of the story: Don't call Format *on Strings* if you don't want to format anything as it might change your input into so called "canonical representation" of the number.

cheers,
</wqw>

[Chrissy]

It's always the best to know how things work, usually means to find the root cause of any problem you meet.

What you have here is a String as a result of Cells(xRow, xCol) invocation. Then you call Format with this String as a parameter but Format expects a number so your String (in a Variant parameter) is cast to Double at some point inside Format code, then this Double is formatted and the result returned by Format is a String.

You "fixed" your code by substituting Format for CStr so now you call CStr on a String just to get a String. This is called no-op from "no operation" like "nothing happens here" and usually is completely redundant and dead code should be quickly removed during code reviews.

The moral of the story: Don't call Format *on Strings* if you don't want to format anything as it might change your input into so called "canonical representation" of the number.

cheers,
</wqw>

That explains why it was adding the leading zero, thank you for the explanation! I guess I don't need Cstr() either.