Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Bloomingdale, IL USA
    Posts
    282

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

    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.

    Code:
        
        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"

    Name:  CellasText.jpg
Views: 102
Size:  21.4 KB



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

    Name:  ErrMessage.jpg
Views: 103
Size:  12.1 KB

    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

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Bloomingdale, IL USA
    Posts
    282

    Re: VBA automatically adds a leading zero to a number stored as text

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

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,287

    Re: VBA automatically adds a leading zero to a number stored as text

    Quote Originally Posted by Chrissy View Post
    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>

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Bloomingdale, IL USA
    Posts
    282

    Re: VBA automatically adds a leading zero to a number stored as text

    Quote Originally Posted by wqweto View Post
    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.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width