How to Insert Formula into Excel – How to Use Excel Formula in Macro

Shouldn’t Have Combined these two topics into One:

In this blog a Formula actually means the Worksheet Functions, that we use in excel sheets directly that begins with “=” (like “=Sum(A1:A10” or “=now()”). Excel Macro can be used to insert either a Formula or Just a Value into a cell.

Require Formula or Just Value from Formula:

• VBA -> Insert Worksheet Formula -> Excel
• VBA -> Use Worksheet Formula -> Insert Value -> Excel

Lets learn how these 2 categories work through an example.

Consider we have a excel sheet with values in Column A and B as below. We are going to calculate Sum and Product of Column A and B in 2 different methods.

1.  Insert Excel Formula Through Program:

'ThisWorkbook.Sheets(1).Cells(1,3) = "=WorkSheetFunction()"
Private Sub Insert_Worksheet_Function()
'Sum

    For i = 2 To 11
        'ThisWorkbook.Sheets(1).Cells(1,3) = "=A1+B1" or "=SUM(A1,B1)
        ThisWorkbook.Sheets(1).Cells(i, 3) = "=A" & i & "+B" & i & ""

    Next i

'Multiply

    For i = 2 To 11
        'ThisWorkbook.Sheets(1).Cells(1,4) = "=A1*B1"
        ThisWorkbook.Sheets(1).Cells(i, 4) = "=A" & i & "*B" & i & ""
    Next i
End Sub

2. Using WorksheetFunction Formula in Macro:

'Value_From_Function = WorkSheetFunction.FunctionName(Parameters)"
Private Sub Use_Worksheet_Function_In_Macro()
'Sum

    For i = 2 To 11
        val1 = ThisWorkbook.Sheets(1).Cells(i, 1)
        val2 = ThisWorkbook.Sheets(1).Cells(i, 2)
        ThisWorkbook.Sheets(1).Cells(i, 3) = WorksheetFunction.Sum(val1, val2)
    Next i

'Multiply

    For i = 2 To 11
        val1 = ThisWorkbook.Sheets(1).Cells(i, 1)
        val2 = ThisWorkbook.Sheets(1).Cells(i, 2)
        ThisWorkbook.Sheets(1).Cells(i, 4) = WorksheetFunction.Product(val1, val2)
    Next i
End Sub

Though this Article explains about 2 different methods, most of the times any one of the method will be used.