Populating array with range object.

[Herakles]

Hi All

I need a bit of help.

I'm developing some code to count the number of rows that meet one or a combination of conditions

The following code in the subTestCode procedure demonstrates two methods of populating an array with the details required to use the COUNTIFS function in the fncGetCount function.

Approach 2 works and approach 1 does not work. I need to use approach 1.

The line generating the 424 Object Required error is shown in the the fncGetCount function.

The subTestCode procedure is only to demonstrate the problem to you.

I only use one approach at a time and I comment the other one out.

Development is only in its early stages so appropriate error checking is yet to come.

Can anybody point me in the right direction?

Thanks

Private Sub subTestCode()
Dim arrParams() As Variant
Dim intCount As Long

    ' Approach 1
    ReDim arrParams(6)
    arrParams(1) = Range("B2:B1001")
    arrParams(2) = "South"
    arrParams(3) = Range("C2:C1001")
    arrParams(4) = "Grapes"
    arrParams(5) = Range("D2:D1001")
    arrParams(6) = ">20"
   
    ' Approach 2
    arrParams = Array(Range("B2:B1001"), "South", Range("C2:C1001"), "Grapes", Range("D2:D1001"), ">20")

    intCount = fncGetCount(arrParams)
       
End Sub

Public Function fncGetCount(ByVal args As Variant)
Dim strString As String
Dim n As Integer

       For n = LBound(args) To UBound(args) Step 2
                       
            ' Error produced on this line with this section of code: args(n).Address(False, False)

            strString = strString & IIf(n > 0, ",", "") & args(n).Address(False, False) & ","
           
            Select Case TypeName(args(n + 1))
               
                Case "Range":
               
                        strString = strString & args(n + 1).Address(False, False)
                   
                Case "Integer", "Long", "Double":
                       
                        strString = strString & args(n + 1)
               
                Case "String":
                   
                        strString = strString & Chr(34) & args(n + 1) & Chr(34)
           
            End Select
   
    Next n
   
    fncGetCount = Application.Evaluate("COUNTIFS(" & Mid(strString, 2) & ")")

End Function

 

[Fluff]

Couple of problems.
1) If you are not running Option Base 1 in that module, then arrParams will be 0 based at which point the function will fail on args(n) when n is 0, as the array will be empty.
2) You are not passing the range object to the array, just the values in the array.

Try

    ReDim arrParams(1 To 6)
    Set arrParams(1) = Range("B2:B1001")

 

[Herakles]

Thanks Fluff

I do set Option Base to 1 but I did not unclude that line in the sample code.
I've been caught out like that before.

I assign the range object to the array and pass the array to the fncGetCount function.

Strange that approach 2 does not need to use Set.

It all works fine now so I can move on.

Thanks

 

[Fluff]

With option 2 you are putting a range into an array, rather than passing a range to an array. It makes all the difference.