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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
It must be the TRUE/FALSE.

Wouldn''t this work

Application.Evaluate("NOT($J$27=0)")
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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() ?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Good point Colin, mine maybe should be


Application.Evaluate("NICHT($J$27=0)")
 

50ld13r

New Member
Joined
Dec 27, 2007
Messages
44

ADVERTISEMENT

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
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

50ld13r

New Member
Joined
Dec 27, 2007
Messages
44
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,581
Messages
5,832,590
Members
430,144
Latest member
bruno92

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
Top