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.
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.
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.