Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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).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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Code
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... " 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Code
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MapPoint Optimizing | Excel Discussion (Misc queries) | |||
optimizing code? (hide) | Excel Programming | |||
optimizing a macro | Excel Programming | |||
optimizing a lookup | Excel Programming | |||
Optimizing in VB | Excel Programming |