CHAPTER 11
Automation Using Visual Basic Applications (VBA)
A powerful capability of all Microsoft Office applications is the ability to program actions using the Visual Basic Applications (VBA) programming language. For Microsoft Excel, VBA is especially useful for frequently used commands that require multiple procedures and repetitive actions, and in more advanced situations for calculations that exceed the spreadsheet’s processing ability. Examples relevant to corporate valuation modeling include creating a system to run various sensitivity scenarios, implementing iterative processes in code that overcome the circular references left in DCF models due to financial plugs, and installing buttons and checkboxes to control sheet printing and report distribution. Implementing such functionality requires a basic understanding of the VBA language and how the language interacts with Excel.
Most users have unknowingly used VBA by recording a macro to complete simple repetitive tasks. However, few take the step to learn how to write and edit VBA code by hand. The problem most users have with unlocking the full potential of VBA is learning how an object-oriented programming (OOP) language works. While entire books can and have been written on using VBA, this chapter introduces the model operator to the basics of using VBA through additions to the example model. Beginners might find additional texts helpful for further explanation, while intermediate-to-advanced users might want to skip to the specific code examples.

THE OBJECT-ORIENTED PROGRAMMING LANGUAGE (OOP)

Programming in VBA requires a shift in thought. We have to move from thinking about financial modeling in terms of Excel worksheet, cells, functions, and formulas to text-based instructions. This means we have the difficult task of taking visual tools that manipulate concepts and replacing those tools with text. To complicate matters, the text is written in English, but not in a manner we are accustomed to.
To help us make sense of this new language, we should learn about its structure. If one pictures the first soon-to-be computer programmers sitting in a room and deciding how they would recreate the world in code, one could imagine grandiose thoughts about virtual reality, artificial intelligence, and genetic algorithms. Although that’s the cutting edge today, the foundations are much simpler. If we were to isolate three basic elements of OOP, we would see this recreation is based on items around us, descriptions of those items, and instructions to put the items into action. These elements are otherwise known as objects, properties, and methods. Examples of each are shown in Figure 11.1.
FIGURE 11.1 There are a multitude of examples for objects, methods, and properties.
170

Objects

As part of the name, it’s clear that objects are important to OOP. Objects can be thought of as the nouns of OOP. They are not as granular as something like atoms or molecules, but more of a representation of items around us, such as people, cars, or houses. To make the transition to finance, we can think of an object like a financial instrument, such as a loan. To make the transition to Excel we must visualize each cell, worksheet, and workbook as objects.

Properties

If objects are the nouns of OOP, properties are the adjectives. Properties describe objects to differentiate each object from similar objects. If we use a car as an example, we can have a green car, a red car, a Honda, a Toyota, and so on. In terms of our financial example, where a loan is an object, that loan can have its own unique balance, rate, and term. Continuing with our Excel example, a cell could have a characteristic such as its fill color.

Methods

Finally we get to action. Methods put objects into action just as verbs bring nouns to life. Cars are brought into action through driving and can move forward, left, right, and backward. A loan can be amortized over time in order to pay the balance down. In Excel we can do many different actions; some of the easiest are copying and pasting the contents of one cell to the next.

FOLLOW THE RULES

With the least amount of reference to the movie The Matrix possible, I should state that in computer programming we must follow the rules. Objects can be described only by properties and put into action only by methods that have been created for the object. For instance, so far, cars cannot fly; therefore, if we tried to use a “fly” method on a car object that we created in the standard sense of a car, the code would crash. Unless we specifically program in the fly method, the code will not work.
On an even simpler note, we will not be creating our own objects in this chapter. This means that we must use the objects that have been created for us in the default VBA libraries and can use only the properties and methods that the Excel designers have associated with those objects. If we try to use existing properties or methods with objects that are not associated with those properties or methods, we will also encounter errors.
Similarly, there will be commands that are part of the VBA language that must be used in specific ways. We cannot deviate from the preprogrammed applications of these commands. Any disordering or misspelling will cause the code to break down. Following such a specific rule set and learning what is virtually an entirely new language is part of the reason why many people become so frustrated learning VBA. In this chapter, we will limit the objects, properties, methods, and commands to those that are absolutely critical to financial modeling. Also, while there may be more efficient VBA coding that can run faster than what we are implementing, the subject matter and techniques that we will learn will be limited to as few new concepts as necessary and the fastest ones to pick up as a new programmer.

THE VISUAL BASIC EDITOR

To help us organize and work with the VBA language, the designers of Microsoft Office included an integrated development environment (IDE) called the Visual Basic Editor (VBE). VBA code can be written, stored, run, and debugged from the VBE. This makes our life easier as new programmers since we do not need to compile in a separate program or create executable files to run our programs. To access the VBE:
• For Excel 2007: Visual Basic options are contained on the Developer tab of the ribbon. Chapter 10 explains how to get the Developer tab to show up on the ribbon if it is not visible. An alternative to this process is pressing ALT-F11; however, the Developer tab should be kept visible to easily access other tools.
• For Excel 2003 or earlier: Go to Tools, Macro, Visual Basic Editor or use the ALT-F11 keyboard shortcut.
The VBE will open in a separate window and should appear as in Figure 11.2.
FIGURE 11.2 The Visual Basic Editor (VBE) is Visual Basic Applications’ (VBA) integrated development environment.
171

The Menu Bar

The general menu bar features recognizable commands such as File and Edit; however, most of the options within each command will seem strange to a new user. View and Insert are the two key menus that we will use. In addition to menus, there are many buttons that will appear new. Look at the Standard Toolbar that should appear as a default setting.
The Standard Toolbar has a few buttons that will be useful for the basic operation of the VBE. Keep in mind the following:
• View Microsoft Excel jumps back to the Excel workbook.
• Run Sub/UserForm runs the code currently selected.
• Break breaks the code currently being run.
• Reset resets the code after a break has occurred.
• Object Browser opens the library of VBA objects.
The menu bar is shown in Figure 11.3.
FIGURE 11.3 The menu bar assists in the code-writing process.
172

The Project Explorer and the Properties Window

To the left side of the VBE, there are two important windows: the Project Explorer and the Properties Window. The Project Explorer looks a little like Windows Explorer in the way it organizes information. It is set up as a directory tree where more detailed information within a general concept can be expanded or compressed by clicking on + and - symbols.
The most general category in VBA is a Project, which is essentially the Excel workbook. The workbook that you are in is distinguished by its name, which is in brackets. A common mistake is to have multiple workbooks open and to start typing code in the wrong workbook. Make sure that the workbook that you want the code stored in is selected. Keep in mind that even if you have just one workbook open there may be multiple VBAProjects listed. The other projects are most likely add-ins, particularly since you may be using Excel after installing the VBA Analysis Tool Pak, as recommended in Chapter 1. Other add-ins that are installed are identified as .xla for .xlam files.
The first subfolder contains the Excel objects, which are the individual sheets in the workbook. Code can be stored under a sheet or for the workbook in general, but code stored in these areas is for very special purposes and should not be done until a user understands more about the VBA language. Just as we will be careful which workbook is selected, we should be careful if a sheet or This Workbook is selected.
The area in which we will be entering our code is in a module. Module is a fancy term for a separate area to enter code. Code is often organized by purpose and functionality into separate modules. Within modules we can write code in two styles: subroutines and functions. A subroutine is like writing a list of commands. The computer reads this list in the order that it is entered and, in Excel’s case, causes calculation or affects the workbook. Basic macros use one subroutine to accomplish a task, whereas more advanced macros often use multiple subroutines. Related subroutines are stored in the same module. For instance, a module might be named Print_Routines and contain three subroutines that format and print different sections of the Excel workbook. Figure 11.4 shows the project explorer.
The area that typically opens below the project explorer is the properties window. The properties window allows a user to graphically view and alter the properties of any object in Excel, simply by clicking on the object and going to the properties window. The properties window is shown in Figure 11.5.
FIGURE 11.4 The VBE project explorer is similar to a Windows directory. It helps us organize and locate our code.
173

WRITING CODE: SUBROUTINES AND FUNCTIONS

Although there are two ways of writing code, all of the code that we will write in this section is structured as subroutines. A subroutine is a way of writing code that is like a list of commands. When the commands are run, they are completed in order from beginning to end.
A function is code that returns a value when parameters are entered into it. Whereas a subroutine can return a value as a result of the commands entered, a function explicitly takes in parameters and returns a value from those parameters. Functions in VBA can be created not only to calculate in the computer’s memory, but also for use on the sheet just as with any preprogrammed function we use.
FIGURE 11.5 Objects in the workbook and on the worksheet can be viewed and altered using the properties window. In this example, a worksheet object is selected and its properties displayed.
174

UNDERSTANDING VBA CODE AND PRACTICING CODING TECHNIQUES

Before we jump right into a financial example using code, we will complete two separate examples to get a foundation in coding. The examples will be quite easy and seem unrelated to finance, but they introduce the core concepts involved in many of the underlying subroutines in the example model. Overall, we will seek to understand the following concepts and techniques:
• Moving data between Excel and VBA
• For Next loops
• Variables
• Offset property
To get practice with these concepts and techniques, the next two Model Builder exercises will not contribute to the actual example model, but are intended to develop the reader’s skill prior to actually implementing the techniques in a financial modeling context.

MODEL BUILDER 11.1: MOVING DATA USING VBA

1. For this Model Builder, you should open a new workbook and save it as VBATestCode.xls. In this workbook we will set up the functionality to enter a name in one range, press a button, and have that button run code that replicates the name in a different cell.
2. The first step we usually take in any VBA project is prepping the workbook with as much Excel functionality as possible. By drawing on VBA only when we absolutely have to, we simplify our code and make it much easier for users to interpret. Obviously, this easy example can be entirely accomplished on the sheet, but let’s learn about VBA by going through the steps.
3. Enter the text Name as a label in cell A1 of the first sheet. Then name cell A2 MyName. Also name cell C5 Destination. Naming ranges in Excel allows us to refer to them in a much easier and more reliable format than referring to them using the R1C1 (row-and-column) notation.
4. Press Alt-F11 to get to the VBE. If you have multiple workbooks open, make sure to navigate to the correct VBAProject (VBAProject(VBATestCode_Book.xls)). In this project, insert a module by going to the Insert menu and selecting module. Click on the newly inserted module (Module1) to ensure that the correct code window is visible to the right.
5. Thinking about the basic structure of OOP and VBA that we learned earlier, we should recognize that the cells in the workbook are objects. In fact, we have applied names, which are properties, to two of the objects so we can refer to them in an easy fashion. We need to understand that we will be writing a list of commands to move the name from the MyRange cell to the Destination cell. This will be done using a subroutine. Click on the code window and enter the code Sub NameMover ( ) and press Enter. An End Sub should automatically appear underneath. This process is how we initiate a subroutine and is done every time we want to create a new subroutine.
6. After we have initiated the subroutine, we should think about our task at hand. We probably want to refer to the MyName cell in some way. In VBA we can refer to a cell or a range of cells as “Range objects.” This is done using the following convention:
Range(“NamedRange”) or Range(“R1C1 reference”)
In our example, we should enter the following code underneath Sub NameMover ( ) :
  Range(“MyName”)
7. We should notice what happens if we type a period (.) after the line of code above. You will see that a drop-down box appears with different named items. This is actually a list of the possible properties and methods that can be used with
FIGURE 11.6 Properties and methods work with objects by entering a period or dot after the object.
175
a Range object. The VBE assists us in using properties and methods correctly in this way. Some may think we should try to use the Copy property (you can check and see that it is there in the drop-down list as shown in Figure 11.6). However, we will learn the most efficient method of doing this procedure.
8. We can directly move or assign values in VBA by using the equal sign (=). Modify the code that was just entered in step 6 so that it reads:
Range(“Destination”) = Range(“MyName”)
Notice here that something seems backwards. We started by focusing on MyName, but now are first referencing the Destination range and setting it equal to the MyName range. This ordering with the equal sign is how VBA works. An easy way to remember this is by thinking of the phrase: “The left side of the equal sign accepts what the right side provides.”
One thought on why this concept causes difficulty for many people is that in Western languages we often refer to objects or nouns later. In English, the noun often comes at the end of the sentence, such as “How are you?” In many non-Western languages, the noun comes first. For instance, in Hindi you would say, “Tum kaise ho?,” which is translated literally into English as “You how are?” Computer code is more similar to the latter style, where the object we want to affect starts the line of code.
9. This single line of code is all that is required to compose the subroutine. Some experienced VBA programmers may suggest using the Value property after each range object so that the code looks as follows:
Range(“Destination”).Value = Range(“MyName”).Value
But this is not necessary, unless we are concerned with cell formats or formulas. If we are concerned with those, we might need to use additional properties, such as the Formula property, if we want a formula to be replicated.
10. To run this subroutine, we could use the Run button on the VBE tool bar that we discussed earlier, but everyone gets satisfaction out of creating buttons and
FIGURE 11.7 The subroutine for the NameMover macro is controlled by a Forms button.
176
pushing them to do things, so we should learn about buttons. Go back to the Excel sheet where we named the ranges and entered our name. To insert a button, go to the Forms tools. If getting to these tools is unclear, refer to Chapter 10 for a review of the process. Select the button tool. Left-click and drag a button directly on the sheet that we were working in. The minute the left-click is released, a dialogue box will come up prompting the user to select a macro to assign to this button. Select the NameMover macro.
11. Once the macro is assigned, we can change the name from Button1 to any custom name that we desire. Name the button Name Mover. Select outside of the button so that we are no longer editing the button itself. When the pointer is scrolled over the button, it should change from the default arrow to a hand. Push the button and the NameMover macros should run successfully. The final view of the sheet is shown in Figure 11.7.
12. The final code should read:
Sub NameMover()
Range( “Destination ”) = Range( “MyName ”)
End Sub

Loops

The real power of VBA is brought out when we work with loops. Looping is a programming technique that repeats a section of code for a certain number of iterations or until a condition is met. This is important for financial modeling for a number of reasons:
• Loops allow systematic movement throughout a model so multiple cells can be referenced and manipulated quickly and repetitively.
• Memory-intensive calculations can be done quickly. For instance, if we were to amortize a pool of 5,000 loans on a monthly basis, an Excel-based implementation would crash. However, a VBA-based implementation would work quickly.
• Loops can exceed Excel’s limitations. If we were to run a simulation for 100,000 iterations, we would not be able to do this on the Excel sheet in Excel 2003 or earlier. Even in Excel 2007 we would still want to use VBA since the previous point about memory and calculation speed problems would occur.
Overall, we will find loops an essential technique to learn in order to have full flexibility with coding.

Variables

In order to learn a basic loop, we must also learn about variables. Variables are just as we remember from algebra: symbols that represent a value that can vary. In the case of VBA, the symbol is most likely text and the value can be a number, text, date, or TRUE or FALSE value. Variables are important to use in coding for two main reasons:
1. Code management: We should structure code so that we assign values to variables early in the code and then use the variables throughout the code. The reasoning behind this is that if we need to change the value reference, we will have to do it only once in the beginning of the code. Then the variable used throughout the code will be fine. However, if we did not use a variable and directly referenced the Excel sheet every time we needed a value, we would have to change all of the references throughout the code if the Excel sheet reference changed.
2. Memory management: Using variables allows us to reduce memory use and make our programs run faster. This is done in two separate ways. The first is a byproduct of proper code management above. If we use variables throughout our code, we limit the in-and-out processes between VBA and Excel. These processes are much more memory demanding than basic calculation with variables. Limiting our code to as few in-and-out processes as possible will create fast, robust code.
The second way is that variables can be defined to hold certain types of data. Some types of data use less memory than others. For example, a Boolean or TRUE/FALSE variable in VBA requires 2 bytes of memory as compared to a Variant, which can require up to 24 bytes of memory. By defining variables we constrain their memory usage, which can greatly speed up our programs.

Offset Property

A useful property to help with movement through Excel worksheets is the offset property. In Chapter 2 we learned the OFFSET function, which starts at a reference point and references another cell depending on the row and column inputs entered. VBA can do a similar process with the offset property. This property works with a Range object. The Range object is the starting reference point. Then two entries are needed: a number for the number of rows to move up or down and a number for the number of columns to move right or left. Here is how this would look in code:
 Range(“Test”).Offset(1, 5)
FIGURE 11.8 In the example code, Range(“Test”).Offset(1, 5), A1, which is named “Test,” would be referenced and offset by 1 row and 5 columns. The example code would then equal the value in cell F2.
177
In this example, the Range object Test would be offset by 1 row and 5 columns. Whatever value is in the cell that is referenced through the offset property will be returned by this section of code. The Excel sheet example is shown in Figure 11.8. We could then load that value into a variable by writing:
Variable = Range(“Test”).Offset(1, 5)

MODEL BUILDER 11.2: A FIRST LOOK AT LOOPS AND VARIABLES IN VBA

1. To learn about loops and variables in an easy manner, we are going to extend our first code example. In this Model Builder example, we will write code that repeats our name as many times as we like. Go back to the Excel sheet in VBATestCode.xls where the name information was entered. Add the text # of Rows in cell A4. Enter the value 1000 in cell A5 and name cell A5 TotalRows.
2. Press Alt-F11 to get to the VBE. In the VBATestCode project, select Module1. We will create new code in the same module as in the previous Model Builder, directly underneath the last code created. Enter the code Sub NameRepeater ( ) on the line after the previous code’s End Sub.
3. The first task we should always do when we get more proficient with VBA coding is to declare variables. Declaring variables makes the system aware of the variable name and what type of data can be entered into it. The term for declaring a variable is more technical in VBA. The proper command to use is known as dimensioning a variable, which is done with the term Dim. Enter the following code on the next line:
Dim Name As String
Dim TotalRows As Integer
Dim RowCounter As Integer
In this step, we declared three different variables. The first one is called “Name,” and it can contain only text, or, in programming terminology, a string. The second variable is called “TotalRows,” and will contain a number that is indicative of the total number of rows the name will be repeated. The third variable is called “RowCounter,” which will be used in the VBA code itself. The idea that a variable can be created just for use in a program causes confusion for some. Variables can be representations of items from the Excel sheet or they can be created only for use in VBA.
4. The second major task of our program is assigning values to the variables. Recall the statement above: “The left side of the equal sign accepts what the right side provides.”
We can apply this same logic to variables. Enter the following code starting on the next empty line in the NameRepeater subroutine:
Name = Range( “MyName ”)
TotalRows = Range( “TotalRows ”)
This section of code assigns values from the Excel sheet to the variables. Now the variable Name has the text value from whatever name is entered in cell A2 of the Excel sheet and TotalRows as the numerical value from cell A5.
5. The next line of code that should be entered is:
   For RowCounter = 1 To TotalRows
The wording of this code is something entirely new. This is the initiation of a For Next loop. These types of loops are initialized by setting a variable (RowCounter) equal to a value (1) and then performing code that follows this line until a Next statement is reached. After the next statement is reached, the variable increases by an integer of 1. This process continues until the first variable is equal to the second variable after the To statement (TotalRows).
Informally, I refer to the RowCounter variable as a counter variable since it essentially keeps the count of the loop. It is not only convenient that the counter variable keeps the count, but it can be also used for calculation and reference throughout the loop, as we will see.
6. Once we initiate the loop, the next line of code is what gets repeated. Enter the following line of code after the previous one:
   Range( “Destination ”).Offset(RowCounter, 0) = Name
In this line, we use the offset property mentioned earlier. However, there are key differences between this line of code and the example shown earlier. First, the code is reversed from the example. This is because we want a range to equal a variable value, not a variable equaling a range value. The second difference is that we use a variable for the value of the offset, rather than a hard-coded value. This is how we draw upon the power of a loop to “move” through a worksheet.
Walking through what would happen, the first loop would be initiated and RowCounter would equal 1. The range object Destination would then be offset by 1 row and 0 columns. When the loop gets repeated, RowCounter would increase to 2 and the range object Destination would then be offset by 2 rows and 0 columns.
FIGURE 11.9 The code repeats the name in cell A2 for the number of times entered in cell A5.
178
7. The final key part of the For Next loop is the Next. We could just write Next, but we should identify the counter variable in case we start using multiple For Next loops. Enter the following code in the next line after the previous line of code:
  Next RowCounter
This line of code instructs the program to go back up to the start of the For Next loop and begin the process again, unless the counter variable is equal to the value after the To statement. Figure 11.9 provides an overview of the code supporting the Excel sheet.
8. Make sure to end the program with a line stating End Sub. The final code for this subroutine should read:
Sub NameRepeater()
Dim Name As String
Dim TotalRows As Integer
Dim RowCounter As Integer
Name = Range( “MyName ”)
TotalRows = Range( “TotalRows ”)
For RowCounter = 1 To TotalRows
Range( “Destination ”).Offset(RowCounter, 0) = Name
Next RowCounter
End Sub

COMMON ERRORS FOR FIRST-TIME VBA PROGRAMMERS

If your macro did not work in Model Builder 11.1 or 11.2, we need to debug the problem. We can save a lot of time by understanding the most common errors for new programmers. The primary errors include:
• Spelling or inconsistent names
• Going beyond scope
• Forgetting part of a multistep process

Spelling

The number-one error is spelling. You will most likely get the run-time error: ‘1004:’ Method ‘Range’ of object ‘Global’ failed. This common error means that you tried to refer to a range object that does not exist. It usually does not exist because the programmer spelled the range object name differently than the name on the Excel sheet. Check the Name Manager names versus the names used in VBA for inconsistencies.
There can also be simple typo problems with spelling. For example, if you misspell Offset, such as Ofset, the code will break down instantly.

Scope

Another common error is trying to use a value with a variable that is beyond the variable’s scope. This means that we could have declared a variable as text and tried to pass a numerical value into it. This generates an error. Also, if we declared a value as an integer and tried to pass the number 1,000,000 through it, we would generate an error since 1,000,000 exceeds the maximum numerical value for an integer (32,767).

Missing Steps

Some VBA techniques are multistep processes and quickly break down if one of those steps is missing. For example, we learned how a basic For Next loop works. If we start a For Next loop with the For section, but forget to insert the Next code, the entire subroutine will break down. There are other similar issues as you learn more about VBA, such as If statements requiring an End If.

VBA WITHIN A FINANCIAL MODELING CONTEXT

The next section of this chapter marks a switch from the introduction of VBA to application. We will focus on three main problems that VBA allows us to overcome:
1. Eliminating circular references
2. Generating multiple scenarios
3. Performing repetitive administrative functions
Each problem will be solved by a Model Builder section that will add functionality to the example model. The circular reference created to balance the balance sheet will be converted to a looping solution, a new scenario generator sheet will be built and run by code, and finally, an Excel/VBA printer solution will be installed to rapidly export results.

MODEL BUILDER 11. 3: ELIMINATING CIRCULAR REFERENCES

1. We will take a simple approach to our first macro for use in the example model. To eliminate the need for the circular reference we need to mimic the iterative process that is taking place. Essentially, the circular reference is loading up initial values, calculating, and then loading up the results of the calculation into the same formula. This goes on for a number of iterations. At the most basic level, this could be solved by a person copying and pasting the asset and liability plugs into the plug areas on the balance sheet. Done repetitively, the balance sheet should balance. Prior to actually writing code, we should set up our sheet for the process. The Excel sheet needs are minimal for this subroutine. All we need to do is name the following ranges with the corresponding names:
‘Debt’!E14:J14 = rng_SurplusOutDS
‘Balance Sheet’!E31:J31 = rng_STOut
‘Balance Sheet’!E48:J48 = rng_SurplusIn
‘Balance Sheet’!E49:J49 = rng_STIn
2. Press Alt-F11 to get to the VBE. Insert a module and name it Iterative. Modules can be named by left-clicking on the newly inserted module and then going to the Properties Window. There you can enter a name for the module under Name.
3. Start a new subroutine by entering the following code in the Iterative module:
  Sub Plugs()
4. Something you may notice in the example model is green text all over the place. The green text is known as comments; these are useful to explain what a subroutine is doing or what specific lines of code intend to accomplish. Comments are not read when the code is run and are purely for the user’s knowledge. Comments are entered by starting a line of code with an apostrophe (’). Every new line of code will require its own apostrophe to start the commenting process.
5. The next lines of code will be variable declarations:
Dim i As Integer
Dim iteration As Integer
These two lines of code create two variables: i and iteration. Many programmers tend to use single-letter variables, such as i. These are frequently used as counter variables for loops.
6. After the previous line of code, enter:
   Application.ScreenUpdating = False
This is a special command that turns off Excel’s screen updating, meaning that as the subroutine runs, nothing will change on the screen even if the code is moving items around in our model. If we did not turn it off, we would see the screen move around as the code ran. Although screen updating does add an impressive “automation” element, it is a waste of memory and slows our programs down.
7. The next two lines of code after turning off screen updating should be:
iteration = 100
Calculate
Here we assign our iteration variable a value of 100, which will be the total number of times to loop or iterate. We also use a special command called “Calculate,” which calculates the entire workbook in case the workbook was last saved with the manual calculation setting turned on.
8. Enter the following line of code:
   For i = 1 To iteration
This line of code initiates a For Next loop. We use the variable i as a counter variable and loop through to the number of iterations that are loaded into the iteration variable. In more advanced sheets, we could create a sheet value for the number of iterations and load that value into the iteration variable. For the purposes of this subroutine, 100 iterations is plenty and probably not necessary to adjust.
9. The heart of the code is next:
Range( “rng_SurplusIn ”).Copy
Range( “rng_SurplusOutDS ”).PasteSpecial xlPasteValues
Range( “rng-STIn ”).Copy
Range( “rng_STOut ”).PasteSpecial xlPasteValues
Every loop copies the surplus cash calculated in the rng_SurplusIn range and pastes only those values into the rng_SurplusOutDS range. Notice that we used the Copy and PasteSpecial methods and that the PasteSpecial method has a qualifier for us to tell VBA the type of special paste that we are looking for. The same process is done for the short-term debt. Refer to Figure 11.10 for additional reference.
FIGURE 11.10 The plugs’ values are copied and pasted using code to avoid having a circular reference in the model.
179
10. Prior to finalizing the loop, we should make sure the sheet is calculated. Enter the following code to calculate again:
  Calculate
11. One problem with running code, particularly with the screen updating turned off, is that we do not know the progress. This can be accomplished by inserting the following code:
  Application.StatusBar = “Solving Assets and Liabilities,
 Iteration ” - & i & “ of ” & iteration
As with the screen updating code, we are interested in affecting the Excel application itself. However, in this case we want to change the status bar (the bar on the bottom of Excel that normally states “Ready”). We will have it say our custom text, plus the two variables. The reason we use the two variables is that they will change with each loop and show the model user which loop the code is processing out of the total number of loops. The text and variables are joined together with the & symbol, which works identically to the Excel sheet version.
12. We are finally done with the loop and close it off with the following line of code:
  Next i
Remember that if we forget this simple line of code, the entire subroutine will break down and generate an error.
13. Since we adjusted some of the Excel application’s settings we should switch them back to what they were before. Enter the following code:
Application.ScreenUpdating = True
Application.StatusBar = False
This turns screen updating back on and changes the status bar back to “Ready.” Be careful of using a True for the status bar. The default setting is False. If a True is entered in this part of the code, the status bar on the Excel sheet will read “True.”
14. Finalize the code with an End Sub.
15. The final code should read (note that comments are inserted):
   Sub Plugs()
   ’ Declare variables
   Dim i As Integer
   Dim iteration As Integer
   Application.ScreenUpdating = False
   iteration = 100
   Calculate
   ’ Loop through each iteration, replacing the Surplus and ST
         Debt values in with their
   ’ corresponding out areas.
   For i = 1 To iteration
         Range( “rng_SurplusIn ”).Copy
         Range( “rng_SurplusOutDS ”).PasteSpecial xlPasteValues
         Range( “rng_STIn ”).Copy
         Range( “rng_STOut ”).PasteSpecial xlPasteValues
         Calculate
         Application.StatusBar = “Solving Assets and Liabili-
               ties, Iteration ”
         & i & “ of ” & iteration
   Next i
   Application.ScreenUpdating = True
   Application.StatusBar = False
   End Sub
16. To make it easier to run, create a button on the Assumptions sheet and assign the Plugs subroutine to it. You may want to locate it near cell J3 and name it Balance Model.

MODEL BUILDER 11.4:CREATING A SCENARIO GENERATOR

1. The next functionality to implement is the ability to generate multiple results by altering assumptions. Although we could run scenarios by hand, it would take a long time to run a number of variations. In the complete example model there are two sheets dedicated to scenario analysis: Basic Scenarios and Scenarios.
In this Model Builder we will learn how to create the Basic Scenarios sheet functionality. This sheet and the code behind it accepts multiple sales growth vectors, runs each vector through the model, balances the model, and captures the resulting change on the firm value prior to running the next scenario. The Scenarios sheet in the complete example model will not be covered since it is an advanced version that requires search code.
The first step in creating the Basic Scenarios functionality is to insert a worksheet after the DCF sheet and name it Basic Scenarios.
2. The purpose of the Basic Scenarios sheet is to create an area where we can enter sensitivity vectors. We also need to create formulas that provide data about the scenarios that we anticipate running. Enter the following text in the corresponding cells on the Basic Scenarios sheet:
A1: Basic Scenarios
B5: Scenario Data
B6: Scenario #
C3: Number of Periods
C6: Vector
F3: Number of Scenarios
K5: Scenario Results Summary
K6: Scenario #
L6: Firm Value
3. Carry over the dates from the Vectors sheet to the Basic Scenarios sheet. Start with a reference to the Vectors sheet cell E10 in cell D6 on the Basic Scenarios sheet. Copy and paste that reference over the Basic Scenarios sheet range D6:I6.
4. We will focus on Sales Growth for now. To give us data to work with for each scenario, let’s try out 10 scenarios. Copy the scenario data from range B7:I16 on the Basic Scenarios sheet in the complete example model and paste it into the same sheet and range in your model. Do the same for the scenario numbers in range K7:K16. Thus far, the Basic Scenarios sheet should look like Figure 11.11.
5. Two formulas are needed to summarize the number of periods and the number of scenarios in the scenario analysis. Enter the following formulas in the corresponding cells on the Basic Scenarios sheet:
D3: =COUNT(D6:I6)
H3: =COUNT(B7:B65536)
Name cell D3 basicscens_VectYears and cell H3 basicscens_VectCount.
FIGURE 11.11 The first step in creating a powerful VBA scenario generator is creating the control sheet for it in Excel.
180
6. There are a number of ranges we need to name as reference points throughout the model. This will allow us to navigate the model very easily in VBA. On the Basic Scenarios sheet, name the following cells with the corresponding names:
C6: strt_BasicVectorValues
L6: strt_BasicResults1
7. If we take a moment to remove ourselves from the minutiae and look at our overall plan, we will see that we have a number of possible sales growth vectors that will be run through the model. They will have to be moved to the Vectors sheet using code, but the question arises: Where do we move them—Base Case area? Downside Case area? Instead of using existing areas, which could confuse users, we should make a separate area on the Vector sheet for VBA-generated cases. Go to the Vectors sheet and copy all of the labels and references from the complete example model so that there is a VBA-generated case section. This new section should look like Figure 11.12.
FIGURE 11.12 A new section on the Vectors sheet accepts the scenario data from the Basic Scenarios sheet. Note that this is a partial screen shot and that there are more rows of data.
181
8. In order to use the VBA-generator case section on the Vectors sheet, we need to modify lists and formulas. On the top of the Vectors sheet in cell B7, enter the text VBA Generator Case. Modify the named range lst_Scenario so that cell B7 is included in that named range. Next go to each CHOOSE formula in the Vectors sheet range E12:J36 and insert a reference for the possibility of the VBA Generator Case. The functionality that is being built in is to have the user select the VBA Generator Case from the Assumptions sheet, which will populate the values from the VBA-generator case section on the Vectors sheet to the live scenario section. The values that are inserted in the VBA-generator case section are modified by the values from the Basic Scenarios sheet. By implementing such a system we allow for both Excel scenario generation and VBA scenario generation, depending on what the user desires. An overview of the process is provided in Figure 11.13.
9. For our Basic Scenarios functionality we will name one cell on the Vectors sheet to make our VBA code easier to write. On the Vectors sheet, name cell D32 strt_SalesGrowth. We are now ready to write the necessary code.
FIGURE 11.13 The Basic Scenarios subroutine utilizes the calculation power of the existing model to quickly return multiple firm values.
182
10. Press Alt-F11 to get to the VBE. Insert a new module in your model and name that module Scenarios. In that module start a subroutine by entering the following code:
   Sub BasicScenarioGen()
11. Just as in the last subroutine, our first step is to declare variables. Enter the following code after the last line of code:
Dim NumScens As Integer
Dim NumPds As Integer
Dim CounterScens As Integer
Dim CounterPds As Integer
Notice that we create four variables: one that will represent the total number of scenarios, one that will represent the total number of periods, one that will count the scenarios as we loop through them, and one that will count the periods as we loop through them.
12. Also similar to the last subroutine, we will assign values to certain variables. Enter the following code in the subroutine:
NumScens = Range( “basicscens_VectCount ”)
NumPds = Range( “basicscens_VectYears ”)
This code takes the values from the Basic Scenarios sheet and loads them into the variables that we created.
13. We will initiate two loops, which is something we have yet to do. This is done because we want to loop through each scenario, but for each scenario we want to loop through each period. Because we will first loop through the scenario, this is known as the outer loop; the second loop, the periods, is referred to as the inner loop. To start this process enter the following code:
   For CounterScens = 1 To NumScens
    For CounterPds = 1 To NumPds
14. The key to each loop is that for each period within a scenario we want to replicate the value that is on the Basic Scenarios sheet and put it into the VBA generator case section of the Vectors sheet. We will use the same offset property as before to accomplish this. Enter the following code:
Range( “strt_SalesGrowth ”).Offset(0, CounterPds) =
Range( “strt_ BasicVectorValues ”).Offset(CounterScens,
CounterPds)
One thing that may confuse new programmers is the use of the underscore. Underscores are used to continue a line of code to another line for readability. The functionality is such that this appears to be one line of code to the computer, but since it would be tedious to scroll right to read long lines of code we can continue the same line of code on the next line by inserting a space and an underscore. Do not forget the space prior to the underscore; otherwise, an error will be generated.
Overall, this section of code does what we want. It offsets from a location on the Basic Scenarios sheet and puts the value in the cell that it is referencing into the corresponding period location on the Vectors sheet.
15. The next few steps should be done carefully since it is extremely important what is put prior to the Next statements in our code. We are fine right now to enter the following code after the last two lines of code:
  Next CounterPds
16. However, before entering another Next statement for the scenarios, there are a few things we need to do in between the scenario loop. At this point, we have entered new values in our model, which means that the balance sheet is most likely unbalanced. Instead of relying on the circular reference or rewriting the Plugs subroutine, we can just run the Plugs subroutine from the BasicScenarioGen subroutine. Do this by entering the following line of code:
  Call Plugs
The Call statement calls a subroutine and is followed by the subroutine’s name.
17. After we balance the model, we should make sure that everything is calculated and force another calculation of the workbook just in case. While this may be redundant, our processing time is relatively low, so we can enter the following line of code:
  Calculate
18. Now, if we were to loop to the next scenario, we would essentially lose any result from the previous scenario! Although we could pull any number of results from our model, we will keep it simple and focus on the firm value, which is already a named range (outputs_FirmValue). Enter the following code to copy the firm value onto the results section of the Basic Scenarios sheet:
Range( “strt_BasicResults 1”).Offset(CounterScens, 0) = _
Range( ”outputs_FirmValue “ )
19. We can loop to the next scenario to repeat the process for each scenario on the Basic Scenarios sheet. Also remember to make sure that the End Sub statement is there. The last lines of code should be:
Next CounterScens
End Sub
20. The final code should be:
   Sub BasicScenarioGen()
   ’ Declare variables
   Dim NumScens As Integer
   Dim NumPds As Integer
   Dim CounterScens As Integer
   Dim CounterPds As Integer
   ’ Assign values
   NumScens = Range( ”basicscens_VectCount “ )
   NumPds = Range( ”basicscens_VectYears “ )
   ’ Loop through each scenario and replace the Vector sheet
        sales growth vector
   ’ with each scenario’s assumption from the Basic Scenarios
        sheet.
   For CounterScens = 1 To NumScens
    For CounterPds = 1 To NumPds
    Range( ”strt_SalesGrowth “ ).Offset(0, CounterPds) =
    Range( ”strt_ BasicVectorValues “ ).Offset(CounterScens,
        CounterPds)
    Next CounterPds
    Call Plugs
    Calculate
    Range( ”strt_BasicResults 1”).Offset(CounterScens, 0) = _
    Range( “outputs_FirmValue ”)
   Next CounterScens
   End Sub
21. For the user to use this code, we should create a button on the Assumptions sheet, beneath the previous button, and assign the BasicScenarioGen subroutine to it. We should name that button Generate Scenarios. Also, the user should know that for this functionality to work the VBA Generator Case needs to be selected on the Assumptions sheet in cell D31 (inputs_ScenSelector).

MODEL BUILDER 11.5: AUTOMATIC SHEET PRINTING

1. The final code that will be explained is the printing subroutine. The overall goal of this functionality is to have the user select checkboxes on the Assumptions sheet for the sheets in the model that are to be sent to the default printer. Most of this macro is actually controlled by items on the Excel sheets. Figure 11.14
FIGURE 11.14 The automatic sheet printer is primarily controlled by form functionality on the Assumptions sheet.
183
depicts this section of the Excel sheet. Go to the Assumptions sheet, and enter the text Sheet Selector in cell F3.
2. Create a checkbox for each sheet that you anticipate printing in the area beneath cell F3 on the Assumptions sheet. Name each checkbox the same as the sheet name that you are referring to. Go to the Format Control of the first checkbox and make the cell link to cell N4 on the Hidden sheet. Go to the Hidden sheet and notice the pattern of implementation that will emerge. When the checkbox is checked, a TRUE should show up in cell N4; when the same checkbox is unchecked, a FALSE should show up. In cell M4, enter the name of the sheet that corresponds to the name of the sheet that the checkbox is referring to (and that should also be the title of the checkbox). Repeat this process for every sheet that should possibly be printed, continuing down the rows. For instance, the next checkbox should be linked to cell N5 with the name of the sheet in cell M5. This section is shown in Figure 11.15. Once this is done, name the range of sheet names on the Hidden sheet ctrl_SheetSelector and cell N3 strt_SheetNameTFs. For labeling purposes, enter the text ctrl_SheetSelector in cell M3.
3. We should also count the number of possible sheets to print. Still on the Hidden sheet, enter the following formula in cell L15:
=COUNTA(ctrl_SheetSelector)
Name L15 ctrl_SheetCt.
4. Notice the functionality that we have built in. For each sheet that could possibly be printed we have a checkbox on the Assumptions sheet. When the user checks
FIGURE 11.15 The checkboxes are linked to the Hidden sheet, where a list of TRUE or FALSE values will be stored to control the printing subroutine.
184
one of those boxes, it changes the value of cells on the Hidden sheet to either TRUE or FALSE. We can probably guess that we are going to loop through each of the sheet references on the Hidden sheet to see whether there is a TRUE or FALSE value and print only the sheets that have a TRUE value next to them. Since our code will look at the actual sheet names in the model as it loops through and use the sheet names listed on the Hidden sheet for referencing purposes, it is extremely important that the sheet names listed in ctrl_SheetSelector are identical to the names on the tab of each worksheet.
5. We are now ready to create the code for the printer functionality. Press Alt-F11 to go to the VBE. Insert a new subroutine and call it Export_Routines. Start the subroutine with the following line of code:
Sub Printer()
6. As we have been doing in the past two Model Builders, the next step is to declare variables. Enter the following code:
Dim iSheetCt As Integer, TotalSheets As Integer
Dim SheetNameTemp As String
7. Our next step is to assign values to certain variables. In this case, we need only know the total number of sheets to possibly print. To accomplish this task, enter the following code:
TotalSheets = Range( “ctrl_SheetCt ”)
8. We will initiate a loop for each sheet. Thinking about the process we want to loop through each sheet to possibly print. Start the loop by entering the following code:
For iSheetCt = 1 To TotalSheets
9. The block of code that is within the loop is the most important. Enter the following code:
If Range( “strt_SheetNameTFs ”).Offset(iSheetCt, 0) = True Then
SheetNameTemp = Range( “strt_SheetNames ”).Offset(iSheetCt, 0)
Worksheets(SheetNameTemp).PrintOut
End If
This code uses an IF THEN statement, a VBA technique that we have not mentioned yet; however, this is relatively easy to understand given our understanding of the IF function in Excel. An IF THEN statement works very similarly, by testing a conditional test and returning one value if the condition is TRUE and possibly a separate value if it is FALSE (note that this would require also using an ELSE statement, but this is not necessary for this example).
In our subroutine we want to test whether each worksheet name on the Hidden sheet has a TRUE value next to it. If this is TRUE, then we store the name of the worksheet into the SheetNameTemp variable. We then take that name and use it within a Worksheet object. A Worksheet object is similar to a Range object in that it references an object within the Excel application. Rather than referencing just a cell, it is referencing an entire sheet. Once we have referenced the sheet we then use the PrintOut method, which sends the sheet to the default printer. If there is no TRUE next to a sheet name on the Hidden sheet, then nothing gets printed. Finally, this block of code is finished off with an End If statement, which is a required parameter for an IF THEN statement to signify the end of the statement.
10. We finish off the code with a Next statement to repeat the loop so every worksheet name on the Hidden sheet is tested and printed if the conditional test is met. An End Sub is also included at the end of the subroutine. The last part of the code should read:
Next iSheetCt
End Sub
11. The final code for the Printer subroutine should be:
Sub Printer()
Dim iSheetCt As Integer, TotalSheets As Integer
Dim SheetNameTemp As String
TotalSheets = Range( “ctrl_SheetCt ”)
For iSheetCt = 1 To TotalSheets
If Range( “strt_SheetNameTFs ”).Offset(iSheetCt, 0) = True Then
SheetNameTemp = Range( “strt_SheetNames ”).Offset(iSheetCt, 0)
Worksheets(SheetNameTemp).PrintOut
End If
Next iSheetCt
End Sub
12. Automate the running of this macro by creating a button on the Assumptions sheet underneath the previous subroutine’s button. Name this button Print Selected Sheets.

CONTINUING WITH VBA

Learning a new language is a constantly evolving process that takes years to perfect. Computer-based languages are included in this category. Much as new learners of foreign languages limit their usage to certain words and phrases at first, we have limited our VBA language to a core set of statements, objects, properties, and methods that are best suited for financial modeling. Although perhaps not the most efficient or robust techniques, they are easy to learn and powerful when implemented.
There are specific techniques that I would suggest financial modelers learn next. These include:
• More practice with multiple For Next loops
• Learning Do While and Do Until loops
• More practice with IF THEN ELSE statements
• Learning how to create and work with arrays
• Learning how to create and use functions both in VBA and for use in Excel
The list could go on and on, but the techniques above and those discussed in this chapter are very useful for financial modeling application. Be careful as your skill develops not to overuse VBA. The general idea is to use it when you absolutely need it. Otherwise, you risk overcomplicating models and making them difficult for yourself and others to troubleshoot.

CONCLUSION

We have dedicated quite a bit of time to constructing a powerful discounted cash flow model for corporate valuation. Do not stop here. The model captures only as much as we tell it to. This thought was reinforced when I was on a consulting engagement in Dubai. A senior member of a prominent investment bank was giving a speech to his junior staff. One thing he said has stayed with me on every analysis I undertake: “Be thoughtful about the deal.”
I took this to mean we should think through each aspect of the deal in detail. This is paramount to corporate transactions since every industry in every region can have a unique set of circumstances requiring thought. Thinking through the deal structure and identifying transaction parties, risks, mitigating factors, market forces, economic situations and so on should be done day-in and day-out until the transaction closes. The financial model will provide an excellent medium for organizing these items and quantifying certain aspects, but by no measure is it an absolute solution.
Financial modeling is about creating a target for business decisions. Occasionally, we will hit a perfect bull’s-eye with little effort, but we must focus our energy on nudging the deal process toward the bull’s-eye. This requires the ability to think through the intricacies of a transaction and deal with the needs of parties involved to get a deal done. The combination of a thoughtful process with a powerful corporate valuation model will produce transactions where risk and reward are properly structured.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.12.232