In Excel you can write 2 different types of macros, although they are often confused and used interchangeably. The first is a sub routine (denoted by the use of "Sub" before the name of the routine). The second is a function (denoted, logically, by the use of "Function" before the name of the function). The difference between the two is quite important.
- A sub routine can change the value and/or format of a cell, but can only be invoked by an event occurring (e.g. the workbook being saved, or the value of a cell changing) or a user command (such as clicking on a button to run the sub routine).
- A function cannot change the value or format of a cell in the workbook, but they can be invoked directly from a cell.
The reason for this difference is simple: Excel must keep track of which cells are dependent on others and which are independent; if you were able to call a sub routine from a cell and then change other cell values from the macro, Excel would no longer be able to track dependencies.
You might think that sub routines sound more flexible and therefore more widely used (and you would probably be right), however user-defined functions also have their, well, functions (I couldn't help myself)! A classic example of this is a function that calculates an area based on dimensions (such as radius, diameter, length, height, etc.). Functions can be much more complicated though. For example, I recently wrote a function that would determine the type of element being considered (cylinder, rectangle, etc.), use that information to read in a formula from a separate worksheet (where all of the possible formulas were listed - a different one for each element type), and then calculate and output the second moment of area for that element. To start with though, we'll write a quick function that just calculates the area of a circle or of a rectangle.
To begin, open a new workbook. Add the following data to your worksheet, in 4 different columns:
| Width | Height | Diameter | Area |
| 10 | 50 | ||
| 5 | 12 | ||
| 6 | 13 | ||
| 6 | |||
| 10 | |||
| 0.75 |
Now add a new Module to the workbook in the Visual Basic Editor (Alt+F11) under "Insert - Module". The function must be written in a module, not in one of the Excel Objects (a sheet or the workbook). Choose a name for your function - I'm going to use "Area". A function requires you to specify the input when you declare the function. In this case the input we require are the variables used to calculate the area of a circle or of a rectangle (width, height, and diameter). Declare the function using the following line of code:
Excel will automatically add End Function when you press enter after typing the line above. The line above tells Excel that all of the inputs and the output are all double variable types. Although not necessary, it's always a good idea to specify the variable type. Now our function needs to work out if the equation for the area of a rectangle or for the area of a circle should be used. We can do this by looking at the dblDiameter input - if this is empty, then we use the formula for a rectangle, if it's not empty (it has a value) then we'll use the formula for a circle. To do this, add the code below between the Function declaration line above and the End Function line:
Your final code should look like this:
Note that the underscores (_) are used for a break in the code - normally each line of code is a new statement, but using an underscore allows you to continue a statement onto a 2nd line of code.
Return to the workbook now; all that is left to do is to call the function for each row. In the first cell under the "Area" header, type "=Area(B3,C3,D3)". Note that the cell values B3, C3 and D3 should correspond to the width, height, and diameter entries for that row - these are the three cells that I have used, but since you may have used different cells make sure that you specify the correct ones. This tells Excel to call the Area function, with the three correct inputs. If your function is written correctly, the cell will now display the area of the rectangle/circle! Simply fill down the rows to the last entry in the table to determine the area for all of the entries.
Function Area(dblWidth As Double, dblHeight As Double,
dblDiameter As Double) As Double
dblDiameter As Double) As Double
Excel will automatically add End Function when you press enter after typing the line above. The line above tells Excel that all of the inputs and the output are all double variable types. Although not necessary, it's always a good idea to specify the variable type. Now our function needs to work out if the equation for the area of a rectangle or for the area of a circle should be used. We can do this by looking at the dblDiameter input - if this is empty, then we use the formula for a rectangle, if it's not empty (it has a value) then we'll use the formula for a circle. To do this, add the code below between the Function declaration line above and the End Function line:
If dblDiameter = "" Then
Area = dblWidth * dblHeight
Else
Area = 2 * WorksheetFunction.Pi * (dblDiameter / 2) ^ 2
End If
Your final code should look like this:
Note that the underscores (_) are used for a break in the code - normally each line of code is a new statement, but using an underscore allows you to continue a statement onto a 2nd line of code.
Return to the workbook now; all that is left to do is to call the function for each row. In the first cell under the "Area" header, type "=Area(B3,C3,D3)". Note that the cell values B3, C3 and D3 should correspond to the width, height, and diameter entries for that row - these are the three cells that I have used, but since you may have used different cells make sure that you specify the correct ones. This tells Excel to call the Area function, with the three correct inputs. If your function is written correctly, the cell will now display the area of the rectangle/circle! Simply fill down the rows to the last entry in the table to determine the area for all of the entries.
No comments:
Post a Comment