VBA Excel | Custom Functions or Formulae

In this tutorial, we will create a custom formula or function using Visual Basic for applications.
Sometimes at work we need to calculate specific metrics, KPIs or just simple percentages.

Having these calculations as custom functions can save some time, and storing them in a library in the company’s network makes it shareable to everyone and very easy to maintain.

I use Mac but the code works on Windows as well.

Excel VBA File

Manual

GitHub Project

About Functions

We also know them colloquially as formulas.

However, a Formula is an equation designed by a user in Excel, while a Function is a predefined calculation in the spreadsheet application.

For example: =2+2 is a formula.

Whereas =SUM(A2,A3) is a function.

We are going to create our own functions just as we would use SUM, COUNT, VLOOKUP, etc.

To demonstrate how to do this, we are going to create a Gross Margin function, and a Gross Margin Percentage function.

Instructions

Follow these steps:

Step 1: Open Visual Basic by pressing alt + F8 (option+F8 Mac) or going to Developer > Visual Basic.

Step 2: Add a new module if there is not one already.

Step 3: Enter or copy and paste the following code in the new module: (Both functions at once in this step)


	Public Function CWGM(RetailSales As Long, RetailSalesAtCost As Long) As Double
		'	Calculates gross margin
		On Error Resume Next
			CWGM = RetailSales - RetailSalesAtCost
		On Error GoTo 0
	End Function

	Public Function CWGMPercentage(GMDollars As Long, Sales As Long) As Double
		'	Calculates gross margin percentage 
		On Error Resume Next
			If GMDollars = 0 Or Sales = 0 Then
				CWGMPercentage = 0
			Else
				CWGMPercentage = GMDollars / Sales
			End If
		On Error GoTo 0
	End Function

Step 4: Close Visual Basic.

Step 5: Step 5. In Excel, cell A1, enter or copy and paste the sample table below:

Retail Sales	Retail Sales At Cost	Gross Margin $	Gross Margin %
1500	1000
2750	2290
1200	900

Step 6: On cell C2, type =CW and select CWGM, then select cells A2 for GMDollars, and B2 for GMSales, then copy the function down to C4.

Step 7: On cell D2, type =CW and select CWGMPercentage, then select cells A2 for GMDollars, and B2 for GMSales, then copy the function down to D4.

Step 8: To save the macro file, save it as .xlsm or better, .xlsb.

How These Functions Work

Creating a Public Function in VBA makes it callable in Excel cells.

Let’s take Gross Margin for example.

After the name CWGM, in brackets, we declare the 2 parameters to be used in the function, which are Retail Sales and Retail Sales At Cost.

We tell VBA that On Error, Resume Next, which means to ignore any errors and keep going without crashing. After this check, the real formula performs the calculation, which is (Retail Sales – Retail Sales At Cost).

In the case of CWGMPercentage, we follow the same procedure as before, but this time, we add an IF, THEN, ELSE statement to check if the divisor is zero, which would also return another error.