VBA

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. 2016 12 09 22 excel vba ide

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

    logic...

    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
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)“. 2016 12 10 excel vba save as

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. 2016 12 09 23 excel vba call function