Automation Error when building collection
Results 1 to 4 of 4

Thread: Automation Error when building collection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automation Error when building collection

    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.

    Code:
    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

  2. #2
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,268
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Automation Error when building collection

    Maybe....
    Code:
    PCores.Add Product
    HTH, Dave

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automation Error when building collection

    Quote Originally Posted by NdNoviceHlp View Post
    Maybe....
    Code:
    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?

  4. #4
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,268
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Automation Error when building collection

    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...
    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •