Excel formulas (e.g. sum, iferror) in VBA

[excelstarter1]

Hi guys,

I am stuck with a little problem. I want to run a macro which adds the iferror function to the selected cell. It does work with the following code, however I dont want to be bound to the "local" formula. E.g. when the macro is used within the organization in Spain or France, the formula should be used/displayed correctly. If a colleague in France opens my macro (in his French Excel Version), the formula does not work because Excel does not recognize the English iferror term.

selection.FormulaLocal = "=IFERROR(" & Right(selection.FormulaLocal,  Len(selection.FormulaLocal) - 1) & ";" & Chr(34) & "error"  & Chr(34) & ")"

(1) How do I get rid of the FormulaLocal part and tell Excel to insert the "correct" iferror formula, no matter which Excel version is used.

(2) Furthermore, I want to run a macro which checks a selected cell for an iferror formula. So basically if the selected cell starts with =iferror(...;"error") then I want to get rid of the "error") part at the end and replace it by "no value") for example. It is important that only formulas with iferror at the beginning are selected. The iferror formula should not be embedded in a longer formular, e.g. =sum(iferror(...).

Do you have an idea how to do that in VBA??

Thank you very much in advance!

Regards

 

[excelstarter1]

I just read something about the WorksheetFunction.IfError or Application.IfError Function. Would that help in my case? I just don't know how to implement it...

 

[Marcelo Branco]

To get rid of FormulaLocal all you have to do is:
Change every instance of FormulaLocal to Formula
Change the argument separator from semicolon to comma

Something like
Selection.Formula = "=IFERROR(" & Right(Selection.Formula, Len(Selection.Formula) - 1) & "," & Chr(34) & "error" & Chr(34) & ")"

M.

 

[Marcelo Branco]

Remark
Provided the user has Excel 2007 or higher (IFERROR is not available on previous versions) the code above should work. Worked for me (Excel 2010 Brazilian version).

For earlier versions you should use
=IF(ISERROR(formula_here),"error",formula_here)

M.

 

[excelstarter1]

Awesome, it works perfectly. Thanks for the help Marcelo!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.