Best way to populate a table with a macro

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,491
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGH
21,000,000Number of iterations
3HCPCACPCSeriesHCCHCSACCACS
4Win% HomeWin% AwaySeriesWith Home Court (Calc)With Home Court (Sim)Without Home Court (Calc)Without Home Court (Sim)
650%50%2-2-1-1-150%50%
751%49%2-2-1-1-150%50%
855%45%2-2-1-1-152%48%
960%40%2-2-1-1-153%47%
1070%30%2-2-1-1-157%43%
1180%20%2-2-1-1-163%38%
1290%10%2-2-1-1-174%26%
13
14Win% HomeWin% Home
15Win% AwayWin% Away
16SeriesSeries
17With Home Court (Calc)With Home Court (Calc)
18With Home Court (Sim)With Home Court (Sim)
19Without Home Court (Calc)Without Home Court (Calc)
20Without Home Court (Sim)Without Home Court (Sim)
Sim vs Calc
Cell Formulas
RangeFormula
F6:F12F6=SeriesSim([@[Win% Home]],[@[Win% Away]], [@Series],TRUE,NumIters)
H6:H12H6=SeriesSim([@[Win% Home]],[@[Win% Away]], [@Series],FALSE,NumIters)
B14B14=Table1[[#Headers],[Win% Home]]
B15B15=Table1[[#Headers],[Win% Away]]
B16B16=Table1[[#Headers],[Series]]
B17B17=Table1[[#Headers],[With Home Court (Calc)]]
B18B18=Table1[[#Headers],[With Home Court (Sim)]]
B19B19=Table1[[#Headers],[Without Home Court (Calc)]]
B20B20=Table1[[#Headers],[Without Home Court (Sim)]]
E14E14=HCPC
E15E15=ACPC
E16E16=Series
E17E17=HCC
E18E18=HCS
E19E19=ACC
E20E20=ACS
Named Ranges
NameRefers ToCells
'Sim vs Calc'!ACC='Sim vs Calc'!$B$19E19
'Sim vs Calc'!ACPC='Sim vs Calc'!$B$15E15
'Sim vs Calc'!ACS='Sim vs Calc'!$B$20E20
'Sim vs Calc'!HCC='Sim vs Calc'!$B$17E17
'Sim vs Calc'!HCPC='Sim vs Calc'!$B$14E14
'Sim vs Calc'!HCS='Sim vs Calc'!$B$18E18
'Sim vs Calc'!NumIters='Sim vs Calc'!$D$2F6:F12, H6:H12
'Sim vs Calc'!Series='Sim vs Calc'!$B$16E16


Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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"
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top