Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Build your own Excel Net to Gross function - Tutorial by Simon Hurst

by
25th Dec 2005
Save content
Have you found this content useful? Use the button above to save it to your profile.

In this Expert Guide, Simon Hurst from The Knowledge Base uses a simple calculation routine to demonstrate how users can build their own Excel functions with the built-in VBA programming tools. Further information about The Knowledge Base's services can be found at the end of the article.

Introduction
Following December's VBA programming workshop, this Expert Guide provides another example of a user-defined Excel function. The "Net to Gross" routine forms part of the latest Courses on Disk CD-ROM, Adding Value with Excel 2000, which is available in the AccountingWEB Mall.

Macros and VBA
Application macros started off as a method of automating a repetitive series of actions. Generally this was done by recording a string of keystrokes, which could then be "replayed" when required.

In more recent times macros have become a lot more sophisticated. Macros in Excel are now constructed in Visual Basic for Applications (VBA), a programming language with almost all of the features of Visual Basic itself. You can still record a macro, and it is often the best way of starting. However instead of the recording being a simple list of keystrokes, your actions are converted to VBA.

Functions v sub-procedures
When you create a macro in Excel, a sub- procedure is created. In the following example we have just recorded the application of a custom number format to a cell:

Sub NumberFormat1()

' NumberFormat1 Macro

' Macro recorded 11/12/1999 by Simon Hurst

'Selection.NumberFormat = "#,##0_);[Red](#,##0);-??"

End Sub

Note that our recorded macro starts with

Sub

and ends with

End Sub
showing that it is a sub-procedure.

There is another type of procedure that you can create - a function. Put simply, sub-procedures perform actions, whilst functions return values.

Built-in functions
You have almost certainly already used one or more Excel functions - for example the Sum function that is used to total any range of cells: =SUM(A2:A19)

There are hundreds of other functions built into Excel and many others available as add-ins. This article will help you create your own user-defined functions and turning them into an Excel add-in.

Creating a function
What follows is a function is designed to perform a calculation on a number, and return an answer, in the same way that the Sum function calculates the total of the range that you give it. The programmed function will take a single numeric value and calculate a tax inclusive figure from it. If you "pass" a value of £100.00 to our function and it would return £117.50.

Unlike a sub-procedure, you cannot create an Excel function directly by recording anything. Instead you need to choose the Tools option from the top menu bar, then Macro-Visual Basic Editor. This will open the Editor screen.

There are three sections of this screen. Top left is the Project Explorer window from which you choose any existing VBA project. Under this is the Properties window which displays the properties of whichever object is selected. Choosing the Module option from the Insert menu will create a new "module1".

Highlighting the name in the Properties window enables you to change the name into something more informative, such as "AccountingFunctions".

Initially, the third section, to the right-hand side, shows the contents of the currently selected module - at this point it should be empty.

Go to the Insert menu and choose Insert-Procedure. Select Function and give it the name "NetToGross".

The code window now displays the first and last line of the function:

Public Function NetToGross()

End Function

The Code
The function should be constructed to take a value and multiply it by 1 + a rate of tax. It would also be a good idea if the result were rounded to two decimal points.

The tax rate could change; therefore it would be sensible to set this up as a variable to make any changes easier to handle. Making this variable more generally available later on will minimise the number of places in which you might need to change it.

The rounding will require the use of an existing Excel function -'Round()'. Incorporating existing functions is straightforward but does vary between Excel 97 and before, and Excel 2000. In Excel 2000, you can just type in the name of the function with the appropriate arguments. In previous versions of Excel you need to precede the function with the following:

Application.WorksheetFunction.

[Excel 97 will be used in this example. While it involves a bit more typing, it will work in both versions.]

Let's look at each part of the code in detail:

The value
When you use a function in an Excel spreadsheet you enter something like:

=AVERAGE(12,6)

Of course you would normally use references to cells rather than actual values but we'll transgress the data/logic separation rules for the sake of simplicity.
After each function there is a set of brackets that enclose the values that the function will work on. If we look at our code, we can see that the function is in a similar format:

NetToGross()

When we call our function by including its name in a formula we will pass values, or in our case a single value, to it. It is this value that our function will work on. So that we can do this, we insert between the brackets in our code a name for our value that we can then use throughout the code:

Public Function NetToGross(Net as Double)

Note that as well as giving our value the name 'Net' we have said that it will be a particular type of value - in our case a double precision number - a potentially big number with decimal places.

The tax rate
We now need to specify the rate of tax that our calculation will use. As we said earlier we will set this up as a variable. A variable is something to which a value can be allocated, and can then be used or changed as required.

It is always a good idea to make Excel force you to define your variables. If you don't do this, a mis-spelt variable will be treated as a new variable and it can be very hard to track the problem down.

To ensure that Excel will make you define any variable you want to use, go to the line above the first line of your code and type:

Option Explicit

This will automatically be placed in the 'Declarations' section of the code. To show the effect of this, we will type the following line within our function:

TaxRate = 0.175

If you now choose the 'Compile VBAProject' option from the 'Debug' menu, the word 'TaxRate will be highlighted and you will see a dialogue box with the following error message: "Compile error: variable not defined".

This is much easier to deal with than an incorrectly spelt variable which may not generate any error message - just an incorrect answer.

To correct the problem, add the following line to the code:

Dim TaxRate As Double

This defines the variable as one that will hold a double precision number. If you compile the project again, you will not get an error message. You have now defined the TaxRate variable, and allocated the value of 0.175 to it.

The calculation
Now it's time to enter the calculation, using the tax rate variable. The following line goes above the 'End Function' line.

NetToGross = Net * (1 + TaxRate)

Notice how all the elements of function appear in this line. In order for the function to return the calculated value to the spreadsheet we set the function itself - NetToGross to be equal to the value that was passed to the function - Net, multiplied by 1 plus the tax rate. As we can see, we use the TaxRate variable rather than the actual value.

The code should now look like this:

Option Explicit

Public Function NetToGross(Net as Double)

Dim TaxRate As Double

TaxRate = 0.175

NetToGross = Net * (1 + TaxRate

End Function

Rounding
Finally we need to round the result to two decimal places. As mentioned above, we will use the in-built 'Round function, and to ensure the code works with both Excel 97 and Excel 2000 we will use the full syntax as shown below.

NetToGross = Application.WorksheetFunction.Round(Net * (1 + TaxRate), 2)

The Round function takes two arguments - the value to be rounded, and the number of decimal places to round to. The two arguments are separated by a comma.

Using the function

Direct entry
You now have your own NetToGross function. You should compile it to make sure there are no problems. Then you can go back to your worksheet and type into a cell:
=NetToGross(100)
The cell should then show the result of 117.50.

If you use: =NetToGross(1)
you should see the effect of using the Round function as 1.18, rather than 1.175 is returned.

Paste function
You can type the function in directly, but any functions you have created will be automatically added to the user-defined category of the 'Paste Function' dialogue, and can be entered in a similar way to any of the built-in functions.

If you select the NetToGross function from the 'Paste Function' dialogue box and click OK, a second 'NetToGross' dialogue box will open into which you should enter the Net value, for example 100.

Because the function only takes a single argument there is a single input box.

Warning
A brief word of warning - if you use a user-defined function, and then change the way the function is calculated in the code editor, the spreadsheet will not be automatically changed to reflect the changed calculation - each cell containing the function would need to be edited to force a recalculation. One way of doing this easily is to use Edit, Replace to replace all '=' with '='. Because any function must be preceded by '=' this will edit all the relevant cells.

Next step
You could quickly add a whole series of similar functions to your new AccountingFunctions module. For example you could create a GrossToNet function, a NetToTax, and a GrossToTax function, all using a similar approach, but slightly different calculations.

All these functions would have the tax rate in common. It would therefore make sense to define and set the tax rate once, rather than for each of the functions.

Constants
We will do this by using a constant. In the declarations section, enter the following:

'Constant for sales tax functions

Const TaxRate As Double = 0.175

The first line shown in green is preceded by an apostrophe and is a remark; that is something included for the purpose of documenting the code, that will be ignored when the code is compiled and run.

The second line defines a constant called TaxRate as a double precision number ' in the same way as we defined our TaxRate variable, and in the same line we set the value of the constant to 0.175.

Now you can remove the two lines that define and set the TaxRate variable from the Function code to leave a single operative line:

Public Function NetToGross(Net as Double)

NetToGross = Application.WorksheetFunction.Round(Net * (1 + TaxRate), 2)

End Function

We can use TaxRate in the same way in other functions that we insert into our module without having to define it or set its value again.

Creating an Add-In
For your functions to be available to an Excel workbook, you need the workbook that contains the functions to be open. This may be inconvenient and confusing for the user. A better alternative would be to turn the code workbook into an add-in, which could then be made available in the same way as any other add-ins.

To turn the file into an add-in, simply use File, Save As and choose 'Microsoft Excel Add-in' as the 'Save as type'. Excel will automatically change the 'Save in' location to the AddIns folder.

All that then has to be done to make the functions available to any workbook is to choose 'Tools-Add-ins' and 'Browse' to the appropriate file, which will then be included as ticked in the Add-in list.

* * *

The Knowledge Base specialises in helping accountants realise the full potential of their investment in IT. For as little as £2 a month, accountants who use Microsoft Office products can get online technical support. Subscribers can e-mail TKB with their problems (and files) and will receive answers via email. Visit The Knowledge Base shop to see what's available. You can also buy certain products online in the AccountingWEB Mall.

Replies (4)

Please login or register to join the discussion.

Simon Hurst
By Simon Hurst
18th May 2005 16:53

F9 and changed functions
Whilst F9 will cause 'normal' calculations to be recalculated, I don't think it will recalculate changed user-defined functions.

Regards

Simon

Thanks (0)
avatar
By jerryhobbs
18th May 2005 11:28

Updating calculation after change in function
You can force all calculations to be re-calculated on a spreadsheet by pressing F9.

Thanks (0)
avatar
By AnonymousUser
31st Jan 2001 15:30

Solution to problem mentioned in "Warning" paragraph
By inserting the code - "Application.Volatile", the function will re-calculate just like an in-built function. This should overcome the problem mentioned in the paragraph entitled Warning. For example:

Function Test (Variable as integer)

Application.Volatile

Code....

End function

Thanks (0)
aw_logo_2019
By Accounting WEB
19th Jan 2001 09:15

Rounding
Depending on whether you want to calculate tax on all gods shown on a tax invoice, or line by line, or tax per unit, you might want to use trunc or rounddown in the worksheetfunction line.

Thanks (0)