Using Custom Functions and Macros

If you come across a custom function or macro that you want to use in your spreadsheets, this post will show you how to do so.

As far as coding goes, they key difference in a function as opposed to a sub procedure in Excel is in the way the code is executed. In a function, you enter in just like you would any other function, by typing out the name of the function and entering in any required arguments. With a sub procedure, it is typically executed via an event, a button, or shortcut key. Also with a function, the user will manually enter values for key variables, whereas for a sub procedure in most cases those will be calculated within the procedure itself.

I’ll demonstrate how a custom function works and how to copy it into your code.

Below is a basic function that will multiply a value by 5:

__________________________________________________________________

Function times5(multiply As Integer)

times5 = multiply * 5

End Function

__________________________________________________________________

If you came across this code and wanted to insert it into your spreadsheet, what you would do is open VBA (alt + F11). Once inside VBA, click on Insert and select Module

Now paste the code into that module

And that’s all you would need to do before you can start using a custom function. Now if I go back into a worksheet, I can begin using the code by using the name of the function in my formula.

 There is only one argument in my function that I have to enter, that is for the multiply variable. Inside the function times5 there is one variable declared (multiply). The formula multiplies the variable by 5 to arrive at the result. If I had multiple variables (e.g.if the first line read Function times5(multiply as integer, multiply2 as integer) then that would signify two variables that need to be identified) then I would need to enter more than one argument, the way you would for any other function in Excel that has multiple arguments. But in this case I only have one variable.

Now as you will see, the result is the multiply variable multiplied by 5.

I can access this function for any worksheet within this workbook. If you want to copy a custom function to your spreadsheet, follow the above steps and then you can utilize that function in your worksheets. 

The one downside of custom functions is that the tool tips that normally show up in regular Excel formulas telling you what arguments are required are not available and so you need to make a note of what arguments are required for a function.

Inserting a Sub Procedure (Macro)

If instead of a custom function you had sub procedure (macro) you wanted to copy into your code, the steps would be the same, except instead of using a formula to execute the code, you would need to assign either a shortcut key or a button.