Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
Hi all,
In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
John Walkenbach shows how to get a nice unique list at:
http://j-walk.com/ss/excel/tips/tip47.htm Paul wrote: Hi all, In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
Two choices to address your specific question. However, you may also
want to check out the Collection object as a means to quickly create a unique list. Choice 1: Always create the array with a 'unused' lbound. So, an 'empty' array will be Dim MyArr() ReDim MyArr(0 to 0) Now, one can always test UBound. If zero it means the array has never been used. Choice 2: Use a error trap On Error Resume Next x=ubound(y) if err.number<0 then 'array is uninitialized On Error Goto 0 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Paul" <PaulNieboer HEREGOESTHEAD Gmail.com says... Hi all, In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
Thanks Dave an Tushar for ur responses!!!
A example similar to the tip on John Walkenbach's site was my starting point. But I was looking for the fasted way to check if an array was empty or not. I had thought about checking if there is a cell in Column B which isn't emtpy but I thought there had to be a better way to check if an array is empty (don't know if that is the correct term for it). Putting an 'empty' value in lbound is the fasted and easiest way. Thanks for that!!!! "Tushar Mehta" wrote in message om... Two choices to address your specific question. However, you may also want to check out the Collection object as a means to quickly create a unique list. Choice 1: Always create the array with a 'unused' lbound. So, an 'empty' array will be Dim MyArr() ReDim MyArr(0 to 0) Now, one can always test UBound. If zero it means the array has never been used. Choice 2: Use a error trap On Error Resume Next x=ubound(y) if err.number<0 then 'array is uninitialized On Error Goto 0 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Paul" <PaulNieboer HEREGOESTHEAD Gmail.com says... Hi all, In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
If I were using an array, I think I'd just use a boolean variable and keep track
of it there. Reset it to false each time you empty the array and set it true the first time you add something to it. But for a collection, you can just look at the count. Stealing from John Walkenbach's code: Option Explicit Sub RemoveDuplicates() Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection Set AllCells = Range("A1:A105") On Error Resume Next For Each Cell In AllCells If Trim(Cell.Value) < "" Then NoDupes.Add Cell.Value, CStr(Cell.Value) End If Next Cell On Error GoTo 0 If NoDupes.Count = 0 Then 'nothing added MsgBox "No cells added" Else 'do what you want. MsgBox "Added " & NoDupes.Count & " unique entries" _ & vbLf & "From a possible: " & AllCells.Cells.Count End If End Sub Paul wrote: Thanks Dave an Tushar for ur responses!!! A example similar to the tip on John Walkenbach's site was my starting point. But I was looking for the fasted way to check if an array was empty or not. I had thought about checking if there is a cell in Column B which isn't emtpy but I thought there had to be a better way to check if an array is empty (don't know if that is the correct term for it). Putting an 'empty' value in lbound is the fasted and easiest way. Thanks for that!!!! "Tushar Mehta" wrote in message om... Two choices to address your specific question. However, you may also want to check out the Collection object as a means to quickly create a unique list. Choice 1: Always create the array with a 'unused' lbound. So, an 'empty' array will be Dim MyArr() ReDim MyArr(0 to 0) Now, one can always test UBound. If zero it means the array has never been used. Choice 2: Use a error trap On Error Resume Next x=ubound(y) if err.number<0 then 'array is uninitialized On Error Goto 0 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Paul" <PaulNieboer HEREGOESTHEAD Gmail.com says... Hi all, In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for subscript out of range
Thanks Dave! I didn't know anything about Collections (actually still don't)
but it might be something to look into. I don't know what the difference is between Collections, Arrays and Dictionaries. I just now searched for Array vs Collections, for now the array works just fine, but I'll look into the other two just the same. I haven't yet decided which solution I am going for, but using a boolean is a good option as well. Cheers for helping me out!! "Dave Peterson" wrote in message ... If I were using an array, I think I'd just use a boolean variable and keep track of it there. Reset it to false each time you empty the array and set it true the first time you add something to it. But for a collection, you can just look at the count. Stealing from John Walkenbach's code: Option Explicit Sub RemoveDuplicates() Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection Set AllCells = Range("A1:A105") On Error Resume Next For Each Cell In AllCells If Trim(Cell.Value) < "" Then NoDupes.Add Cell.Value, CStr(Cell.Value) End If Next Cell On Error GoTo 0 If NoDupes.Count = 0 Then 'nothing added MsgBox "No cells added" Else 'do what you want. MsgBox "Added " & NoDupes.Count & " unique entries" _ & vbLf & "From a possible: " & AllCells.Cells.Count End If End Sub Paul wrote: Thanks Dave an Tushar for ur responses!!! A example similar to the tip on John Walkenbach's site was my starting point. But I was looking for the fasted way to check if an array was empty or not. I had thought about checking if there is a cell in Column B which isn't emtpy but I thought there had to be a better way to check if an array is empty (don't know if that is the correct term for it). Putting an 'empty' value in lbound is the fasted and easiest way. Thanks for that!!!! "Tushar Mehta" wrote in message om... Two choices to address your specific question. However, you may also want to check out the Collection object as a means to quickly create a unique list. Choice 1: Always create the array with a 'unused' lbound. So, an 'empty' array will be Dim MyArr() ReDim MyArr(0 to 0) Now, one can always test UBound. If zero it means the array has never been used. Choice 2: Use a error trap On Error Resume Next x=ubound(y) if err.number<0 then 'array is uninitialized On Error Goto 0 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Paul" <PaulNieboer HEREGOESTHEAD Gmail.com says... Hi all, In a function I want to fill an array with values. This function goes through a given column on a sheet (say coumn B) and stores the values in the array, skipping all the duplicates. Every time the function is called the array is redimmed so it starts with an 'empty' array and then fills it. Now in the next column (column C) i also have some values which also have to be put into the array, again skipping the duplicates. Depending on if column B was empty or not, the array has already got some values in it. When proceeding to column C how can i check if the array is already populated by at least 1 value? I cannot use Ubound, but I can also not check for the error (subscript is out of range) by using IsError, or something else. code: dim boolArrayHasNoValues as Boolean boolArrayHasNoValues = IsError(UBound(NoDuplicates)) <<<<<<supscript is out of range ''if NoDuplicates is not yet redim / rather if NoDuplicates has no values yet then For iTemp = 1 To AllCells.Cells.Count 'get first value in selection of cells, and stop this part of the function 'this is to make sure that there are no empty values in the array If Not AllCells.Cells(iTemp) = "" Then ReDim NoDupes(1) NoDupes(1) = AllCells.Cells(iTemp) Exit For End If Next iTemp ''End If -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript Out of Range | Excel Programming | |||
Subscript out of range? | Excel Programming | |||
Subscript out of range | Excel Programming |