for loop using variable

breilly00

Board Regular
Joined
Sep 15, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
I am trying to use a variable to determine how many times to go thru a loop. The code that I am trying to get to work is shown below. If I use 'For S1 = 1 to 2", I go inside my loop 2 times. If I use 'For S1 = 1 to NumofTimes', I do not get inside my loop.

The Msgboxes show a value of 1 and a value of 2 when I cannot get into my loop. The msgbox display right after the evaluate function show a value of 2. I can uncomment the 'For s1 = 1 to 2' and comment the 'for s1 = 1 to numoftimes' and i get inside the loop.

If there is a different way to go thru a range of values in a column or something simple to change in my attempt I cannot figure it out. What should I do?

Sub testforloop()


'Define Variables
Dim s1 As Integer
Dim numoftimes As Integer


'Activate Schedule sheet and create variable values
Sheets("schedule").Activate
numoftimes = Evaluate("=SUM(IFERROR(1/COUNTIF(B:B,B:B),0))") - 1
MsgBox "NumofTimes = " & numoftimes

' Loop thru values using variable to calculate how many times to loop
For s1 = 1 To Num0fTimes
'For s1 = 1 To 2
MsgBox "Inside Loop s1 = " & s1
Next
MsgBox "out side of loop s1 = " & s1 & " num of times = " & numoftimes

End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Rich (BB code):
For s1 = 1 To Num0fTimes

you have a zero (0) in your variable name - Try replacing with letter O & see if this resolves your problem

Dave
 
Upvote 0
Rich (BB code):
For s1 = 1 To Num0fTimes

you have a zero (0) in your variable name - Try replacing with letter O & see if this resolves your problem

Dave


Can you see the tail tucked between my legs? Geeeez man, what a dufus I am. Thank you for the answer. Yup, works like a charm
 
Upvote 0
Can you see the tail tucked between my legs? Geeeez man, what a dufus I am. Thank you for the answer. Yup, works like a charm

No worries, you will not be the first to make spelling mistake with variable.

You can avoid this by placing Option Explicit at very top of your module.
If you attempt to use an undeclared variable name (like your one with spelling error) - an error occurs at compile time.

Dave
 
Last edited:
Upvote 0
I never use option explicit becaue there is another very easy way to find these errors and that is to look at the local window. This means you can avoid cluttering up the start of every bit of code with loads of unnecesary declarations.
SO if it doesn't work, put a breakpoint in and just have a look at the variables. This will sort out lots of problems at a glance
 
Upvote 0
offthelip;5302923 I never use option explicit becaue there is another very easy way to find these errors and that is to look at the local window.[

All programmers have their preferred approach to coding and maybe what you say does work for you.
Personally, I disagree that Adding Option Explicit to top of code page causes unnecessary clutter and it's use in this case, would have immediately reported the missing declaration at compile time.


Dave
 
Last edited:
Upvote 0
I never use option explicit becaue there is another very easy way to find these errors and that is to look at the local window. This means you can avoid cluttering up the start of every bit of code with loads of unnecesary declarations.
SO if it doesn't work, put a breakpoint in and just have a look at the variables. This will sort out lots of problems at a glance
A lot of us do not think declaring variable types "clutters up" one's code. As for your method of discovering errors... you are opening yourself up to possible catastrophic code failures, catastrophic because you won't possibly ever know the error occurred. For example, let's say you the variable breilly00 had NumOfTimes and it was used in a calculation for, say, a structural element in a, I don't know, maybe a bridge. Let's say the calculation was this...

CriticalElement = SomeCalculatedValue + NumOfTimes * SomeSignificantValue

If, by mistake, you used Num0fTime (that is a zero, not an "O" as it is supposed to be)... this line of code will calculate fine with no error being raised and your program will continue on until the end of the procedure. Unless the value assigned to the CriticalElement variable was so far out of whack because of the error that it was recognizable by the scale of its miscalculation, no one would ever know the error occurred except, perhaps, until the bridge collapsed at which point a review might find the error. It is always a good idea to use Option Explicit... always. And it is always a good idea to declare variables... your code will be more efficient.
 
Upvote 0
I must endorse what Rick says here - I have not long retired but for many years I worked developing vba platformed software for a global pharmaceutical company where using Option Explicit and declaring variables was a must and not following this would mean failure at the source code review during validation. This was due to the fact that in the early days, in another major company, the problem that Rick describes manifested itself and a number of batches were formulated incorrectly.

Mick
 
Upvote 0
I have also recently retired having been writing code for many decades, often using languages where finding a typo was not as easy as it is with option explicit. So I got into the habit of being very careful with variables.
Also I would suggest that any code that failed because of ambiguous variables hasn't been tested properly
I was just trying to point out to the user that there was another very easy way of finding the error, and also spotting other obvious errors which option explicit doesn't catch
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top