Microsoft Office Tutorials and References
In Depth Information
Important Tools for Any Business
The SUBTOTAL function
can be placed above the
corresponding weeks to
Building Custom Functions
There are times you may want to build your own custom function that does a calculation
specific to your needs. For example, if you run a construction company and have several pieces
of equipment with specific rates, you could apply the rates to the function and customize Excel
to your needs, even nesting your own custom functions. You could establish your company’s
new rates at the beginning of the year and post the functions with syntax to all the estimators in
the organization—then all they have to do is use the functions. To create this example—a
custom function—follow these steps.
1. From the Visual Basic toolbar select the Visual Basic Editor command.
2. From the Insert menu choose Module. A new module opens.
3. In the first line of the module enter the following code, also shown as Module 1 code in
Figure 2.29. Use the tab key to indent.
Function Cat769(hours, units)
Cat769 = ((82.5 * hours) * Units)
4. Click Save.
5. Switch back to the worksheet and create a column heading for the syntax “Hours”
starting in cell D7 and then “Units” in E7.
6. Type 1,250 in cell D8 and 5 in cell E8.
7. In cell F8 type the formula =Cat769(D8,E8) . The return result is $515,625 as shown in