Create a Simple VBA Function in Excel

Microsoft Excel provides most functions you will ever need out of the box. By chaining the built in functions, almost anything can be accomplished. However, sometimes you may need to get extra fancy.

1. In Excel, Open the VBA Editor

Open the VBA editor by using the shortcut Alt-F11. This shortcut is the same in all Office products, so it will become second nature once you become comfortable with VBA.

2. Create New Module

Within the VBA editor, go to Insert->Module. A module is simply a way to group functions or procedures together, and is where you will create your function. At its most basic form, a VBA function will have the following syntax:

Function function_name(parameter_name as parameter_type) as return_type


    function_name = whatever you want to return

End Function 

For our example, we will be creating a function to return the sum of two numbers. Granted, you will likely never use VBA for something that can easily be accomplished using =SUM(num1, num2), but we are simply using it as an example.

Function add_two_numbers(num1 As Integer, num2 As Integer) As Integer

    add_two_numbers = num1 + num2

End Function

Your editor should look like this:

3. Save as Macro Enabled Workbook

Starting with Excel 2007, Microsoft disables VBA by default for security reasons. To enable macros/VBA for your workbook, go back to the sheet (close the VBA editor), go to File-Save As and save as an "Excel Macro-Enabled Workbook (*.xlsm)".

4. Call the New Function from the Sheet

You should now be able to call your VBA function from any cell in the workbook, as you would with any other Excel function.