Simplify Multiple If Function

  • Hi all,

    I have the following function,

    Function tsi7(H, TD, LC, HK, HO, E1, NG1, PT1, E2, NG2, PT2, CC1, CC2, TOL, L, DC, INS, P, LR, TSO, WL, PR)

    I dont want to waste anyones time here but I would like to format the code as efficiently as possible because I have about 50 other functions that are very similar to it and my workbook is starting to get large. Which do you guys think is better? does anyone have a better way?


    option 1

    option 2

    option 3

  • Re: Simplify Multiple If Function


    Option 3 is out. Every If statement is considered even if the correct one has been found.

    I'm a big fan of arrays but this could be even better with a collection, or some structure where you don't need to cycle through the elements to get the value of the key.

    Not a crime to make variable names longer - think of the next person (or you) coming along in 2 Months.


  • Re: Simplify Multiple If Function

    Wow, thanks alot for the reply georgS. I will study your code in depth.
    Its a lot different to what I expected, and it looks a lot more efficient!

    The Caller part I have no idea about but I can understand most of the tsi7 function.

    I dont understand how to reference back to the worksheet. To call the function I originally had something like:


    I have also attached a small section of my program for you to have a look at to see what a shocka Ive been having, feel free to make any comments. Please note all userforms, other modules and other worksheets have been removed to make the file small so it looks a bit of a mess but you get the idea of the functionality i am trying to acheive.

    For your info the variables meaning:

    H Hours
    TD Todays Date
    D Days
    M Months
    Y Years
    U Landing Cycles
    E1 Engine No1 HOurs
    PT1 Engine No1 PT Cycles
    NG1 Engine No1 NG Cycles
    HK Hook Cycles
    HO Hoist Cycles
    CC1 Custom Counter 1
    CC2 Custom Counter 2
    TOL Type Of Life
    L Limit
    DC Date Changed
    INS Installed
    P Periodic
    LR Life Remaining
    TSO Time Since Overhaul
    WL Warning Limit
    ALF After Last Flight
    CHK Check
    IPF Initial Pre Flight
    PI Pilot Inspection

  • Re: Simplify Multiple If Function


    Hmmm, a bit different to what I anticipated. I keep telling myself to get the full specs before I start programming, but do I listen?

    I think that you have made the best of a bad lot, and your code, although having a lot of repetition, does the job and is easily audited and relatively readable.

    I'd like to know where the data on Sheet1 and Sheet2 is coming from as this may point to Pivot tables if it is downloaded from somewhere.

    Apart from that, If I make a mistake in one of my applications, my users are saying "oops" at sea level.


  • Re: Simplify Multiple If Function


    I have other sheets in the workbook, one of those is a flight log of the aircrafts hours which is where the data comes from, The rest is inputted manually from the aircraft maintenance logbooks. I have userforms and other modules so that I can input dates and hours which changes all the values throughout the sheet to forecast where the parts will be at in say 500 hours or 2 years. Thats the idea.

    Im still thinking of changing alot of the formulas to option 1 think it will be better.

    Thanks for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!