For Each Class Property in Excel VBA

Question

I have some code that looks like this:

pos.Clutch = sh2.Cells(R, Clutch)
pos.Wiper = sh2.Cells(R, Wiper)
pos.Alternator = sh2.Cells(R, Alternator)
pos.Compressor = sh2.Cells(R, Compressor)
...
pos.Telephone = sh2.Cells(R, Telephone)
poss.Add pos

poss is a collection, and Clutch, Wiper etc. are column indexes (starting from 1). This currently works but is very ugly. I'm looking for a way to do something like this...

Do While i <= classProperty.count
    For each classProperty in pos
        classProperty = sh2.Cells(R + 1, i)
    Next classProperty
Loop

Obviously that wouldn't work but does anyone have any advice on how to make a method or collection within a class that would accomplish roughly the same?

Since your indexes start from 1, it may be tidier to use a subcollection instead of a class so you can loop over column indexes with `pos.add sh2.Cells(R,i)`. Then use the Column index to access the value eg `pos(Clutch)`. — lori_m, Jun 18 '13 at 22:15

Dick Kusleika · Accepted Answer · 2013-06-21T20:43:06.933

4

I don't know of a good way. The only reason it's ugly is because you haven't hidden it in a class yet. Take this procedure

Sub Main()

    Dim clsPos As CPos
    Dim clsPoses As CPoses

    Set clsPoses = New CPoses
    Set clsPos = New CPos

    clsPos.AddFromRange Sheet1.Range("A10:E10")
    clsPoses.Add clsPos

End Sub

Nothing ugly about that. Now the AddFromRange method is a little ugly, but you only have to look at that when you write it or when you're data changes.

Public Sub AddFromRange(ByRef rRng As Range)

    Dim vaValues As Variant

    vaValues = rRng.Rows(1).Value

    Me.Clutch = vaValues(1, 1)
    Me.Wiper = vaValues(1, 2)
    Me.Alternator = vaValues(1, 3)
    Me.Compressor = vaValues(1, 4)
    Me.Telephone = vaValues(1, 5)

End Sub

Update: Alternative method for eating an array instead of a Range.

Public Sub AddFromArray(vaValues as Variant)

    Me.Clutch = vaValues(1, 1)
    Me.Wiper = vaValues(1, 2)
    Me.Alternator = vaValues(1, 3)
    Me.Compressor = vaValues(1, 4)
    Me.Telephone = vaValues(1, 5)

End Sub

edited Jun 21 '13 at 20:43

answered Jun 18 '13 at 21:32

Dick Kusleika

32,673
4
52
73

I'll try this too. The setup is easier than what @CuberChase suggested and adding new properties is still easy, kind of. – Al.Sal Jun 19 '13 at 13:21
Hm. @dickkusleika I changed my code around so that the entire range is dumped into an array. How can I make .AddFromRange still valid with an array? – Al.Sal Jun 21 '13 at 20:35
I would make a new method for eating an array. I'm not a fan of all-purpose methods. See update. – Dick Kusleika Jun 21 '13 at 20:43
Ah, thanks for your very timely reply. I'll see how well this works next week. What exactly do you mean by "all-purpose methods"? What are the disadvantages? – Al.Sal Jun 21 '13 at 20:50
I mean methods that can consume anything: range, array, collection, dictionary. They get cluttered and hard to maintain. – Dick Kusleika Jun 23 '13 at 18:18
That makes sense, trying to fit the same method to different things would not only be difficult but hard to follow. Thank you again for your input; I still have a lot to learn but I'm getting the feel for good flow control, etc. – Al.Sal Jun 24 '13 at 12:34
+1 Dick. I usually try to fake enumeration of the class properties by creating something like a `PropertiesOf` and having a Get and let/set item on them. see [my example](http://stackoverflow.com/questions/20489594/data-structure-which-needs-to-be-filled-in-vba?lq=1) if interested – May 01 '14 at 08:14

score 4 · Answer 2 · edited May 23 '17 at 12:33

As others have stated there is no direct way to loop through an object properties. I have a spreadsheet which stores many values which I need to read in at run time, similar to yours. The best method I have found to do this is by using the CallByName method which allows you set or get a property by name.

Now, some might say the initial set up is overkill, but I frequently add and remove these properties so doing likewise with code is even more hassle. So the beauty of this method is you can frequently modify your number of properties without having to change this code. You can use the awesome functions that make use of CallByName from here: https://stackoverflow.com/a/5707956/1733206

Then for your example, I would do the following in my poss collection (note this doesn't do any error checking etc which you may like to do):

Public Sub ReadInData()
    Dim vInputs As Variant, ii As Integer, jj As Integer, cp As pos
    Dim sPropertyName As String, vPropertyValue As Variant

    'Raead in the data.  I've set it from the activesheet, you can do it how you like
    With ActiveSheet
        vInputs = .Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value2
    End With

    'Look through the rows of data, one row per 'pos' object
    For ii = LBound(vInputs, 1) + 1 To UBound(vInputs, 1)

        'Set up your object
        Set cp = New pos

        'Loop through the columns of data eg Clutch, wiper, etc
        For jj = LBound(vInputs, 2) To UBound(vInputs, 2)
            'Put in seperate variables so its easy to see what's happening
            sPropertyName = vInputs(1, jj)
            vPropertyValue = vInputs(ii, jj)

            'Use the callable method to set the property (from here: https://stackoverflow.com/a/5707956/1733206)
            Call SetProperty(sPropertyName, vPropertyValue, cp)
        Next jj

        Me.Add cp
        Set cp = Nothing
    Next ii
End Sub

Here is an example in a workbook: https://dl.dropboxusercontent.com/u/13173101/VBAObject.xlsm

Edit: Since you will be changing the object often, I've included another module which is really handy and will actually write the pos class for you based on the column headings in your worksheet. That means if you add another column it will add those properties to the object! It assumes that all properties are strings but you can modify to suit.

I'm going to see if this works- it should. I too have to add new properties often so this is an ideal solution. Thank you! — Al.Sal, Jun 19 '13 at 12:59
Glad it helps. I've done a slightly off topic edit which I'm pretty sure you'll want to check out. — CuberChase, Jun 19 '13 at 23:14

score 0 · Answer 3 · answered Jun 18 '13 at 21:29

VBA classes don't allow to define a constructor.

In the main module I would create a "creator":

  For R = R1 To R2
    pos.Add NewPos(Range("A" & R & ":E" & R)
  Next R

Function NewPos(R As Range) As classProperty
  Set NewPos = New ClassProperty
  NewPos.Init(R)
Exit Function

In the class:

Sub Init(R As Range)
  Clutch = R.Cells(1, 1)
  Wiper = R.Cells(1, 2)
  ...
End Sub

score 0 · Answer 4 · edited Jun 19 '13 at 12:49

Might be able to use some code like this. As is this prints off every procedure and property thought:

Function getPropCount(ClassName As String) As String 
   Dim classes, Class 
   Dim i As Integer 
   Dim strClass As String 
   Dim propCount As Integer

   For Each classes In Application.VBE.CodePanes
      If classes.CodeModule.Name = ClassName Then
         Set Class = classes
      End If 
   Next
   For i = 1 To Class.CodeModule.CountOfLines
      If Class.CodeModule.ProcOfLine(i, 1) <> strClass Then
         strClass = Class.CodeModule.ProcOfLine(i, 1)
         Debug.Print strClass
         propCount = propCount + 1
      End If 
   Next 
   getPropCount = propCount
End Function

Good luck, LC

For Each Class Property in Excel VBA

4 Answers4

Linked