Check for subscript out of range

[Paul]

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]

John Walkenbach shows how to get a nice unique list at:
http://j-walk.com/ss/excel/tips/tip47.htm

 

[Tushar Mehta]

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

 

[Paul]

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!!!!

 

[Dave Peterson]

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]

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!!