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

Value ! | Value 2 | Sum | Product |

1 | 2 | ||

2 | 2 | ||

3 | 2 | ||

4 | 2 | ||

5 | 2 | ||

6 | 2 | ||

7 | 2 | ||

8 | 2 | ||

9 | 2 | ||

10 | 2 |

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