Simplify Multiple If Function

**Reafidy** · Jan 18th 2007

Hi all,

I have the following function,

Code

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?

Thanks.

option 1

Code

Select Case L
    Case "H"
        tsi7 = H - INS
    Case "U"
        tsi7 = LC - INS
    Case "HK"
        tsi7 = HK - INS
    Case "HO"
        tsi7 = HO - INS
    Case "E1"
        tsi7 = E1 - INS
    Case "NG1"
        tsi7 = NG1 - INS
    Case "PT1"
        tsi7 = PT1 - INS
    Case "E2"
        tsi7 = E2 - INS
    Case "NG2"
        tsi7 = NG2 - INS
    Case "PT2"
        tsi7 = PT2 - INS
    Case "D", "M", "Y"
        tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    Case Else
        tsi7 = L
    End Select
    Select Case TOL
    Case "OC", "N/A", "PI"
        tsi7 = TOL
    End Select

Display More

option 2

Code

If L = "H" Then
        tsi7 = H - INS
    ElseIf L = "U" Then
        tsi7 = LC - INS
    ElseIf L = "HK" Then
        tsi7 = HK - INS
    ElseIf L = "HO" Then
        tsi7 = HO - INS
    ElseIf L = "E1" Then
        tsi7 = E1 - INS
    ElseIf L = "NG1" Then
        tsi7 = NG1 - INS
    ElseIf L = "PT1" Then
        tsi7 = PT1 - INS
    ElseIf L = "E2" Then
        tsi7 = E2 - INS
    ElseIf L = "NG2" Then
        tsi7 = NG2 - INS
    ElseIf L = "PT2" Then
        tsi7 = PT2 - INS
    ElseIf L = "D" Or L = "M" Or L = "Y" Then
        tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    ElseIf TOL = "OC" Or TOL = "N/A" Or TOL = "PI" Then
        tsi7 = TOL
    Else
        tsi7 = L
    End If

Display More

option 3

Code

If L = "H" Then tsi7 = H - INS
    If L = "U" Then tsi7 = LC - INS
    If L = "HK" Then tsi7 = HK - INS
    If L = "HO" Then tsi7 = HO - INS
    If L = "E1" Then tsi7 = E1 - INS
    If L = "NG1" Then tsi7 = NG1 - INS
    If L = "PT1" Then tsi7 = PT1 - INS
    If L = "E2" Then tsi7 = E2 - INS
    If L = "NG2" Then tsi7 = NG2 - INS
    If L = "PT2" Then tsi7 = PT2 - INS
    If L = "D" Then tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    If L = "M" Then tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    If L = "Y" Then tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    If L = "" Or L = "ALF" Or L = "CHK" Or L = "IPF" Or L = "PF" Then tsi7 = L
    If TOL = "OC" Or TOL = "N/A" Or TOL = "PI" Then tsi7 = TOL

Display More

GeorgS · Jan 18th 2007

Re: Simplify Multiple If Function

Reafidy,

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.

Code

Private Function Caller()
    Dim va(1, 4) As String
    Dim sL As String
    Dim iINS As Integer
    Dim sTOL As String
    
        'Load up your array by looping through the worksheet somehow
        va(0, 0) = "H"
        va(1, 0) = 5
        
        va(0, 1) = "TD"
        va(1, 1) = 10
        
        va(0, 2) = "LC"
        va(1, 2) = 15
        
        va(0, 3) = "HK"
        va(1, 3) = 20
        
        va(0, 4) = "HO"
        va(1, 4) = 25


        'These variables are different from the others, so they don't belong in the array
        sL = "HK"
        sTOL = "OC"
        iINS = 20
        'Now to call your function:
        Call tsi7(sL, sTOL, iINS, va)


End Function


Function tsi7(ByRef sL As String, ByRef sTOL As String, ByRef iINS As Integer, ByRef va() As String) As String
'Please excuse the Hungarian notation
Dim TD As Integer
Dim DC As Integer


    Select Case sTOL
    Case "OC", "N/A", "PI"
        tsi7 = sTOL ' Does this make sense?
        Exit Function ' We've got the answer do we don't need to bother
        'going any further?
    End Select
    
    Select Case sL
    Case "H", "U", "O", "HK" 'etc
        For x = 0 To UBound(va, 2)
            If va(0, x) = sL Then
                tsi7 = va(1, x) - iINS
                Exit For
            End If
        Next
    Case "D", "M", "Y"
        For x = 0 To UBound(va, 1)
            If va(0, x) = "TD" Then
                TD = va(1, x)
            End If
            If va(0, x) = "DC" Then
                DC = va(1, x)
            End If
        Next
        tsi7 = (Year(TD) - Year(DC)) * 12 + Month(TD) - Month(DC)
    End Select
    
End Function

Display More

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

G.

**Reafidy** · Jan 18th 2007

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:

=tsi7(E4,F4,G4,H4,I4,,,N4,O4)

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

GeorgS · Jan 18th 2007

Re: Simplify Multiple If Function

Reafidy,

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.

G.

**Reafidy** · Jan 18th 2007

Re: Simplify Multiple If Function

Ok,

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.

Simplify Multiple If Function

Participate now!

Share