Excel: Subroutines and Functions
When you write VBA code you can use a subroutine (sub for short) or a function. The generic name for both is a procedure.
Difference between a subroutine and a function
The basic difference between a function and a subroutine is that the function returns value and the subroutine doesn’t.
To better illustrate this to you, let's create the function and the subroutine that calculate the cube root.
First open the VBE by using the Left Alt + F11 keyboard shortcut. Right-click inside the Project Window to insert a new module.
After you create the new module, it will appear in the project window and the code window will open.
A function to calculate the cube root
Enter the following code, which calculates the cube root.
Function cubeRoot(value) cubeRoot = value ^ (1 / 3) End Function
Close the window and start typing the function name inside a cell.
As you can see, one of the functions in the list is the function you’ve just created. Write the full name or press the tab key. Now you can enter the number for which you want to return the cube root.
A subroutine to calculate the cube root
You cannot use a subroutine in the same way you use a function because it doesn’t return a value. You have to use a different way to calculate a cube root. Take a look at the following code:
Sub cubeRootSub() ActiveCell.value = ActiveCell.value ^ (1 / 3) End Sub
To calculate the cube root you have to enter the value into the cell first, then execute the sub. The value will automatically change to cube root.
When you name your VBA subroutines or functions, it is a good practice to be consistent with your names. There is no strict convention, but it’s very frustrating when you forget what your old procedure does.
The are a few rules in Excel you must follow when naming functions and subs.
- There is a limit of 255 characters you can use in function names.
- You can’t use special characters, such as: @, #, $, %, ^, &, *, !, .,.
- You can use both: uppercase and lowercase letters in VBA, Excel doesn’t distinguish between them.
- You can’t use spaces.
- The first character must be a letter.
When naming a procedure, it’s a good idea to use descriptive names. There are two main methods of naming.
Create descriptive names, but not too long because they can create unnecessary clutter in your code.