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 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
Your editor should look like this:!(/content/images/2016/12/2016-12-10_excel_vba_finished_function.png)
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.