Run a formula directly through a macro

​As discussed in my previous blog post, this week I will go through how to input a formula within a macro. Here is the generic formula which needs to be inputted in the module tab of the sheet (i.e. press ALT+F11 > Insert>Module)

Sub Formula_property()
  Range("CELL").Formula = "=FORMULA”
End Sub
 
In the formula above input the reference cell where there is a the word CELL and input the formula where there is the word FORMULA.
 
 Reasons for having a formula within a macro:

1. Speeds up the spreadsheet If calculations are set at Manual as oppose to Automatic (i.e. "Formula">"Calculation Options" in the ribbon) Many users tend to change the calculation option to manual especially if the spreadsheet contains many records (i.e. thousands of records or more) in order to speed up data processing and to avoid having the spreadsheet crash
2. Helps store fewer formulas within the actual spreadsheet
3. This way the formulas will be executed only when needed and when triggered through an event. E.g. Upon opening/closing the spreadsheet or clicking on a macro-enabled button, filter, etc.

Here’s an example: let’s input total revenue in Cell F3 of our spreadsheet

Now let’s use a more advanced formula. i.e. COUNTIF statement with multiple criteria. The important note about this is to make sure not to enter any values within the formula itself. Rather we must enter references to those values (from within the spreadsheet).

=SUMPRODUCT(COUNTIF(B2:B30,'Sales Branch List'!$A$2:$A$4))

For example, the formula above returns total number of IDs belonging to sales branches located in either “BARRIE","KINGSTON", or "BRAMPTON". However, I have not inputted these values directly into the formula but rather inputted the reference to them located within a different tab of the spreadsheet (i.e. Cells A2, A3, and A4 of the “Sales Branch List” tab). For more information on this please visit my previous blog post.

​And here is the outcome of both formulas executed directly through a macro.