Here is a simple example of inline error handling. Notice the line "On
Error Resume Next ' ignore errors." In the loop, I am ignoring the error
raised by trying to add duplicate keys, so the collection ends up containing
a unique set of values, but ignores the error I know I want ignored. Be
warned: Do not take this and add it indiscriminately to your code and begin
ignoring all errors. You may mask errors you want to know about. Even here,
I probably should put the Error statements in the loop so I surround only
the Add operation.

Function UniqueNames() As Collection
'Returns a unique set of text values from rngNames.
'It depends on the property of the Collection
'object to require unique keys (the second arg to
'col.Add). Trying to add a duplicate throws an
'error which we conveniently ignore. This is a
'common technique.
Dim col As Collection 'we'll return this
Dim wks As Worksheet 'the raw data sheet
Dim rngNames As Range 'the names
Dim cell As Range

Set wks = Workbooks("Book1.xls").Worksheets("Sheet0")
Set rngNames = Intersect(wks.Columns("A"), wks.UsedRange)
Set col = New Collection

On Error Resume Next ' ignore errors
For Each cell In rngNames
col.Add cell.Text, cell.Text
Next cell
On Error GoTo 0 'un-ignore errors

Set UniqueNames = col
Set col = Nothing

End Function

"jordanctc " wrote in message
...
I have a weekly shipment checking macro and in it I use *Find* to look
for specific shipment numbers. From time to time the number I'm
looking for isn't there so I'm left inserting a temporary hack to get
through that week.

Being self-taught with tons of help from this group there are definite
gaps in the VBA education (like error-handling). What is the best way
to avoid these errors? Do I need to count the number of times the
number comes up in the column and if it's 0 then exectute else skip?

There are usually some good functions or tricks and I'm about to add
some more finds (since they're so useful) but only if I can keep the
'ol macro running

Thanks,
Jordan

PS. Any good links to sites/pages with programming advise for VBA
error handling of any kind?


---
Message posted from http://www.ExcelForum.com/