Tutorial 8: Creating a Function Macro

Tutorial 8: Creating a function macro

This macro takes you through the creation of a function macro called ‘Celsius’ which converts Fahrenheit degrees to Celsius degrees and then uses the Round worksheet function to round the result up or down to two decimal places.

Step 1: Create the function in the VBA Editor

1.Start the Visual Basic Editor (Select Tools/Macros/Visual Basic Editor).

2.Make sure that you are in the General section, and New Module is selected

3.Create the following lines of code

The Celsius function

Note:

Celsius is the name of the function. You could use any name here but use something meaningful. Then you must use the same name when you define the function in the body of the code.
fDegrees is a variable (Fahrenheit Degrees). When the function is used, a value or a cell reference enters a number for the function to calculate.

Step 2: Use the Function

You can now use your Celsius function just as you would any other worksheet function.

For example you can select it by clicking the Edit Formula button (the one with an equals sign =) found just to the left of the Formula Bar.

Pressing this button activates the formula editing mode of Excel as shown in the figure below.

Excel Formula Bar in the formula editing mode

Clicking on the drop down button to the left opens a quick access list of possible functions to use as shown below.

Quick selection list of commonly used functions

Select the More Functions option at the bottom of the list to display the Paste Function dialog box from which you can select any listed function.

Since you have created a User Defined function, you’ll need to select that category. Your Celsius function should appear in the Function name list as shown below.

The Paste Function dialog box displaying the User Defined functions available

Hint: You can also get to this point more directly from following the menu sequence Insert/Function.

The function wizard

When you select the Celsius function and press OK you are presented with the Function Wizard as shown below.

Function Wizard showing the Celsius function

Try different values into the FDegrees input box and check your code for errors by checking the results given in the lower part of the dialog box.

In the example the boiling point of water is 212 degrees Fahrenheit so we enter 212 in the FDegrees input box and this shows that the Formula result is 100 - the temperature in degrees Celsius, the boiling point of water.

Practice activity

In this activity you’ll create a function macro.

Below are two small functions that work with dates. The first returns a string (text) value that is either ‘Work Day’ or ‘WEEK END’ depending on the date given.

The second returns the name of the day of the week (Saturday through to Friday). In each a calculation is made to convert the date given to a code from 0 to 6 where 0 is Saturday, 1 is Sunday and so on. Then decisions are made and the return value set based on that code.

Sample code for the functions IsWeekend() and IsDay()

What you have to do

Type in the above code and create a sheet with the formulas as below. Try changing the Cell A1 from =Today() to =Today() + 1, and so on.

What is the effect of changing the date?

Sample sheet showing formulas using the functions IsWeekend() and IsDay()

What do you think such functions could be used for?

Feedback

One thing spreadsheets generally lack is a way to write the day of the week in a cell. Many writers use the built-in WEEKDAY() function to give a value between 1 and 7 then use large conditional formulas in each cell that needs it. This could be replaced by the IsDay() function above.

The IsWeekend() function is also suitable for use when planning. It becomes easy to identify the weekends so that you don’t book work to be done on weekends or alternatively you book system upgrades to be done only on weekends.

Sample sheet showing the use of the functions IsWeekend() and IsDay()

You will find that the spreadsheet above will move the days up each day that you open it since Cell A1 depends on the ToDay() function which changes value each day. To lock the dates just enter the first date into Cell A1.

Can you think of other uses for this type of behaviour?

2005