Indirect Accessing of Collection Items
 

I am trying to figure an easy way to access specific variables in a Class.
Consider having a Class module called "Phonebook" with Name and Address as
two of the variables. The Phonebook class also contains a €œFields€� array
variable containing the desired variables names (Name, Address). The Class
definition is at the end of this message.

My sample code fails on
Application.Evaluate("myPhonebook." & vItem)
with Error 2029.

Any suggestions on making this work? Is there a better way?
Thanks, - Pat


Sample code
--------------------------------------------------------------------------------
Option Explicit
Sub TestPhonebook()
Dim myPhonebook As cPhonebook
Dim vItem As Variant

'initialize myPhonebook content
Set myPhonebook = New cPhonebook
myPhonebook.Name = "myName"
myPhonebook.Address = "MyAddress"

'display myPhonebook content
For Each vItem In myPhonebook.Fields

Debug.Print vItem, Application.Evaluate("myPhonebook." & vItem)

Next vItem
End Sub
--------------------------------------------------------------------------------

cPhonebook class
--------------------------------------------------------------------------------
Option Explicit
Private FName As String
Private FAddress As String
Private FFields As Variant

'FName
Public Property Let Name(ByVal Value As String)
FName = Value
End Property
'
Public Property Get Name() As String
Name = FName
End Property
'
'FAddress
Public Property Let Address(ByVal Value As String)
FAddress = Value
End Property
'
Public Property Get Address() As String
Address = FAddress
End Property
'

'FFields
Public Property Get Fields() As Variant
Fields = FFields
End Property

Private Sub Class_Initialize()
FFields = Array("Name", "Address")
End Sub
--------------------------------------------------------------------------------

Indirect Accessing of Class Variables
 

My mistake, the Subject line should have been "Indirect Accessing of Class
variables"

"Dreiding" wrote:

I am trying to figure an easy way to access specific variables in a Class.
Consider having a Class module called "Phonebook" with Name and Address as
two of the variables. The Phonebook class also contains a €œFields€� array
variable containing the desired variables names (Name, Address). The Class
definition is at the end of this message.

My sample code fails on
Application.Evaluate("myPhonebook." & vItem)
with Error 2029.

Any suggestions on making this work? Is there a better way?
Thanks, - Pat


Sample code
--------------------------------------------------------------------------------
Option Explicit
Sub TestPhonebook()
Dim myPhonebook As cPhonebook
Dim vItem As Variant

'initialize myPhonebook content
Set myPhonebook = New cPhonebook
myPhonebook.Name = "myName"
myPhonebook.Address = "MyAddress"

'display myPhonebook content
For Each vItem In myPhonebook.Fields

Debug.Print vItem, Application.Evaluate("myPhonebook." & vItem)

Next vItem
End Sub
--------------------------------------------------------------------------------

cPhonebook class
--------------------------------------------------------------------------------
Option Explicit
Private FName As String
Private FAddress As String
Private FFields As Variant

'FName
Public Property Let Name(ByVal Value As String)
FName = Value
End Property
'
Public Property Get Name() As String
Name = FName
End Property
'
'FAddress
Public Property Let Address(ByVal Value As String)
FAddress = Value
End Property
'
Public Property Get Address() As String
Address = FAddress
End Property
'

'FFields
Public Property Get Fields() As Variant
Fields = FFields
End Property

Private Sub Class_Initialize()
FFields = Array("Name", "Address")
End Sub
--------------------------------------------------------------------------------

Indirect Accessing of Class Variables
 

I found the answer to my question. It's documented in and Q&A in this
discussion group. It's entitled "Call the property of a call using a
variable" dated 8/11/2008.

If there are better or alternate ways of doing this, please let me know.
Thanks,
- Pat

"Dreiding" wrote:

My mistake, the Subject line should have been "Indirect Accessing of Class
variables"

"Dreiding" wrote:

I am trying to figure an easy way to access specific variables in a Class.
Consider having a Class module called "Phonebook" with Name and Address as
two of the variables. The Phonebook class also contains a €œFields€� array
variable containing the desired variables names (Name, Address). The Class
definition is at the end of this message.

My sample code fails on
Application.Evaluate("myPhonebook." & vItem)
with Error 2029.

Any suggestions on making this work? Is there a better way?
Thanks, - Pat


Sample code
--------------------------------------------------------------------------------
Option Explicit
Sub TestPhonebook()
Dim myPhonebook As cPhonebook
Dim vItem As Variant

'initialize myPhonebook content
Set myPhonebook = New cPhonebook
myPhonebook.Name = "myName"
myPhonebook.Address = "MyAddress"

'display myPhonebook content
For Each vItem In myPhonebook.Fields

Debug.Print vItem, Application.Evaluate("myPhonebook." & vItem)

Next vItem
End Sub
--------------------------------------------------------------------------------

cPhonebook class
--------------------------------------------------------------------------------
Option Explicit
Private FName As String
Private FAddress As String
Private FFields As Variant

'FName
Public Property Let Name(ByVal Value As String)
FName = Value
End Property
'
Public Property Get Name() As String
Name = FName
End Property
'
'FAddress
Public Property Let Address(ByVal Value As String)
FAddress = Value
End Property
'
Public Property Get Address() As String
Address = FAddress
End Property
'

'FFields
Public Property Get Fields() As Variant
Fields = FFields
End Property

Private Sub Class_Initialize()
FFields = Array("Name", "Address")
End Sub
--------------------------------------------------------------------------------

Indirect Accessing of Class Variables
 

See the CallByName method.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

On Fri, 3 Oct 2008 17:26:02 -0700, Dreiding
wrote:

My mistake, the Subject line should have been "Indirect Accessing of Class
variables"

"Dreiding" wrote:

I am trying to figure an easy way to access specific variables in a Class.
Consider having a Class module called "Phonebook" with Name and Address as
two of the variables. The Phonebook class also contains a “Fields” array
variable containing the desired variables names (Name, Address). The Class
definition is at the end of this message.

My sample code fails on
Application.Evaluate("myPhonebook." & vItem)
with Error 2029.

Any suggestions on making this work? Is there a better way?
Thanks, - Pat


Sample code
--------------------------------------------------------------------------------
Option Explicit
Sub TestPhonebook()
Dim myPhonebook As cPhonebook
Dim vItem As Variant

'initialize myPhonebook content
Set myPhonebook = New cPhonebook
myPhonebook.Name = "myName"
myPhonebook.Address = "MyAddress"

'display myPhonebook content
For Each vItem In myPhonebook.Fields

Debug.Print vItem, Application.Evaluate("myPhonebook." & vItem)

Next vItem
End Sub
--------------------------------------------------------------------------------

cPhonebook class
--------------------------------------------------------------------------------
Option Explicit
Private FName As String
Private FAddress As String
Private FFields As Variant

'FName
Public Property Let Name(ByVal Value As String)
FName = Value
End Property
'
Public Property Get Name() As String
Name = FName
End Property
'
'FAddress
Public Property Let Address(ByVal Value As String)
FAddress = Value
End Property
'
Public Property Get Address() As String
Address = FAddress
End Property
'

'FFields
Public Property Get Fields() As Variant
Fields = FFields
End Property

Private Sub Class_Initialize()
FFields = Array("Name", "Address")
End Sub
--------------------------------------------------------------------------------