VBA - "subobject" of a class

Question

I want to build the structure of skills and those skills areas. For example: skill multiplication in the maths area.

My areas have some properties (name or weight), and my skills also have properties (name, weight, rank and so on).

I would like to use the following syntax:

Areas(1).Skills(2).Name = "solving equations"

Is it possible? Thanks to your help I can now use Areas(1).Name in my code and now I'm looking for help with the next step.

I suppose if I have Areas(1).Skill.Name syntax, I can handle with adding that index for skills, so the problem is exactly how to create a "subobject" of a class (using Area.Skill.Name still having the Area as an object)?

I would guess you have an `Area` class. If so, you need to add a `Skills` collection containing `Skill` objects to this class. — Brian M Stafford, Aug 27 '18 at 14:37

score 0 · Answer 1 · answered Aug 27 '18 at 16:09

In order to do that you first need to insert to class modules into you VBA project.

In VBE right clikc your VBA project: Insert > Class module.

Add two class modules:

First you'll call Skill - in properties you'll change the name to Skill. Double click it and enter this code there:
```
Public Name As String
Public Weight As Double
Public Rank As Long
'any other fields you want
```
Second class module - again in properties change the name to Area. From your description I assumed that you want to have collection of skills there. In order to do that, you need to click Tools from ribbon, go to References and add Microsoft.Scripting.Runtime. Then paste the code for this class:
```
Public Name As String
Public Weight As Double
Private pSkill As Collection

Property Get Skills() As Collection
    Set Skill = pSkill
End Property

Property Let Skills(arg As Collection)
    Set pSkill = arg
End Property
```

Note that we don't specify type of elements in pSkill, so you need to be careful when dealing with it.

After all this is done, you can use it as you want, like in this code:

Sub s()
    Dim s1 As Skills, s2 As Skills
    Set s1 = New Skill
    Set s2 = New Skill
    s1.Name = "solving equations"
    s1.Weight = 2.03
    s1.Rank = 1
    s2.Name = "counting to ten"
    s2.Weight = 1.01
    s2.Rank = 2

    Dim a(1) As Area, someSkills As Collection
    Set someSkills = New Collection

    someSkills.Add s1
    someSkills.Add s2

    Set a(0) = New Area
    a(0).Skill = someSkills

    MsgBox a(0).Skills(1).Name

End Sub

VBA - "subobject" of a class

1 Answers1