troubleshooting Question

Loaded array sumlookup - can we go faster?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Visual Basic ClassicMicrosoft ExcelVisual Basic.NET
8 Comments1 Solution537 ViewsLast Modified:
follow on from here a37709867

Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
    Dim Found            As Variant
    Dim varData
    Dim varVal
    Dim varValue
    Dim n                As Long

    varData = RangeToSum.Value

    For n = LBound(varData, 1) To UBound(varData, 1)
        ' load array data
        If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then
        Found = Evaluate(varData(n, 1))
        If Not IsError(Found) Then
            ' check for match on lookup value
            varVal = Application.VLookup(Lookupvalue, Found, 1, False)

            If Not IsError(varVal) Then
                ' return relvant value from second column
                varValue = Application.VLookup(Lookupvalue, Found, 2, False)
                ' make sure it's a number
                If IsNumeric(varValue) Then
                    SumVlookups = SumVlookups + varValue
                    SumVlookups = CVErr(xlErrValue)
                    Exit For
                End If
            End If
        End If
    End If
    Next n

End Function

Is this:

InStr(varData(n, 1), Lookupvalue & ",") > 0

any faster than this:

vlookup(lookupvalue, Evaluate(varData(n, 1)),1)

Ans: ok, I get it, of course it is, it's checking the existence of lookupvalue without having to run the evaluate. ok. clever. because by definition of array constant, the comma is obligatory for a valid lookup, and it has to be much faster than running a lookup on the evaluated result in order to decide whether to run the lookup on col2, because at the least it's running one function instead of two.

I am pondering removal of the error process because it would be much faster to mask everything and then in the event the control totals are out, we can run a more sophisticated UDF to see where/if the errors are. which will be invalid input errors; of course that won't catch wrong valid input errors and I think these invalid input errors are unlikely because there is also a single total being generated from each string at time of input and that would have to be accepted while wrong... and that itself gets check against another control total and as an individual amount in its own right. Conclusion: kill the error trapping to speed up the calcs. mask everything. finally. I can use a separate evaluate to run data check on this, rather than run audit checks inside every calculation for eternity.

this is versus the original code:

Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single

    Dim ArrayStr As Range
    Dim Found As Variant
    For Each ArrayStr In RangeToSum
       Found = Evaluate(ArrayStr.Value)
       If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)
      Next ArrayStr
End Function

so it's now a speed test, and all errors can be masked.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros