MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Why isn't this code working?

  Asked By: Bonita    Date: Oct 19    Category: MS Office    Views: 905

The second IIf in the below line doesn't seem to be evaluating.

bMFE = IIf(TT = 2, TT, IIf(TT > 2 And MFE < CumGn, TT, bMFE))

In pseudocode the above reads as:

bMFE = IIf(Case1, True1, IIf(Case2, True2, False2))

All are public variables that are updated every minute as new data are
received from a datafeed. When TT = 1, bMFE = 0. What's happening is
that the bMFE gets stuck at "2", as if the second IIf is not being
evaluated. I've checked the value of TT and it is incrementing as it

Is there a problem with using a compound statement in the second IIf?



3 Answers Found

Answer #1    Answered By: Blas Fischer     Answered On: Oct 19

Don't think so. It looks like 'MFE < CumGn' always equates to False.
Is there a typo? 'MFE < CumGn' should be 'bMFE < CumGn'? What is the
value of CumGn?
If you replace 'MFE < CumGn' with 'True' bMFE rises above 2

Answer #2    Answered By: Tara Ryan     Answered On: Oct 19

Following on from what Pascal said, I'm also not happy that your False2
case - which is the default case - is equivalent to


Is this really what you want?

However, I think Pascal is on the right track. Do you have "Option
Explicit" at the top of your code? If not, you should. Otherwise, a typo
like MFE instead of bMFE will not cause a compile error.

Answer #3    Answered By: Sam Anderson     Answered On: Oct 19

I'm embarassed to say that after the umpteenth time of looking through
this code  and thinking about it, the solution jumped out at me.
Amending the code to read:

bMFE = IIf(TT = 2, TT, IIf(TT > 2 And MFE <= CumGn, TT, bMFE))

fixed it. The complete sequence is shown below.

MFE = IIf(TT = 2, CumGn, IIf(TT > 2 And MFE < CumGn, CumGn, MFE))
bMFE = IIf(TT = 2, TT, IIf(TT > 2 And MFE <= CumGn, TT, bMFE))

MFE and CumGn are double precision calculated values that are compared
and the higher chosen (first line). Then, an identifying number, or
the "bar" number (which actually is the TT)of the winner is chosen in
the second line, and that becomes the new bMFE, an integer. There is
no MFE or CumGn at TT = 1, or bar 1; at bar 2 the CumGn is calculated
and it also becomes the first MFE. From bar 3 on the new CumGn is
calculated and then compared to the MFE from the step before it, etc.
The MFE increases only when the CumGn increases; it never decreases,
and the bar number, the bMFE, will then change. I had overlooked the
instance when the CumGn and MFE are the same.

Didn't find what you were looking for? Find more on Why isn't this code working? Or get search suggestion and latest updates.