Best way to populate a table with a macro

[JenniferMurphy]

Suppose I have a table like the one below. It lists the winning percentages for both home games and away games for a number of teams. Columns E & G show the calculated odds of each team winning a series (such as the 2-2-1-1-1 series shown) both when they have home court advantage (E) and when they don't (G). Columns F & H show the same odds but by simulation.

The simulation code is working. I am still working out a few details in the calculation code. The problem I have is that in order to get an accurate simulation result, I need to set the iterations to about 1 million. That takes about 20 seconds to complete. If the table had more rows, it would take longer. If I have Calculations Options set to Automatic, when I test the calculation code, it triggers the simulation code and I have to wait 20 seconds before I can do anything.

One solution is to set the iteration value to something like 1,000. That completes very quickly, so it isn't much of a delay.

But I'd really like to replace the SeriesSim UDF with a SeriesSim macro. That way I can run it whenever I want by clicking on the Button control. Between runs, the data doesn't change. But that raises another problem: How can the macro code know which table columns contain which data? I want the table columns to be readable, but that makes them unwieldy for the VBA code. And if I change one even slightly, I have to change the code.

I came up with three solutions.

Solution #1: Add a row above the table with fixed codes for each column, as I have done here. The macro code will look at that row and know to put the "With Home Court Simulation" results in Column F (HSC = Home Court Simulation). This has something of a problem of keeping the row in sync with the table if columns get added, deleted, or moved.

Solution #2: Same as #1, but put the codes in the header. The "With Home Court (Sim)" header would become "(HCS) With Home Court (Sim)". This eliminates the sync problem, but makes the header ugly.

Solution #3: Define a set of named cells containing the header labels. The range names would be the same as the codes in Solution #1 (ASC = Away Court Simulation). This is implemented in B14:B20. This is probably the the cleanest and more reliable, but takes up more space & more setup.

I prefer #1. It's more compact and easier to manage. Does anyone have any comments on these solutions or suggestions for better solutions? I just want a way for the macro to know which columns contain what data.

Odds Series Home & Away.xlsm
	B	C	D	E	F	G	H
2			1,000,000	Number of iterations
3	HCPC	ACPC	Series	HCC	HCS	ACC	ACS
4	Win% Home	Win% Away	Series	With Home Court (Calc)	With Home Court (Sim)	Without Home Court (Calc)	Without Home Court (Sim)
6	50%	50%	2-2-1-1-1		50%		50%
7	51%	49%	2-2-1-1-1		50%		50%
8	55%	45%	2-2-1-1-1		52%		48%
9	60%	40%	2-2-1-1-1		53%		47%
10	70%	30%	2-2-1-1-1		57%		43%
11	80%	20%	2-2-1-1-1		63%		38%
12	90%	10%	2-2-1-1-1		74%		26%
13
14	Win% Home			Win% Home
15	Win% Away			Win% Away
16	Series			Series
17	With Home Court (Calc)			With Home Court (Calc)
18	With Home Court (Sim)			With Home Court (Sim)
19	Without Home Court (Calc)			Without Home Court (Calc)
20	Without Home Court (Sim)			Without Home Court (Sim)
Sim vs Calc

Cell Formulas
Range	Formula
F6:F12	F6	=SeriesSim([@[Win% Home]],[@[Win% Away]], [@Series],TRUE,NumIters)
H6:H12	H6	=SeriesSim([@[Win% Home]],[@[Win% Away]], [@Series],FALSE,NumIters)
B14	B14	=Table1[[#Headers],[Win% Home]]
B15	B15	=Table1[[#Headers],[Win% Away]]
B16	B16	=Table1[[#Headers],[Series]]
B17	B17	=Table1[[#Headers],[With Home Court (Calc)]]
B18	B18	=Table1[[#Headers],[With Home Court (Sim)]]
B19	B19	=Table1[[#Headers],[Without Home Court (Calc)]]
B20	B20	=Table1[[#Headers],[Without Home Court (Sim)]]
E14	E14	=HCPC
E15	E15	=ACPC
E16	E16	=Series
E17	E17	=HCC
E18	E18	=HCS
E19	E19	=ACC
E20	E20	=ACS

Named Ranges
Name	Refers To	Cells
'Sim vs Calc'!ACC	='Sim vs Calc'!$B$19	E19
'Sim vs Calc'!ACPC	='Sim vs Calc'!$B$15	E15
'Sim vs Calc'!ACS	='Sim vs Calc'!$B$20	E20
'Sim vs Calc'!HCC	='Sim vs Calc'!$B$17	E17
'Sim vs Calc'!HCPC	='Sim vs Calc'!$B$14	E14
'Sim vs Calc'!HCS	='Sim vs Calc'!$B$18	E18
'Sim vs Calc'!NumIters	='Sim vs Calc'!$D$2	F6:F12, H6:H12
'Sim vs Calc'!Series	='Sim vs Calc'!$B$16	E16

Thanks

 

[montecarlo2012]

This macro assumes that you have a table named "Table1" on the active sheet, with a header row that contains the codes for the relevant columns (i.e. "H" for home win percentage, "A" for away win percentage, "(HCS)" for home court advantage with simulation, and "(ACS)" for away court advantage with simulation).

The macro uses the Match function to find the column numbers for the relevant columns based on the codes in the header row. It then loops through each row in the table and each series type, runs the simulation using the SeriesSim UDF, and writes the results back to the table.

You can add a Button control to the sheet and assign this macro to it, so that you can run the simulation by clicking the button. To do this, go to the "Developer" tab, click "Insert" in the Controls group, and select "Button" from the ActiveX Controls section. Then, right-click the button and select "View"

Sub RunSimulation()
    Dim table As ListObject
    Set table = ActiveSheet.ListObjects("Table1") ' Replace "Table1" with the actual name of your table
    
    ' Find the column numbers for the relevant columns using the header row with the codes
    Dim homeWinCol As Long
    homeWinCol = Application.Match("H%", table.ListRows(1).Range, 0) ' Matches any column with a header starting with "H"
    
    Dim awayWinCol As Long
    awayWinCol = Application.Match("A%", table.ListRows(1).Range, 0) ' Matches any column with a header starting with "A"
    
    Dim hcsCol As Long
    hcsCol = Application.Match("(HCS)%", table.ListRows(1).Range, 0) ' Matches the column with header "(HCS) With Home Court (Sim)"
    
    Dim acsCol As Long
    acsCol = Application.Match("(ACS)%", table.ListRows(1).Range, 0) ' Matches the column with header "(ACS) With Away Court (Sim)"
    
    ' Run the simulation for each row in the table
    Dim i As Long
    For i = 1 To table.ListRows.Count
        Dim homeWinPct As Double
        homeWinPct = table.ListRows(i).Range.Cells(homeWinCol).Value
        
        Dim awayWinPct As Double
        awayWinPct = table.ListRows(i).Range.Cells(awayWinCol).Value
        
        ' Run the simulation for each series type (e.g. 2-2-1-1-1)
        Dim j As Long
        For j = 1 To 3 ' Replace 3 with the actual number of series types in your table
            Dim hcsOdds As Double
            hcsOdds = SeriesSim(homeWinPct, awayWinPct, True, j)
            
            Dim acsOdds As Double
            acsOdds = SeriesSim(homeWinPct, awayWinPct, False, j)
            
            ' Write the simulation results to the table
            table.ListRows(i).Range.Cells(hcsCol + j - 1).Value = hcsOdds
            table.ListRows(i).Range.Cells(acsCol + j - 1).Value = acsOdds
        Next j
    Next i
End Sub

 

[JenniferMurphy]

This macro assumes that you have a table named "Table1" on the active sheet, with a header row that contains the codes for the relevant columns (i.e. "H" for home win percentage, "A" for away win percentage, "(HCS)" for home court advantage with simulation, and "(ACS)" for away court advantage with simulation).

The macro uses the Match function to find the column numbers for the relevant columns based on the codes in the header row. It then loops through each row in the table and each series type, runs the simulation using the SeriesSim UDF, and writes the results back to the table.[/CODE]

Cool code. Very helpful.

Does this mean that you prefer having some unique code (text) in the headers rather than in an external row or the named cells? My concern is that I have to remember to be careful about any changes I might make to those headers. I can't have total freedom to name them any way I want.