application.evalaute error 2015 when in germany

50ld13r

New Member
Joined
Dec 27, 2007
Messages
44
HI,

I have developed a bit of VBA code in the UK and have sent to someone in germany. They are getting type mismatch error and it is caused by the statement

Application.Evaluate(FC.Formula1)

where FC.Formula1 was quite a complex conditional format formula. I simplified the formula with:

Application.Evaluate("IF($J$27=0;FALSE;TRUE)=TRUE")

this again produced the same error. The formula works perfectly fine on the sheet and when i set my regional settings to UK. As soon as i change it to german i get the error message.

I also changed application to workbook which resulted in the same error message

do any of you guys know why this would be the case and could you point me in the right direction towards a solution.

Many thanks
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
It must be the TRUE/FALSE.

Wouldn''t this work

Application.Evaluate("NOT($J$27=0)")
 
Upvote 0
You have to be really careful evaluating strings such as that: the names of worksheet functions vary depending on the language. I think the German equivalent of IF() is WENN() ?
 
Upvote 0
Good point Colin, mine maybe should be


Application.Evaluate("NICHT($J$27=0)")
 
Upvote 0
hi thanks for your quick replies.

Regarding the formula this is for demonstration purpurse your new formula also did not work. I have tried it with may different formulue and all have failed - although syntactically correct

Regarding the difference in the If statement - the formula's work on the worksheet. If they work here i would suspect they are ok when evaluating (possibly could be wrong though) - i will test further
 
Upvote 0
I don't have much experience of this but my guess is it would be more robust (and possibly more efficient) to avoid Evaluate altogether. If I got hit with a problem like this, I think I'd either look to store the formula in a cell and read the result, or I'd look to split the formula logic up into "VBA native expressions" (If statements, loops etc). Calling the functions via the WorksheetFunction object would also be an option. You've most likely dealt with this sort of thing more than me Bob, so you might have a more qualified opinion?

Regarding the difference in the If statement - the formula's work on the worksheet. If they work here i would suspect they are ok when evaluating (possibly could be wrong though) - i will test further
Formulas (functions, booleans etc) in the worksheet would automatically be converted; string expressions in VBA would not which is why the evaluation fails.
 
Last edited:
Upvote 0
hi colin,

i think your idea about having the formula evaluate on the worksheet will be the best option currently - is strange that the applciation.evaluate is not universal.

Thanks for your help
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top