CODEWITHSUNDEEP
performanceexcelvba
user366121
user366121

Reputation: 3281

excel vba worksheetfunction.sum

I have a function which should calculate the sum in an array of cells. This function is called in another subroutine very often and therefore has to be real fast. First I used a for-loop to get the numbers but it was too slow. Then I wanted to use a worksheetfunction but this is not working for reasons unknown. Here is the code of the function:

Function CalcMD(rownumbers, colnumber)

Dim MMDRow As Integer
Dim SearchRange As String
Dim FirstAddress As Boolean

MMDRow = MMDRow()
FirstAddress = False
SearchRange = ""

'building the search range
For i = 0 To UBound(rownumbers)
    If rownumbers(i) > 0 And rownumbers(i) < MMDRow Then
        If FirstAddress = False Then
            SearchRange = SearchRange & cells(rownumbers(i), colnumber).Address(False, False)
            FirstAddress = True
        Else
            SearchRange = SearchRange & ";" & cells(rownumbers(i), colnumber).Address(False, False)
        End If
    End If
Next

CalcMD = WorksheetFunction.Sum(Range(SearchRange))

End Function

Is there a better/faster way to get this result?

Upvotes: 1

Views: 2138

Answers (2)

Jon49
Jon49

Reputation: 4606

Using JMax's approach:

Function CalcMD(rownumbers, colnumber)

    Dim MMDRow As Integer, iMaxRow As Integer
    Dim SearchRange As String

    MMDRow = MMDRow()

    'Find the last row where you want to sum the data
    iMaxRow = WorksheetFunction.Min(MMDRow - 1, WorksheetFunction.Max(rownumbers))

    CalcMD = WorksheetFunction.SumIF(Cells(iMaxRow, colnumber),">0")

End Function

Is that what you are looking for?

Upvotes: 1

JMax
JMax

Reputation: 26591

Here is a try. The trickiest part is finding the last row you want to sum, I think this is where we should work if this first attempt doesn't solve your case.

Function CalcMD(rownumbers, colnumber)

    Dim MMDRow As Integer, iMaxRow As Integer
    Dim SearchRange As String

    MMDRow = MMDRow()

    'Find the last row where you want to sum the data
    iMaxRow = WorksheetFunction.Min(MMDRow - 1, WorksheetFunction.Max(rownumbers))

    CalcMD = WorksheetFunction.Sum(Cells(iMaxRow, colnumber))

End Function

Please also note that there aren't many error checking here (especially to check the input data)

Upvotes: 2

Related Questions