Call VBA Gurus - Help to Optimise two small UDF's

  • Thread starter Thread starter orekin
  • Start date Start date
O

orekin

I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0ADF449356342553B196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F70160A5983AF60BEDB0DD29262F7248

Thanks In Advance
Orekin
 
Orekin ,
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions.
Click to expand...


Good News!

these CAN simply be done in standard worksheetfunction..
it's no more then a multi column lookup..

enter in c2:
=SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)
copy it down the column..

and you're done :)

of course you could make it nicer by adding
some names...

names define..
CNnrs = offset($h$1,1,0,counta($h:$h)-1,1)
CNamt = offset(cnnrs,0,1)

now in c2 = SUMPRODUCT(--(A2=cnnrs),cnamt)


excel can be simple.. you just gotta learn how:)

<vbg>


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
arghh :)

sumproduct multi column lookup comes up in 5% of all questions here ...

it compares a2 with all cells in h2:h21
--(a2=h2:h21) produces a series of zeros and ones..

sumproduct(--(a2=h2:h21),i2:i21)
multiplies that series of 0/1 with value in i2:i21
and sum it...


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Just butting in...

Try this formula in another cell:
=SUMPRODUCT(--($A2=$H$2:$H$6),$I$2:$I$6)
(just a smaller range)

Now highlight ($A2=$H$2:$H$6) in the formula bar and hit F9.
You'll see something like:
=SUMPRODUCT(--{FALSE;TRUE;FALSE;TRUE;FALSE},$I$2:$I$6)
Now highlight the last minus through the closing right curly bracket
-{FALSE;TRUE;FALSE;TRUE;FALSE}
and hit F9

You'll see something like:
=SUMPRODUCT(-{0;-1;0;-1;0},$I$2:$I$6)
Now the same for: -{0;-1;0;-1;0}
and hit f9
you'll see:
=SUMPRODUCT({0;1;0;1;0},$I$2:$I$6)

Since =sumproduct() likes to deal with numbers, you had to convert those
true/falses to positive 1's and 0's.

The first unary minus converted true/falses to -1/0's and the second one made
the -1's, +1's.

=====
And if I highlight $i$2:$6 and hit f9, I get:
(with my test data)
=SUMPRODUCT({0;1;0;1;0},{3;3;5;2;0})

The I get 0*3 + 1*3 + 0*5 + 1*2 + 0*0

or just 5.

Remember to hit escape or edit|undo so you don't screw up your real formula when
evaluating this way.
 
orekin said:
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0ADF449356342553B196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F70160A5983AF60BEDB0DD29262F7248

Thanks In Advance
Orekin
Click to expand...
 
Back
Top