»
Making Your Own Excel Functions
Often when you're working with Excel you will use a calculation that is not available as a built-in Excel function. If you make this calculation repeatedly, you can save having to write the calculation each time you use it and instead create a custom Excel function to do the work. Excel custom functions can also be created as Excel add-ins so that they are available for use in all workbooks.
To create a function, open a new Excel workbook and create a small worksheet as shown in the image to the right:
I'll show you how to create a custom function to calculate the selling price of an item using the following formula:
To make this calculation in cell D4 of the example worksheet (given the markup percentage is 50%) you would write this formula and copy it down the column:
=C4*(1+50%)
If you want to make this calculation repeatedly or have someone else do it, you can create a function that can be used in the same was as any other Excel function.
To do this, first display the Developer tab if it isn't yet visible by clicking the Office button, choosing Excel Options > Popular, and enabling the Show Developer tab in the Ribbon checkbox. Click OK.
Next, click the Developer tab on the Ribbon and click the Visual Basic icon to open the Visual Basic window. Locate VBA Project for the workbook you are working on. In our case it's VBA project (Book1). Select Insert > Module to add a code, which is where you type your VBA code.
Function SellPrice(CostPrice, Percent_markup)
SellPrice = CostPrice * (1 + Percent_markup)
End Function
The function is called sellprice and this is also the name that you use to refer to it when you write your formula in the worksheet. The function takes two values, one for cost price and one for percentage markup, and it uses these to make the calculation of the selling price.
If you're familiar with using Visual Basic, a lot of this detail will already be familiar to you. If not, there is one thing to be aware of when writing functions, which is that you must always assign the function name a value in the function code itself so you have a return value. In this case the function name is sellprice so you need at least one line that commences: "sellprice =" or the function won't work.
Return to the workbook and try the function by typing this formula into cell D4:
=sellprice(C4,50%)
You should see the result 34.5 which represents a selling price that is the original cost price of 23 marked up by 50%.
»
Making Your Functions Accessible
If you were to try this formula in another workbook it wouldn't work as written because the module containing the function code wouldn't be in that workbook.
To use it you need to prefix the function name with the workbook name — a process that is undesirably complex and cumbersome but which can be avoided by making the workbook an Excel add-in, which we'll do shortly.
»
More Advanced Calculations
In the meantime let's see a more complex function at work. Consider the situation where, if an item's cost price is less than ten dollars we add an additional one dollar to the selling price after the markup has been calculated and, if the item's cost price is ten dollars or more, then we add two dollars to the selling price after the markup has been calculated.
Here's the code for this particular function:
Function SellPrice(CostPrice, Percent_markup) as Currency
SellPrice = CostPrice * (1 + Percent_markup)
if CostPrice < 10 then
SellPrice = SellPrice + 1
else
SellPrice = SellPrice +2
end if
End Function
This replacement function can be accessed in Excel in the same way as the earlier version but this time a more complex calculation is taking place behind the scenes. In addition, because this is Visual Basic code we can hide it from our user.
As a result, we could instruct someone how to write the formula to calculate the selling price of an item by telling them to type =sellingprice(item, percent markup) but we don't need to tell them how the actual calculation is made.
