How to wrap an array function in VBA

**kayard** · 06-03-2010, 05:48 AM

Hi to everyone,

I need to wrap a complicated worksheet array function is some VBA code to improve sheet readability. How can I do it ?

This is the array function

Please Login or Register  to view this content.

And this is how i'd like to call it

Please Login or Register  to view this content.

being

Please Login or Register  to view this content.

Any Help would be very appreciated

Paolo

PS: Sorry for my english

**DonkeyOte** · 06-03-2010, 06:41 AM

Originally Posted by kayard

I need to wrap a complicated worksheet array function is some VBA code to improve sheet readability. How can I do it ?

Firstly, note UDFs will generally perform slower than their native equivalents.

In terms of improving readability - why not use Named ranges ?

If you are intent on using VBA you can use Evaluate to process the Array if you like:

Please Login or Register  to view this content.

Note: in the above I used SUM in the Evaluate rather than SOMMA on the basis that I suspect Evaluate will use the Americanised function name rather than Local but I could be wrong (have not tested).

**kayard** · 06-03-2010, 09:00 AM

Thanks,

I would also like to try the named ranges approach but how can I do that considering that two ranges ($M$1:N$1 and $M642:N642) are dynamic in the sense that the first cell of the range is blocked while the second is free to change accordingly with the collumn in which the array formula is copied ?

For instance the formula in the example above (which is taken from collumn N) would be the following when copied in collumn P

Please Login or Register  to view this content.

Thanks

**DonkeyOte** · 06-03-2010, 01:42 PM

Sorry, I've not been on line this afternoon.

Select the cell in which the formula in N is to reside (essential) - then create the following names:

Please Login or Register  to view this content.

Now you should find your formula can be changed to:

=SOMMA(((this_period-periods-60+this_period_days)/this_period_days)*revenues)
confirmed with CTRL + SHIFT + ENTER

and copy up / down / right / left etc as required

**kayard** · 06-04-2010, 10:54 AM

Thanks, it worked.

I would put the SOLVED tag if I knew how to do it.

BTW i'll stick with the slower UDF approach cause the formula I'm actually trying to wrap is way more complex than the example I provided therefore, even with ranged names, it would be very long and difficult to read.

thanks again

bye