CODEWITHSUNDEEP
vbaexcel
kod
kod

Reputation: 13

Else statement confusion

If trigger = "Reconcile" Then
    If InStr(XXlist, checkmi) > 0 Then
          If checkmi = "XX1000" Then
            a = a + 1
            Call XX1000Check(location, a, checkmi)
          End If
          If checkmi = "XX1001" Then
            Call XX1001Check(location, checkmi)
          End If
          Else: Call SenseCheck(location, location2, location7, checkmi)
    End If
End If

I want my code to check if Checkmi is equal to one of the hardcoded codes(XX1000, XX1001) and to then call the appropriate VBA code. If however, there is no specific VBA module for that specific code, I want it to call the generic SenseCheck. Currently it executes SenseCheck if the initial condition If InStr(XXlist, checkmi) > 0 is wrong, which is not what I want. And I am not entirely sure how to fix that.

Upvotes: 1

Views: 72

Answers (3)

DisplayName
DisplayName

Reputation: 13386

expanding on given answers, just to add that you may consider enhancing your code and improving its maintenance by adopting the same sub for both "XX1000" and "XX1001" checkmi values

this by means of "optional" parameters that a Sub/Function can have with the only requirement they must be kept as the last ones in their signature

for instance:

Sub XX100XCheck(checkmi As String, location As Long, Optional a As Variant)

would require the first two parameters to be passed and leaving the third one as optional. and by declaring this latter of Variant type you can check whether it has actually been passed or not:

Sub XX100XCheck(checkmi As String, location As Long, Optional a As Variant)

    If Not IsMissing(a) Then 'if "optional" 'a' parameter has actually been passed
        ' here place the code to process "optional" 'a' parameter
    End If

    '... rest of your code to handle "fixed" parameters

End Sub

so that your question code would be:

Select Case checkmi
    Case "XX1000"
        a = a + 1
        XX100XCheck checkmi, location, a ' call XX100Check sub passing all kind of parameters ("fixed" and "optional")

    Case "XX1001"
        XX100XCheck checkmi, location ' call the same sub as above, but now you're not passing it the "optional" a parameter

    Case Else
        SenseCheck(location, location2, location7, checkmi)
End Select

where you can use (and maintain) one sub only (i.e. XX100XCheck()) instead of two (i.e. XX1000Check() and XX1001Check())

of course this is may not be taken as a mandatory coding pattern, since it may get at odds with another "good practice" one which calls for plain and simple routines as opposite to "all-in-one " ones

so it's a matter of balance (as always) and my proposal went out of a guessing from your code that the two XX...Check() subs would have minimal variations between them

Upvotes: 0

L42
L42

Reputation: 19737

You already terminated the If statement by End If below:

If checkmi = "XX1001" Then
    Call XX1001Check(location, checkmi)
End If '<~~ termination point

And you have an open If statement where you placed your Else statement.

If InStr(XXlist, checkmi) > 0 Then
      If checkmi = "XX1000" Then
        a = a + 1
        Call XX1000Check(location, a, checkmi)
      End If
      If checkmi = "XX1001" Then
        Call XX1001Check(location, checkmi)
      End If '<~~ termination point as pointed above
      Else: Call SenseCheck(location, location2, location7, checkmi)
End If '<~~ termination point

So the Else statement will be associated to the top most non-terminated If statement.
And that is your very first If statement If InStr(XXlist, checkmi) > 0 Then.

Edit1: To correct your code, include Else statement before terminating the entire If statement and also incorporate using ElseIf although if you have more conditions, using Select Case is desirable.

If InStr(XXlist, checkmi) > 0 Then
    If checkmi = "XX1000" Then
        a = a + 1
        Call XX1000Check(location, a, checkmi)
    ElseIf checkmi = "XX1001" Then '<~~ incorporate ElseIf statement
        Call XX1001Check(location, checkmi)
    Else '<~~ transfer the Else statement here
        Call SenseCheck(location, location2, location7, checkmi)
    End If
End If

Upvotes: 1

nutsch
nutsch

Reputation: 5962

This might be a good spot for a SELECT...CASE Syntax

      SELECT CASE checkmi 
         CASE "XX1000" 
               a = a + 1
               Call XX1000Check(location, a, checkmi)
         CASE "XX1001" 
               Call XX1001Check(location, checkmi)
         CASE ELSE
               SenseCheck(location, location2, location7, checkmi)
      END SELECT

Upvotes: 2

Related Questions