VBA UDF multiple array arguments

Question

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!

You seem to be conflating Ranges and arrays. Probably worth reading cpearson.com/excel/ArraysAndRanges.aspx — BigBen
– BigBen, Commented Jun 10, 2021 at 13:07
Even removing the "as range" or changing it to something else doesn't impact my problem. — tolle1038
– tolle1038, Commented Jun 10, 2021 at 14:00
RIght, because a Variant array from a Range is 2D and you shouldn't be using a For Each loop ... did you read the referenced link? — BigBen
– BigBen, Commented Jun 10, 2021 at 14:01

Tim Williams · Accepted Answer · 2021-06-10 16:53:23Z

1

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

answered Jun 10, 2021 at 16:53

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA UDF multiple array arguments

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related