Better Data Tables with VBA

Data tables have a number of problems for simulation. Instead of using the data tables you create VBA code. The VBA code avoids the necessity to put the data table in a single sheet and it can be used to make the tables more flexible. The code below is an illustration of how to create the VBA.

.

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


.
.

 

 

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.