SUMIFS3D User-Defined-Function
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
asked May 11 '17 at 16:18
PG_Develop
162
|
show 1 more comment
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
asked May 11 '17 at 16:18
PG_Develop
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
|
show 1 more comment
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
asked May 11 '17 at 16:18
PG_Develop
162
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
performance vba excel
asked May 11 '17 at 16:18
PG_Develop
162
asked May 11 '17 at 16:18
PG_Develop
162
edited May 11 '17 at 17:34
asked May 11 '17 at 16:18
PG_Develop
162
asked May 11 '17 at 16:18
PG_Develop
162
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
|
show 1 more comment
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
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
answered Mar 20 at 1:28
Raystafarian
5,8241048
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Mar 20 at 1:28
Raystafarian
5,8241048
add a comment |
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.
answered Mar 20 at 1:28
Raystafarian
5,8241048
add a comment |
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.
answered Mar 20 at 1:28
Raystafarian
5,8241048
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.
answered Mar 20 at 1:28
Raystafarian
5,8241048
edited Mar 20 at 6:01
answered Mar 20 at 1:28
Raystafarian
5,8241048
answered Mar 20 at 1:28
Raystafarian
5,8241048
answered Mar 20 at 1:28
Raystafarian
5,8241048
5,8241048
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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