My problem is that I am trying to create a reference to an object and pass
that objects address in a subroutine called examineData, which should
produce a msgbox detailing the information placed in the object "person".
I believe I am passing a reference address to my object I created in
subroutine testReference, to the subroutine examineData. But this module
wont compile.

From microsofts help files:

"You can combine declaring an object variable with assigning an object to it
by using the New keyword with the Set statement. For example:Set MyObject =
New Object ' Create and Assign"

'-------------------------------------------------------------------------
Type myData
age As Integer
wageEarner As Boolean
End Type



Sub testReference()
Dim person As myData

' here I believe I created the pointer to my object person
Set personPtr = New person

' here I am giving my object some data
person.age = 10
person.wageEarner = True

'here I pass my reference to my object person in the following
subroutine
examineData (personPtr)
End Sub


Sub examineData(aPerson As myData)

If (aPerson.wageEarner = True) Then
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
Else
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
End If
End Sub

Ian,

You are confusing Type variables, created with a Dim statement and a Type
statement, and Object variables, which are created with a Dim/New statement
and a class module. Also, you shouldn't think in terms of pointers when
working with VBA. VBA uses pointers under the hood, but as a programmer you
can't do much with pointers directly.

You can't use the New keyword with a variable declared as a Type. If you
want to use your own object variables, define them in a class module. In
your code, get rid of the Type declaration, and create a class module called
CMyData containing the following variable declarations

Public age As Integer
Public wageEarner As Boolean

Then, declare an object variable in the standard code module based on that
class with
Dim person As CMyData
To actually create an instance of CMyData, use
Set person = New CMyData

Finally, get rid of the parentheses when you pass person to the called
procedure. Change

examineData (person)
to
examineData person

When you pass object variables to a procedure, they are always passed by
reference. The ByVal and ByRef keywords indicate whether the reference
(pointer) is passed by reference or value, but it is always the reference
(pointer) that is passed, not the object itself.

So, in summary, your CMyData class looks like

Public age As Integer
Public wageEarner As Boolean

and your standard procedures should look like

Sub testReference()
Dim person As CMyData
Set person = New CMyData
person.age = 10
person.wageEarner = True
examineData person
End Sub

Sub examineData(aPerson As CMyData)
If (aPerson.wageEarner = True) Then
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
Else
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
End If
End Sub

I hope this clarifies things a little bit.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ian Stanborough" wrote in message
...
My problem is that I am trying to create a reference to an object and pass
that objects address in a subroutine called examineData, which should
produce a msgbox detailing the information placed in the object "person".
I believe I am passing a reference address to my object I created in
subroutine testReference, to the subroutine examineData. But this module
wont compile.

From microsofts help files:

"You can combine declaring an object variable with assigning an object to
it
by using the New keyword with the Set statement. For example:Set MyObject
=
New Object ' Create and Assign"

'-------------------------------------------------------------------------
Type myData
age As Integer
wageEarner As Boolean
End Type



Sub testReference()
Dim person As myData

' here I believe I created the pointer to my object person
Set personPtr = New person

' here I am giving my object some data
person.age = 10
person.wageEarner = True

'here I pass my reference to my object person in the following
subroutine
examineData (personPtr)
End Sub


Sub examineData(aPerson As myData)

If (aPerson.wageEarner = True) Then
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
Else
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
End If
End Sub

A user defined type is not an object.

You have to create a class module to create a person object using new.

Just use an array.

Type myData
age As Integer
wageEarner As Boolean
End Type



Sub testReference()
Dim person() As myData

redim person(1 to 1)


for personPtr = 1 to 5
redim preserve person(1 to personPtr)


' here I am giving my object some data
person(personPtr).age = Int(rnd()*10+1)
person(personPtr).wageEarner = iif(rnd()<.7,True,False)

examineData (Person(personPtr))
Next
End Sub


Sub examineData(aPerson As myData)

If (aPerson.wageEarner = True) Then
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
Else
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
End If
End Sub

--
Regards,
Tom Ogilvy


Ian Stanborough wrote in message
...
My problem is that I am trying to create a reference to an object and pass
that objects address in a subroutine called examineData, which should
produce a msgbox detailing the information placed in the object "person".
I believe I am passing a reference address to my object I created in
subroutine testReference, to the subroutine examineData. But this module
wont compile.

From microsofts help files:

"You can combine declaring an object variable with assigning an object to
it
by using the New keyword with the Set statement. For example:Set MyObject
=
New Object ' Create and Assign"

'-------------------------------------------------------------------------
Type myData
age As Integer
wageEarner As Boolean
End Type



Sub testReference()
Dim person As myData

' here I believe I created the pointer to my object person
Set personPtr = New person

' here I am giving my object some data
person.age = 10
person.wageEarner = True

'here I pass my reference to my object person in the following
subroutine
examineData (personPtr)
End Sub


Sub examineData(aPerson As myData)

If (aPerson.wageEarner = True) Then
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
Else
MsgBox "The age is " + Trim(Str(aPerson.age)) + Chr(13) + _
"They are wage earner"
End If
End Sub

I think you are getting confused with objects defined in a Class module
It looks like this would sit you :-

'-------------------------------------------------------------------------
Type myData
age As Integer
wageEarner As Boolean
End Type


'-----------------------------------
Sub testReference()
Dim person As myData
person.age = 10
person.wageEarner = True
MsgBox (person.age & vbCr & person.wageEarner)
End Sub
'-------------------------------------

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com