[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Adding to a collection that is in a class

Posted on 2014-04-02
4
Medium Priority
?
362 Views
Last Modified: 2014-04-03
I'm writing a quoting application in Excel. It's a multilevel deal ... meaning, components get grouped into assemblies, that get grouped into assemblies, that ... repeat up all the levels.

I've written my own class called clsComponents. For each component, I have a property of SubComponents as type Collection. My intention is to fill that collection with clsComponents that are a sub-component of that particular component (are you still with me?).

 I've written a routine that loops through and puts all the subcomponents in an array. It works. However, when I go and loop through the array to assign each sub component to the component, it get an error of ...
Object variable or With block variable not set

Open in new window

Ok ... now for some code.

This is my routine to assign parent and subcomponent. Ignore the parent stuff as it works.
    For x = 1 To UBound(aryIBOM)
        'CREATE COMPONENT RECORD FOR COMPONENT X
        Set recComponent = colAssembly.Item(aryIBOM(x, 4))

        'FIND THE PARENT COMPONENT AND SET IT FOR COMPONENT X
        colAssembly.Item(aryIBOM(x, 4)).Parent = FindParent(x, aryIBOM)
        'FIND ALL THE SUBCOMPONENTS OF X AND PLACE THEM IN AN ARRAY
        childComponents = FindChildren(x, aryIBOM)
        'LOOP THOUGH AND ASSIGN SUBCOMPONENTS TO THE X
        For y = 1 To UBound(childComponents)
            'CREATE SUBCOMPONENT Y
            Set subComponent = colAssembly.Item(childComponents(y))
            'ASSIGN SUBCOMPONENT Y TO COMPONENT X
            recComponent.AddSubComponent subComponent
        Next y
    Next x

Open in new window

This is the relevant parts of my class
Public Line As Integer                  'LINE COLUMN FROM INPUT BOM
Public LV As Integer                    'LV COLUMN FROM INPUT BOM
Public PartNumber As String             'PART# FROM INPUT BOM
Public Rev As String                    'REV FROM INPUT BOM
Public Description As String            'DESCRIPTION FROM INPUT BOM
Public Qty As Integer                   'QTY FROM INPUT BOM
Public Parent As String                 'THE COMPONENT ONE LEVEL ABOVE THIS ONE
Public SubComponents As Collection      'COLLECTION CONTAINING REFERENCE TO ALL SUBCOMPONENTS

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NAME:     AddSubComponent
' AUTHOR:   QHOLMBERG
' PURPOSE:
' The class keeps track of all the subcomponents off the assembly
'   through a collection. This function adds a subcomponent to
'   that collection.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function AddSubComponent(subComponent As clsComponent)
    Me.SubComponents.Add subComponent, subComponent.PartNumber
End Function

Open in new window

The error is a result of that line in AddSubComponent function but I'm at a loss as to why. Collections and classes are a little new to me.

Thanks.
0
Comment
Question by:qholmberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 2000 total points
ID: 39973460
Hi,

I suspect that you are attempting to .Add to the SubComponent collection before it is instantiated.  You could check for this before attempting to add to it like this..

Public Function AddSubComponent(subComponent As clsComponent)
    If Me.SubComponents Is Nothing Then
        Set Me.SubComponents = New Collection
    End If
    Me.SubComponents.Add subComponent, subComponent.PartNumber
End Function

Open in new window

0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 39973503
If my suggestion does work for you I would recommend that rather than doing this check in your AddSubComponent function you could optimise by adding a constructor and instantiating the collection there instead.  This is done by implementing a Class_Initialize method which essentially runs when you create a new instance of your class.  Your class would then look like this..


Public Line As Integer                  'LINE COLUMN FROM INPUT BOM
Public LV As Integer                    'LV COLUMN FROM INPUT BOM
Public PartNumber As String             'PART# FROM INPUT BOM
Public Rev As String                    'REV FROM INPUT BOM
Public Description As String            'DESCRIPTION FROM INPUT BOM
Public Qty As Integer                   'QTY FROM INPUT BOM
Public Parent As String                 'THE COMPONENT ONE LEVEL ABOVE THIS ONE
Public SubComponents As Collection      'COLLECTION CONTAINING REFERENCE TO ALL SUBCOMPONENTS

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NAME:     AddSubComponent
' AUTHOR:   QHOLMBERG
' PURPOSE:
' The class keeps track of all the subcomponents off the assembly
'   through a collection. This function adds a subcomponent to
'   that collection.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Class_Initialize()
    Set Me.SubComponents = New Collection
End Sub

Public Function AddSubComponent(subComponent As clsComponent)
    Me.SubComponents.Add subComponent, subComponent.PartNumber
End Function

Open in new window

0
 

Author Comment

by:qholmberg
ID: 39975069
I realized I was not instantiating it last night (prior to seeing this). It was nice to have confirmation this morning. My resolution was even simpler than yours.

Change ...
Public SubComponents As Collection

Open in new window

To ...
Public SubComponents As New Collection

Open in new window

I do have an initialization routine that sets all the rest of those public properties. I could very easily move this functionality into it. I would have to rearrange the flow of my main program but it is very doable. Thanks for the suggestion.
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 39975118
Thanks for the points, I'm glad you got it working.  

Declaring variables As New can lead to problems further down the line, I tend to avoid that construct completely.  For what it's worth I don't think it will be an issue in your case but you might want to look into the subject further.  

A good explanation can be found here, starting towards the end of page 55..  http://books.google.co.uk/books?id=VnegO0pMYlIC&pg=PA56&lpg=PA56&dq=vba+dim+and+new+in+declaration+Steve+Bullen&source=bl&ots=DvBEMmSeVx&sig=E_MXmPFw3SXIXb4kfLfS9lKNwQ4&hl=en&sa=X&ei=jRZ5T7TID4vZiALQrd2nDg&redir_esc=y#v=onepage&q&f=false
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question