#Excel for mac vba to call a subroutine how to#
#Excel for mac vba to call a subroutine code#

If I wanted to make the formula more complex I could also get the commission rate. 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. There we go, now I'm creating my function. So C-U-R-sale, as a currency, followed by a right parentheses, and enter. This function will accept one argument, and will make that the sale. 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. So I'll click below the 'End Sub' line, and now I can create my function. It is much more efficient to use a function. 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.

#Excel for mac vba to call a subroutine how to#

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. 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. Because there's only one worksheet in my workbook, I don't need to specify which one, VBA just knows where to go. 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. The parentheses won't always be empty, but for this simple example, I'll leave them that way. 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. I'll start by defining a subroutine that calculates the commission that is due for the sale in cell C7.

#Excel for mac vba to call a subroutine code#

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. You can define those as well in Excel VBA, but we'll start with a subroutine. So for example sum, S-U-M, is a function that you can use in a formula. 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 you might wonder what's the difference between a subroutine and a function. My sample file is the Commission workbook, and you can find it in the chapter 1 folder of your exercise files collection. In this movie, I will show you how to create 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.