I developed this macro in Excel 97 and then upgraded to Excel 2003. It worked fine in my Excel 97 but now i get an error message saying "Compile Error: Can't find project or Library" when i execute the code. Can someone help me understand what i need to do to fix this so it works on my Excel 2003. My code is using an array to delete duplicate entries out of a combo box list.
i believe the line of code having the problem is If Err.Number = 0 And Trim(c) <> "" Then. I have included the whole module below so you can evaluate for yourself.
i believe the line of code having the problem is If Err.Number = 0 And Trim(c) <> "" Then. I have included the whole module below so you can evaluate for yourself.
Code:
'array to get rid of duplicates in cmbShort
Function CreateArray(r As Range)
Dim col As New Collection, c As Range, TempArray(), i As Long
'for each cell in range r
For Each c In r
On Error Resume Next
col.Add c.Value, CStr(c.Value)
If Err.Number = 0 And Trim(c) <> "" Then
ReDim Preserve TempArray(i)
TempArray(i) = c.Value
i = i + 1
End If
Err.Clear
Next
CreateArray = TempArray
Erase TempArray
End Function