Copy an Excel Formula to the Clipboard as a VBA String
Question details
Copy a selected Excel cell’s formula to the clipboard as a VBA string literal with embedded quotation marks escaped.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to paste a cell formula into VBA code without using SendKeys.
- Observed behavior
- The desired clipboard text is a quoted VBA string containing the formula, with each embedded quotation mark doubled.
Use desktop Excel on Windows and check that Microsoft Forms DataObject is available; its registration can vary between installations. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the macro.
Copy the selected formula with a VBA DataObject
Use this macro to validate a single formula cell and copy its formula as a VBA string literal without SendKeys.
The macro reads the cell’s Formula property, doubles embedded quotation marks, and adds quotation marks around the result. The clipboard content is a string literal that can be used on the right side of a VBA assignment.
In desktop Excel, press Alt+F11, then choose Insert > Module.
Paste this code into the module: Sub CopyFormulaAsVBAString() Dim cell As Range Dim clipboard As Object Dim formulaText As String If TypeName(Selection) <> "Range" Then MsgBox "Select one cell containing a formula." Exit Sub End If If Selection.Cells.CountLarge <> 1 Then MsgBox "Select only one cell." Exit Sub End If Set cell = Selection.Cells(1) If Not cell.HasFormula Then MsgBox "The selected cell does not contain a formula." Exit Sub End If formulaText = Chr(34) & Replace(CStr(cell.Formula), Chr(34), Chr(34) & Chr(34)) & Chr(34) On Error Resume Next Set clipboard = CreateObject("Forms.DataObject") On Error GoTo 0 If clipboard Is Nothing Then MsgBox "Microsoft Forms DataObject is not available on this installation." Exit Sub End If clipboard.SetText formulaText clipboard.PutInClipboard MsgBox "Formula copied as a VBA string." End Sub
Return to Excel and click exactly one cell containing a formula.
Press Alt+F8, select CopyFormulaAsVBAString, and click Run. Paste the copied text into a VBA statement, such as Range("A1").Formula = followed by the clipboard contents.

Work with spreadsheet formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier move between applications. It does not fix Excel’s Microsoft Forms or VBA clipboard behavior.

Frequently Asked Questions
What does the copied VBA string look like?
For a cell containing the formula ="Hello", the clipboard contains "=""Hello""". Paste it after an assignment such as Range("A1").Formula = in the VBA editor.
Why does the macro ask me to select one cell?
The macro checks Selection.Cells.CountLarge and stops if more than one cell is selected. Click a single cell containing a formula, then run CopyFormulaAsVBAString again from Alt+F8.
What if Microsoft Forms DataObject is unavailable?
The macro displays a message if CreateObject("Forms.DataObject") fails. Check whether Microsoft Forms is installed and registered on the Windows Excel installation, or use a different clipboard method.
Does the macro copy the displayed result or the formula?
It copies the formula from the selected cell’s Formula property, not the calculated result. For dynamic-array formulas, consider whether Formula2 is more appropriate for your workbook.




