UDF Efficiency Question

[David Evans]

Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double
Dim arr As Variant
Dim total As Double
Dim j As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
    If IsNumeric(arr(1, j)) Then total = total + arr(1, j)
Next
SumIntervalCols = total
End Function

I use the above code to sum a range, most of which is Not Populated. The UDF sums every nth. column. Is there a better way of doing it so that columns that are empty are ignored? There are 225*80 cells that contain this UDF - but for the most part the columns are empty (I know, before you ask, it's a legacy worksheet and it's like the author's grandfather - he doesn't want to kill him off)
Is the If IsNumeric excluding any empty cells in the above code? It's recycled code

Thanks for you insight, as always

D

 

[NARAYANK991]

Hi David ,

I am not sure this UDF has been written correctly. Or your description of what it is supposed to do is not correct.

You say that there are 225 * 80 cells which have this in a formula ; it would be nice if you could post at least one such formula.

I assume 225 is the number of rows and 80 is the number of columns.

Since the UDF is supposed to sum every nth column , do you mean the cells in every nth column or do you mean every nth entire column ? What is the purpose of this UDF ?

Either this UDF sums cells in entire columns within the range or it sums cells in entire rows within the range. Going by the code , you are only adding arr(1,j) , which means you are adding a single cell at a time , over a row of columns. Thus if you sum over 80 columns , one row at a time , why would you need this formula in 80 columns ?

Narayan

 

[David Evans]

Hi Narayan -

The intent is to sum each nth column within a specific row. The formula goes in the Total Col/Row.

The actual Worksheet is 225 columns wide (can handle 75 accounts of 3 cols each) Each row represents a type of investment ...

Although it works, it has some issues - one being you have to start the range to the left of the 1st column you want to sum - it's OK if you have room, which I do ....

My main worry is it's efficiency - this worksheet is used to loop through 1300 accounts and write a summary of each - i just don't need to add empty cells if it's causing a degradation in performance.


I've attached an example for you - hope it makes sense to you. In row 2 of the example, AH2 holds the sum of every third cell (the green ones) ...

Thanks again

D

 

[NARAYANK991]

Hi David ,

The first problem is easily resolved by changing the line :

For j = 1 To UBound(arr, 2) Step interval

where the change is highlighted.

With this change to the code , the formula can be written with the correct range reference.

Narayan

 

[NARAYANK991]

Hi David ,

See if this performs better :

Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double
        Dim total As Double
        total = Application.Evaluate("=SUMPRODUCT((" & WorkRng.Address & ")*(MOD(COLUMN(" & WorkRng.Address & ")-MIN(COLUMN(" & WorkRng.Address & "))+1," & interval & ")=1))")

        SumIntervalCols = total
End Function

Narayan

 

[David Evans]

Hi Narayan -

You are indeed a scholar and a gentleman, in addition to being a Ninja! I have adopted your UDF and will raise and care for it as if it were my own

Sincere thanks

D

 

[David Evans]

Do I need to set Application.Volatile? It seems to not calculate at times - because of the size of the spreadsheet, I have Calculation set to manual ...

If I select the cell with the formula and press F2, the function result appears .... otherwise it is empty ... F9 does not produce a result ....

 

[NARAYANK991]

Hi David ,

I am at a loss to know what the problem can be.

I tried out both the methods in your workbook , after copying the first row of data to another 4000 rows.

Your first method , which looped through all the cells , completed execution is about 1.5 seconds or less.

The method I suggested , which used the SUMPRODUCT function , completed execution in 4 seconds !

I do not know whether you have seen any performance improvement.

For your second question , you can get full information here :

https://fastexcel.wordpress.com/201...10-volatile-functions-and-function-arguments/

From what I can understand , your UDF has 2 parameters :

1. The range which needs to be summed up

2. The interval which says at what column interval the summing up needs to be done

Since the UDF makes use of only these 2 parameters , the use of Application.Volatile should not make any difference , unless the values in the parameterized range are themselves formulae whose values can change due to other cells in the same or any other worksheet changing. I am not sure about even this , since the above link does not specifically state this.

However , I think that the Application.Volatile statement works only if the calculation mode is Automatic ; if you are working in manual calculation mode , then whether you use this or not , all calculation should happen only when you press the F9 key.

Narayan

 

[David Evans]

I continue to have this weird "toggling" of calculations that involve this UDF. The worksheet is set to manual recalculation. If I go to the worksheet with the UDF, the cells are not calculated. If I hit F9 or "Calculate Sheet" from the Formulas Menu the answers appear. If I move away to another sheet and hit F9, the UDF Calculations disappear again, only to return on hitting F9 when I'm on the "UDF" sheet ....

I think I need to investigate any Worsheet-Calculate code in this workbook ...

 

[David Evans]

I fixed it by reverting to the original formula - although i liked the elegance of your formula, narayan, something was causing this little glitch in the calculation ... Thanks again though, always appreciate your help on these things!