Is there a way to copy "Evaluate Formula" steps?

[Fractalis]

Hi to all,

I'm trying to understand better how it works certain Array Formula and I see that "Evaluate Formula" is a great way to
see step by step how it works the formula. The issue I see is that the window of "Evaluate Formula" is very small and is needed
to scroll down/up for each step, and even I do the scroll, cannot see the content in an easy way and worst if the formula handles
many values/cells.

Is there a way (with VBA) to copy to a blank sheet each step of the "Evaluate Formula" to see the complete picture for each step?

Thanks for any help.

 

[Dan_W]

I know of an add-in that I've always found really helpful at the E90E50 blog (link).
You could inspect the VBA of that if that's you wanted, and the website gives an explanation of how it does it.

 

[Fractalis]

I know of an add-in that I've always found really helpful at the E90E50 blog (link).
You could inspect the VBA of that if that's you wanted, and the website gives an explanation of how it does it.
View attachment 77882

Thanks Dan_W for share this. Do you know if does it work in newer versions of Excel (2016, 2019, 365) and if shows the data how is being evaluated (for example, values in TRUE, FALSE during array formulas) and every step that shows the built-in Evaluate Formula tool?

 

[Dan_W]

I have 365 (64bit) and it works fine on that - note that there are four versions - either 2003 or 2007. You'll want the 2007+ version. Within that, you can choose from either the Treeview or the Textbox versions - the treeview probably looks nicer (as per my screenshot), but some users might complain that they don't have the Treeview control installed on their systems.

As for evaluation, I note that it has an evaluate formula option, but honestly I don't use it that much, so you're going to have to give it a test run and report back. I would note, though, that you can adjust the font size of the display. ANd given that it's open source, you can always adjust the VBA code to test whether each of the elements of an array will return True or False if it doesn't already have that capability.

 

[Fractalis]

I've tried to test it. I've added the plugin and the new menu appeared but when I did click to parse a formula(with cell that contains the formula selected), nothing happened. Any pop up window like the one you shared, not sure why. I'm using Excel 365. Thank you

 

[Dan_W]

You click on this button?

 

[Fractalis]

You click on this button?
View attachment 77927

I clicked on "parsing" and nothing happens

 

[Dan_W]

Do you have Macros enabled? Did you unblock the download before loading it?
I just tried it on a blank cell to see what it does, and the form still pops up.
How about pressing the User Manual button? If that doesn't work either, then I suspect your Excel settings aren't permitting VBA to run.

 

[Fractalis]

Do you have Macros enabled? Did you unblock the download before loading it?
I just tried it on a blank cell to see what it does, and the form still pops up.
How about pressing the User Manual button? If that doesn't work either, then I suspect your Excel settings aren't permitting VBA to run.

Hi again. It seems was VBA security that blocked the add-in to work correctly. I tested and it parses the the formula, but in evaluate formula only opens the built-in evaluate tool of Excel. Anyway. Is still useful when someone wants to parse the formula.