Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. -- -John Please rate when your question is answered to help us and others know what is helpful. "Graham Y" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Graham Y" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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 "Graham Y" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. "RB Smissaert" wrote: 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 "Graham Y" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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 "Graham Y" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. "Peter T" wrote: 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 "Graham Y" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
This assumes that the region always includes row 1. It is safer to use
Range("H3").CurrentRegion.Row+Range("H3").CurrentR egion.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 "Graham Y" wrote: 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. "Peter T" wrote: 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 "Graham Y" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. "barnabel" wrote: This assumes that the region always includes row 1. It is safer to use Range("H3").CurrentRegion.Row+Range("H3").CurrentR egion.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 "Graham Y" wrote: 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. "Peter T" wrote: 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 "Graham Y" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. On Jul 31, 3:20 am, Graham Y wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
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. " wrote: 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. On Jul 31, 3:20 am, Graham Y wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
So has it 'happened' since, what did y debug to just beforehand.
Regards, Peter T "Graham Y" wrote in message ... 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. "Peter T" wrote: 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 "Graham Y" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does my code randomly ignore variable initialisation?
For Excel things I'd expect that you'd have to reset the form each
time. The form is unloaded after each use so I would expect the memory to be cleared of all knowledge of previous values. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Let's try again: VBA Code stops randomly | Excel Programming | |||
String Array initialisation | Excel Programming | |||
String array initialisation | Excel Programming | |||
DTPicker to be dropped down on initialisation | Excel Programming | |||
Excel formula randomly changes to hard-code number | Excel Discussion (Misc queries) |