none
type mismatch error

    Question

  • Three pieces of code below

    1. msgbox val( "449%" )

    2. msgbox val( "4.49" )

    3. msgbox val( "4.49%" )

    Run each of these in turn and the results are:

    1. returns 449

    2. returns 4.49

    3. gives "Type mismatch error"

    Does any one know why the 3rd one gives an error?

    Is there a way round the problem?

    Monday, November 26, 2012 9:00 AM

Answers

  • You can use some characters as postfix in VBA to force a specific data type.

    For example, 449 will by default be interpreted as an Integer, but 449& forces VBA to treat it as a Long Integer.

    Similarly, % forces VBA to treat a number as an Integer. Since Integers and Long Integers can't contain decimal places, 4.49% and 4.49& cause a Type Mismatch error.

    You could use

    MsgBox Val(Replace("4.49%", "%", ""))

    or if you want to take the percentage into account

    MsgBox Val(Replace("4.49%", "%", "")) / 100

    Please note that VBA doesn't "know" anything about percentages.


    Regards, Hans Vogelaar

    Monday, November 26, 2012 9:20 AM
  • The variable mytext is a string containing 4.49%. The variable itself doesn't interpret this value in any way.

    The Val function takes this string and tries to interpret it as a number. It sees a % at the end, so it assumes that it must be an Integer value.


    Regards, Hans Vogelaar

    Tuesday, November 27, 2012 3:40 PM

All replies

  • You can use some characters as postfix in VBA to force a specific data type.

    For example, 449 will by default be interpreted as an Integer, but 449& forces VBA to treat it as a Long Integer.

    Similarly, % forces VBA to treat a number as an Integer. Since Integers and Long Integers can't contain decimal places, 4.49% and 4.49& cause a Type Mismatch error.

    You could use

    MsgBox Val(Replace("4.49%", "%", ""))

    or if you want to take the percentage into account

    MsgBox Val(Replace("4.49%", "%", "")) / 100

    Please note that VBA doesn't "know" anything about percentages.


    Regards, Hans Vogelaar

    Monday, November 26, 2012 9:20 AM
  • Thanks for your reply. That makes it clear what the problem is. However, I slightly over simplified things for reasons of clarity. Here is an example of what doesn't work.

    Dim mytext as string

    mytext = "4.49%"

    msgbox val(mytext)

    gives a type mismatch error. Even though mytext is declared as a string. Is this to do with the order in which VB does things? So it sees the % and turns the string variable into the type "integer" then does the val?

    Seems a bit odd. I had expected that declaring mytext as string would stop VB seeing the % as a signal to treat mytext to an integer.

    Tuesday, November 27, 2012 2:58 PM
  • The variable mytext is a string containing 4.49%. The variable itself doesn't interpret this value in any way.

    The Val function takes this string and tries to interpret it as a number. It sees a % at the end, so it assumes that it must be an Integer value.


    Regards, Hans Vogelaar

    Tuesday, November 27, 2012 3:40 PM