Evaluate - Most Powerful Command in VBA?

  • Re: Evaluate - Most Powerful Command in VBA?


    Hi

    I know this thread is years old, but it keeps coming at the top of my Google search about EVALUATE

    I would fully support the concept that EVALUATE is POTENTIALLY of the most powerful command in Excel VBA



    However, unless I am missing something, there is a very big BUT after that statement.

    The major problem that I have with EVALUATE is debugging. If there is a cell with an normal = sign and a function, you can use the evaluate formula auditing tool to step through the evaluation step by step. You could also put break points or stops in the function to step through the code and debug.

    However, EVALUATE in VBA does not allow any of this. It does not allow you to “step into” the evaluation, nor does it even stop at stops or break points in the function being evaluated.

    If I could overcome this problem, I would be the fist to support the concept that EVALUATE is actually the most powerful command in Excel VBA



    Does anyone know how to overcome the problem?

    Thanks in advance for any advice

    Ian

  • Re: Evaluate - Most Powerful Command in VBA?


    I appreciate that this thread is fairly old, but I thought it worthwhile to add a caveat to the greatness of the .Evaluate method (and it is indeed great in many instances)...


    If you have a formula that references a named range that spans a few columns (i.e. the named range is 1 row by many columns, or even refers to a whole row), Excel will pull the corresponding value from the named range that matches the column your forumula is in. For instance, if you have a named range called 'Test' that refers to cells A1:Z1 and you enter the formula '=Test' into cell F2 then Excel will look at the named range and return the value that is in column F of that named range (essentially the intersect of the named range and the column of the cell your formula is in). This is handy in many situations (especially building timeline based models, as I do for a living). Unfortunately, the .Evaluate method doesn't know what to do here because it's not being called with reference to any source cell on the worksheet and so it can't know which cell within the named range you want, so it returns the whole range.


    Whilst this behaviour might be useful in some circumstances, it means that .Evaluate won't return the same value as an equivalent formula would return, so can't be used as a proxy in all situations. Unless I'm missing something...?


    HTH,


    Thinqer.

  • Re: Evaluate - Most Powerful Command in VBA?


    Hi Thinqer


    I don't know whether the following is relevant to your situation, but the way that I overcame the problems with .Evaluate being used in VBA is as follows:


    In VBA you may have the string of the command that you wish to evaluate (in your case "Test"). You use VBA to add the string"=" to the front of the formula and then get vba to write it to any cell. Excel then evaluates it in the spreadsheed, rather than in VBA. You can then use VBA to read the cell and allocate the answer to a variable.


    I hope that make sense


    Ian

  • Re: Evaluate - Most Powerful Command in VBA?


    You can just adapt your formula to use a construction like
    =INDEX(range,1,column(formula_cell))
    and pass that to Evaluate. IMO that is preferable anyway to using an implicit intersection.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Evaluate - Most Powerful Command in VBA?


    Thanks for the replies...


    Perception, I've thought about doing that but I'm writing tools that help me review models other people have built so shouldn't really change the model I'm reviewing (by e.g. writing a formula to a cell), although the more I think about this, the less concerned I am about amending the model (could delete the formula immediately after entering and evaluating it) as the benefits of using Excel's engine probably outweigh the risks of changing the model.


    Rory, as per my reply to Perception, I don't have control over the formulae that I'm reviewing and I need whatever solution I come up with to work with all possible (valid) formulae, however they might be written. I've found some other 'unusual' formulae over the years that play havok with home-brewed formulae parsers so am moving more and more towards entering formulae into cells and using Excel's native engine to work out what should happen. Why won't MS devs open up the Excel formula engine to VBA, eh?!


    Examples of problematic formulae are as follows:


    =SUM(A1:F1:G2:N20) - this is perfectly legit, even with the multiple colons - you can keep adding and adding and adding them. The 'gotcha' to this is that the range Excel's cell highlighting shows on screen is not the same range that Excel sums up - it actually works out the top-most row of all refs, the left-most column, right-most column and bottom-most row and sums everything between those four points.


    =Sum(A1:OFFSET(A1,5,5)) - you can use any function that returns a range either side of a colon, even IF functions so long as the 'active' branch of the IF statement returns a range, so the following is also fine: =SUM(IF(TRUE,INDIRECT("A1"),33):IF(FALSE,OFFSET(A1,5,5),OFFSET(A1,6,6)))


    Enjoy!


    Thinqer

  • Re: Evaluate - Most Powerful Command in VBA?


    I know it is an old thread but I am also old.


    Was looking for an evaluate of boolean like
    evaluate( CatisHere and FredGone and ( a or b and c ) to work
    the and(a,b,d) works as it is a worksheet function .. but ..


    on the topic of speed .. the code below seems to give [] at about 1/5 of the speed
    that it takes about 5 times as long as either evaluate


    Code
    1. Private Sub CommandButton2_Click()
    2. Dim b!, ll&
    3. [m1] = Timer
    4. For ll = 1 To 10000
    5. 'b = [min(d5:d23)]
    6. b = Application.WorksheetFunction.Min(Range("d5:d23"))
    7. 'b = WorksheetFunction.Min(Range("d5:d23"))
    8. Next ll
    9. [m2] = Timer - [m1]
    10. End Sub
  • Re: Evaluate - Most Powerful Command in VBA?


    This is not a question and answer section. Please ask your question in the Excel forum, add a link to this post if you think it will help.