Array of properties in VBA

Question

I have 13 properties following similar syntax as the one below:

Public Property Get Town() As String
    Town = txtTown.Text
End Property

I would like to be able to use a loop and iterate through a collection of these properties instead of referencing each of the 13 properties. How would I go about creating an array of these prexisting properties. I would very much prefer for them to keep their meaningful names.

EDIT:

aSet IDCell = customerDBSheet.Range("CustomerDBEntryPoint").Offset(ID() - 1)
Dim properties()
properties = Array("ID()", "FirstName()", "LastName()", "Address 1()", "Address 2()",     "Town()", "Postcode()", "Phone()", "Email()", "Sex()", "Username()", "PasswordHash()")
For i = 0 To 11
IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))
Next i

I get an error on the line before last: IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))

Final Code:

Dim properties()
properties = Array("ID", "FirstName", "LastName", "Address1", "Address2", "Town", "Postcode", "Phone", "Email", "Sex", "Username", "PasswordHash")
For i = 0 To 11
IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbMethod))
Next i

The code used in the end, shown above specifically uses the CallByName function edited from Radek's answer as the property was converted to a function. Furthermore, the For loop needed to use a 0-based index. Also, an exception was thrown when the 4th optional parameter was an empty string literal.

Radek · Accepted Answer · 2012-02-23 22:53:54Z

2

You can iterate through array of property names:

Dim vProperties()
Dim vPropertyName

vProperties = Array("Town", "Street", "ZipCode")
For Each vPropertyName In vProperties
    '''Do something
Next

Now is the "tricky" part: "Do something" block have only vPropertyName set to consecutive property names. In order to access property value by its name from string variable use CallByName function:

...
For Each vPropertyName In vProperties
    CallByName MyObject1, vPropertyName, VbLet, "" 
Next

The second option iteration through "Controls" collection of UserForm:

Dim vControl As Control

For Each vControl In UserForm1.Controls
    If TypeName(vControl) = "TextBox" OR vControl.Name Like "MyPattern*" Then
        '''Do something
    End If
Next

EDIT:

Dim properties()
properties = Array("ID", "FirstName", "LastName", "Address1", "Address2", "Town", "Postcode", "Phone", "Email", "Sex", "Username", "PasswordHash")
For i = LBound(properties) To UBound(properties)
    IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))
Next i

I found few things in your code

Parentheses are unnecessary
Something is wrong witch properties "Address 1" and "Address 2". You can't define property with space inside the name
I believe that the LBound and UBound functions are little bit more handy than using explicit boundaries of array

edited Feb 23, 2012 at 22:53

answered Feb 19, 2012 at 22:46

Radek

1,0547 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kian Over a year ago

What does the MyObject1 refer to?

Radek Over a year ago

MyObject1 is reference to instance of the class where 13 properties are implemented

Kian Over a year ago

OK Thanks so it would be frmCustomerForm in this instance?

Radek Over a year ago

VBA dont clearly distinguish class and class instance for the Form objects/classes. But still CallByName for frmCustomerForm should works great.

Kian Over a year ago

"Run-time error '438': Object doesn't support this property or method"

|

Collectives™ on Stack Overflow

Array of properties in VBA

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related