Optimizing Code

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).Column
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

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" <JimThomlinson@discussions.microsoft.com> wrote in message
news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
> 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).Column
> 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

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" <JimThomlinson@discussions.microsoft.com> wrote in message
> news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
> > 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).Column
> > 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
>
>
>

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" <JimThomlinson@discussions.microsoft.com> wrote in
message
> > news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
> > > 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).Column
> > > 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
> >
> >
> >

I am working on single rows only so your solution does not give me a lot of
advange over Bernies. I will give yours a try though and see if I can find a
difference. When I tried it for 1000 iterations I got

Mine 2.1 Sec
Bernie 1.1 Sec

Thanks...

"toppers@johntopley.fsnet.co.uk" wrote:

> 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" <JimThomlinson@discussions.microsoft.com> wrote in
> message
> > > news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
> > > > 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).Column
> > > > 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
> > >
> > >
> > >
>
>

Some comments:

1. FWIW, in the past (haven't tried with current version of XL), I've had
problems with Evaluate and array formulas: one day they will calculate, the
next day (with no code changes), they don't.

2. I'm confused by your use of ByVal with Worksheet and Range objects. VBA
functions called from a worksheet cell cannot change the arguments. They can
only calculate and return a value to the cell with the formula.

3. I don't know what happens (if anything) when you pass an entire worksheet
ByVal. If VBA is making a copy of the entire sheet, that's a waste of time.
Ditto for the Range object.

4. If you pass a range object, you don't need to pass the worksheet, too. The
range object "knows" what worksheet it's on. You can get at the worksheet with
the parent property of the range (see below).

5. One major speed problem with your current code is that you are pulling data
from the worksheet once cell at a time. You can read the values from the
entire range in approximately the same time it takes to read one cell.

The following should help. I assume you want to include the 1st, 3rd, 5th,
etc, cells in the range and rngTarget is just the first cell.

Public Function AddAlternatingColumns(rngTarget As Range) As Double
Dim C As Long
Dim dblReturnValue As Double
Dim LastCell As Range
Dim V As Variant

With rngTarget.Parent
Set LastCell = .Cells(rngTarget.Row, 256).End(xlToLeft)
V = .Range(rngTarget.Cells(1), LastCell).Value
End With

For C = 1 To UBound(V, 2) Step 2
dblReturnValue = dblReturnValue + V(1, C)
Next C
AddAlternatingColumns = dblReturnValue
End Function

As far as speed is concerned, an array formula entered on the worksheet
evaluated in 0.2 msec, while this VBA function took 0.27 msec. The filled
range was C3:Q3, so 8 cells were summed.


On Wed, 2 Mar 2005 11:53:04 -0800, "Jim Thomlinson"
<JimThomlinson@discussions.microsoft.com> 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" <JimThomlinson@discussions.microsoft.com> wrote in message
>> news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
>> > 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).Column
>> > 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
>>
>>
>>