SUMIFS3D User-Defined-Function












3














I wrote user-defined function in Excel, but it seems to slow down work significantly. Function performs sumifs for several worksheets in workbook.



Could you advise me, please, on potential optimization of it?



Main idea of the function - to create sumifs, which would sum data on the same criterias from different worksheets.



Arguments of function:



wblist - Range with names of worksheets, where sumifs should be performed



SumRng - Range of cells to be summed on each worksheet



Optional CritRangeN - Range of cells to be assessed with criteria (CritN)



How it works
1) Understands, how many criterias were passed to the function
2) Sums sumifs (with already defined number of arguments) for worksheets, which were mentioned in the wblist Range



Code is presented below



Public Function SUMIFS3D(wblist As Range, SumRng As Range, _
Optional CritRange1 As Variant, Optional Crit1 As Variant, _
Optional CritRange2 As Variant, Optional Crit2 As Variant, _
Optional CritRange3 As Variant, Optional Crit3 As Variant, _
Optional CritRange4 As Variant, Optional Crit4 As Variant, _
Optional CritRange5 As Variant, Optional Crit5 As Variant, _
Optional CritRange6 As Variant, Optional Crit6 As Variant, _
Optional CritRange7 As Variant, Optional Crit7 As Variant)

Dim cell As Range
Dim wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
Dim paramN As Integer

'Application.Volatile
paramN = 0

If Not IsMissing(CritRange1) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange2) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange3) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange4) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange5) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange6) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange7) Then
paramN = paramN + 1
End If


Select Case paramN
Case 1
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1)
Next cell
Case 2
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2)
Next cell
Case 3
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3)
Next cell
Case 4
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4)
Next cell
Case 5
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5)
Next cell
Case 6
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6)
Next cell
Case 7
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6, _
wkb.Sheets(cell.Value).Range(CritRange7.Address), Crit7)
Next cell

End Select

End Function









share|improve this question
























  • Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
    – Mathieu Guindon
    May 11 '17 at 16:44










  • Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
    – PG_Develop
    May 11 '17 at 17:35










  • I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
    – Máté Juhász
    May 12 '17 at 9:38










  • Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
    – PG_Develop
    May 12 '17 at 9:49












  • I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
    – Máté Juhász
    May 12 '17 at 11:26
















3














I wrote user-defined function in Excel, but it seems to slow down work significantly. Function performs sumifs for several worksheets in workbook.



Could you advise me, please, on potential optimization of it?



Main idea of the function - to create sumifs, which would sum data on the same criterias from different worksheets.



Arguments of function:



wblist - Range with names of worksheets, where sumifs should be performed



SumRng - Range of cells to be summed on each worksheet



Optional CritRangeN - Range of cells to be assessed with criteria (CritN)



How it works
1) Understands, how many criterias were passed to the function
2) Sums sumifs (with already defined number of arguments) for worksheets, which were mentioned in the wblist Range



Code is presented below



Public Function SUMIFS3D(wblist As Range, SumRng As Range, _
Optional CritRange1 As Variant, Optional Crit1 As Variant, _
Optional CritRange2 As Variant, Optional Crit2 As Variant, _
Optional CritRange3 As Variant, Optional Crit3 As Variant, _
Optional CritRange4 As Variant, Optional Crit4 As Variant, _
Optional CritRange5 As Variant, Optional Crit5 As Variant, _
Optional CritRange6 As Variant, Optional Crit6 As Variant, _
Optional CritRange7 As Variant, Optional Crit7 As Variant)

Dim cell As Range
Dim wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
Dim paramN As Integer

'Application.Volatile
paramN = 0

If Not IsMissing(CritRange1) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange2) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange3) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange4) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange5) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange6) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange7) Then
paramN = paramN + 1
End If


Select Case paramN
Case 1
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1)
Next cell
Case 2
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2)
Next cell
Case 3
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3)
Next cell
Case 4
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4)
Next cell
Case 5
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5)
Next cell
Case 6
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6)
Next cell
Case 7
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6, _
wkb.Sheets(cell.Value).Range(CritRange7.Address), Crit7)
Next cell

End Select

End Function









share|improve this question
























  • Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
    – Mathieu Guindon
    May 11 '17 at 16:44










  • Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
    – PG_Develop
    May 11 '17 at 17:35










  • I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
    – Máté Juhász
    May 12 '17 at 9:38










  • Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
    – PG_Develop
    May 12 '17 at 9:49












  • I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
    – Máté Juhász
    May 12 '17 at 11:26














3












3








3







I wrote user-defined function in Excel, but it seems to slow down work significantly. Function performs sumifs for several worksheets in workbook.



Could you advise me, please, on potential optimization of it?



Main idea of the function - to create sumifs, which would sum data on the same criterias from different worksheets.



Arguments of function:



wblist - Range with names of worksheets, where sumifs should be performed



SumRng - Range of cells to be summed on each worksheet



Optional CritRangeN - Range of cells to be assessed with criteria (CritN)



How it works
1) Understands, how many criterias were passed to the function
2) Sums sumifs (with already defined number of arguments) for worksheets, which were mentioned in the wblist Range



Code is presented below



Public Function SUMIFS3D(wblist As Range, SumRng As Range, _
Optional CritRange1 As Variant, Optional Crit1 As Variant, _
Optional CritRange2 As Variant, Optional Crit2 As Variant, _
Optional CritRange3 As Variant, Optional Crit3 As Variant, _
Optional CritRange4 As Variant, Optional Crit4 As Variant, _
Optional CritRange5 As Variant, Optional Crit5 As Variant, _
Optional CritRange6 As Variant, Optional Crit6 As Variant, _
Optional CritRange7 As Variant, Optional Crit7 As Variant)

Dim cell As Range
Dim wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
Dim paramN As Integer

'Application.Volatile
paramN = 0

If Not IsMissing(CritRange1) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange2) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange3) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange4) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange5) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange6) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange7) Then
paramN = paramN + 1
End If


Select Case paramN
Case 1
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1)
Next cell
Case 2
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2)
Next cell
Case 3
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3)
Next cell
Case 4
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4)
Next cell
Case 5
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5)
Next cell
Case 6
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6)
Next cell
Case 7
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6, _
wkb.Sheets(cell.Value).Range(CritRange7.Address), Crit7)
Next cell

End Select

End Function









share|improve this question















I wrote user-defined function in Excel, but it seems to slow down work significantly. Function performs sumifs for several worksheets in workbook.



Could you advise me, please, on potential optimization of it?



Main idea of the function - to create sumifs, which would sum data on the same criterias from different worksheets.



Arguments of function:



wblist - Range with names of worksheets, where sumifs should be performed



SumRng - Range of cells to be summed on each worksheet



Optional CritRangeN - Range of cells to be assessed with criteria (CritN)



How it works
1) Understands, how many criterias were passed to the function
2) Sums sumifs (with already defined number of arguments) for worksheets, which were mentioned in the wblist Range



Code is presented below



Public Function SUMIFS3D(wblist As Range, SumRng As Range, _
Optional CritRange1 As Variant, Optional Crit1 As Variant, _
Optional CritRange2 As Variant, Optional Crit2 As Variant, _
Optional CritRange3 As Variant, Optional Crit3 As Variant, _
Optional CritRange4 As Variant, Optional Crit4 As Variant, _
Optional CritRange5 As Variant, Optional Crit5 As Variant, _
Optional CritRange6 As Variant, Optional Crit6 As Variant, _
Optional CritRange7 As Variant, Optional Crit7 As Variant)

Dim cell As Range
Dim wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
Dim paramN As Integer

'Application.Volatile
paramN = 0

If Not IsMissing(CritRange1) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange2) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange3) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange4) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange5) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange6) Then
paramN = paramN + 1
End If
If Not IsMissing(CritRange7) Then
paramN = paramN + 1
End If


Select Case paramN
Case 1
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1)
Next cell
Case 2
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2)
Next cell
Case 3
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3)
Next cell
Case 4
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4)
Next cell
Case 5
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5)
Next cell
Case 6
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6)
Next cell
Case 7
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range(CritRange1.Address), Crit1, _
wkb.Sheets(cell.Value).Range(CritRange2.Address), Crit2, _
wkb.Sheets(cell.Value).Range(CritRange3.Address), Crit3, _
wkb.Sheets(cell.Value).Range(CritRange4.Address), Crit4, _
wkb.Sheets(cell.Value).Range(CritRange5.Address), Crit5, _
wkb.Sheets(cell.Value).Range(CritRange6.Address), Crit6, _
wkb.Sheets(cell.Value).Range(CritRange7.Address), Crit7)
Next cell

End Select

End Function






performance vba excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 11 '17 at 17:34

























asked May 11 '17 at 16:18









PG_Develop

162




162












  • Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
    – Mathieu Guindon
    May 11 '17 at 16:44










  • Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
    – PG_Develop
    May 11 '17 at 17:35










  • I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
    – Máté Juhász
    May 12 '17 at 9:38










  • Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
    – PG_Develop
    May 12 '17 at 9:49












  • I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
    – Máté Juhász
    May 12 '17 at 11:26


















  • Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
    – Mathieu Guindon
    May 11 '17 at 16:44










  • Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
    – PG_Develop
    May 11 '17 at 17:35










  • I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
    – Máté Juhász
    May 12 '17 at 9:38










  • Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
    – PG_Develop
    May 12 '17 at 9:49












  • I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
    – Máté Juhász
    May 12 '17 at 11:26
















Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
– Mathieu Guindon
May 11 '17 at 16:44




Please take the time to present your code. Describe the inputs, how it works, how it's used, why it's needed, etc.
– Mathieu Guindon
May 11 '17 at 16:44












Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
– PG_Develop
May 11 '17 at 17:35




Thanks, Mat's. Added description. Please, tell me, if I need to put some additional data
– PG_Develop
May 11 '17 at 17:35












I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
– Máté Juhász
May 12 '17 at 9:38




I don't see why it would run slowly. Did you tried debugging to see where it lags? How slow exactly it is? How many times do you run it? on how many sheets? How big the ranges are?
– Máté Juhász
May 12 '17 at 9:38












Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
– PG_Develop
May 12 '17 at 9:49






Hi, @Máté. I debugged this code, and there is no lagging.. Now it sums full columns ranges from two spreadsheets. This function is used in 360 cells. If I do two sumifs on those spreadsheats manually and then sum their results (for all 360 cells), model works fast. But if I put this macro, which combines those manual sumifs, it starts to work much slower (1-2 sec instead of 0,5 sec)
– PG_Develop
May 12 '17 at 9:49














I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
– Máté Juhász
May 12 '17 at 11:26




I suppose you've a lot of formulas in your workbook, disabling calculation before running the macro might help. (see some tips here: stackoverflow.com/documentation/excel-vba/1107/…)
– Máté Juhász
May 12 '17 at 11:26










1 Answer
1






active

oldest

votes


















0














The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.



Given the purpose of your function and its similarity to two other functions



SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range1, criteria1, ...)


Also note that SUM can go across sheets-



=SUM(Sheet1:Sheet2!D1:D5)


I think it would make sense to follow the (standard) naming conventions prevailing in this function family.



SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])


I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.



What you might not be aware of is that MS has some specs on UDFs:




Arguments in a function      255
Length of formula contents 8,192 characters
Cross-worksheet dependency 64,000 worksheets that can refer to other sheets



So, you're in the clear no problem, but it's something to consider if you take an array of arguments.





But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?




WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)



Looks very similar to SUMIFS(INDIRECT( in a loop, eh?



Let's see if we can't get your 3D function to take a 3D Range



NOPE Sorry, I tried, I'll probably post a question. I did



All right, that's too bad.



We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.



I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup



Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.



Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this



Dim rangeNumber As Long
For rangeNumber = 1 To 7
If Not IsMissing("CritRange" & i) Then
parameternumber = parameternumber + 1
Else: Exit For
Next


And then with your Case selection, that could be simplified as well



For rangeNumber = 1 To parameternumber
For Each cell In wblist
SUMIFS3D = SUMIFS3D + _
Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
Next
Next


You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.






share|improve this answer























    Your Answer





    StackExchange.ifUsing("editor", function () {
    return StackExchange.using("mathjaxEditing", function () {
    StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
    StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
    });
    });
    }, "mathjax-editing");

    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "196"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f163090%2fsumifs3d-user-defined-function%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.



    Given the purpose of your function and its similarity to two other functions



    SUMIF(range, criteria, [sum_range])
    SUMIFS(sum_range, criteria_range1, criteria1, ...)


    Also note that SUM can go across sheets-



    =SUM(Sheet1:Sheet2!D1:D5)


    I think it would make sense to follow the (standard) naming conventions prevailing in this function family.



    SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])


    I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.



    What you might not be aware of is that MS has some specs on UDFs:




    Arguments in a function      255
    Length of formula contents 8,192 characters
    Cross-worksheet dependency 64,000 worksheets that can refer to other sheets



    So, you're in the clear no problem, but it's something to consider if you take an array of arguments.





    But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?




    WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)



    Looks very similar to SUMIFS(INDIRECT( in a loop, eh?



    Let's see if we can't get your 3D function to take a 3D Range



    NOPE Sorry, I tried, I'll probably post a question. I did



    All right, that's too bad.



    We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.



    I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup



    Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.



    Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this



    Dim rangeNumber As Long
    For rangeNumber = 1 To 7
    If Not IsMissing("CritRange" & i) Then
    parameternumber = parameternumber + 1
    Else: Exit For
    Next


    And then with your Case selection, that could be simplified as well



    For rangeNumber = 1 To parameternumber
    For Each cell In wblist
    SUMIFS3D = SUMIFS3D + _
    Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
    wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
    Next
    Next


    You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.






    share|improve this answer




























      0














      The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.



      Given the purpose of your function and its similarity to two other functions



      SUMIF(range, criteria, [sum_range])
      SUMIFS(sum_range, criteria_range1, criteria1, ...)


      Also note that SUM can go across sheets-



      =SUM(Sheet1:Sheet2!D1:D5)


      I think it would make sense to follow the (standard) naming conventions prevailing in this function family.



      SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])


      I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.



      What you might not be aware of is that MS has some specs on UDFs:




      Arguments in a function      255
      Length of formula contents 8,192 characters
      Cross-worksheet dependency 64,000 worksheets that can refer to other sheets



      So, you're in the clear no problem, but it's something to consider if you take an array of arguments.





      But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?




      WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)



      Looks very similar to SUMIFS(INDIRECT( in a loop, eh?



      Let's see if we can't get your 3D function to take a 3D Range



      NOPE Sorry, I tried, I'll probably post a question. I did



      All right, that's too bad.



      We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.



      I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup



      Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.



      Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this



      Dim rangeNumber As Long
      For rangeNumber = 1 To 7
      If Not IsMissing("CritRange" & i) Then
      parameternumber = parameternumber + 1
      Else: Exit For
      Next


      And then with your Case selection, that could be simplified as well



      For rangeNumber = 1 To parameternumber
      For Each cell In wblist
      SUMIFS3D = SUMIFS3D + _
      Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
      wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
      Next
      Next


      You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.






      share|improve this answer


























        0












        0








        0






        The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.



        Given the purpose of your function and its similarity to two other functions



        SUMIF(range, criteria, [sum_range])
        SUMIFS(sum_range, criteria_range1, criteria1, ...)


        Also note that SUM can go across sheets-



        =SUM(Sheet1:Sheet2!D1:D5)


        I think it would make sense to follow the (standard) naming conventions prevailing in this function family.



        SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])


        I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.



        What you might not be aware of is that MS has some specs on UDFs:




        Arguments in a function      255
        Length of formula contents 8,192 characters
        Cross-worksheet dependency 64,000 worksheets that can refer to other sheets



        So, you're in the clear no problem, but it's something to consider if you take an array of arguments.





        But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?




        WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)



        Looks very similar to SUMIFS(INDIRECT( in a loop, eh?



        Let's see if we can't get your 3D function to take a 3D Range



        NOPE Sorry, I tried, I'll probably post a question. I did



        All right, that's too bad.



        We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.



        I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup



        Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.



        Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this



        Dim rangeNumber As Long
        For rangeNumber = 1 To 7
        If Not IsMissing("CritRange" & i) Then
        parameternumber = parameternumber + 1
        Else: Exit For
        Next


        And then with your Case selection, that could be simplified as well



        For rangeNumber = 1 To parameternumber
        For Each cell In wblist
        SUMIFS3D = SUMIFS3D + _
        Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
        wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
        Next
        Next


        You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.






        share|improve this answer














        The only other standard formula that could do this is =SUMPRODUCT(SUMIF(INDIRECT, and I applaud you for going with a UDF over that monstrosity.



        Given the purpose of your function and its similarity to two other functions



        SUMIF(range, criteria, [sum_range])
        SUMIFS(sum_range, criteria_range1, criteria1, ...)


        Also note that SUM can go across sheets-



        =SUM(Sheet1:Sheet2!D1:D5)


        I think it would make sense to follow the (standard) naming conventions prevailing in this function family.



        SUMIF3D(sheet_names, sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2])


        I might rename sum_range to sum_range3D if you like. The standard way you'd do your argument testing is by passing the array of arguments to a test function.



        What you might not be aware of is that MS has some specs on UDFs:




        Arguments in a function      255
        Length of formula contents 8,192 characters
        Cross-worksheet dependency 64,000 worksheets that can refer to other sheets



        So, you're in the clear no problem, but it's something to consider if you take an array of arguments.





        But getting back to using that first standard function I mentioned - you're essentially requiring a list of sheets that you can use indirectly, aren't you?




        WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address)



        Looks very similar to SUMIFS(INDIRECT( in a loop, eh?



        Let's see if we can't get your 3D function to take a 3D Range



        NOPE Sorry, I tried, I'll probably post a question. I did



        All right, that's too bad.



        We'll continue with your variable names - make them a little more concise so it's obvious what they do. Characters are free. Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.



        I'd use parameterNumber instead of paramN and I wouldn't use cell but that's up to you. I'd also go with targetWorkbook or maybe ActiveWorkbook or ThisWorkbook depending on your setup



        Otherwise, your function is mostly taking arguments to execute the built-in function, which is a difficult task.



        Your IsMissing setup is kind of weird. Would CritRange5 be available if CritRange4 is missing? It could be simplified like this



        Dim rangeNumber As Long
        For rangeNumber = 1 To 7
        If Not IsMissing("CritRange" & i) Then
        parameternumber = parameternumber + 1
        Else: Exit For
        Next


        And then with your Case selection, that could be simplified as well



        For rangeNumber = 1 To parameternumber
        For Each cell In wblist
        SUMIFS3D = SUMIFS3D + _
        Application.WorksheetFunction.SumIfs(wkb.Sheets(cell.Value).Range(SumRng.Address), _
        wkb.Sheets(cell.Value).Range("CritRange" & parameternumber.Address), "Crit" & parameternumber)
        Next
        Next


        You've eliminated 6/7 of the IF and 6/7 of the SELECT CASE code.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Mar 20 at 6:01

























        answered Mar 20 at 1:28









        Raystafarian

        5,8241048




        5,8241048






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Code Review Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            Use MathJax to format equations. MathJax reference.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f163090%2fsumifs3d-user-defined-function%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Сан-Квентин

            Алькесар

            Josef Freinademetz