CODEWITHSUNDEEP
arraysexcelvbauser-defined-functions
tolle1038
tolle1038

Reputation: 21

VBA UDF multiple array arguments

I am trying to make a UDF in VBA that takes multiple arrays of equal size as an argument and then loops through them staying at the same index for each array.

I have set the code up as follows.

Public Function TwoArrays(TargetRange(), CriteriaRange())
dim value as range
for each value in TargetRange

next
end function

The issue is I can't get the index of the TargetRange to use in the CriteriaRange and even if I could for whatever reason whenever I put something like

CriteriaRange(2)

I get an error instead of what happens to be within that index.

Is there a way I can get the UDF to treat the array like a normal VBA array where I can do something along the lines of


Public Function TwoArrays(TargetRange(), CriteriaRange())
dim result as range
for i = lowerbound(TargetRange) to ubound(TargetRange)
     If CriteriaRange(i) > 0 then
           result = result + TargetRange(i)
     end if
next i
end function

Thank you!

Upvotes: 0

Views: 264

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Like this:

Public Function TwoArrays(TargetRange As Range, CriteriaRange As Range)
    Dim result, arrT, arrC, r As Long, c As Long
    arrT = TargetRange.Value
    arrC = CriteriaRange.Value
    
    'probably should add some code here to check both ranges are the same size...
    
    For r = 1 To UBound(arrT, 1)
        For c = 1 To UBound(arrT, 2)
            If arrC(r, c) > 0 Then result = result + arrT(r, c)
        Next c
    Next r
    TwoArrays = result
End Function

Upvotes: 1

Related Questions