VBA Example of Using WHILE and LOOP?

[Mike]

Hi everyone,

Can someone please refer me to an example on how to use WHILE and LOOP
in Excel's VBA?

Thanks,
Mike

 

[Martin Brown]

Hi everyone,

Can someone please refer me to an example on how to use WHILE and LOOP
in Excel's VBA?

Thanks,
Mike

DO WHILE/UNTIL condition
something
LOOP

or

DO
something
LOOP WHILE/UNTIL condition

or

WHILE condition
statements
WEND

F1 on the keyword should get you syntax related help on a good day.

Regards,
Martin Brown

 

[joeu2004]

Can someone please refer me to an example on how
to use WHILE and LOOP in Excel's VBA?

In VBA Help, type "while wend statement" and "do loop statement" without
quotes, and you will get lots of information. A few simple examples....

x = 1
While x <= y
[...some statements...]
x = x + 1
Wend

x = 1
Do While x <= y
[...some statements...]
x = x + 1
Loop

x = 1
Do Until x > y
[...some statements...]
x = x + 1
Loop

x = 1
Do
[...some statements...]
x = x + 1
Loop While x <= y

x = 1
Do
[...some statements...]
Loop Until x > y

The first 3 are equivalent to:

For x = 1 to y
[...some statements...]
Next

 

[Mike]

Can someone please refer me to an example on how
to use WHILE and LOOP in Excel's VBA?

In VBA Help, type "while wend statement" and "do loop statement" without
quotes, and you will get lots of information.  A few simple examples.....

x = 1
While x <= y
    [...some statements...]
    x = x + 1
Wend

x = 1
Do While x <= y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do Until x > y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do
    [...some statements...]
    x = x + 1
Loop While x <= y

x = 1
Do
    [...some statements...]
Loop Until x > y

The first 3 are equivalent to:

For x = 1 to y
    [...some statements...]
Next

What if you have two or more conditions instaed of one?

 

[Mike]

In VBA Help, type "while wend statement" and "do loop statement" without
quotes, and you will get lots of information.  A few simple examples.....

x = 1
While x <= y
    [...some statements...]
    x = x + 1
Wend

x = 1
Do While x <= y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do Until x > y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do
    [...some statements...]
    x = x + 1
Loop While x <= y

x = 1
Do
    [...some statements...]
Loop Until x > y

The first 3 are equivalent to:

For x = 1 to y
    [...some statements...]
Next

What if you have two or more conditions instaed of one?- Hide quoted text-

- Show quoted text -

It is not exactly plain WHILE/Loop. Let me write you how I have the
While Loop thing:

WHILE( ( PlnHrznTop <= CARD(ts))
AND Continue <> 0)),

!! t is the first time period in the upcoming solve
LOOP( t with condition that (ORD(t) = PlnHrznTop),

 

[Mike]

In VBA Help, type "while wend statement" and "do loop statement" without
quotes, and you will get lots of information.  A few simple examples.....

x = 1
While x <= y
    [...some statements...]
    x = x + 1
Wend

x = 1
Do While x <= y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do Until x > y
    [...some statements...]
    x = x + 1
Loop

x = 1
Do
    [...some statements...]
    x = x + 1
Loop While x <= y

x = 1
Do
    [...some statements...]
Loop Until x > y

The first 3 are equivalent to:

For x = 1 to y
    [...some statements...]
Next

What if you have two or more conditions instaed of one?- Hide quoted text-

- Show quoted text -

My WHILE/LOOP structure is as follows:

WHILE condition1 and condition2
LOOP over t while condition3 as below:

WHILE( PlnHrznTop <= ts
AND Continue <> 0),

LOOP( t while (ORD(t) = PlnHrznTop),

 

[joeu2004]

It is not exactly plain WHILE/Loop. Let me write
you how I have the While Loop thing:

WHILE( ( PlnHrznTop <= CARD(ts))
AND Continue <> 0)),
!! t is the first time period in the upcoming solve
LOOP( t with condition that (ORD(t) = PlnHrznTop),

My WHILE/LOOP structure is as follows:
WHILE condition1 and condition2

LOOP over t while condition3 as below:
WHILE( PlnHrznTop <= ts
AND Continue <> 0),
LOOP( t while (ORD(t) = PlnHrznTop),

I do not understand your pseudocode. I don't know if ts and t are intended
to be the same (typo?) or different. Unless you can write your pseudocode
or algorithm clearly, it is hopeless to try to help you.

Here are some ideas that might be helpful. Then again, maybe not.

If PlnHrznTop <= ts And Continue <> 0 Then
Do
[...some code...]
Loop While ORD(t) = PlnHrznTop
End If

--or--

Do While PlnHrznTop <= ts And Continue <> 0
[...some code...]
If ORD(t) = PlnHzrnTop Then Exit Do
[...some more code...]
Loop

 

[joeu2004]

PS....

LOOP over t while condition3 as below:
WHILE( PlnHrznTop <= ts
AND Continue <> 0),
LOOP( t while (ORD(t) = PlnHrznTop),

Two more possible interpretations of your cryptic pseudocode come to mind.
Again, this might be misdirection.

If PlnHrznTop <= ts And Continue <> 0 Then
Do
[...some code...]
Loop While ORD(t) = PlnHrznTop And PlnHrznTop <= ts And Continue <> 0
End If

--or--

Do While PlnHrznTop <= ts And Continue <> 0
Do
[...some code...]
Loop While ORD(t) = PlnHrznTop
Loop

PS: The outer Do While...Loop could be written using While...Wend. I kept
it as Do While to demonstrate that the looping constructs can be nested. I
did not want you to misunderstand that one must be While...Wend if the other
is Do...Loop.

PPS: I tend to use Do While...Loop instead of While...Wend because we can
use Exit Do in the middle. There is no Exit While; we must use Go To. No
biggie; it's just a style thing.