AUTOMATED LOAN CALCULATION WORKSHEET
Macro Programming to Hide Rows
Pull up the Exercise 13-Macros spreadsheet. This is where we left off from Exercise 12 when we made a drop-down box to automatically find the appropriate interest rate and loan duration. We also selected the print area of the amortization table and used Page Format to center the table for printing purposes. In this exercise, we will create a macro to automatically hide and unhide rows to match the duration of the loan. Macro programming has the same difficulties as writing IF statements – it often helps to diagram what you want the program to do. Like IF statements, you have to try to anticipate every possible outcome. Mistakes will be made, and you will have to edit your program to get it to work properly.
Macro to Hide and Unhide Cells
A macro is a programming step that automates what you would otherwise have to do by hand. It is particularly helpful when you intend to use a spreadsheet repeatedly for different applications. If all of the actions are identical, creating a macro is very easy since Excel allows you to simply record the action once. When you want flexibility, you will need to use a little bit of computer programming skills which in Excel is referred to as Visual Basic. Let’s start with the creation of a couple of buttons that will be used to initiate the macros that we will use for the spreadsheet.
You will again need the Developer tab showing on the ribbon that we used to create the drop-down box in Exercise 10. If it is not already showing, click on the Microsoft Office Button (top-left with colored squares) and select Excel Options at the bottom. In the form that appears, check the Show Developer tab in the Ribbon option under the Top Options section and then click OK.
Now click on the Insert drop-down of the controls section of the ribbon and click on the Button icon in the Forms Control area(far left on the first row). With the cross-hairs, create a box that covers cells F1 through G2. Or you can make it any size that you want. A box will appear asking you to name the macro. Click OK as we will change this later. Now click in the box that was just created and change the text from Button 1 to Hide Rows. If you clicked on any empty cell outside of the box before you renamed it, you will need to right-click on the box again and then left-click on it (to get rid of the options box that appears over it) in order to rename the button. Next, highlight all of the text in the button and then right-click the button. Click on the Home tab of the ribbon and click on the Format drop-down in the Cells section. From the drop-down selections, choose Format Control. This will produce a box where you can choose the font, size, etc. for the button text. When you are finished formatting the button text, click on any cell outside of the button.
First, let’s make sure that the Type of Loan drop-down box is set on 4-year New Car. What we would like to do for printing purposes is to hide rows 13 through 38. In order to be able to tie this to the button, we need to create a macro. Click on the Developer tab of the ribbon and then the Record Macro icon in the Code section. A box will appear indicating that the macro name will be Macro1. Change it to HideRows and click OK. Next, highlight rows 13 through 38. Click on the Home tab of the ribbon and then Format – Hide and Unhide – Hide Rows. Finally, click on cell A1. Clicking on cell A1 sets the screen view of the spreadsheet so we can see it from that viewpoint after the macro has performed its function. Now click on theDeveloper tab of the ribbon and click on the Stop Recording icon in the Code section. A macro has been created that has recorded the row-hiding procedure that we just went through.
To connect the Hide Rows button to the macro, right click on the Hide Rows button and select Assign Macro. Now click on HideRows and click on OK (or double-click HideRows). This has tied the button to the hiding of rows 13 through 38.
Let’s now create an Unhide Rows button. Again, click on the Insert drop-down and choose the Button icon in the Forms Control section. With the cross-hairs, create a box that covers cells I1 through J2. An easier way is to simply right-click the Hide Rows button that we previously created, click on cell I1 and then paste it (Control-V). Click inside the box and rename it Unhide Rows.
Now we need to create a macro for this button. Again, go to the Developer tab and click on Record New Macro change the Macro2 name to UnhideRows and click OK. Now highlight rows 12 and 39 and, from the Home tab of the ribbon, click on Format – Hide and Unhide – Unhide Rows. Click on cell A2 and then click on the Stop Recording icon in the box that appeared and the macro has been created that has recorded the unhiding procedure that we just went through. Right-click on the Unhide Rows button and select Assign Macro. Now click on UnhideRows and click on OK (or double-click UnhideRows). This has tied the button to the unhiding of rows 13 through 38. Notice that if you copied the Hide Rows button, the new Unhide Rows button was tied to the HideRows macro since that link got copied as well.
Now click on the Type of Loan drop-down box and select 15-year Fixed Mortgage. The amortization table will update to reflect the 15-year mortgage and interest rate. Click on the Hide Rows button. What you will see is that the same rows were hidden as occurred when the type of loan was the 4-year New Car loan. This is because the macro was set to only hide the rows 13 through 38 regardless of which ones had numbers in them. This is where it is necessary to go into the macro programming code itself and create a way of adjusting it for differing maturities of loans. The way to do this is to create a “marker” cell that indicates the beginning and the end of the rows to hide. This is the tricky part.
Click on the MacroPage tab. In cell A14 type in “Begin Hide” and in cell A15 type in “End Hide”. The End Hide is not really needed in this case since we always know that the maximum for loans is 30 years, but other applications might need an adjustment. Now we need to calculate the first row to hide. The shortest loan that would be possible would be a 1-year loan, so we would never want to hide row 9 on LoanAmort. Thus, we would always want to start on row 9 plus the number of years of the loan that is being analyzed. In cell B14 on MacroPage, type in the formula that adds the number of years of the loan to the number “9” of row 9 on LoanAmort.
=9+B12
In cell B15, we know that the last line will always be in row 38 of worksheet LoanAmort, so we can simply type in “38”.
Now we need to edit the HideRow macro. In order to do this, click on the Developer tab and then click on Macros. In the dialogue box that appears, click on the HideRows macro and click Edit. You will see coding for the HideRows macro (Sub HideRows()) and the UnhideRows macro (Sub UnhideRows()). The green print always appears in the line starting with an apostrophe (‘) indicating that it is a comment row. This is where you can label the macro with a description so you know exactly what its purpose is.
The first line of the HideRows macro is stating that the cursor is to go to cell D13 (or C13 or whatever column you started the hide rows in) on the current page (LoanAmort is the worksheet you’re on when you click the Hide Rows button). If you were already in row 13, your macro probably does not have this line. If you scrolled down with the ball on your mouse to get to row 38, the second line (and others if you rolled it more than once)will show that you scrolled down. If you used the scroll bar on the right of your spreadsheet screen, there will not be a Scroll in the second line of the macro.
The next line is saying to “select” (highlight) rows from cell D13 to D38 (or whichever columns you used). One of the problems with macro recording is that it records everything that you do. This is not efficient. Specifically, we don’t need the first two lines that say to go to cell d13 and then scroll down. All the macro needs to know is to hide rows D13 to D38 (actually, the column is unimportant as well). Erase the first two lines of code so that your first line of code is the one ending with “dot”-Select. In order to see that this has not affected the macro, click on the spreadsheet (at the bottom of your monitor screen), click on the LoanAmort worksheet tab and then click the two buttons, Hide Rows and Unhide Rows.
Now for the “tricky” part. First, let’s “define” two variables, BeginHide and EndHide. Type in the following for the first line (where the first line that you deleted was in the Visual Basic Editor). Or go to the blank line just above the “dot”-Select line and enter to make a new code line.
BeginHide= Sheets("MacroPage").Range("B14")
You need the worksheet name and cell in quotations so that Excel does not think you’re referring to a named range.
Now type in as the new second line the following variable definition:
EndHide= Sheets("MacroPage").Range("B15")
The easiest way to do this is to just copy the BeginHide (first line) and then edit it to say “EndHide” and “B15”.
In the third line, we will need to replace the term Range(“D18:D38”) with another way of designating the cells. Specifically, the term “D18” reflects the 18th row in the 4th column. This can also be written as Cells(18,4). The 18 refers to the row and the 4 refers to the column. Since we don’t really care what column we use, let’s write it as row 18 and column 1. However, we will use the BeginHide variable definition that is tied to our calculation from the MacroPage cell B14. We also need to have the ending row defined as the 38th row and the 1st column. The following term defines this range:
Cells (BeginHide,1),Cells(EndHide,1)
Replacing the “D13:D38” in the Range parentheses of line 3 in the macro will do this. Line 3 should now read as
Range(Cells(BeginHide, 1), Cells(EndHide, 1)).Select
Click on the spreadsheet and play with the Hide Rows and Unhide Rows buttons with different Type of Loan choices to see that it works.
Actually, you will see that it does NOT work for loans of 30 years. That is because if the loan is 30 years, we do not need to hide any rows. This is where an IF statement needs to be used, just like we do in equations. A Visual Basic IF statement requires that we begin the HideRows macro with an IF that, if true, we jump to a later point, otherwise we do what we have programmed. In this case, if the loan duration is 30 years, we simply jump to the end of the macro, otherwise we will hide the rows. To do this insert a new line 1 in the macro that reads as follows:
If Sheets("MacroPage").Range("B12").Value = 30 Then GoTo Finish
The Sheets tells us what worksheet to look at and the Range tells us the cell. The Value indicates that this is a value (rather than a text). If the value is 30, then we go to Finish (which we have not created yet), otherwise go ahead with the macro that we have written and tested. To end this IF statement, we need to add a line at the end of the macro (after Range("A1").Select but before End Sub). The line is simply the following:
Finish:
If this were a longer macro, the jumping point could have been before we end the macro, in which case the remaining commands would still be executed. Also, you can have several IF statements in the macro that direct the execution to the same point in the coding (“Finish” in this case) or other points (such as “Continue”, “Continue1”, etc.).
Now go to the spreadsheet and play with the Hide Rows and Unhide Rows buttons with different loan types. Everything works fine.
Combining the Hide and Unhide Macros to a Single Button
Finally, we could have been even more efficient. Rather than have an Unhide Button, we could just make sure that anytime we pick a new loan type we simply unhide the rows and then hide them to fit the new loan. In other words, let’s combine the UnhideRows macro with the HideRows macro. This is easily done by just copying the first two lines of the UnhideRows macro and putting it at the beginning of the HideRows macro (before the IF statement). Do that now. In addition, change the range of row to unhide from “D12:D39” to “D8:d39”. Since D8 is year 0, this will always unhide any number of rows that have been hidden. Now you can delete everything about the UnhideRows macro in Module1 of the Visual Basic Editor. You can also go to the LoanAmort worksheet and delete the Unhide Rows button by right-clicking on it and pressing “cut”. Go to the spreadsheet and play with it some more.
Did you find anything you would change? I did. If you put in a 30-year mortgage, the unhidden rows are still highlighted and we don’t see the beginning of the amortization table. Move the line Range("A1").Select to after the Finish: line so that even if a 30-year mortgage is selected the cursor will return us to cell A1 of the worksheet.
As you should have noticed, it is important to continually test the buttons and macros as we create them in order to make sure that everything works properly. In the end, it will save you a lot of time since you find errors as you create the product rather than trying to track them all down after you’ve created a lot of objects (like buttons) or written a lot of code.