Tip for creating syntax in VBA for Worksheet formulas

Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky to get the syntax right for Worksheet formulas.  Tricky because string quotes need to be doubled for VBA and this is tedious because unlike a worksheet formula which gives you instant feedback, entering via VBA can mean running a few times to get it right.

A couple of simple tips on this.

To just quickly get the formula text to manually add the extra quotes select the working formula on the worksheet and then in the Visual Basic Editor Ctrl + G to get into the immediate window.  Type ?activecell.formula and this returns the string of your activecell’s formula.   Add the double quotations and then copy this to your vba code.

But I often use the macro recorder to select the working formula on the sheet, pretend I am editing it by deleting a character and putting it back, then return it to it’s original state stop the recorder.  Then go to the module and code created by the recorder and there is your formula in VBA syntax done for you.