对于Excel VBA中的每个类属性

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

poss是一个集合，Clutch，Wiper等是列索引（从1开始）。这目前有效，但非常难看。我正在寻找一种方法来做这样的事......

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?

显然这不起作用，但是有没有人对如何在类中完成大致相同的方法或集合有任何建议？

4 个解决方案

#1

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.

这没什么难看的。现在AddFromRange方法有点难看，但是你只需要在编写它或数据发生变化时查看它。

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.

更新：吃数组而不是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

#2

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.

正如其他人所说，没有直接的方法来循环对象属性。我有一个电子表格，存储了许多我需要在运行时读取的值，与您的类似。我发现这样做的最好方法是使用CallByName方法，该方法允许您按名称设置或获取属性。

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://*.com/a/5707956/1733206

现在，有些人可能会说初始设置过度，但我经常添加和删除这些属性，因此使用代码执行操作会更加麻烦。因此，这种方法的优点是您可以经常修改您的属性数量，而无需更改此代码。您可以使用从这里使用CallByName的强大功能：https：//*.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):

然后，对于您的示例，我将在我的poss集合中执行以下操作（请注意，这不会执行任何错误检查等您可能想要执行的操作）：

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://*.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

以下是工作簿中的示例：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.

编辑：由于您将经常更改对象，我已经包含了另一个非常方便的模块，它实际上会根据工作表中的列标题为您编写pos类。这意味着如果你添加另一个列，它会将这些属性添加到对象！它假定所有属性都是字符串，但您可以修改以适应。

#3

VBA classes don't allow to define a constructor.

VBA类不允许定义构造函数。

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

#4

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

祝你好运，LC

#1