CDbl giving type mismatch error

[welchs101]

Hi, I wrote a macro for someone and in this macro there is a section where i take a string.....strip off special character and spaces and i am left with somethign like this "198.40"

Basically a string.

i then use CDbl to convert it to a number.

Junk1=CDbl("198.40")


but for some reason i get a type mismatch error at this line...........welll actually my friend gets a type mismatch error at this point ......AND I DO NOT get error.

ANy ideas?

Note: i can not publish code with example files as some have private information.

[welchs101]

dont know if this is important but i am in the USA and my friend is in Poland.

[Haluk]

Without the file, it's almost impossible to guess what's going run.

Try to include sample data in a workbook, WITHOUT confidential information and attach here.

Note: As per your second message, it seems that the problem is arising because of the decimal point character used in USA and Poland are different.

[Richard Buttrey]

What version of Excel is your friend using?

These things are easier to debug if we can see the workbook. Are you not able to remove stuff that's not involved and anonymise anything that's left and relevant.

[JohnnySix]

It may be that US / Poland have different qualifiers for strings, and/or for numbers ( thousand's separator / decimal qualifier ).

In some countries , the decimal place is a comma, others it's a period/full-stop.

TL:DR Looks like it's that Poland uses a comma, USA uses a period.

Google : brilliantmaps.com/decimals/ ( Since I'm not allowed to post links / images etc.. )

[Richard Buttrey]

dont know if this is important but i am in the USA and my friend is in Poland.

Have you tried temporarily changing your Windows Regional setting to the same as your friend's and seeing whether you get the same problem. That would confirm whether a regional setting is the problem.

[Haluk]

@welchs101;

Can you try this:

Please Login or Register  to view this content.

[welchs101]

thanks all i will look into all of those questions and get back to you.........quick question how to change regional settings that you are referring to ..... i just want to make sure i am changing the right thing

[Haluk]

In Office 2010;

File >> Options >> Advanced > Editing Options [Uncheck the box "Use system operators"] and change the settings.

[Andy Pope]

get them to do a simple test, via the immediate window.

if they try with . or , do they get the same values as below? Well I guess they would actually get the reverse as , is the decimal separator

Please Login or Register  to view this content.

ALT+F11 to open VBE
CTRL+G to open Immediate window
Paste ?CDbl("198.40"),CDbl("198,40")
and press enter

[welchs101]

ok, still verifying all the things you mentioned. i did go to settings and they are using "," instead of "." but in my code i accounted/checked for this.

I did try replacing "CDbl" with "Val" and............IT WORKED!!!!!!!

But i have NO IDEA WHY?

Any thoughts?

[Andy Pope]

Val may not have raised an error but are you sure you are getting the correct value?

Please Login or Register  to view this content.

Note the loss of 40 and the factor reduction 198 instead of 19840.

[welchs101]

so i did the test you asked for using the immediate window and i will show the results below

Val("198.40")
198,4

Val("198,40")
198

CDbl("198.40") => Produced the Type Mismatch Run-Time Error 13

CDbl("198,40")
198,4


So the CDbl("198.40") produced the type mismatch error...........still not sure why really
and the Val did not produce any error. so thats good right?

[Andy Pope]

The VAL result is only any good if you don't mind the value being different to the amount you thought it might be.

For me , and . return values without error. But I don't have Polish regional settings.

This help page mentions not to use VAL.
https://docs.microsoft.com/en-us/off...sion-functions

Add here is lot more info on regional isses.
http://www.oaltd.co.uk/excelprogref/...rogrefch22.htm

[sintek]

Works for me...

Please Login or Register  to view this content.

[welchs101]

note: all of the work i did in the immediate window was done on friends computer in poland..........they did it and sent me screen shots.
that work was not done on "my computer in the USA" but rather on "a computer in poland"

Please Login or Register  to view this content.

did NOT work on the computer in poland it gave a type mismatch error
on MY computer the code above does work


andy, i have checked several files after running using the VAL function and it seems to give the right result. Note: for some reason the numbers in the "file" that the macro is
processing has "." and not "," I am checking each number and if one exists that has a "," in it i convert it to a "." if i do this i think i will be ok but i will read
the links you sent before i decide for sure......if i dont use VAL what should i use? do i even have an alternative?

[Andy Pope]

If you can determine the locale then you could do the , . replacement before CDBL.

[Haluk]

@welchs101;

The code in message #7 should work in both US and Poland locales ... Did you check it ?

It should get a valid CDbl value (198.40 or 198,40) depending on the locales.

[welchs101]

?How would knowing the local help me? Also, how to even identify the local within excel-vba?

What i am doing right now is this...........i look to see if the "3rd" character in the number is a "comma".

if it is then i convert this comma to a "." but leave all other commas if they are present. Then i use the VAL command.......

But i still dont understand why the CDbl gives a mismatch error...........do you get why this is so?

[welchs101]

Haluk, i have not tried it .....i will try it .....if i cant contact my friend i will try tomorrow and let you know.

thanks.

[Andy Pope]

determine which character is what

Please Login or Register  to view this content.

[welchs101]

Andy thanks , i did not know this even existed.

Haluk, i tried your code on my machine...works fine. and you're right it should work on computer in poland so long as i do a
little checking on value to make sure it has a decimal separator and not just an integer value.

[welchs101]

again, thanks for everyone's input but i just wanted to ask this again so i understand something.

If i used CDbl("198.40") on PolandCOmputer.........i get type mismatch

so why dont i get a similar error when i do CDbl("198,40") which equal 19840 on my USAcomputer

and just to be clear........do you all agree that i can use "VAL" as long as i remove the decimal separator if its a comma?
it seems to work anyway i just have not used it as much as the CDbl

[welchs101]

not sure if this is applicable or even correct but i found this statement online and wanted to share

Please Login or Register  to view this content.

[welchs101]

andy, i was going back and reviewing the posts........in Post@17 it sounds like you're saying to convert "," to "." (if there is one) and then use the CDbl.......is this right?

Because if it is this is what i tried in the beginning and it did not work.

recall that you suggested i use the intermediate window to test the CDBL("198.40") on the polandcomputer and it gave a type mismatch error

[Andy Pope]

It would be the other way. Replace the decimal point in the text with , before CDBL. put this would be on the Polish PC

[Haluk]

....... so long as i do a little checking on value to make sure it has a decimal separator and not just an integer value.

If it looks like an integer, you can add a period and then use the same code;

Please Login or Register  to view this content.

[welchs101]

hey, let me ask a dumb question.........if i ask my friend to change their region settings...........would that fix the problem as well?

[welchs101]

fyi: i tried changing decimal separator from "," to "." on polandcomputer and this did not work..........


"File >> Options >> Advanced > Editing Options [Uncheck the box "Use system operators"] and change the settings." this did not work



do i need to change the "REGION" setting or somethign?