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

formula or vba code
 

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

formula or vba code
 

See response in worksheet.functions

--
HTH

Bob Phillips

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

"Nader" wrote in message
...
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

formula or vba code
 

that's my formula :

=IF($A9<L$1;IF(OR(NOT(ISNA(MATCH(L$1;LEGACY_CURRE NCY;0)));NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0)))); IF(OR(L$1="EUR";$A9="EUR");IF(NOT(ISNA(MATCH(L$1;L EGACY_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(CONCATEN ATE("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(ISNU MBER(BLP(CONCATENATE("EUR";L$1;"
Curncy");"PX_LAST"));VLOOKUP($A9;EU_CURRENCY;2;FAL SE);
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 !

a écrit dans le message de news:
...
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