VBA Express : Excel - Copy formula from Excel to Clipboard in VBA compatible format!

Excel

Copy formula from Excel to Clipboard in VBA compatible format!

Ease of Use

Easy

Version tested with

2010

Submitted by:

shrivallabha

Description:

Activecell Formula text gets copied to clipboard.

Discussion:

While writing VBA codes it is not uncommon to come across a situation where your worksheet formula needs to be implemented through VBA code. Typing quotes can be headache and sometimes irritating with longer formulas. Of course, you can use macro recorder but it will copy the formula in R1C1 style (not everyone likes it)

Code:

instructions for use

			Public Sub CopyExcelFormulaInVBAFormat() 
    Dim strFormula As String 
    Dim objDataObj As Object 
     
     'Check that single cell is selected!
    If Selection.Cells.Count > 1 Then 
        MsgBox "Select single cell only!", vbCritical 
        Exit Sub 
    End If 
     
     'Check if we are not on a blank cell!
    If Len(ActiveCell.Formula) = 0 Then 
        MsgBox "No Formula To Copy!", vbCritical 
        Exit Sub 
    End If 
     
     'Add quotes as required in VBE
    strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34) 
     
     'This is ClsID of MSFORMS Data Object
    Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 
    objDataObj.SetText strFormula, 1 
    objDataObj.PutInClipboard 
    MsgBox "VBA Format formula copied to Clipboard!", vbInformation 
     
    Set objDataObj = Nothing 
     
End Sub

How to use:

1. Copy the code to VBA Module.
2. Stay on the cell from where you want to copy formula to VBA.
3. Invoke "Run Macro" dialog.
4. Run "CopyExcelFormulaInVBAFormat" macro.
5. Message box will pop up indicating formula has been copied to clipboard.
6. Go to Visual Basic Editor and press CTRL+V to paste the formula in VBA format in your code.

Test the code:

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 33 times.