Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to code the formula? | Excel Discussion (Misc queries) | |||
Add formula in the code | Excel Programming | |||
formula or code | Excel Worksheet Functions | |||
Help with my code and formula | Excel Programming | |||
Formula or code | Excel Programming |