Posts by JBC

    Re: Evaluate - Most Powerful Command in VBA?


    Thanks for a valuable tip. Unfortunately, Evaluate() doesn't seem willing to process UDF's, meaning it's not quite "Capable of processing any formula a worksheet cell can process," as you state.

    Re: Evaluate - Most Powerful Command in VBA?

    Thanks for the welcome.

    Let me give a little more background. I have a number of worksheets that use a UDF called ShowIfEqual(), which has a ParamArray as its argument. If the arguments of ShowIfEqual() are equal, the function returns that value. If the arguments are unequal, it returns CVErr(xlErrNum). I use the function as a check in various parts of my worksheets. A typical use would be ShowifEqual(SUM(E32:E37),F76), though the arguments are often more complex.

    I'd like to write a macro to simplify the error hunt when ShowIfEqual() returns #NUM!. First I'd write a UDF called ShowIfEqualInfo() that would take the same arguments as ShowIfEqual() and would provide more information on an error, returning a text string, for example, that reports that "Argument 1 equals 100, argument 2 equals 200". I'd then write a macro that would read the ShowIfEqual() formula in the offending cell, retain the arguments but substitute ShowifEqualInfo() for ShowIfEqual(), and use Evaluate(ShowifEqualInfo()) to obtain more detailed information on the error. The macro would then pop a dialog box showing the text returned by ShowifEqualInfo(). Because the arguments would change, I wouldn't be able to hard code ShowIfEqualInfo() into the macro.

    Hope this explanation is understandable.