I have a VBA macro in excel that has run fine for a while. I’ve been making changes with these loops already working and suddenly the code is not compiling:
Compile error: Wend without While
I have tried changing to Do While…Loop loops, but I got the equivalent
Compile error: Loop without Do
Here is the structure of my code:
'This loop is fine
While '(code for evaluation)
'code to run in loop
Wend
While '(more eval code)
'code
While '(eval code)
'code
Wend '<--This is where the compile error occurs
'code
While '(eval code)
'code
Wend
'code
Wend
Does anyone have any idea what could be the issue? (Bonus: Does the indentation of code actually matter?)
asked Mar 12, 2015 at 15:51
5
This message Wend without While usually indicates an un-terminated IF
Somewhere there may be an IF without the matching End If
(This kind of error message is a bit annoying since it hides the true error)
answered Mar 12, 2015 at 16:02
Gary’s StudentGary’s Student
94.9k9 gold badges58 silver badges97 bronze badges
The real problem is that you have a method that does too many things.
You can locate the mismatched code block, and fix it — but you’d be dismissing a readability issue with an «hey it works, leave it alone» excuse.
While
'code to run in loop
Wend
' **** move to another method
While
'code
' **** move to another method
While
'code
Wend
' **** move to another method
'code
While
'code
Wend
'code
Wend
Extracting methods in VBA code can be a bit of a pain, because parameters and return values must be determined, and then the extraction point needs to be turned into a method/function call. Fortunately, there’s a tool for that (disclaimer: I wrote it).
answered Mar 12, 2015 at 16:09
Mathieu GuindonMathieu Guindon
69.2k8 gold badges109 silver badges230 bronze badges
2
I have a VBA macro in excel that has run fine for a while. I’ve been making changes with these loops already working and suddenly the code is not compiling:
Compile error: Wend without While
I have tried changing to Do While…Loop loops, but I got the equivalent
Compile error: Loop without Do
Here is the structure of my code:
'This loop is fine
While '(code for evaluation)
'code to run in loop
Wend
While '(more eval code)
'code
While '(eval code)
'code
Wend '<--This is where the compile error occurs
'code
While '(eval code)
'code
Wend
'code
Wend
Does anyone have any idea what could be the issue? (Bonus: Does the indentation of code actually matter?)
asked Mar 12, 2015 at 15:51
5
This message Wend without While usually indicates an un-terminated IF
Somewhere there may be an IF without the matching End If
(This kind of error message is a bit annoying since it hides the true error)
answered Mar 12, 2015 at 16:02
Gary’s StudentGary’s Student
94.9k9 gold badges58 silver badges97 bronze badges
The real problem is that you have a method that does too many things.
You can locate the mismatched code block, and fix it — but you’d be dismissing a readability issue with an «hey it works, leave it alone» excuse.
While
'code to run in loop
Wend
' **** move to another method
While
'code
' **** move to another method
While
'code
Wend
' **** move to another method
'code
While
'code
Wend
'code
Wend
Extracting methods in VBA code can be a bit of a pain, because parameters and return values must be determined, and then the extraction point needs to be turned into a method/function call. Fortunately, there’s a tool for that (disclaimer: I wrote it).
answered Mar 12, 2015 at 16:09
Mathieu GuindonMathieu Guindon
69.2k8 gold badges109 silver badges230 bronze badges
2
Sub Button1_Click()
'To ask user for highest exponent and dimension coefficient variables
highestexponent = InputBox("Input largest exponent in f(x).")
Dim coefficient(1000) As Double
'To ask user for coefficient values
For counter = 0 To highestexponent
coefficient(counter) = InputBox("Input coefficients on the x^" & counter)
Next counter
'To define the function f(x)
functionstring = "f(x)="
For counter = highestexponent To 0 Step -1
If counter > 0 Then
functionstring = functionstring & coefficient(counter) & "xˆ " & counter & " + "
Else
functionstring = functionstring & coefficient(counter) & "xˆ " & counter
End If
Next counter
Cells(2, 1) = functionstring
Dim xmin As Integer
Dim xmax As Integer
xmin = InputBox("Input a lower bound x value to be evaluated in f(x) function.")
xmax = InputBox("Input a higher bound x value to be evaluated in f(x) function.")
'Cells(5,2) = xmin
'Cells(4,2)=xmax
Dim tolerance As Single
tolerance = InputBox("Input a tolernace value for thr root.")
'Cells(6,2)=root
'Cells(7,2)=tolernace
Dim functionvaluemin As Double
Dim functionvaluemax As Double
functionvaluemin = 0
functionvaluemax = 0
signdifference = 0
While signdifference = 0
For counter = 0 To highestexponent
functionvaluemax = functionvaluemax + coefficient(counter) * xmax ^ (counter)
Next counter
For counter2 = 0 To highestexponent
functionvaluemin = funcionvaluemin + coefficient(counter2) * xmin ^ (counter2)
Next counter2
'Cells(8,2)= functionvaluemax
'Cells(9,2)= functionvaluemin
If functionvaluemin > 0 And functionvaluemax > O Then
MsgBox ("There is no sign difference between f(xmin) and f(xmax). Input new values.")
xmin = InputBox("Input a lower bound x value to be evaluated in f(x) function.")
xmax = InputBox("Input a higher bound x value to be evaluated in f(x) function.")
ElseIf functionvaluemin < 0 And functionvaluemax < 0 Then
MsgBox ("There is no sign difference between f(xmin) and f(xmax). Input new values.")
xmin = InputBox("Input a lower bound x value to be evaluated in f(x) function.")
xmax = InputBox("Input a higher bound x value to be evaluated in f(x) function.")
Else
signdifference = 1
End If
Wend
Dim errormax As Double, trueroot As Double, xbisect As Double
Dim functionvaluexnewbisect As Double
xbisect = (xmin + xmax) / 2
'Cells(6,2)=xbisect
'Loop to solve for true root
signdifference = 0
While signdifference = 0
errormax = (xmax - xmin) / 2
'Cells(10,2)=errormax
xnewbisect = (xmin + xmax) / 2
'Cells(12,2)=xnewbisect
'To evaluate f(xmin), f(xmax), and f(newbisect)
functionvaluexnewbisect = 0
functionvaluexmin = 0
functionvaluemax = 0
For counter4 = 0 To highestexponent
functionvaluexnewbisect = functionvaluexnebisect + coefficient(counter4) * xnewbisect ^ (counter4)
Next counter4
For counter5 = 0 To highestexponent
functionvaluexmax = functionvaluexmax + coefficient(counter5) * xmax ^ (counter5)
Next counter5
For counter6 = 0 To highestexponent
functionvaluexmin = functionvaluexmin + coefficient(counter6) * xmin ^ (counter6)
Next counter6
'Cells(13,2)= functionvaluexnewbisect
'Cells(14, 2) = functionvaluexmin
'Cells(15,2)=functionvaluemax
'To replace xmin/xmax with xnewbisect
If errormax < tolerance Then
trueroot = xnewbisect
'Cells(11,2)=trueroot
Stop
ElseIf functionvaluexnewbisect > 0 And functionvaluexmin > 0 Then
xmin = xnewbisect
ElseIf functionvaluexnewbisect < 0 And functionvaluexmin < 0 Then
xmin = xnewbisect
ElseIf functionvaluaexnewbiseet > 0 And functionvaluexmax > 0 Then
xmax = xnewbisect
ElseIf functionvaluexnewbisect < 0 And functionvaluexmin < 0 Then
xmax = xnewbisect
ElseIf functionvaluexmin = 0 Then
trueroot = xmin
Cells(11, 2) = trueroot
Stop
ElseIf functionvaluexmax = 0 Then
trueroot = xmax
Cells(11, 2) = trueroot
Stop
ElseIf functionvaluexnewbisect = 0 Then
trueroot = xnewbisect
Cells(11, 2) = trueroot
Stop
'Cells(13, 2)= functionvaluexnewbisect
'Cells (14,2)=functionvaluexmin
'Cells (15,2)=functionvaluexmax
'To replace xmin/xmax with xnewbisect
If errormax < tolerance Then
trueroot = xnewbisect
'Cells (11, 2) = trueroot
Stop
ElseIf functionvaluexnewbisect > 0 And functionvaluexmin > 0 Then
xmin = xnewbisect
ElseIf functionvaluexnewbisect < 0 And functionvaluexmin < 0 Then
xmin = xnewbisect
ElseIf functionvaluexnewbisect > 0 And functionvaluexmax > 0 Then
xmax = xnewbisect
ElseIf functionvaluexnewbisect < 0 And functionvaluexmax < 0 Then
xmax = newbisect
ElseIf functionvaluemin = 0 Then
trueroot = xmin
Cells(11, 2) = trueroot
Stop
ElseIf functionvaluemax = 0 Then
trueroot = xmax
Cells(11, 2) = trueroot
Stop
ElseIf functionvaluexnewbisect = 0 Then
trueroot = xnewbisect
Cells(11, 2) = trueroot
Stop
End If
Wend
End SubFirst, I’m extremely new to all programming languages so treat this like trying to explain calculus to someone who just found out what a derivative is.
My task this summer is to help a friend translate about 7000 lines of C# to VBA. I’ve done the best I could and am now going through trying to correct the plethora of errors. I’m currently getting a «Wend without While» compile error, but it appears to me that the While is clearly there. What am I missing?
For i = 1 To UBound(COREGIONS)
co_dispatch_region_id = CO_REGION_ID_INDEX(coregion_id(i))
currentunit = 0
While currentunit < CO_DISPATCH_NUMAV(co_dispatch_region_id, ICURMN)
UnitNum = CO_DISPATCH_IUNAV(co_dispatch_region_id, currentunit, ICURMN)
region_id = CO_DISPATCH_IREGIONAV(co_dispatch_region_id, currentunit, ICURMN)
Dim rev_unit As Integer
rev_unit = REVERSE_NUMUNT(region_id, UnitNum)
currentunit = currentunit + 1
If LIST(rev_unit) = something Or LIST(rev_unit) = that Then
invalidUnit(rev_unit) = True
End If
For ihour = 0 To 167
storelist(rev_unit, ihour) = GlobalDefinitions.SAVAIL
Next
If LIST(rev_unit) = GlobalDefinitions.Commit Then
If UNTTYP(rev_unit) = "C" Then
LIST(rev_unit) = GlobalDefinitions.QAVAIL
If QUICKSTART_UNIT(rev_unit) = "N" Then
NON_QS_PEAKING(region_id) = array(region_id) + array(rev_unit)
End If
If NON_START_SPIN_CAPABLE(rev_unit) Then
this(region_id) = that(region_id) + SUPPLYCURVE_MW(rev_unit)
End If
Else
started_on(region_id, UnitNum) = True
End If
ElseIf LIST(rev_unit) = GlobalDefinitions.PAROUT Then
For ihour = 0 To 167
storelist(rev_unit, ihour) = GlobalDefinitions.Commit
coregion_min_cap(co_dispatch_region_id, ihour) = lots of stuff
coregion_comcap(co_dispatch_region_id, ihour) = more stuff
region_min_cap(region_id, ihour) = stuffity stuffity stuff stuff stuff
region_comcap(region_id, ihour) = dickbutt + cumbox
Next
ElseIf LIST(rev_unit) = GlobalDefinitions.Start Then
INTO = GlobalDefinitions.SAVAIL
'objtrnsfr.TrnsFr (rev_unit)
started_on(region_id, UnitNum) = False
ElseIf LIST(rev_unit) = GlobalDefinitions.FUEL_DEFICIENCY Then
INTO = GlobalDefinitions.QAVAIL
If QUICKSTART_UNIT(rev_unit) = "N" Then '//transfer
NON_QS_PEAKING(region_id) = NON_QS_PEAKING(region_id)
If NON_START_SPIN_CAPABLE(rev_unit) Then
NON_START_SPIN_PEAKING(region_id) = NON_START_SPIN_PEAKING(region_id)
'objtrnsfr.TrnsFr (rev_unit)
started_on(region_id, UnitNum) = False
End If
region_econ_used(region_id, UnitNum) = False
Wend
Next