Convert Formula to VBA, copy to Clipboard

For many, many years I've been using the below code to
1) Convert a formula in the cell to it's VBA equivalent code and
2) copy it to the clipboard so it's ready to be pasted into the macro I'm creating.

The code works well, and it's very handy, but it has a problem;
if I have any Explorer windows open the formula is NOT copied to my clipboard, but only two ASCII symbols.
When I see that I have to go and close Explorer and try it again.

I'm wondering if I could put either a check in the macro to see if Explorer is open, and if it is either display a message or close Explorer.

Or even better, is there a way to rewrite the macro to work regardless of what Explorer windows are open?

Please Login or Register  to view this content.

Give this a try - using a different object library:

Please Login or Register  to view this content.

WBD

Hmm, got a Compile error on "Replace", saying "Wrong number of arguments or invalid property assignment"

Tried a combination of old and new. This one errors on the "CreateObject" line, saying "Invalid Argument".

Please Login or Register  to view this content.

The code is correct. Do you have a routine called Replace anywhere? If so, change this code to use VBA.Replace

I don't have a "Replace" macro, but I tried your "VBA." suggestion. Still getting the "Invalid Argument" error.

Please Login or Register  to view this content.

Oh yes - you need to declare the VBA_formula variable as Variant, not String.

All working as originally posted for me. Strange. See animated GIF in the attached ZIP.

WBD

I get the same error as the OP with Excel 2016 and Win10.

Originally Posted by rorya

I get the same error as the OP with Excel 2016 and Win10.

Oh. On which line?

On the setData line

Sorry, got called away. Replacing string with variant did the trick! AND, it seems to work when Windows Explorer is open. Thanks! You guys are the best!

Hey. Cool. Glad we got there. Sometimes tricky when you're accessing external libraries with different versions of Excel.

WBD