End If without Block If error VBA

Question

I wrote this code to try and assign a value to a variable based on the value of another variable generated using vba's Rnd() function and if statements but for some reason its giving me the "end if without block if error." This is just a portion of the code, I iterate this process 5 times for the 5 different products and do 10000 iterations of the number generators aggregating the results. Initially I tried it this way nesting everything, but when that didnt work I tried doing single if statements and same deal. Any help with this would be awesome.

For i = 0 To 10000

ProdE = Rnd()
ProdF = Rnd()
ProdG = Rnd()
ProdH = Rnd()
ProdI = Rnd()

If ProdE <= 0.1 Then DaysLateE = 2
If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
If 0.9 < ProdE <= 1 Then DaysLateE = 11
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


TotalDaysLateE = DaysLateE + 8
SumDaysLateE = SumDaysLateE + TotalDaysLateE

If TotalDaysLateE > 15 Then CountE = CountE + 1
End If

You don't use End If with this form of the If statement (where the action is specified on the same line). It's a self-contained one-line statement. — James McLeod
– James McLeod, Commented Feb 11, 2016 at 22:12

Community · Accepted Answer · 2017-05-23 12:07:29Z

The syntax for If allows two variants:

"Inline"

If {bool-expression} Then {do something}

"Block"

If {bool-expression} Then
    {do something}
End If

An End If token is illegal when you're using the "inline" syntax.

So this (i.e. removing the End If tokens) makes your code compilable again:

If ProdE <= 0.1 Then DaysLateE = 2
If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
If 0.9 < ProdE <= 1 Then DaysLateE = 11

However, as @Rohan K suggested, a better option would be to use a Select Case construct, because right now, all these conditions are evaluated all the time - with a Select Case block, execution would exit the Select block after finding a matching condition, and as a bonus you gain readabiilty:

Select Case ProdE
    Case Is <= 0.1
        DaysLateE = 2
    Case Is <= 0.2
        DaysLateE = 3
    Case Is <= 0.3
        DaysLateE = 4
    Case Is <= 0.4
        DaysLateE = 5
    Case Is <= 0.5
        DaysLateE = 6
    Case Is <= 0.6
        DaysLateE = 7
    Case Is <= 0.7
        DaysLateE = 8
    Case Is <= 0.8
        DaysLateE = 9
    Case Is <= 0.9
        DaysLateE = 10
    Case Is <= 1
        DaysLateE = 11
    Case Else
        'DaysLateE = ??
End Select

~~So, what happens when ProdE is greater than or equal to 1?~~ (hadn't read where ProdE came from, nevermind) It seems there's a straight linear relationship between the value of ProdE and DaysLateE - you could try to come up with a formula to calculate it instead.

This probably isn't perfect, but comes pretty close:

DaysLateE = Int(ProdE * 10 - 0.000000000001) + 2

And then you don't need If or Select blocks.

Zaider · Accepted Answer · 2016-02-11 22:13:37Z

1

The problem is your final If statement there. It is completely valid syntax to have an If statement all on one line without an End If. So when you put that End If there it is expecting an If statement with lines after it.

These two would be valid without an error

If TotalDaysLate > 15 then CountE = CountE + 1

Or

If TotalDaysLate > 15 Then 
    CountE = CountE + 1
End If

answered Feb 11, 2016 at 22:13

Zaider

2,0231 gold badge24 silver badges33 bronze badges

Comments

Stupid_Intern · Accepted Answer · 2016-02-11 22:41:22Z

1

Try this: I would suggest Use Select Case incases like this

    For i = 0 To 10000

ProdE = Rnd()
ProdF = Rnd()
ProdG = Rnd()
ProdH = Rnd()
ProdI = Rnd()

If ProdE <= 0.1 Then
DaysLateE = 2
End If

If 0.1 < ProdE And ProdE <= 0.2 Then
DaysLateE = 3
End If

If 0.2 < ProdE And ProdE <= 0.3 Then
DaysLateE = 4
End If

If 0.3 < ProdE And ProdE <= 0.4 Then
DaysLateE = 5
End If

If 0.4 < ProdE And ProdE <= 0.5 Then
DaysLateE = 6
End If

If 0.5 < ProdE And ProdE <= 0.6 Then
DaysLateE = 7
End If

If 0.6 < ProdE And ProdE <= 0.7 Then
DaysLateE = 8
End If

If 0.7 < ProdE And ProdE <= 0.8 Then
DaysLateE = 9
End If

If 0.8 < ProdE And ProdE <= 0.9 Then
DaysLateE = 10
End If

If 0.9 < ProdE And ProdE <= 1 Then
DaysLateE = 11
End If


TotalDaysLateE = DaysLateE + 8
SumDaysLateE = SumDaysLateE + TotalDaysLateE

If TotalDaysLateE > 15 Then
CountE = CountE + 1
End If

edited Feb 11, 2016 at 22:41

answered Feb 11, 2016 at 22:16

Stupid_Intern

3,4808 gold badges42 silver badges78 bronze badges

3 Comments

Scott Craner Over a year ago

vba does not allow X<Y<Z. It has to be X<Y and Y<Z Also the first part X<Y is not needed if you do a Select case as you stated or ELSEIF.

Stupid_Intern Over a year ago

Ohh yess, I didn't thought about that I thought just the if end was a problem will correct it Thanks

Mathieu Guindon Over a year ago

I would upvote this answer for suggesting Select Case, but I'd also downvote it for making OP's code worse than it already is with If...End If blocks.

Xavier Navarro · Accepted Answer · 2016-02-11 22:12:19Z

0

When you use single line ifs you don't need to write the End if. Check this: https://msdn.microsoft.com/en-us/library/office/gg251599.aspx

answered Feb 11, 2016 at 22:12

Xavier Navarro

365 bronze badges

Comments

grug.0 · Accepted Answer · 2016-02-11 23:38:26Z

Thinking about your branching and conditional path, and for 10000 iterations, I'd suggest to just Bifurcate your If... Then statement. Better yet, use this in conjunction with two smaller Case... Select for an easily readable combination of all the suggestions. FASTER!

If ProdE <= 0.5 Then
    If ProdE <= 0.1 Then DaysLateE = 2
    If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
    If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
    If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
    If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
Else
    If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
    If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
    If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
    If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
    If 0.9 < ProdE <= 1 Then DaysLateE = 11
End If

Collectives™ on Stack Overflow

End If without Block If error VBA

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related