vba custom function sum highest consecutive numbers in range excel

[trimline]

I am trying to write a custom function HICON which takes 2 arguments :the
range to be used and the number of consecutive cells to be used . The idea is
that if for example the range was A1:A100 and the number was 5 the formula
would evaluate the sum of A1:A5 then A2:A6 and so on until it got to A94:A100
then it would use whichever of these sums gave the highest total as the
result of the function.
Can anyone suggest the VBA code to use in EXCEL 2003 for this function
please.
Email for replies is (e-mail address removed)
Thank you

 

[Per Jessen]

Hi

Try this:

Dim MyArray()
Dim TargetRange As Range

Function HICON(Target As Range, Evaluate As Integer) As Double
Size = Target.Rows.Count / Evaluate
ReDim MyArray(1 To Size)
Pointer = 1
For c = 1 To Size
Set TargetRange = Range(Target.Cells(Pointer, 1), Target.Cells(Pointer +
Evaluate - 1, 1))
MyArray(c) = Application.WorksheetFunction.Sum(TargetRange)
Debug.Print MyArray(c)
Pointer = Pointer + Evaluate
Next
For c = 1 To UBound(MyArray)
If MyArray(c) > t Then t = MyArray(c)
Next
HICON = t
End Function

Best regards,
Per