• 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.
 
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. :)
 
Thank you, Alan. It's a valid point. VBA seems to deal with this as well for the preliminary test I carried out. See attached example of implementation.
 

Attachments

  • Implementation_Test.xlsm
    16.7 KB · Views: 57
It returns two questionmarks when i paste. ?? Anyone got the same? Using excel 2019

I had the same problem, used another code i found to put to clipboard and it works.

>>> use code - tags <<<
Code:
Public Sub CopyExcelFormulaInVBAFormat()
    Dim strFormula As String
  
    '\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)
  
   Clipboard (strFormula)
  
    MsgBox "VBA Format formula copied to Clipboard!", vbInformation
End Sub


Function Clipboard(Optional StoreText As String) As String
    'PURPOSE: Read/Write to Clipboard
    'Source: ExcelHero.com (Daniel Ferry)

    Dim X As Variant

    'Store as variant for 64-bit VBA support
    X = StoreText

    'Create HTMLFile Object
    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
            Case Len(StoreText)
                'Write to the clipboard
                .setData "text", X
            Case Else
                'Read from the clipboard (no variable passed through)
                Clipboard = .GetData("text")
            End Select
        End With
    End With

End Function
 
Last edited by a moderator:
Back
Top