IF statements in VBA

Question

I am using an if statement which is giving the the error message End if without block if

Ive tried placing the End if just before the sub and just after the if statement.

I also tried placing 2 end if statements at the end of each IF statement.

If IDtype = "UK" Then sqluserfix = " & UserId & "

If IDtype = "NE" Then sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"

End If

Any ideas?

Thanks.

Answer 1

Are there only 2 options? Just surprised no one has suggested Select Case

Select Case IDtype
    Case "UK"
        sqluserfix = " & UserId & "
    Case "NE"
        sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"
End Select

Answer 2

You can only use End If if you use multi-line formatting:

If something Then
    'do something
End If

Answer 3

In this case use ElseIf:

If IDtype = "UK" Then 
  sqluserfix = " & UserId & "
ElseIf IDtype = "NE" Then 
  sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"
End If

---

By the Way this Statements can also look like this:

If IDtype = "UK" Then 
  sqluserfix = " & UserId & "
End If

If IDtype = "NE" Then 
  sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"
End If

---

Formating Code in VBA shows you how to do this in a right way.

Answer 4

In your case you are using what is called a one line statement

If (true) then MsgBox "No End If required!"

This works and doesn't require an End If statement because it's a one-liner.

Because you do have an End If statement that's why the error occurs, because you trying to end an If statement which has not been started ( 2 one-liners ).

In C# you for example a ; semi-colon acts as a line separator. In VB/VBA you use the Return key to separate lines.

---

You can do exactly the same thing two other ways

1)

If (true) then _ 
    MsgBox "No End If required!"

2)

If (true) then
    MsgBox "End If required!!!"
End If

---

However in your case it seems that a more suitable decision would be to use a combination of If and ElseIf like this

If IDtype = "UK" Then
    sqluserfix = " & UserId & "
ElseIf IDtype = "NE" Then
    sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"
End If

Answer 5

VBA If statements can be done inline like so:

If IDtype = "UK" Then sqluserfix = " & UserId & "

Or be done on multiple lines like so:

If IDtype = "NE" Then
    sqluserfix = "(select USER_ID from USER_PARAMS where USER_ID2='" & UserId & "')"    
End If

Your problem is that you are mixing both at the same time, choose one format and your problem will be fixed.