formula or vba code

Hello,

I'm having a little problem, recently I wrote a formula for excel which is
too long so I have to decide between trying to find away too shorten that
formula (i'm not sure if it's possible) or written some of the formula code
in vba ?

However, I read in different website that If I wrote some code in vba it
will not be as efficient as a formula.

What should I do ? Shorten the formula or Vba code ?

PS : My data are consentenly updated because they are exchange rates (almost
every second)

Thank you all in advance.

Nader

I don't know whether a formula or vba would be quicker, if you use
excel's built in functions then they should be quicker. Anyway I would
say it would depend on what your formula is doing, also post the
formula that is too long.

James

Nader wrote:

> Hello,
>
> I'm having a little problem, recently I wrote a formula for excel which is
> too long so I have to decide between trying to find away too shorten that
> formula (i'm not sure if it's possible) or written some of the formula code
> in vba ?
>
> However, I read in different website that If I wrote some code in vba it
> will not be as efficient as a formula.
>
> What should I do ? Shorten the formula or Vba code ?
>
> PS : My data are consentenly updated because they are exchange rates (almost
> every second)
>
> Thank you all in advance.
>
> Nader

See response in worksheet.functions

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nader" <nader@infomaniak.ch> wrote in message
news:44e1b9ef$0$7962$5402220f@news.sunrise.ch...
> Hello,
>
> I'm having a little problem, recently I wrote a formula for excel which is
> too long so I have to decide between trying to find away too shorten that
> formula (i'm not sure if it's possible) or written some of the formula
code
> in vba ?
>
> However, I read in different website that If I wrote some code in vba it
> will not be as efficient as a formula.
>
> What should I do ? Shorten the formula or Vba code ?
>
> PS : My data are consentenly updated because they are exchange rates
(almost
> every second)
>
> Thank you all in advance.
>
> Nader
>
>

that's my formula :

=IF($A9<>L$1;IF(OR(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0))));IF(OR(L$1="EUR";$A9="EUR");IF(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));
VLOOKUP(L$1;EU_CURRENCY;2;FALSE); 1/VLOOKUP(L$1;EU_CURRENCY;2;FALSE));
IF(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));VLOOKUP(L$1;EU_CURRENCY;2;FALSE);IF(ISNUMBER(BLP(CONCATENATE("EUR";L$1;"
Curncy");"PX_LAST"));BLP(CONCATENATE("EUR";L$1;" Curncy");"PX_LAST");
BLP(CONCATENATE("USD";L$1;" Curncy");"PX_LAST") ))
/IF(NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0)));IF(ISNUMBER(BLP(CONCATENATE("EUR";L$1;"
Curncy");"PX_LAST"));VLOOKUP($A9;EU_CURRENCY;2;FALSE);
VLOOKUP($A9;EU_CURRENCY;2;FALSE) / BLP("EURUSD Curncy";"PX_LAST") );
IF(ISNUMBER(BLP(CONCATENATE("EUR";$A9;"
Curncy");"PX_LAST"));BLP(CONCATENATE("EUR";$A9;"
Curncy");"PX_LAST");BLP(CONCATENATE("USD";$A9;" Curncy");"PX_LAST")))
);IF(NOT(ISNUMBER(BLP(CONCATENATE($A9;L$1;"
Curncy");"PX_LAST")));BLP(CONCATENATE(L$1;"
Curncy");"PX_LAST")/BLP(CONCATENATE($A9;"
Curncy");"PX_LAST");BLP(CONCATENATE($A9;L$1;" Curncy");"PX_LAST")));1)

PS : BLP is a bloomberg function which return an exchange rate ! Also, this
formula should not be split.

Thanks for you help James !

<james.billy@gmail.com> a écrit dans le message de news:
1155644150.306482.39130@p79g2000cwp.googlegroups.com...
>I don't know whether a formula or vba would be quicker, if you use
> excel's built in functions then they should be quicker. Anyway I would
> say it would depend on what your formula is doing, also post the
> formula that is too long.
>
> James
>
> Nader wrote:
>
>> Hello,
>>
>> I'm having a little problem, recently I wrote a formula for excel which
>> is
>> too long so I have to decide between trying to find away too shorten that
>> formula (i'm not sure if it's possible) or written some of the formula
>> code
>> in vba ?
>>
>> However, I read in different website that If I wrote some code in vba it
>> will not be as efficient as a formula.
>>
>> What should I do ? Shorten the formula or Vba code ?
>>
>> PS : My data are consentenly updated because they are exchange rates
>> (almost
>> every second)
>>
>> Thank you all in advance.
>>
>> Nader
>