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?

 

[nuked]

The answer is almost certainly 'yes', but to advise more specifically, you will need to paste a sample of the functions you have created.

 

[ukmikeb]

Hi

Initial thoughts :-
1, Autofilter the data according to the parameters or
2, Use the database functions eg DSUM, DCOUNT etc

Maybe post some of your code and a sample of your data using the routines in my signature.

hth

 

[Twi78]

This is one of the functions. They are all practically the same, I just change the output type and machine name for the various machines and measures.

Function TandOutRFT(InpUOM As String, InpYear As Integer, InpMthFr As Integer, InpMthTo As Integer, InpProda As String, InpProdb As String, InpProdc, InpProdd As String, InpProde As String) As Single
    ''Calculates by looping through each row of the data checking criteria in each column
Dim quantity As Long
Dim PcCount As Single
Dim n As Long
Dim NumRows As Long
Dim r As Range
Dim MonthNum As Integer
Dim ProdType As String
Dim YearNum As Integer
quantity = 0
PcCount = 0
      Set r = Sheets("Production Data").Range("Table1")
      For n = 1 To r.Rows.Count
      If r.Cells(n, 4) = "Machine_A" Then
      If r.Cells(n, 13) = "Output" Then
      
      'Reviews the date of the transaction and places it in the correct "shift" period
      If Day(r.Cells(n, 7)) = 1 And Hour(r.Cells(n, 7)) < 6 Then _
      MonthNum = Month(r.Cells(n, 7)) - 1 _
      Else _
      MonthNum = Month(r.Cells(n, 7))
      ProdType = r.Cells(n, 5)
      If Month(r.Cells(n, 7)) = 1 And Day(r.Cells(n, 7)) = 1 And Hour(r.Cells(n, 7)) < 6 Then _
      YearNum = Year(r.Cells(n, 7)) - 1 _
      Else _
      YearNum = Year(r.Cells(n, 7))
            
      '
      'Compares to the period and product type paramters
      If YearNum = InpYear Then
      If MonthNum >= InpMthFr Then
      If MonthNum <= InpMthTo Then
      If (ProdType = InpProda _
      Or ProdType = InpProdb _
      Or ProdType = InpProdc _
      Or ProdType = InpProdd _
      Or ProdType = InpProde) _
      Then
      quantity = quantity + r.Cells(n, 14).Value
      PcCount = PcCount + r.Cells(n, 20).Value
      End If
      End If
      End If
      End If
      End If
      End If
      Next n
      
'Creates 2 measures from the same function, one of qty the other on piece count
If InpUOM = "Pcs" Then
TandOutRFT = PcCount
ElseIf InpUOM = "Qty" Then
TandOutRFT = quantity
End If
End Function

Dataset Sample (200,000 rows of)

Pc ID

Mat Number

Customer Name

Machine Name

Product Type

Stock Code

Date Time

Cycle

From Machine

From Process

To Process

Process Type In

Process Type Out

Qty

Gauge Out

Width

Length

Process Yield

Yield %

Coil Count

2Z9388A

1057935-2

Customer A

Machine_B

A

10100

07/01/13 14:31:00

10

Machine_A

PASSED_PREP

ROUGH_PASS

NA

Output

5

10

20

400

100.0%

1

2Z9201

61235043-1

Customer A

Machine_B

A

10105

07/01/13 14:31:00

12

Machine_A

PASSED_PREP

ROUGH_PASS

NA

Output

4

10

20

400

100.0%

1

<tbody>
</tbody>

 

[nuked]

Have you considered using Pivot Tables?

 

[Twi78]

Its not that simple unfortunatley. I would have to make amendments in the dataset to feed the pivot table. Plus I want to create functions in Excel that can be used company wide.

 

[Twi78]

Mike,

Can you expand on your thoughts?

 

[Peter_SSs]

I haven't studied your function or data in detail but I note your function has many references to cell values.
With that many rows, I think you could speed things up a lot by reading the worksheet values into an array in memory and reference the values there instead of on the sheet.
To demonstrate the concept, start a blank workbook and run these codes.
The first is just to populate 200,000 rows with integers from 1 to 9.

Sub MakeData()
    Dim a
    Dim r As Long
    
    Const rws As Long = 200000
    Const MaxNum As Long = 9
    
    ReDim a(1 To rws, 1 To 1)
    For r = 1 To rws
      a(r, 1) = 1 + Int(Rnd() * MaxNum)
    Next r
    Range("A1").Resize(rws).Value = a
End Sub

The next two codes are to sum the values that are greater than 5.
The first references the cells on the worksheet, the second loads the values into a memory array.
I think you'll find the second one ball-park 10 times faster than the first.

Sub LoopSheet()
  Dim lr As Long, r As Long, Tot As Long
  Dim t As Single
  
  t = Timer
  lr = Range("A" & Rows.Count).End(xlUp).Row
  For r = 1 To lr
    If Cells(r, 1).Value > 5 Then
      Tot = Tot + Cells(r, 1).Value
    End If
  Next r
  MsgBox Tot & ": Code took " & Format(Timer - t, "0.000 secs")
End Sub


Sub LoopArray()
  Dim a
  Dim lr As Long, r As Long, Tot As Long
  Dim t As Single
  
  t = Timer
  lr = Range("A" & Rows.Count).End(xlUp).Row
  a = Range("A1:A" & lr).Value
  For r = 1 To lr
    If a(r, 1) > 5 Then
      Tot = Tot + a(r, 1)
    End If
  Next r
  MsgBox Tot & ": Code took " & Format(Timer - t, "0.000 secs")
End Sub

 

[ukmikeb]

Hi

Here's a good video explaining the database features :- Excel Database Functions e.g. DSUM, DCOUNT, DAVERAGE |

You would be able to specify the parameters for the functions from vba

 

[Twi78]

I haven't studied your function or data in detail but I note your function has many references to cell values.
With that many rows, I think you could speed things up a lot by reading the worksheet values into an array in memory and reference the values there instead of on the sheet.
To demonstrate the concept, start a blank workbook and run these codes.
The first is just to populate 200,000 rows with integers from 1 to 9.

Sub MakeData()
    Dim a
    Dim r As Long
    
    Const rws As Long = 200000
    Const MaxNum As Long = 9
    
    ReDim a(1 To rws, 1 To 1)
    For r = 1 To rws
      a(r, 1) = 1 + Int(Rnd() * MaxNum)
    Next r
    Range("A1").Resize(rws).Value = a
End Sub

The next two codes are to sum the values that are greater than 5.
The first references the cells on the worksheet, the second loads the values into a memory array.
I think you'll find the second one ball-park 10 times faster than the first.

Sub LoopSheet()
  Dim lr As Long, r As Long, Tot As Long
  Dim t As Single
  
  t = Timer
  lr = Range("A" & Rows.Count).End(xlUp).Row
  For r = 1 To lr
    If Cells(r, 1).Value > 5 Then
      Tot = Tot + Cells(r, 1).Value
    End If
  Next r
  MsgBox Tot & ": Code took " & Format(Timer - t, "0.000 secs")
End Sub


Sub LoopArray()
  Dim a
  Dim lr As Long, r As Long, Tot As Long
  Dim t As Single
  
  t = Timer
  lr = Range("A" & Rows.Count).End(xlUp).Row
  a = Range("A1:A" & lr).Value
  For r = 1 To lr
    If a(r, 1) > 5 Then
      Tot = Tot + a(r, 1)
    End If
  Next r
  MsgBox Tot & ": Code took " & Format(Timer - t, "0.000 secs")
End Sub

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