Answered by:
VBA script  How to evaluate the value of an EXCEL formula assigned to a Name?
Question

Hi all,
one of the possibilities of using Names in EXCEL is to attach a formula to a name instead of constants or references/ranges, e.g. =(Description!$B$15 = AnlagenschlüsselLengthÜNB) which evaluates to True or False when used in a cell. Nothing special and often used to ease changemangement of complex formulars.
When accessing the Name thru something like ActiveWorkbook.Names("Checkit").Value or .RefersTo I get the string of the formula and not the value of the result.
So my question is how to get the calculated result of such formulas behind names in a VBA script. I know Application.Calculate() method but this seems to refer to formula in cells only.
Regards Jörg
 Edited by Jörg Debus Wednesday, March 9, 2016 3:13 PM
Answers

Hi folks,
looking at older Help files available locally sometime helps. Here is how to evaluate formular strings: Method Application.Evaluate(<string>) does the trick.
Using my code fragment above:
Dim Value As Boolean
Value = Evaluate("=(Description!$B$15 = AnlagenschlüsselLengthÜNB)")results in True or False. A formula in a cell referenced from a range will be evaluated as well.
Would be nice if someone could check this and credits this as an answer ;)
Regards Jörg
 Marked as answer by Jörg Debus Friday, March 11, 2016 8:29 AM
All replies


Hi folks,
looking at older Help files available locally sometime helps. Here is how to evaluate formular strings: Method Application.Evaluate(<string>) does the trick.
Using my code fragment above:
Dim Value As Boolean
Value = Evaluate("=(Description!$B$15 = AnlagenschlüsselLengthÜNB)")results in True or False. A formula in a cell referenced from a range will be evaluated as well.
Would be nice if someone could check this and credits this as an answer ;)
Regards Jörg
 Marked as answer by Jörg Debus Friday, March 11, 2016 8:29 AM
