Programmatically control/intercept a Data Table refresh

Question

Background

I have an extremely large data table that takes up to 12 hours to run for around 1 million input scenarios on a high-end 64bit machine. The scenarios are based on a number of discrete Excel models, that are then fed into a financial model for detailed calculations

To improve the process, I am looking to test and compare the speeds of:

1. The current manual process
2. Using VBA to refresh the Data Table (with Calculation, ScreenUpdating etc off)
3. Running a VBS to refresh the Data Table in a invisible Excel instance

So, I am looking for the best approach to programmatically manage a Data Table

Update: using code in (2) and (3) did not provide a benefit on testing a simple example with a workbook with a single large data table

Rather surprisingly there seems to be very little - possibly no - direct support in VBA for Data Tables

My current knowledge and literature search

• QueryTable BeforeRefresh and AfterRefresh Events can be added with this class module code. Intellisense doesn't provide this as an option for Data Tables
• Individual PivotTables and QuertyTables can be accessed like so ActiveWorkbookk.Sheets(1).QueryTables(1). Not so Data Tables
• Eliminating all other Data Tables and then running a RefreshAll was suggested in this MrExcel thread as a workaround.

The workaround is certainly do-able as I only have a single Data Table, but I'd prefer a direct approach if one exists.

Yes, I'm sticking to Excel :)

Please do not suggest other tools for this approach, both the input models and the overarching model that uses the data table are

• part of a well established ongoing process that will stay Excel based,
• have been professionally audited,
• have been streamlined and optimised by some experience Excel designers

I was simply curious if there was a way to tweak the process by refreshing a specific data table with code, which my initial test results above have concluded no to.

Answer 1

So, you are looking for the best approach to programmatically manage a Data Table.

Well, Excel 2013 does record a macro for me when I manually create a data table, it goes

Selection.Table ColumnInput:=Range("G4")

The signature is

Range.Table(RowInput as Range, ColumnInput as Range) as Boolean

which is documented in Range.Table Method. The Range.Table() function seems to always return true.

This is the only way to create data tables using VBA. But that's all there is to data tables anyway.

AFAIK there is no class or object for data tables, so there is no dt.refresh() or similar method. And there is no collection of data tables you could query. You have to refresh the sheet or recreate the table with Range.Table().

There is a DataTable Interface, but it is related to charts and has nothing to do with Range.Table().

As you mention, you should turn off the usual suspects, i.e.

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Try to have as little formulas in your workbook. Remove all formulas not related to the cells you base the data table on. Remove any intermediate results. Best have one cell with one, possibly big, formula.

Example: G4 is your ColumnInput, and it contains =2*G3, with G3 containing =G1+G2, then better put =2*(G1+G2) into G4.

You may have 6 cores in your high end machine. Divide your scenarios into 6 chunks and have 6 Excel instances calculate them in parallel.