This is one of those mundane little activities I found myself doing frequently enough that I finally just had to automate it.

When working in VBA I frequently need to range enter a formula across multiple cells. It's not particularly difficult to convert the cell-based formula to the appropriate VBA R1C1 format and adjusting for double quotes if necessary, but if you do it enough, it gets old; especially when converting formulas with lots of double quotes.

So, here's the new gadget I just added to my personal macro toolbar that will convert the activecell formula to the proper VBA text string, and write it to the clipboard so I can then just paste it in my code.

I've even seen some questions posted here along the lines of, "What's the proper syntax for inputting my formula in VBA so the range refs are incremented properly?" This pretty much instantly answers that type of question.

Simple little code, but I find it rather helpful.

```
Option Explicit
Sub Get_VBA_Formula()
Dim VBA_Formula As String, n$, x$
Dim i As Integer
Dim MyData As DataObject
VBA_Formula = ActiveCell.FormulaR1C1Local
'double quote substitution
For i = 1 To Len(VBA_Formula)
n$ = Mid(VBA_Formula, i, 1)
If n$ = """" Then
x$ = x$ & """"""
Else
x$ = x$ & n$
End If
Next i
'post formula to clipboard
Set MyData = New DataObject
VBA_Formula = """" & x$ & """"
MyData.SetText VBA_Formula
MyData.PutInClipboard
'confirmation
MsgBox VBA_Formula, vbOKOnly, "VBA Formula Copied to Clipboard"
End Sub
```

Display More
I believe it works for any formula (array or not). If you can think of a character other than the double quote that I may not be accounting for properly in VBA, or some other issue I've overlooked, please add your comments to the thread.