Results 1 to 7 of 7

VBA Function Val Problem

This is a discussion on VBA Function Val Problem within the Excel Questions forums, part of the Question Forums category; I am using teh Val function in my vba code and it rounds off the number in the string, for ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    76

    Default

    I am using teh Val function in my vba code and it rounds off the number in the string, for example "28.7" to 29 and "28.3" to 28.
    Is there a way to report the numbers in the string as they are!
    Thanks for the help.
    Karraj

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167

    Default

    Have tested this and don't agree that this happens, are you outputting the result to cells where the format does not allow the detail to be shown?

    I usually use the newvalue=ABS("28.4") function for this, but guess only due to habit.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    76

    Default

    It happens if there is a % sign at the end ie. "28.4%"

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Hi Karraj

    I am not to sure what you want returned, but this may help

    Dim sVal As Single

    sVal = CSng(Left("28.4%", Len("28.4%") - 1))
    [a1] = sVal / 100
    End Sub

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    76

    Default

    any pointers why rounding is done with the %?
    stripping the text of % returns the number?

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Sure

    For Excel Help:

    The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized

    This also includes decimal points, as you have found out.



  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167

    Default

    solution of using Left() would work fine so long as the % is always there. else you could use a separate function to strip invalid characters from the string before converting it to a value...


    Sub test()
    Range("b2").Value = trim_numeric("21.46%")
    End Sub

    Function trim_numeric(strg As String) As Double
    strg2 = "0"
    For charpos = 1 To Len(strg)
    charx = Mid(strg, charpos, 1)
    If (charx >= "0" And charx <= "9") Or charx = "." Then
    strg2 = strg2 & charx
    End If
    Next
    trim_numeric = Abs(strg2)
    End Function

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com