• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy formula from Excel to Clipboard in VBA compatible format!

shrivallabha

Excel Ninja
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).

Following code will do this for you.
Code:
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
Usage instructions:
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.
 

AlanSidman

Active Member
Neat code. Works for pasting code in that will work for a particular range, ie Range("B1"), but what about similar code that you would want to enter into a range such as Range("B1:B" & lastrow). I'm thinking you would need to supply a R1C1 type code. I could see using that more often than what you supplied. Good show, however. :)
 
Top