application.evalaute error 2015 when in germany

50ld13r

New Member
Joined
Dec 27, 2007
Messages
38
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
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
38

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, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
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
38
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,573
Messages
5,512,132
Members
408,881
Latest member
Pooxie

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top