How to create Sub procedure and Function in Excel VBA

Microsoft Excel has a built-in VBA (Visual Basic for Application) editor that allows us to create sub procedure and function to do some calculations or other automation tasks.


This post quickly shows how to enable a developer ribbon bar and creating sub procedure and function.

To enable the developer ribbon bar in excel, just follows the steps below.

1. Select File
2. Select Excel Options
3. Select Customize Ribbon
4. On the Main Tabs, select Developer and you'll see the developer ribbon bar

Once the developer ribbon appears on excel ribbon bar, click at "Visual Basic" Button to enter the Visual Basic editor. However, you have an alternative choice just to use a shortcut "Alt+F11".

In Visual Basic editor, you'll have to add a module which will contain VBA codes. To do that just right-click at the VBAProject (Book1) and select Insert > Module.

Please note that Book1 is the current filename of excel. If you save the workbook as another name, then you'll see the different name.

To create a sub procedure, we have to follow the below syntax.

Sub SubName()
    ...
    ...
End Sub

Once the sub procedure called "test" is ready, you have to assign macro to a button that calls this sub procedure. To do so, switch back to spreadsheet and create a rectangle and right-click > Assign macro > Select sub procedure named "test" and click OK. The rectangle now becomes a button. Once you click it, it will call sub test to run and it will display the message Hello World!.

Not only a sub procedure can be added in the module, but also a function. Function returns the value and we often make it to do some calculations that you can't do with the built-in excel spreadsheet functions. However, in the example we're going to make a simple multiply function just to see how it's created and how to use.

Switch back to VBA editor (Alt+F11) and put the following codes.

Function multiply(a as single, b as single)
    multiply=a*b
End Function

The explanation to this code is simple. This function requires 2 inputs as number (a and b) and the function will return the value of axb.

To use the function just created, switch back to spreadsheet and type in any cell the formula to call this function: =multiply(D9,D10) where D9 and D10 refer to cell D9 and D10 respectively. Excel then call multiply function and automatically calculate the value once value in cell D9 or D10 changes just like other built-in excel spreadsheet functions.