How to switch between two Do Until Loop procedures

[StaffordStag]

Can anyone help me please. I am writing VBA code which, if I can get it to work as I want it to, allows the user to select one of two ‘Do Until, Loop’ statements. Allow me to explain. My program outputs numbers printing them on a Worksheet. I wish to give the user the choice of either (A) limiting the output to a range of numbers, e.g. 100 to 10,000, or (B) allow the output of numbers to run without check until the user intervenes and halts the process. The lines of code are as follows:

• Do Until y = EndNumber …procedure….. y = y + 1, Loop
• Do Until y > StartNumber \ 10 + y …procedure….. y = y + 1, Loop

Having done some online searching I think I need to invoke the Evaluate function. I have made several attempts but have failed to achieve my objective. Is there anyone out there who can tell me exactly how to code for this feature.

Just so you know I am a novice when it comes to coding so if my terminology is questionable, please excuse me.

I am using Excel 2019 and VBA 7.1. I am running it on Windows 10, 64-Bit.

In anticipation of your help thank you.

 

[NateSC]

A few points and possible solutions:
- How are you capturing the input from the user in terms of which case they want to follow? Is it through a cell, a checkbox, a radio button, a user form, etc?
- Once you have their input, you can store it in a variable (or reference it directly) and use that information in a "If Then Else" statement. I will put a simplified example below.
- In general, be very careful with routines that "run without check until the user intervenes". The computer can crunch through a LOT of loops before they think to hit stop unless you are stopping with every loop and waiting for the user to decide what to do. Just be careful (i.e. I would recommend rethinking this as an option). I would use a "For Next", but that is my preference.

Assuming you have gathered the user's input as a "True" or "False" (Boolean) of which option they would like to follow:

Sub MakeLoops()

Dim myUserInput As Boolean
Dim y As Integer
Dim EndNumber As Integer
Dim StartNumber As Integer

' Get user input here and store in myUserInput as a Boolean value
' My assumption is that a "True" myUserInput means they want to limit the loop
' Get or calcualte EndNumber
' Get or calculate StartNumber

If myUserInput Then
    Do While y < EndNumber
        ' …procedure…..
        y = y + 1
    Loop
Else
    Do While y <= StartNumber / 10 + y
        '…procedure…..
        y = y + 1
    Loop
End If

'Alternate Loop structure:
If myUserInput Then
    Do
        ' …procedure…..
        y = y + 1
    Loop Until y = EndNumber
Else
    Do
        '…procedure…..
        y = y + 1
    Loop While y > StartNumber / 10 + y
End If

End Sub

 

[StaffordStag]

Good evening NatSC.
Thank you for your very swift reply, I very much appreciate it. Tomorrow I will play with your solution and see if it does what I want it to. With regards to your questions, I offer the following.
At the moment I am developing my code and I have not yet used data input forms. These will come once I have the program performing as I want. So, at the moment I am putting my values directly in to variables at the module level. This works fine for me at the moment but appreciate it’s not a very practical application for others to use and I will need a data input form of some sort.
I hear what you say about launching a program that just keeps on running but this is intentional on my part as I want to take the numbers to very high values to demonstrate a formula I have written still outputs the correct answers at those heady values. At present when I run it in this mode, I use the Esc key to interrupt the program but there may be other methods which I have yet to explore.

 

[NateSC]

The User Forms are pretty easy to work with. You might end up putting the loop code inside the form or have the form Call the code from the previously written module. The trick is how you pass the information. You can use Public variables or have your module actually look at the form and get the information directly. The way you are starting with hard coded variables is a good way to approach it if you are new to VBA.

If you would rather make myUserInput a different type of variable, you just change the first line of the "If" statement. For example, if you were using an integer as the trigger then your "If" might look like this:

If myUserInput = 1 Then

Good luck and I'll help if I am able.

 

[StaffordStag]

The User Forms are pretty easy to work with. You might end up putting the loop code inside the form or have the form Call the code from the previously written module. The trick is how you pass the information. You can use Public variables or have your module actually look at the form and get the information directly. The way you are starting with hard coded variables is a good way to approach it if you are new to VBA.

If you would rather make myUserInput a different type of variable, you just change the first line of the "If" statement. For example, if you were using an integer as the trigger then your "If" might look like this:

If myUserInput = 1 Then

Good luck and I'll help if I am able.

Good evening NateSC. I have implemented your solution and I am pleased to report the code now does as I want it to. So thank you for your help. However, now with effectively the formula being duplicated I have wondered if I could put it in its own module and Call it up as the logic required. Your latest response suggests that I could indeed do this but I need time to investigate further as all this is very new to me. Thanks again for your help.

 

[NateSC]

If the procedure inside both loops is the same, then you should be able to make it a Sub that is Called. You may have to pass it arguments or use Public variables.

Sub MakeLoops()
Dim myUserInput As Boolean
Dim y As Integer
Dim EndNumber As Integer
Dim StartNumber As Integer
' Get user input here and store in myUserInput as a Boolean value
' My assumption is that a "True" myUserInput means they want to limit the loop
' Get or calcualte EndNumber
' Get or calculate StartNumber
    If myUserInput Then
        Do While y < EndNumber
            Call MakeUpStuff(y)
            y = y + 1
        Loop
    Else
        Do While y <= StartNumber / 10 + y
            Call MakeUpStuff(y)
            y = y + 1
        Loop
    End If
End Sub

Sub MakeUpStuff(myNum As Integer)
    ActiveSheet.Range(myNum, 1) = Int((Rnd * 100) + 1)
    ' Do other stuff here
End Sub

 

[StaffordStag]

Thank you for that timely guidance NatesSC. I have tried over the last couple of evenings to Call a Module and I kept getting the message 'Compile error: Expected variable or procedure, not module' and I couldn't make sense of the message or resolve the problem. So thank you for your input once again.

 

[NateSC]

Did you name your Sub and your Module with the same name? If so, try changing one of them. I ran the code shown above and it works fine if included in a Module or a Sheet. If you can simplify your code and paste it here, I will try to take a look.

 

[NateSC]

Here is a related issue and its resolution:

Macro error- Expected variable or procedure, not module

Hello, I found this usefull macro and mz VBA Editor gives me error: Expected variable or procedure, not module. and Highlight Sheets("Index").Delete Sub CreateIndex() 'updateby Extendoffice 20150914 Dim xAlerts As Boolean Dim I As Long Dim xShtIndex As Worksheet Dim xSht As...

www.mrexcel.com

 

[bebo021999]

Hello

With StartNumber > 0, does this expression:
y <= StartNumber / 10 + y
always evaluate to TRUE?
So,

Do While y <= StartNumber / 10 + y
...
y = y + 1
Loop

Is it certain that the loop is infinite?