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?

Answer 1

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

Answer 2

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.

Answer 3

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