none
VBA script - How to evaluate the value of an EXCEL formula assigned to a Name? RRS feed

  • 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
    Wednesday, March 9, 2016 9:17 AM

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
    Wednesday, March 9, 2016 3:27 PM

All replies

  • Name is normally use for Range of cells. For individual Cell then you can just point to the cell itself for example, Range("C10"). Value.

    chanmm


    chanmm

    Wednesday, March 9, 2016 2:01 PM
  • 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
    Wednesday, March 9, 2016 3:27 PM
  • Hi Channm,

    thanks for your hint. But referencing the value in a cell was not my goal. I want to evaluate a formula directly attached to an Name. See my screen shot.

    A formula attached to a Name (Excel)


    Regards Jörg

    Wednesday, March 9, 2016 3:34 PM