While without wend vba ошибка

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

ZX9's user avatar

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 Student's user avatar

Gary’s StudentGary’s Student

95.4k9 gold badges58 silver badges99 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 Guindon's user avatar

Mathieu GuindonMathieu Guindon

69.5k8 gold badges107 silver badges233 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

ZX9's user avatar

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 Student's user avatar

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 Guindon's user avatar

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

ZX9's user avatar

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 Student's user avatar

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 Guindon's user avatar

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 Sub

First, 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
Источник

Permalink

Cannot retrieve contributors at this time

title keywords f1_keywords ms.prod ms.assetid ms.date ms.localizationpriority

While without Wend

vblr6.chm1011164

vblr6.chm1011164

office

e922803e-32a1-8745-1814-5bf3ddfdcef6

06/08/2019

medium

A While statement is used without a corresponding Wend statement. This error has the following cause and solution:

  • You opened a While…Wend construct, but did not close it. Check for an incorrectly matched While…Wend structure inside the outer While…Wend structure.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Источник
  • Remove From My Forums

  • Question

  • Trying to modify my excel export to look like the colored imaged posted below.

    • Edited by

      Friday, September 6, 2019 2:31 PM

Answers

  • Hi InnVis,

    So, your data is a Columnar from the SQL String? By default we normally assumed it is row by row.

    You just change the X,Y like this…

            x = 2   'position insert point at Row (second row)
        y = 2   'position insert point at Column (second column)
        With rsEXHuddle
        Do Until .EOF
        For i = 0 To .Fields.Count - 1
        xlWS.Cells(x, y) = .Fields(i)
        x = x + 1   'increment Row
        Next
        y = y + 1   'increment Colmun
        x = 2       'Reset Row
        .MoveNext
        Loop
        End With
        rsEXHuddle.Close

    As for the starting point, you need to find out yourself where would the Cell on Excel starts from.

    You need to ask yourself where is the Cell position.

    I’m just guessing it’s at B2. If the starting point is at F2, Change X,Y starting point. Y=6 (F2 in Excel), X remain as second row (2).

            x = 2   'position insert point at Row (second row)
        y = 6   'position insert point at Column (second column)
        With rsEXHuddle
        Do Until .EOF
        For i = 0 To .Fields.Count - 1
        xlWS.Cells(x, y) = .Fields(i)
        x = x + 1   'increment Row
        Next
        y = y + 1   'increment Colmun
        x = 2       'Reset Row
        .MoveNext
        Loop
        End With
        rsEXHuddle.Close

    • Marked as answer by
      InnVis
      Tuesday, September 10, 2019 3:49 AM

Источник
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 Sub

Источник

Понравилась статья? Поделить с друзьями:
  • While true python ошибка
  • Whatsapp ошибка неправильная дата и время
  • Whatsapp ошибка инициализации
  • Whatsapp ошибка загрузки невозможно загрузить файл
  • Whatsapp ошибка загрузки медиа