macro function is too slow!

[Tommy Brown]

I am using many macro functions
But it seems that they are quite slower than the built-in functions
For example, the macro I wrote as below is very similar with the built-in function SUMPRODUCT(), but the speed is very different
I guess, the reason may be due to the fact that the built-in functions exist as binary codes or as a part of the EXCEL program, while macro functions should be interpreted line-by-line all the time the macro functions run

If so, is there any way to make macro functions as a par of excel
--------------------------------------------------------------------------------------------------
Public Function MySumProduct(targets As Range, weights As Range) As Doubl

n = targets.Rows.Coun
MySumProduct =
For i = 1 To
If Application.WorksheetFunction.IsNumber(targets(i, 1)) And
Application.WorksheetFunction.IsNumber(weights(i, 1)) The
MySumProduct = MySumProduct + targets(i, 1).Value * weights(i, 1).Valu
Els
MySumProduct = MySumProduc
End I

Next

End Function

 

[Juan Pablo Gonzalez]

Create an XLL AddIn, which is written in C.

--
Regards,

Juan Pablo González

I am using many macro functions.
But it seems that they are quite slower than the built-in functions.
For example, the macro I wrote as below is very similar with the built-in

function SUMPRODUCT(), but the speed is very different.

I guess, the reason may be due to the fact that the built-in functions

exist as binary codes or as a part of the EXCEL program, while macro
functions should be interpreted line-by-line all the time the macro
functions run.

 

[Bob Phillips]

Try this

Public Function MySumProduct(target As Range, weights As Range) As Double
MySumProduct = 0
For i = 1 To target.Rows.Count
If IsNumeric(target(i, 1)) And IsNumeric(weights(i, 1)) Then
MySumProduct = MySumProduct + target(i, 1).Value * weights(i,
1).Value
End If
Next i
End Function

It will be slower than built-ins, buit it should be faster than yours.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am using many macro functions.
But it seems that they are quite slower than the built-in functions.
For example, the macro I wrote as below is very similar with the built-in

function SUMPRODUCT(), but the speed is very different.

I guess, the reason may be due to the fact that the built-in functions

exist as binary codes or as a part of the EXCEL program, while macro
functions should be interpreted line-by-line all the time the macro
functions run.

 

[Don Guillett]

Perhaps it would be faster if you used isnumeric instead of
worksheet.function.isnumber?

Public Function MySumProduct(targets As Range, weights As Range) As Double
For i = 1 To targets.rows.count
If isnumeric(targets(i, 1)) And isnumeric(weights(i, 1)) Then _
MySumProduct = MySumProduct + targets(i, 1) * weights(i, 1)
Next i
End Function

--
Don Guillett
SalesAid Software
[email protected]

I am using many macro functions.
But it seems that they are quite slower than the built-in functions.
For example, the macro I wrote as below is very similar with the built-in

function SUMPRODUCT(), but the speed is very different.

I guess, the reason may be due to the fact that the built-in functions

exist as binary codes or as a part of the EXCEL program, while macro
functions should be interpreted line-by-line all the time the macro
functions run.

 

[Charles Williams]

Hi Tommy,

It is always slow to retrieve values from Excel cell by cell.

This should be faster, particularly with large ranges (but still a lot
slower than SUMPRODUCT)

Public Function MySumProduct(targets As Range, weights As Range) As Double

dim vTargets as variant
dim vWeights as variant
dim i as long
dim n as long

n = targets.Rows.Count
MySumProduct = 0
vtargets=targets
vweights=weights

on error resume next
For i = 1 To n
MySumProduct = MySumProduct + vtargets(i, 1)* vweights(i, 1)
Next i

End Function


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

I am using many macro functions.
But it seems that they are quite slower than the built-in functions.
For example, the macro I wrote as below is very similar with the built-in

function SUMPRODUCT(), but the speed is very different.

I guess, the reason may be due to the fact that the built-in functions

exist as binary codes or as a part of the EXCEL program, while macro
functions should be interpreted line-by-line all the time the macro
functions run.