IF statements in VBA

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.

Solution 1:

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

Solution 2:

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.

Solution 3:

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

If something Then
    'do something
End If