VBA Type mismatch Error - inconsistency

Question

Okay, I'm so confused right now, I have written some VBA which is a plugin in Excel. There is a line which reads:

obsValue = CDbl(dataSplit(1))

The value of dataSplit(1) is the String 21440.5395043259

This line of code works okay...for me. For another user of my code they get

Run time error '13:' Type mismatch.

They click on debug, it takes them to this line of code, and they hover their cursor over the dataSplit varaible, and it does indeed read the same value as above.

We are both using Excel 2010.

How can this be?

How on earth is it easier to post a screenshot of code rather then simply copying and pasting the code from the VBE into your question? — user4039065
@Jeeped True, but the screenshot does do a nice job of showing the location of the error, and the value of the variable. — ThunderFrame

ThunderFrame ThunderFrame · Accepted Answer · 2017-05-23T11:38:42

The language settings of Excel will determine what the decimal separator is. It seems likely that for your user, their language settings are different, and so the "." is probably treated as a thousands separator. The CDbl function is locale aware, so it "sees" numeric strings according to the regional settings of the current user.

You could try using the Val function, which will always treat the period as a decimal separator.

obsValue = Val(dataSplit(1))

VBA Type mismatch Error - inconsistency

1 Answers