There are two main types of procedures you can create in Excel VBA—subroutines and functions. In this video, learn how to use both procedure types to work with your Excel data.
- There are two main types of procedures you can create in Excel VBA: subroutines and functions. In this movie, I will show you how to create both procedure types to work with your Excel data. My sample file is the Commission workbook, and you can find it in the chapter 1 folder of your exercise files collection. So you might wonder what's the difference between a subroutine and a function. Well a subroutine can affect a workbook, and even write values to cells, and perform calculations, but you can't use them in a formula.
So for example sum, S-U-M, is a function that you can use in a formula. You can define those as well in Excel VBA, but we'll start with a subroutine. To create a subroutine you need to go the Visual Basic Editor, or press Alt+F11, and I don't have any code modules available, so I will go to the insert menu, and click module. Now I can define my subroutine. I'll start by defining a subroutine that calculates the commission that is due for the sale in cell C7.
So I'll start by typing "Sub", which is the declaration indicating of a subroutine, and then the name, I'll call it "commission", followed by an open and closed parentheses. The parentheses won't always be empty, but for this simple example, I'll leave them that way. Now I can define variables and assign values, so I will create a sale valuable, C-U-R-sale, equals, and the value will come from cell C7, so I will say "range", followed by left parentheses, and double quote C7, double quote and right parentheses, period, value.
Because there's only one worksheet in my workbook, I don't need to specify which one, VBA just knows where to go. So we have the range value for the sale, and then we have the commission rate, C-U-R-rate, equals, and I will assign that the value of 0.1, so ten percent, then enter, and now we have the commission variable, and that will be equal to the sale, times the rate, and then after return, message box, left parentheses, C-U-R-C-O-M-M-I-S-S-I-O-N, followed by a right parentheses.
Alright, everything looks good, I'll press F5 to run it, and we get a commission of $625 and 1.9 cents; I'll show you elsewhere how to handle rounding to specific number of digits after the decimal point. And I'll just go ahead and click okay. The subroutine I created works, but you can really only use it for a couple of variables at a time, by using loops and message boxes. It is much more efficient to use a function.
So I'll click below the "End Sub" line, and now I can create my function. The key word as you might imagine is function, and I'll make the name "CalcComm," C-A-L-C-C-O-M-M, Followed by a left parentheses. This function will accept one argument, and will make that the sale. So C-U-R-sale, as a currency, followed by a right parentheses, and enter.
There we go, now I'm creating my function. You assign the result of a function to the name of the function, so C-A-L-C-C-O-M-M, equals, that will be curSale, times 0.05, so we have a five percent commission here. If I wanted to make the formula more complex I could also get the commission rate. Right, everything looks good, I'll press Alt+F11, to move back to the worksheet, and I'll click on cell D7, then I'll type an equal sign, and then C-A-L-C, and you'll see that the function is available in formula auto-complete.
I'll press tab, and we're sending it the value from C7, which is in the Excel table, you see the reference there, right parentheses and enter, and we get the commission of five percent, rounded to two decimal places. The formula results aren't actually rounded to two decimal places, but the accounting format applied to these cells makes it work that way. Subroutines and functions are the basic structures you build your VBA code on. If you want to use your procedure in a formula, define a function.
If not, use a subroutine.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures