VBA function in Excel Optimization

[Charles]

Dear all

I have the following problem. I created a function in VBA which I use
as a formula in my Excel spreadsheet. The issue is that I use it in
many many cells. As a result, the spreadsheet takes something like 3 to
4 minutes to calculate. So every millisecond I save on the calculation
of this function means much more confort for the user of the
spreadsheet.

I am not an expert in VBA/Formula using VBA. Would anyone have an idea
on how to improve the following function?

Public Function CX_TO_C1(A As Range, B As Range, C As Range,
compartment As Double) As Double
Dim nbrow, i, imax As Integer
nbrow = A.Rows.Count
imax = nbrow
If nbrow > 130 Then
imax = 130
End If
Dim temptotal As Double
temptotal = 0
For i = 1 To imax
If C.Rows(i).Value = compartment Then
temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1
- i).Value
End If
Next i
CX_TO_C1 = temptotal / 100
End Function

Is there a better (and faster) way to manipulate cells? Would it be
better to copy every cell in an array at the begining? Any other
suggestion?

I thank you in advance for your help
Best regards
Charles

 

[Bernie Deitrick]

Charles,

If you use it with many cells, then you can often array enter the function, so that it is
essentially called once for each array-entered area in which it is used. Is there a pattern to the
parameters - same A, B, and C, with the compartment incrementing, perhaps?

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Charles,

I would try to calculate the values per row within the worksheet in a helper
column (or helper matrix if necessary). Then the macro does not need to race
through all rows from 1..nbrow and from nbrow..1

Not quicker but safer you can make your macro by defining
Dim nbrow as long
Dim i as long
Dim imax as long
(your definition will define nbrow and i as variants. In this example your
row count will not exceed 32k but generally you should use longs to represent
row numbers)
And I suggest not to compare doubles with = but with ABS(double -
compare_value) < tolerance_value (0.000001 or similar)

In your worksheet you might want to create a helper column like
X1: =IF(ABS(C1-compartment)<0.00001,A1*OFFSET(B$1,rangeA-ROW()),0)
X2: =X1+IF(ABS(C2-compartment)<0.00001,A2*OFFSET(B$1,rangeA-ROW()),0)
now copy X2 down as far as necessary

HTH,
Bernd

 

[Charles]

Hello Bernie

A, B and C are fixed, but the part of A,B and C plugged into the
function increases. The compartment may also vary. Is there a way to
store an array in VBA for the time of the calculation of the
spreadsheet, such that each formula can have access to this same array,
even if the formula are in different cells?

Thanks in advance for your answer
Charles

 

[Bernie Deitrick]

Charles,

You really need to do it all at once, though you could use other ranges of equal size to pass those
parameters. For example, you could put the compartment of interest into range D, and then write the
function:

Public Function CX_TO_C1(A As Range, B As Range, C As Range,
compartment As Range) As Double

and index through compartment.

It would be better if you posted the formulas from a few adjacent cells....

HTH,
Bernie
MS Excel MVP

 

[George Nicholson]

Not sure how noticeable any performance improvements derived from these
comments will be....

1)

Dim nbrow, i, imax As Integer

As written, nbrow and i are initialized as Variants (the default variable
type). Only imax is initialized as an integer. (That's how VB does things)
This forces unnecessary variable coercion at runtime.
Specifying each variable type at the start may improve performance. Then
again, maybe not since I'm not sure if any coercion is taking place within
the loop. I'm not sure if is getting coerced once or repeatedly...):
Dim nbrow as Integer, i as Integer, imax As Integer

2)

temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1 - i).Value

Is there any reason you can't reset [nbrow] to equal [nbrow +1] just before
you start the loop
and then change the above line to:
temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow - i).Value
That's one less calculation within the loop (when the condition is True).

HTH,

 

[Charles]

Thanks for all your answer. I thought that dim a,b as integer would dim
a AND b as integers... Always good to challenge our own basic
knewledge...

In addition to that, I was given the advice on another forum to try
Dim varA As Variant
Dim varB As Variant
varA = A
varB = B
For i = 1 To imax
temptotal = temptotal + varA(i, 1) * varB(nbrow + 1 - i, 1)
Next i
which happened to be extremly efficient

Thanks for all
Charles

 

[Harlan Grove]

Charles wrote...

I have the following problem. I created a function in VBA which I use
as a formula in my Excel spreadsheet. The issue is that I use it in
many many cells. As a result, the spreadsheet takes something like 3 to
4 minutes to calculate. So every millisecond I save on the calculation
of this function means much more confort for the user of the
spreadsheet.

....

UDFs are intrinsically slow due to the Excel to VBA interface. No
matter how much you streamline the VBA function, your performance would
remain bound by this interface lag. IOW, greater efficiency in the
formula probably wouldn't be worth the development time.

[reformatted]

Public Function CX_TO_C1(A As Range, B As Range, C As Range, _
compartment As Double) As Double
Dim nbrow, i, imax As Integer

nbrow = A.Rows.Count
imax = nbrow

If nbrow > 130 Then
imax = 130
End If

Dim temptotal As Double

temptotal = 0

For i = 1 To imax
If C.Rows(i).Value = compartment Then
temptotal = temptotal + A.Rows(i).Value * _
B.Rows(nbrow + 1 - i).Value
End If
Next i

CX_TO_C1 = temptotal / 100
End Function

....

First, imax is equivalent to MIN(ROWS(A),130).

Next, this is a conditional sum on C = compartment.

The terms summed are entries in A from top down times B from bottom up,
subject to a restriction to the first 130 rows.

Finally, you're dividing the conditional sum by 100.

Far & away the fastest way to calculate this is by using only built-in
formulas and *avoiding* udfs. This can be done using the array formula

=SUM(A*(ROW(A)-ROW(INDEX(A,1,1))<MIN(ROWS(A),130))
*MMULT(--(MOD(ROW(B)+TRANSPOSE(ROW(B)),MIN(ROWS(A),130))=1),
B*(ROW(A)-ROW(INDEX(A,1,1))<MIN(ROWS(A),130)))*(C=compartment))/100

Note that this uses no volatile function calls, so such formulas would
only recalculate when values in ranges A, B, C or comparment change.

 

[Charles]

This seems to be a very interesting approach. I am still trying to
understand the formula, but I fear that it might not solve the specific
problem I am confronted to.

What I try to calculate is this:
I have to columns. They do not start at row 1 but row 7 (115 is on row
7)
A B
-----------
115 | 9
101 | 8
203 | 7
210 | 6
etc.


And I am trying to calculate a third column defined as
C
-----------------------
115*9
115*8+101*9
115*7+101*8+203*9
115*6+101*7+203*8+210*9

The compartment is another story but first this simple problem.
The thing is that the number of rows in column A is limited to 130. But
the number of rows in columns B and C is not limited. Basically the
column A is origination: we have 115 things coming up the 1st yr, 101
the second, etc. The column B is the aging table: things originated in
year 1 are worth 9 the year of origination, 8 the year after, 7 after
two years and so on.

I only originate the first 130 years. But the life of my things is much
longer than 130 years. I could actually get rid of the condition for
the first 130 years as I have only zeros after in column A. But it
allowed me to limit the number of loops in VBA.

Do you think that this more complex problem can still be handled
directly using Excel formulas?

Thanks for your help, I really appreciate
Charles

 

[Harlan Grove]

Charles wrote...

This seems to be a very interesting approach. I am still trying to
understand the formula, but I fear that it might not solve the specific
problem I am confronted to.

What I try to calculate is this:
I have to columns. They do not start at row 1 but row 7 (115 is on row
7)

Change the MOD(ROW(B)+TRANSPOSE(ROW(B)),MIN(ROWS(A),130))=1) term in
the formula to

MOD(ROW(B)+TRANSPOSE(ROW(B))-2*(ROW(INDEX(A,1,1))-1),MIN(ROWS(A),130))=1)

A B
-----------
115 | 9
101 | 8
203 | 7
210 | 6
etc.


And I am trying to calculate a third column defined as
C
-----------------------
115*9
115*8+101*9
115*7+101*8+203*9
115*6+101*7+203*8+210*9

....

This is *NOT* what your udf did. The key statement in your udf code was

temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1 - i).Value

which would be equivalent in the example above to

115*9
115*9+101*8
115*9+101*8+203*7
115*9+101*8+203*7+210*6

Which of these two inconsistent calculations do you really want?

 

[Bernie Deitrick]

And I am trying to calculate a third column defined as

...

This is *NOT* what your udf did. The key statement in your udf code was

temptotal = temptotal + A.Rows(i).Value * B.Rows(nbrow + 1 - i).Value

Harlan,

That is what his UDF did - the B.Rows index starts at nbrow and decreases as
i increases to nbrow.

Bernie

 

[Harlan Grove]

Bernie Deitrick wrote...
....

That is what his UDF did - the B.Rows index starts at nbrow and decreases as
i increases to nbrow.

You're right. Sorry, Charles.

That so, if every entry in C is desired, i.e.,

C[1] = A[1] * B[1]
C[2] = A[1] * B[2] + A[2] * B[1]
C[3] = A[1] * B[3] + A[2] * B[2] + A[3] * B[1]
C[4] = A[1] * B[4] + A[2] * B[3] + A[3] * B[2] + A[4] * B[1]
etc.

and the topmost entry in C were in cell C7, then

C7 [array formula]:
=SUM(A*MMULT((MOD(ROW(A)+TRANSPOSE(ROW(A))-2*(ROW(INDEX(A,1,1))-1),
ROWS(C$7:C7)+1)=0)*(ROW(A)-ROW(INDEX(A,1,1))<ROWS(C$7:C7))
*(TRANSPOSE(ROW(A))-ROW(INDEX(A,1,1))<ROWS(C$7:C7)),B))

The key is the first argument to MMULT. For A and B spanning only 4
rows, in row 7 the first argument to MMULT evaluates to

1 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0

so MMULT evaluates to {B[1];0;0;0}. In row 8 the first arg to MMULT
equals

0 1 0 0
1 0 0 0
0 0 0 0
0 0 0 0

so MMULT evaluates to {B[2];B[1];0;0}.

It may be a good idea to shrink the formula by using defined names.
Using A.1 referring to =ROW(A)-ROW(INDEX(A,1,1))+1 and A.2 referring to
=TRANSPOSE(A.1), the C7 (first result) formula becomes

C7 [array formula]:
=SUM(A*MMULT((MOD(A.1+A.2,ROWS(C$7:C7)+1)=0)
*(A.1<=ROWS(C$7:C7))*(A.2<=ROWS(C$7:C7)),B))

 

[Charles]

It seems to be the answer to my problem but I don't manage to make it
work on my spreadsheet. It gives a "#NAME?" at every attempt. It seems
A.1 and A.2 also give a "#NAME?"

Should I define A.1 and A.2 in a specific way or just using
Intert/Name/Define?

On another forum, I was advised to use a UDF based on an array such
that there is only one "exchange" between Excel and VBA per column to
calculate. It's quite efficient. Do you think using this formula
approach would significantly improve the performance of the
calculations?

Best regards
Charles

 

[Harlan Grove]

Charles wrote...

It seems to be the answer to my problem but I don't manage to make it
work on my spreadsheet. It gives a "#NAME?" at every attempt. It seems
A.1 and A.2 also give a "#NAME?"

Of course they do. I did state that they were defined names. Did I need
to state that you'd need to define them?

Should I define A.1 and A.2 in a specific way or just using
Intert/Name/Define?

Define them as I stated in my previous response. They depend on your
range A already being a defined name. If it isn't, then replace
references to A with the actual range address.

On another forum, I was advised to use a UDF based on an array such
that there is only one "exchange" between Excel and VBA per column to
calculate. It's quite efficient. Do you think using this formula
approach would significantly improve the performance of the
calculations?

The Excel/VBA interface is slow, probably slower than several built-in
function calls. When unsure, time the different approaches.

 

[Charles]

I did define them but even that way it was not working (I'm not that
bad in Excel...)

I'll try to figure out what happened

Thanks again for your help
Charles

 

[Harlan Grove]

Charles wrote...

It seems to be the answer to my problem but I don't manage to make it
work on my spreadsheet. It gives a "#NAME?" at every attempt. It seems
A.1 and A.2 also give a "#NAME?"

Should I define A.1 and A.2 in a specific way or just using
Intert/Name/Define?

....

I'll try again.

I define the name A referring to A7:A16, which contains
{1;2;3;4;5;6;7;8;9;10}. I also define the name B referring to B7:B16,
which contains 10^{0;1;2;3;4;5;6;7;8;9}. Then I define A.1 referring to

=ROW(A)-ROW(INDEX(A,1,1))+1

and A.2 referring to

=TRANSPOSE(A.1)

My topmost C entry would just be A[1] * B[1], or A7*B7, which I put in
cell C7. I use the array formula

C7 [array formula]:
=SUM(A*MMULT((MOD(A.1+A.2,ROWS(C$7:C7)+1)=0)
*(A.1<=ROWS(C$7:C7))*(A.2<=ROWS(C$7:C7)),B))

I select C7 and fill it down into C8:C16. These formulas return

C7 1
C8 12
C9 123
C10 1234
C11 12345
C12 123456
C13 1234567
C14 12345678
C15 123456789
C16 1234567900