Convert worksheet formula to string that can be used in vba

I have been using this procedure for many years.
Easily converts worksheet formula to vba syntax
Outputs result clipboard and immediate window
How to use; select cell containing formula then run this macro.
Author: michael@excelexperts.co.uk

Sub FormulaToVBA()

    Dim clpData As New Msforms.DataObject
    Dim sForm As String

    sForm = Replace(ActiveCell.Formula, """", """""")
    clpData.SetText ("""" & sForm & """")
    clpData.PutInClipboard
Debug.Print """" & sForm & """"
    MsgBox ("The formula for VBA is :   " & """" & sForm & """" & "and has been placed on the clipboard.")

End Sub

Address

Brookfield,
55 Heath Drive
Brookwood, Surrey
GU24 0HQ England

Scroll to Top