From the course: Excel: Learning VBA

Create a function - Microsoft Excel Tutorial

From the course: Excel: Learning VBA

Start my 1-month free trial Buy for my team

Create a function

“

- [Instructor] There are two types of procedures that you can create in Excel VBA, subroutines and functions. You can use a subroutine to manipulate data in your workbook, but you can't use them as a formula. In other words, there's no way to reference them from your worksheet. When you define a function in Excel VBA though, you are able to create something that you can use in a formula. So I'll demonstrate how to do that in this movie. My sample file is 0 1 0 7 Function, and you can find it in the chapter one folder of the Exercise Files Collection. In this workbook, I have a single worksheet and on it I have a couple of different pieces of data. The first is sales data for each of the different types of olive oil that this particular representative worked with, and then also a rate of 5% which will be used to calculate their commission. So my goal is to create a function in VBA that I can use to calculate the commission based on sales and rate. So with that in mind, I will switch to the Visual Basic Editor by pressing alt F11. And I need a code module. So I'll click insert and module and now I can create my function. Creating a function is very like a sub-routine except instead of saying or using the sub keyword as you probably guessed, you use the function keyword. So I will give the function a name, I'll call it CalcComm, which will be short for calculate commission. And then in this case, the function needs two incoming values and that will be the sale. So I'll have sale as a currency and then the rate (keyboard clicking) as a single precision floating point decimal number so SNG rate as single. So there we go and press enter a couple of times to give myself some room and we have the function. And the function will just consist of a single line. So I'll have CalcComm as our variable, and this is the name of the function. If you don't use the name of the function as what's called the return variable, then the function will return zero or an empty string or something like that. So you always need to assign the result of the function to the name of the function as a variable. So I have that and it will be currency sale times single rate. Right, that looks good. I'll press alt F11 to move back to my worksheet. And then in in cell D7, I will start creating my formula. So I'll type equal and then start typing c-a-l-c. And after I type c-a, you can see that I have CalcComm available as a function and it appears in formula auto complete just like every other built-in function that you can use. What I do to distinguish functions that I create from those that are built in is to only have initial caps for each word within my function name. So here I have CalcComm and only the two Cs at the start of the words are capitalized. And if I press backspace, you can see that all the built-in functions have in fact all caps in their names. So that's just something I do. You don't need to do it, but it's a habit I got into many years ago. Alright, so I have CalcComm, I'll press tab, and now I can enter in the two values. I need to remember the order that they're in. So the first is the sale, and that is in C7 then a comma, and then the rate, which is in E2. Now I'm going to copy this formula throughout the table. So I want to make the reference to cell E2 an absolute reference. So on windows, I'll press F4 and that way that reference won't change but the reference to sales will. So in the row below, I should see C8 comma E2, C9 comma E2, and so on throughout the table. All right, so I will type aright parentheses and enter. And there I get my commissions. And the great thing about creating a function is that if you change the rate, then the formulas will update the next time the worksheet recalculates. So if I go up to 6%, everything increases. And if I go down to say 2%, then everything decreases. Functions are extremely useful. Most of your Excel VBA programming will use subroutines. However, if there's a calculation that you find is easier to create using a VBA function as opposed to a formula within an Excel worksheet, then go ahead and create it and you can use it throughout your workbook.

Contents