Jim,

If you have a large number of (contiguous?) rows to calculate, the
quickest method is to load the data into a variant (array) and then
calculate. On a simulation of 1000 rows and all columns (up to column
number 256) I got the following results:

Your method ... approx 2800 miiliseconds (simply looped round 1000
times)
Bernie's method ... approx 1000 milliseconds (simply looped round 1000
times)
Using variant array .. approx 150 milliseconds

The looping/calling function may account for some of the differences.

Even with one row it will be quicker [but impossible to measure!]

Sample code:

Dim x as variant

x = Range("D2:IV1000")

For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2) Step 2
dblReturnValue = dblReturnValue + x(r, c)
Next c
Next r

HTH


Jim Thomlinson wrote:
Thanks I will give that a try and see if there is much improvement.
Traversing is always slow so with any luck this will give me a
boost...

"Bernie Deitrick" wrote:

Jim,

You can use a sumproduct formula to evaluate that directly through
Excel,
which should speed things up: (tested for all but speed ;-))

x = Application.Evaluate("=SumProduct((mod(column(" & _
Range(ActiveCell(1, 3), Cells(ActiveCell.Row, 256)).Address & _
"),2)=" & ActiveCell.Column Mod 2 & ")*" & _
Range(ActiveCell(1, 3), Cells(ActiveCell.Row, 256)).Address &
")")
MsgBox x

HTH,
Bernie
MS Excel MVP

"Jim Thomlinson" wrote in
message
...
I have a function that I need to run very frequently for an end
user
application that is already slow enough. What I need to do is to
add up
the
values in every second column to the right of a given cell. I was
hoping
that
someone could look at this code and tell me if there is any way
to squeek
a
little more speed out of it...

Option Explicit

Sub test()
Dim x As Double

x = AddAlternatingColumns(Sheet2, Sheet2.Range("B2"))
MsgBox x
End Sub

Public Function AddAlternatingColumns(ByVal wks As Worksheet,
ByVal
rngTarget As Range) As Double
Dim dblReturnValue As Double
Dim intLastColumn As Integer

intLastColumn =
wks.Range("A1").SpecialCells(xlCellTypeLastCell).C olumn
Do While rngTarget.Column < intLastColumn
Set rngTarget = rngTarget.Offset(0, 2)
dblReturnValue = dblReturnValue + rngTarget.Value
Loop
AddAlternatingColumns = dblReturnValue
End Function

--
Thanks In Advance...

Jim Thomlinson