How to declare a Public Array in Declarations

[Twi78]

I am trying to declale a public array so I can use the dataset throughout the module for quite a few functions. At the moment I am creating the same array for each function and then iterating through the array to calculate the function, so rather than doing this many times I am trying to speed up the process by declaring a public array and using the 1 data set for all the functions.

So far my code looks like this, but it isnt working??? Any ideas??? The Sub fills the array and then the funcion calculates.

Option Explicit
Public DataArray
Public MaxRows As Long

Sub FillArray()
Dim DataArray
Dim r As Range
Set r = Sheets("MyData").Range("Table1")
DataArray = r.Value
MaxRows = r.Rows.Count
End Sub

 

[Firefly2012]

Hi

You need to remove the DataArray declaration from inside your sub - this one will be used in preference to the Public declaration otherwise (and is thus limited in scope to your sub):

Option Explicit
Public DataArray
Public MaxRows As Long

Sub FillArray()
'''Dim DataArray   ====commented out!
Dim r As Range
Set r = Sheets("MyData").Range("Table1")
DataArray = r.Value
MaxRows = r.Rows.Count
End Sub

This needs to be the case for all your other subs too.

 

[Twi78]

Hi

You need to remove the DataArray declaration from inside your sub - this one will be used in preference to the Public declaration otherwise (and is thus limited in scope to your sub):

Option Explicit
Public DataArray
Public MaxRows As Long

Sub FillArray()
'''Dim DataArray   ====commented out!
Dim r As Range
Set r = Sheets("MyData").Range("Table1")
DataArray = r.Value
MaxRows = r.Rows.Count
End Sub

This needs to be the case for all your other subs too.

I actually realised that as soon as I posted it!!

However its still not working. I think it is populating the array OK but the function doesnt seem to want to know?? How do I change this function (which worked but slowly) to read the array rather than the range? I replaced r.cells with DataArray, removed 'Set r = Sheets("MyData").Range("Table1")' and replaced 'For n = 1 To r.Rows.Count' with 'For n = 1 To MaxRows' and removed .value from the measure addition. Not working though??

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("MyData").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

 

[Firefly2012]

Well, if DataArray has been populated, then you need to change the function to look at this array:

For i = 1 To UBound(DataArray,1)
   If DataArray(i,4) = "Machine A" Then
        MonthNum = Month(DataArray(i,7))
   End If
Next i

etc etc

 

[Twi78]

Newbie errors, manged to solve now. Thank you for your help.