macro function is too slow!
 

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

macro function is too slow!
 

Create an XLL AddIn, which is written in C.

--
Regards,

Juan Pablo González

"Tommy Brown" wrote in message
...
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 Double

n = targets.Rows.Count
MySumProduct = 0
For i = 1 To n
If Application.WorksheetFunction.IsNumber(targets(i, 1)) And _
Application.WorksheetFunction.IsNumber(weights(i, 1)) Then
MySumProduct = MySumProduct + targets(i, 1).Value * weights(i,
1).Value
Else
MySumProduct = MySumProduct
End If

Next i


End Function

macro function is too slow!
 

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)

"Tommy Brown" wrote in message
...
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 Double

n = targets.Rows.Count
MySumProduct = 0
For i = 1 To n
If Application.WorksheetFunction.IsNumber(targets(i, 1)) And _
Application.WorksheetFunction.IsNumber(weights(i, 1)) Then
MySumProduct = MySumProduct + targets(i, 1).Value * weights(i,
1).Value
Else
MySumProduct = MySumProduct
End If

Next i


End Function

macro function is too slow!
 

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

"Tommy Brown" wrote in message
...
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 Double

n = targets.Rows.Count
MySumProduct = 0
For i = 1 To n
If Application.WorksheetFunction.IsNumber(targets(i, 1)) And _
Application.WorksheetFunction.IsNumber(weights(i, 1)) Then
MySumProduct = MySumProduct + targets(i, 1).Value * weights(i,
1).Value
Else
MySumProduct = MySumProduct
End If

Next i


End Function

macro function is too slow!
 

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

"Tommy Brown" wrote in message
...
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 Double

n = targets.Rows.Count
MySumProduct = 0
For i = 1 To n
If Application.WorksheetFunction.IsNumber(targets(i, 1)) And _
Application.WorksheetFunction.IsNumber(weights(i, 1)) Then
MySumProduct = MySumProduct + targets(i, 1).Value * weights(i,
1).Value
Else
MySumProduct = MySumProduct
End If

Next i


End Function