for loop using variable

[breilly00]

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

 

[dmt32]

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

 

[breilly00]

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

 

[dmt32]

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

 

[offthelip]

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

 

[dmt32]

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

 

[Rick Rothstein]

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.

 

[Mick_Webb]

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

 

[offthelip]

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