Cian has attended:
Excel Forecasting and Data Analysis course
PowerPoint Intermediate Advanced course
EXCEL VBA
Hi there,
I have a master tab in my spreadsheet that acts as my 'template'.
In cell A1 I have a data validation list with all potential values in a drop down. When I select a value in cell A1, my formularised 'template' dynamically updates.
This is a very formula heavy process (there are many calculations that are powered either directly or indirectly from A1). The end result is that I have 36 'results' at the bottom of my template.
Since I have roughly 60-70 possible values in A1, I wanted to find a way to extract all results into a single tab (i.e 36 * 70 = 2520).
I have experimented with VBA for this, and while I can get it to work, running the macros is extremely time consuming as they are very slow.
Would you have a suggestion about how I could either speed this up, or an alternative method to use?
I have tried DataTables, but this effectively crashes my workbook due to the large amount of calculations taking place.
Looking forward to your response,
Cian
RE: EXCEL VBA
Hello Cian,
Thank you for your interesting question.
If your VBA code already works, albeit slowly, you could try the following additional code to speed it up:
Disable screen updating, events, and automatic calculation while the macro runs:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Or only calculate when needed (after setting the value in A1):
Application.Calculate
While not having seen your code, here is a macro which might run a bit faster than the one you wrote:
Sub ExtractAllResults()
Dim wsTemplate As Worksheet
Dim wsOutput As Worksheet
Dim inputValues As Variant
Dim resultRange As Range
Dim outputData() As Variant
Dim i As Long, j As Long
Dim numInputs As Long
Dim numResults As Long
Set wsTemplate = ThisWorkbook.Sheets("Master") ' Your template sheet
Set wsOutput = ThisWorkbook.Sheets("Results") ' Output sheet
Set resultRange = wsTemplate.Range("A100:A135") ' Adjust to match your 36 output cells
inputValues = wsTemplate.Range("ValidationList") ' Named range with values for A1
numInputs = UBound(inputValues, 1)
numResults = resultRange.Rows.Count
ReDim outputData(1 To numInputs * numResults, 1 To 2)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For i = 1 To numInputs
wsTemplate.Range("A1").Value = inputValues(i, 1)
DoEvents ' Allows Excel to recalculate if needed
For j = 1 To numResults
outputData((i - 1) * numResults + j, 1) = inputValues(i, 1) ' Input value
outputData((i - 1) * numResults + j, 2) = resultRange.Cells(j, 1).Value ' Result
Next j
Next i
' Output the data in one shot (much faster)
wsOutput.Range("A1").Resize(UBound(outputData), 2).Value = outputData
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "Extraction complete!"
End Sub
Please let us know if this worked for you.
Kind regards
Marius Barnard
STL
