Results 1 to 8 of 8
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Evalute a Statement (Excel 2002/sp3)

    I was just fooling around with the instructions from the latest "Office for Mere Mortals" as follows (for those that don't receive it):
    EVALUATE A STATEMENT
    There are some hidden or undocumented functions you can use in Excel. For example, an old Excel 4.0 macro function called Evaluate can be used to calculate the result of a text expression such as 365*43 entered in an Excel cell. Information about the function is difficult to find but here, in a nutshell, is how to use it.

    Begin by typing the text expression in a cell, for example into B1 you can type 365*43 and, in cell A1 type a label such as Expression to evaluate: so you know what is what. Now choose Insert, Name, Define and type the word answer in the Names in workbook area and in the Refers to area type =Evaluate($B$1) and click Add and then Ok. Now, in the cell where you want the answer to the calculation 365 * 43 to appear type =answer.

    You can replace the expression in cell B1 with any valid expression of the type that Excel can calculate such as 9^3 to find the result of 9 cubed.

    Why use this? For starters it is handy for teaching purposes - you can display a cell formula as text in one cell and the result in a neighboring cell knowing that the display text will always match the result. _______


    Anyhow, I can't make it work - what am I missing?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    I don't know if the attached worksheet will work for you - the macro function might not be translated correctly. If you get #NAME, click in an empty cell, select Insert | Name | Define..., click on Answer and edit the definition so that it reads

    =evaluate($B$1)

    then click Add and OK.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    Oh, of course I didn't name the range properly. I thought there was something special about the word "Answer".

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    I was able to make this work as described in the newsletter, but I was confused as to how the function could work when used in a range name, but not when typed directly into a cell. If I type 4+5 into A1 and =evaluate(A1) into B1, I get a dialog box that says "That function is not valid". Are there other functions that only work when used in a range name? Maybe I'm not familiar enough with Excel4. I didn't start using Excel until version 5.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    You can use the old Excel 4.0 macro functions in defined names, and hence indirectly in a formula in a cell, but not directly in a formula in a cell. The Excel 4.0 Macro Help file is available from Download details: Excel 2000 Help File: Running Excel 4.0 Macros.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    Does anyone know if functions like EVALUATE will continue to work in Excel 2007?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    According to Application Object Members [Excel 2007 Developer Reference], ExecuteExcel4Macro is still a member of the Application object in Excel 2007, so I suppose the answer is Yes.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Evalute a Statement (Excel 2002/sp3)

    Yes, they still work.

    See arg2name.zip on my page for some examples of what can be done with those xlm macro functions.

    Also, there is some info on them here: XLM In Names
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •