对于 Excel VBA 中的每个类属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17178461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
For Each Class Property in Excel VBA
提问by Al.Sal
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?
显然这行不通，但是有人对如何在类中创建一个方法或集合来实现大致相同的效果有任何建议吗？
采纳答案by Dick Kusleika
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.
更新：吃数组而不是范围的替代方法。
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
回答by CuberChase
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 CallByNamemethod which allows you set or get a property by name.
正如其他人所说，没有直接的方法来循环对象属性。我有一个电子表格，其中存储了许多我需要在运行时读取的值，类似于您的。我发现最好的方法是使用CallByName允许您通过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 CallByNamefrom here: https://stackoverflow.com/a/5707956/1733206
现在，有些人可能会说初始设置太过分了，但我经常添加和删除这些属性，因此对代码进行同样的操作更加麻烦。所以这种方法的美妙之处在于您可以经常修改您的属性数量而无需更改此代码。您可以使用CallByName从这里使用的很棒的功能：https: //stackoverflow.com/a/5707956/1733206
Then for your example, I would do the following in my posscollection (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://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
以下是工作簿中的示例：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 writethe posclass 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基础上在工作表中的列标题类你。这意味着如果您添加另一列，它会将这些属性添加到对象中！它假定所有属性都是字符串，但您可以修改以适应。
回答by dennythecoder
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
回答by stenci
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
