Speed up my VBA functions - looping through a large dataset

[Twi78]

I have just started using VBA and my knowledge is limited to a couple of books (VBA for dummies for example), so I am looking for some help to speedup an excel workbook I have created using a number of functions I have programmed in VBA.

Basically my spreadsheet is a summary of production activity across 10 machines, each machine having 12 'measures'. I have therefore defined 120 functions in VBA. To add to this the summary table has a set of parameters such as period, product type etc, so the data can be dynamically changed when these values are changed.

Each measure is calculated by looping through a table in a worksheet adding to its total if the criteria of that function is met. So far I have 4 months worth of data which is 200,000 rows of data, and it calculates each request on the summary sheet in about 120 seconds.

My question is, is their a better way to handle the dataset/table to speed up the process further? My inital thoughts are:

a) On selecting the parameters of the summary report and clicking the update button, filter the table by that criteria and load it into an array of which each function will loop through. That way it reduces the dataset speeding up the looping process?

b) Holding the dataset in a database and referring to the data here rathert han in excel? (although I would like ot have a drill down facility so users can see the detail of the summarised data)

Any thoughts?

 

[Peter_SSs]

Peter, this is great. I tried the code and yes I can see the array is much faster.

However, as a bit of a newbie to this I'm struggling with converting to your array code above. If I hav the following range code, how do I change this to an array? I then appreciate I need to change the cell references afterwards.

Dim n As Long
Dim r As Range
Set r = Sheets("Production Data").Range("Table1")
      For n = 1 To r.Rows.Count

Here is a basic structure.
It reads the table values into an array and works through the table, row by row.
If you only want to work down a column of the table/array leave out the j loop shell and use a fixed value when accessing the array element. eg a(i,3) would give each element in the 3rd column as i loops.

Dim i As Long, j As Long
Dim r As Range
Dim a '<- This will be your array. Can give meaningful name if you want.
Dim rws As Long, cols As Long

Set r = Sheets("Production Data").Range("Table1")
a = r.Value         'a will now hold all the values from Table1
rws = UBound(a, 1)  'This is how many rows in the array (& the table)
cols = UBound(a, 2) ''This is how many columns in the array (& the table)
For i = 1 To rws
  For j = 1 To cols
    'reference each element of the array by a(i,j) which gives you
    'the value from the ith row and jth column
  Next j
Next i

 

[Twi78]

Thanks for that Peter . I did manage to get it to work last night but slightly different to your code but it was twice as slow . I think the issue is the fact I am running 120 functions populating 120 arrays . How would I create the arrays in the module rather than each function ?

 

[Twi78]

I have now managed to solve this issue. Peter very much appreciated, my run time has gone from 2 minutes to less than 8 seconds!

 

[Peter_SSs]

I have now managed to solve this issue. Peter very much appreciated, my run time has gone from 2 minutes to less than 8 seconds!

Great news!