Automation Error when building collection

[bbrother]

Dear Excel Experts,

I have a simple piece of VBA code with some (messy) error handling that builds a collection by constructing a string and seeing if it exists in a larger collection.

This code generates an application execution error on the second pass through the for next loop.

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Product As String
    Dim PCores As Collection
    
    If MyTools.PLProducts.count = 0 Then Call ConfiguratorMain.DataLoader
    
Resum:
    ConfidentialL.Caption = ConfiguratorMain.ConfidentialL.Caption
    OutputLB.ColumnCount = 4
    OutputLB.TextAlign = fmTextAlignRight
    
    Set PCores = New Collection
    On Error GoTo eh  'If the constructed product name doesn't exist in the collection, then skip
    For i = 1 To 2 ^ 16
        Product = "5TB-XFD-" & i
        If MyTools.PLProducts(Product) Then
            PCores.Add i
        End If
eh:
    Next
    On Error GoTo 0


End Sub

 

[NdNoviceHlp]

Maybe....

PCores.Add Product

HTH, Dave

 

[bbrother]

Maybe....

PCores.Add Product

HTH, Dave

Nope, it's really more convenient for me to have a number I can calculate with. That integer represents number of CPU cores for which I need to use for various calculations. Are you saying that using a collection of strings rather than a collection of Longs will avoid the error?

 

[NdNoviceHlp]

No, I was just guessing that you were trying to make a new collection of products from the MyTools.PLProducts collection. That doesn't really explain the error. Maybe...

Product = "5TB-XFD-" & CSTR(I)

Why 2 to the power of 16? You could take the error code out and insert some msgboxes to find out where and what is actually causing the error. HTH. Dave