Using Custom Functions and Macros

by with No Comment advancedfunctionsvba
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. 

Brief Intro to Macros and VBA

by with No Comment advancedmacrosvba

If you do a lot of repetitive tasks (and even if you don’t), macros can help save lots of time. In some cases you can be talking hours worth of work in one macro.

Recording a Macro

Now, if you’re not familiar with macros or VBA at all, a good way to learn is through the macro recorder tool (this is under the View tab in the ribbon). When you record your macro, you can see what was created on the VBA side by pressing ALT+F11 – you can also edit your macro here. You can do much more in VBA by coding straight from that screen, as the macro recorder is limited (e.g. it can’t create variables or other complex codes). But the macro recorder helps you learn how to reference different items.

When you create a macro – whether directly from VBA or the macro recorder, you can assign a shortcut (or a button to it) to it, saving you time in executing it.

To assign a shortcut: select the view macros option under the view tab and then select options for the macro you want. There it will allow you to assign a shortcut (see below)

For a button, you will need the developer tab enabled. If you don’t have it, go under Excel options and under ribbon options you will see an option to enable it.

Once enabled, under the developer tab you will see an insert controls button where you can insert a button (see below)

Once you do that you will create the size of the button, and when you are done it will ask you to assign a macro to it. And now when you click on that button your macro will run.

Inserting VBA Code

If you run across VBA code that you want to copy into your spreadsheet, you want to make sure it is put in the proper section.

If it is a custom function, you want to make sure that is put in a module to work properly. If you do not see a module section in VBA, you will need to add one from the Insert menu.

If it is just a macro (denoted by ‘Sub’ and then the name of the procedure) it can be put in a module, the worksheets, or the workbook section. If you want the macro to work in all worksheets, then I would recommend putting it in the workbook rather than the individual sheets.