-1

so I m writing a UDF in Excel vba of the kind:

function ... if ... then ... else for i... to ... equations ... next i * end function

Now I noticed, that the function seems to work just fine, however I was wondering whether I shouldnt be using an "end if" at the position of (*) in the code? If I do, I receive an error msg stating there was no if corresponding IF to that "end if" block, though!? So in general, isn't there a Need for an "end if" in if then else constructions ? Thanks in advance!

/edit: 'if ... then ... else' is a one liner. However the else block does contain multiple lines and in particular a loop...

LaH
  • 17
  • 1
  • 2
  • 8
  • If your If is a one liner(If, Then, Else in one line) you don't need an End If – gizlmo Jun 01 '16 at 09:48
  • Copy the actual code so we can see it better. You can do 1 line if statements. If it's on a single line, it doesn't need end if - but the for cycle is not part of the if. In short, the for cycle will run either way. If it's multiple lines, then it needs a end if. – CyberClaw Jun 01 '16 at 09:49
  • All this is fully described in the VBA Help. Have you read it? – chris neilsen Jun 01 '16 at 10:07

3 Answers3

5

If you put IF THEN ELSE in one line, then the if condition ends on that line and next line will be executed no matter what.

For example,

If true then x =1 else x = 2
y = 1

this case

if true, x will be 1 and y will be 1

if false, x will be 2 and y will be 1

if true then
    x = 1
else
    x = 2
    y = 1
end if

this case,

if true, x will be 1 and y will be empty

if false, x will be 2 and y will be 1

In addition, if you do

If true then x = 1 else 
x = 2

x will always be 2 no matter true or false

MutjayLee
  • 578
  • 3
  • 6
0

If you don't want END IF, but still want IF, ELSE IF, ELSE, just write all in single line.
Use ":" for multiple actions.

Sub test(Optional x As Long)

    If x < 0 Then MsgBox "x<0" Else If x = 0 Then MsgBox "x=0": MsgBox "I told ya x=0" Else MsgBox "x>0": MsgBox "I told ya x>0"

End Sub
Na Nonthasen
  • 172
  • 2
  • 4
-1

Yes, you need the End If statement or you will get an error.

It is only when your If statement is a one-liner that you don't need it, like:

If 1 = 1# Then result = "true"

but:

If 1 = 1# Then
    result = "true"

doesn't work unless it is closed out with End If like this:

If 1 = 1# Then
    result = "true"
End If

Can't see exactly how you structured your function, but if it works without End If, then I guess it is a valid one-liner If statement or you have On Error Resume Next somewhere in your code.

Neil
  • 54,642
  • 8
  • 60
  • 72
Duncan
  • 1
  • 1