{"id":759,"date":"2018-06-30T17:02:48","date_gmt":"2018-06-30T17:02:48","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=759"},"modified":"2024-02-19T14:55:25","modified_gmt":"2024-02-19T14:55:25","slug":"subroutines-and-functions","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/subroutines-and-functions\/","title":{"rendered":"Subroutines and Functions"},"content":{"rendered":"\n

When you write VBA code you can use a subroutine (sub for short) or a function. The generic name for both is a procedure.<\/p>\n\n\n\n

Difference between a subroutine and a function<\/h2>\n\n\n\n

The basic difference between a function and a subroutine is that the function returns the value and the subroutine doesn\u2019t.<\/p>\n\n\n\n

To better illustrate this to you, let’s create the function and the subroutine that calculate the cube root.<\/p>\n\n\n\n

First, open the VBE by using the Left Alt + F11<\/span><\/strong> keyboard shortcut. Right-click inside the Project Window<\/strong> to insert a new module.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After you create the new module, it will appear in the project window and the code window will open.<\/p>\n\n\n\n

A function to calculate the cube root<\/h2>\n\n\n\n

Example 1:<\/h3>\n\n\n\n

Enter the following code, which calculates the cube root.<\/p>\n\n\n\n

Function cubeRoot(value)\n    cubeRoot = value ^ (1 \/ 3)\nEnd Function<\/pre>\n\n\n\n
Close the window and start typing the function name inside a cell.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As you can see, one of the functions in the list is the function you\u2019ve 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.<\/p>\n\n\n\n
A subroutine to calculate the cube root<\/h2>\n\n\n\n
Example 2:<\/h3>\n\n\n\n
You cannot use a subroutine in the same way you use a function because it doesn\u2019t return a value. You have to use a different way to calculate a cube root. Take a look at the following code:<\/p>\n\n\n\n
Sub cubeRootSub()\n    ActiveCell.value = ActiveCell.value ^ (1 \/ 3)\nEnd Sub<\/pre>\n\n\n\n
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 the cube root.<\/p>\n\n\n\n
Naming convention<\/h2>\n\n\n\n
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\u2019s very frustrating when you forget what your old procedure does.<\/p>\n\n\n\n
There are a few rules in Excel you must follow when naming functions and subs.<\/p>\n\n\n\n