Data Tables with VBA

On this webpage I deal with a number of serious problems inherent with data tables using simple VBA programs. I demonstrate that relatively simple and short VBA code can be used as an alternative to data tables that the problems.  The VBA alternative to data tables can be made with a couple of lines of code using a FOR and NEXT loop along with the CELLS tool. You can make the code very flexible so that it can start on different rows and different columns. You can also make the code flexible so you can use alternative range names for inputs and outputs. The VBA to make data tables is a good way work through the fundamental ideas of creating VBA programs.  On this page I present two different methods to program the data tables.  The first method that I present is using an output range and deriving the rows and columns from the range name.  The second method uses definitions of row start and row end along with a FOR and NEXT loop.

Some Problems with the Excel Data Table Tool

When you use VBA code instead of data tables you can fix many of the problems. You can illustrate the scenario analysis with a graph because you don’t have to fill in the blank with a number.  If you have a big database file while your little file with a data table is open (even with the calculation method adjusted).  This will slow things down a lot.  Say you do not want to use code numbers but you want to put your sensitivity analysis in another sheet. You will be stuck.  If you have a goal seek and would like to make your sensitivity analysis work with the goal seek you will also be frustrated.

VBA code can fix problem with data tables. These problems involve writing a macro that should take two minutes:

  • Inputs for the data table must be in same sheet as the data table itself.  This is fixed by using range names and changing the inputs with the CELLS function.
  • Data tables slow you down when save and open even if using automatic except data tables calculation option is selected
  • When you press the F9 to re-calculate, you can mess things up even if you have a large data table
  • Because of the restricted formatting when you make a data table, you cannot easily make a graph with the F11 key.
  • You cannot use the goal seek with a data table where excel would somehow magically know to run the goal seek function every time the sensitivity tables are run.
  • It can be a pain to put the input variables in different sheets when do not use code numbers.
  • Data tables cannot be used with copy and paste macros (they can be used with the UDF method).

Output Range Method of Computing Data Tables

In my revised method, I simply define a range named output that is a square with rows and columns.  You can use this square to find the number of rows and columns as well as the starting row and the starting column. You can use the total rows and the total columns in the FOR and NEXT loop and you can also use the starting row and the starting column to create sensitivity ranges for the data table.  You can download the file with VBA code by clicking on the button below.  The screenshot below the button demonstrates the output of the data table. The key things to add to your VBA include:

rows = range(“output”).rows.count
columns = range(“output”).columns.count
row_start = range(“ouptut”).row
col_start = range(“output”).col
row_sensitivity = row_start – 1
col_sensitivity = col_start – 1

Excel File with VBA Code for Creating a Data Table from the an Output Range with Efficiency Tests

The method works by creating a range name that is illustrated on the screenshot below.  You can use CNTL, F3 to change the dimensions of the range name and the data table will change as hopefully shown on the screenshot.  This changes the dimension of the FOR and NEXT loop from the range(“output”).rows.count and the range(“output”).columns.count.  The VBA code is shown at the bottom of this section.

In testing alternative VBA code including only re-calculating the calculation of calculated cell; using the application.screenupdating =false and using an array variable rather than directly writing to the output variable.  In evaluating the alternative strategies I have printed the length of time it takes to run the loop.  The manner in which the time can be converted to seconds is illustrated in the screenshot below.  To present the time, you can use the Range(“Start_time”) = time  before the loop and the statement Range(“End_time”) = time after the loop.  This is illustrated in the screenshot below.

The screenshot below illustrates how you can make a graph from the data table.  This is not possible with the normal data table because of the formula between the row and the column.  Remember that the row and the column title must be blank for the x-axis to be used. 

The screenshot below illustrates how you can change the colour of the output range with the code range(“output”) = RGB( red number, green number, blue number).  In this case the red, green and blue codes are read in from the excel sheet.  The code for this is shown in the VBA code below.

.

test


Sub table()

' When you change to manual and define the calculation area you can speed up a lot

current_status = Application.Calculation
' Get the current calcluation method

If Range("Manual_option") Then Application.Calculation = xlCalculationManual

' Change to manual

Application.ScreenUpdating = True
' To show the cleared area

MsgBox " Before clear "
' Standard mgsbox

Range("output").ClearContents
' Clear out the range

Application.ScreenUpdating = False
If Range("screen_updating") Then Application.ScreenUpdating = True

range_method = Range("range_method")
' read the true or false from the sheet

Dim result() As Variant
' sets-up a flexibe array variable

test = MsgBox(" Do you want to proceed ", vbYesNo)
' put a question in msgbox

If test <> 6 Then Exit Sub
' exit the sub if not equal to 6=true

Range("output").Interior.Color = RGB(Range("R_"), Range("G"), Range("B"))
' try the RGB with the output

Total_Rows = Range("output").Rows.Count
' Use the .rows to find the total rows

Total_Columns = Range("output").Columns.Count
' Use the .columns to find the total rows

ReDim result(Total_Rows, Total_Columns)
' Used for the range method

start_row = Range("output").Row
' The start row for the range name

start_col = Range("output").Column
' The start column

Range("time_start") = Time
' put the start time in the sheet

For Row = 1 To Total_Rows
' Start of nested loop

Range("col_input") = Cells(Row + start_row - 1, start_col - 1)
' Key -- define the column input

For col = 1 To Total_Columns
' loop around columns

Range("row_input") = Cells(start_row - 1, col + start_col - 1) ' Go around columns and set the row input

Range("result").Calculate
' Only calculates the output cell -- the key to making the model work fast

If range_method = False Then Range("output").Cells(Row, col) = Range("result") ' Simple methood

If range_method = True Then result(Row, col) = Range("result") ' Puts output into an array to make faster

Next col

Next Row

If range_method = True Then Range("output") = result
' If put into array, fill up output with value

Range("time_end") = Time

Application.Calculation = current_status
' Reset the calculuation method to the original

End Sub

Data Table using Prior Method with Row Start, Row End, Column Start, and Column End

I have made a simple file that demonstrates how to make multiple data tables and solve the problems with data tables in excel. This file includes the VBA code that is demonstrated in the video below and creates four different flexible data tables from the valuation analysis.

Excel File with Simple Valuation Model that Illustrates VBA Principles that you can use to make Multiple Data Tables

I am using the case of building data tables with VBA to demonstrate some VBA principles of FOR/NEXT and using VBA with the INDEX function in excel.  In the exercise, I use a very simple valuation example where we want to investigate the effect of WACC, Terminal Growth, Short-term Growth and the length of the explicit period.  The screenshot below illustrates the inputs.  Note that I put names next to the inputs and I used the SHIFT, CNTL, F3 keystrokes to make named ranges.  I also put the base case values with range names next to the values that are used in the model.  The range names are important because they are used in the VBA code.

The second screenshot demonstrates the model with outputs that will be used in the data tables.  Note that I put a name next to the total value and next to the terminal value which will be outputs of a series of data tables.  The model allows you to use alternative terminal value dates and includes a half-year convention.  I pretend that you want a whole lot of data tables with different explicit periods; different short-term growth rates as well as the WACC and the terminal growth which are in almost every sensitivity table.

Now let’s move to creation of the data tables in a new sheet.  The data tables will not have the irritating thing in the middle that prevents you from making graphs.  The format of the graphs is shown in the screenshot below.  Note that I have chosen different variables for the row and column input.

To make the data tables, define the row start, row end, column start and column end.  Then also define the range names that will be used for the row input and column input.  When entering the data, use in INDEX function so that you can choose alternative data tables.  The VBA code will move through the different data tables by changing the code number.  The values that are currently in use are in the left hand side of the table. As the data table 4 is in place, the start row is 35 and the start column is 40 and the output is the terminal value.  You can make as many data tables as you want and this will work with by changing code number that is shown is 4 in the example below.

Now lets run the data table.  This is done with VBA code that loops through the rows and columns of the data table defined from the range names shown in the screenshot above.  The VBA code is illustrated at the bottom of this explanation.

The VBA code is shown below.  There are two programs.  The first program creates one data table with FOR/NEXT and the CELLS statements.  You go up one row from the start row to define the series of row scenarios.  You go one to the left to get the column scenarios associated with the column input.  The code includes an error check and at the end of the data table I re-assigned the data to the base case.

.

Sub table()

'cash
'st_growth
'term_growth
'terminal
'Value
'wacc

For Row = Range("start_row") To Range("end_row")

Range(Range("col_input")) = Cells(Row, Range("start_col") - 1)

For col = Range("start_col") To Range("end_col")
Range(Range("row_input")) = Cells(Range("start_row") - 1, col)

On Error GoTo out_of_loop:
Cells(Row, col) = Range(Range("output"))

back_to_loop:

Next col
Next Row


'
' Reset the values to base case
'

Range("cash") = Range("Base_cash")
Range("st_growth") = Range("Base_st")
Range("term_growth") = Range("Base_terminal")
Range("wacc") = Range("Base_wacc")
Range("term_growth") = Range("base_term_growth")


Exit Sub

out_of_loop:

Resume back_to_loop:

End Sub

.

The second VBA code demonstrates how to create multiple data tables from this code.  You define the code used in the INDEX excel function.  Then you use a FOR/NEXT loop to change the index code number.

.

Sub all_tables()

For table_no = 1 To 4

Range("table_code") = table_no
table

Next table_no

End Sub

.

.

Graphs with Data Tables made by VBA

One of the things I really hate about data tables made with excel is that you cannot quickly make graphs because of the number at the top left of the table.  With the VBA method you have much more flexibility.  With the data tables created from the code shown in the associated file, you can select the area of the table including the row and column sensitivity variables.  Then you can press F11 or press ALT and F9.  This will create a graph that shows the inter-relationships between the variables.  This is demonstrate in the screenshots below.  The second screenshot shows how you can change the row and column variables by clicking on the select data thing.

Video Explanations

.

.

Speeding up Data Tables by Assigning the Output to a Range Name

Going around rows and columns can take some time.  To speed things up you can assign the output to an array variable.  This is demonstrated in the little test VBA code below.

.

Sub table()

Dim temp_out(9000, 1) As Single

num = Range("row_end") - Range("row_start") + 1

range_name = "P" & Range("row_start") & ":P" & Range("row_end")

num = 1

For Row = Range("row_start") To Range("row_end")

Range("gross_load") = Cells(Row, Range("column"))

temp_out(num, 1) = Range("total_cost_for_hour")

num = num + 1

Next Row

Range(range_name) = temp_out


End Sub


.
.