对于Excel VBA中的每个类属性 [英] For Each Class Property in 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

b $ b

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

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?

推荐答案

我不知道一个好的方法。唯一的原因是它的丑陋是因为你还没有隐藏在一个类中。执行此过程

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

没有什么丑陋的。现在AddFromRange方法有点丑陋，但你只需要看看，当你写它或数据更改。

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

这篇关于对于Excel VBA中的每个类属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！