VBA & Macro Problems

Copy an Excel Formula to the Clipboard as a VBA String

Huda QurayshiHuda Qurayshi Nov 20, 2026 869 views

Question details

Copy a selected Excel cell’s formula to the clipboard as a VBA string literal with embedded quotation marks escaped.

Copy an Excel Formula to the Clipboard as a VBA String
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, press Alt+F11, then choose Insert > Module.

2
Add the macro

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

3
Select a formula cell

Return to Excel and click exactly one cell containing a formula.

4
Run and paste

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.

Copy the selected formula with a VBA DataObject
DataObject availability: CreateObject("Forms.DataObject") requires Microsoft Forms to be available and registered. If the macro reports that it is unavailable, check the Excel installation or use another clipboard method; this macro does not provide a fallback.
Free Microsoft Office alternative

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.

Create and edit spreadsheet formulas in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar interface for everyday spreadsheet workMove existing Office documents into a lightweight office suite
QA img-9

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.