Why does my code randomly ignore variable initialisation?

[Guest]

Can anyone tell me why sometimes this code should fail on line 5 with y=0 ...
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[Guest]

Looks like it could happen if your CurrentRegion = 0 rows, if you get it to
error, debug and find out what y is and what your CurrentRegion is at that
moment.

 

[Arvi Laanemets]

Hi

You probably got 'Out of Range' (or something like this) error.
You refer to range Cells(0,8), i.e. to cell above H1 - but there doesn't
exist any rows above 1st one. Or otherwise - both parameters for Cells(i,j)
MUST BE POSITIVE.

 

[RB Smissaert]

One other suggestion although it may not matter.
You declared y as Single, but as this are row numbers it would be better to
declare as Long as it always will and should be whole numbers.

RBS

 

[Guest]

Thanks I usually declare it as integer, not sure why I chose single, biut the
real point is that surely if y is in a loop going from 3 and I don't have any
step value, how could y ever be less than 3?
I think there must be something amiss in the VBA compiler.

One other suggestion although it may not matter.
You declared y as Single, but as this are row numbers it would be better to
declare as Long as it always will and should be whole numbers.

RBS

Can anyone tell me why sometimes this code should fail on line 5 with y=0
...
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[Peter T]

Add a new line between 4 & 5
Debug.Print y

What's y just before it fails on the next line. If y=0 that would be more
than strange!

In passing, do you need to populate from the entire region, if so I assume
you need to loop like this
For y = 3 To .[H3].CurrentRegion.Rows.count + 3 - 1

FWIW, the square brackets to return the cell are fine for doing quick &
dirty stuff, but in real code better to do .Range("H3").etc

Regards,
Peter T

 

[Guest]

Thanks I'll try it, but it does happen seemingly randomly!
..[H3].CurrentRegion.Rows.count
gives me the last row number which is what I want. Rows 1 and 2 are headings.

Add a new line between 4 & 5
Debug.Print y

What's y just before it fails on the next line. If y=0 that would be more
than strange!

In passing, do you need to populate from the entire region, if so I assume
you need to loop like this
For y = 3 To .[H3].CurrentRegion.Rows.count + 3 - 1

FWIW, the square brackets to return the cell are fine for doing quick &
dirty stuff, but in real code better to do .Range("H3").etc

Regards,
Peter T

Can anyone tell me why sometimes this code should fail on line 5 with y=0 ....
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[Guest]

This assumes that the region always includes row 1. It is safer to use
Range("H3").CurrentRegion.Row+Range("H3").CurrentRegion.Rows.Count-1

Also, always use Longs for row indexes not Integers. You may not think
anything will ever be beyond 32767 rows but safe practices are better.

As to the original problem, what are cmbGCodes and cmbGrades? I don't see
how anything could be effecting the variable y. Is it possible you have a
global variable also called y that is an integer and the macro is picking up
that one since you are using it as an int? Try changing the variable y to
something more descriptive in this code fragment and see if that helps.

Peter Richardson

Thanks I'll try it, but it does happen seemingly randomly!
.[H3].CurrentRegion.Rows.count
gives me the last row number which is what I want. Rows 1 and 2 are headings.

Add a new line between 4 & 5
Debug.Print y

What's y just before it fails on the next line. If y=0 that would be more
than strange!

In passing, do you need to populate from the entire region, if so I assume
you need to loop like this
For y = 3 To .[H3].CurrentRegion.Rows.count + 3 - 1

FWIW, the square brackets to return the cell are fine for doing quick &
dirty stuff, but in real code better to do .Range("H3").etc

Regards,
Peter T

Can anyone tell me why sometimes this code should fail on line 5 with y=0 ....
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[Guest]

Peter
I use x & y as coordinates for cell, it's just a shame Excel goes down first
(y,x) and not across like 'ordinary' maths (x,y) coordinates
the cmb's are 2 comboboxes that are on a form that is being initialised, It
sets up about 10 values for the user to choose from.
Thanks for the tip about getting the first row of a range.

I do have some global variables but y is not one of them.

And yes I really must start using longs for cell row counters.

This assumes that the region always includes row 1. It is safer to use
Range("H3").CurrentRegion.Row+Range("H3").CurrentRegion.Rows.Count-1

Also, always use Longs for row indexes not Integers. You may not think
anything will ever be beyond 32767 rows but safe practices are better.

As to the original problem, what are cmbGCodes and cmbGrades? I don't see
how anything could be effecting the variable y. Is it possible you have a
global variable also called y that is an integer and the macro is picking up
that one since you are using it as an int? Try changing the variable y to
something more descriptive in this code fragment and see if that helps.

Peter Richardson

Thanks I'll try it, but it does happen seemingly randomly!
.[H3].CurrentRegion.Rows.count
gives me the last row number which is what I want. Rows 1 and 2 are headings.

Add a new line between 4 & 5
Debug.Print y

What's y just before it fails on the next line. If y=0 that would be more
than strange!

In passing, do you need to populate from the entire region, if so I assume
you need to loop like this
For y = 3 To .[H3].CurrentRegion.Rows.count + 3 - 1

FWIW, the square brackets to return the cell are fine for doing quick &
dirty stuff, but in real code better to do .Range("H3").etc

Regards,
Peter T

Can anyone tell me why sometimes this code should fail on line 5 with y=0
....
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[gimme_this_gimme_that]

It looks to me like the problem has to do with vestige values of
cmbGCodes.
Setting the ListIndex to zero doesn't clear stale values. Each time
PopulatecmbGrades executes all you're doing is adding new values to
the end of cmbGCodes.

Try recreating a brand new cmbGCodes in PopulatecmbGrades() or each
time just before PopulatecmbGrades() gets called.

 

[Guest]

The form is unloaded after each use so I would expect the memory to be
cleared of all knowledge of previous values. The form appears as the result
of an event ie selecting a cell, and is unloaded when accept of cancel is
pressed. But if this relies on some form of garbage collection (like C#.NET
programming), then that might have some bearing, but it still doesn't account
for the fact that the line before says for y=3 to end value, so as soon as
this line is processed (for the first time) y should = 3 and there is no -ve
step value so it should never be less than 3.

It looks to me like the problem has to do with vestige values of
cmbGCodes.
Setting the ListIndex to zero doesn't clear stale values. Each time
PopulatecmbGrades executes all you're doing is adding new values to
the end of cmbGCodes.

Try recreating a brand new cmbGCodes in PopulatecmbGrades() or each
time just before PopulatecmbGrades() gets called.

Can anyone tell me why sometimes this code should fail on line 5 with y=0 ...
1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[Peter T]

So has it 'happened' since, what did y debug to just beforehand.

Regards,
Peter T

Thanks I'll try it, but it does happen seemingly randomly!
.[H3].CurrentRegion.Rows.count
gives me the last row number which is what I want. Rows 1 and 2 are headings.

Add a new line between 4 & 5
Debug.Print y

What's y just before it fails on the next line. If y=0 that would be more
than strange!

In passing, do you need to populate from the entire region, if so I assume
you need to loop like this
For y = 3 To .[H3].CurrentRegion.Rows.count + 3 - 1

FWIW, the square brackets to return the cell are fine for doing quick &
dirty stuff, but in real code better to do .Range("H3").etc

Regards,
Peter T

Can anyone tell me why sometimes this code should fail on line 5 with

y=0
....

1 Private Sub PopulatecmbGrades()
2 Dim y As Single
3 With Sheets("Sheet5")
4 For y = 3 To .[H3].CurrentRegion.Rows.count
5 cmbGCodes.AddItem .Cells(y, 8)
6 cmbGrades.AddItem .Cells(y, 9)
7 Next
8 End With
9 cmbGCodes.ListIndex = 0
10 cmbGrades.ListIndex = 0
11 End Sub

.[H3].CurrentRegion.Rows.count = 13 currently

I do use y elsewhere and it may be 0 but a) it would be out of scope here
and b) it should be set by the For loop.

 

[gimme_this_gimme_that]

For Excel things I'd expect that you'd have to reset the form each
time.