Creating working with Class

[JMay]

I have in Module1

Global MyGuys()

Public Type Person
strFName As String
strLName As String
strAddress As String
strCity As String
strState As String
strZip As String
End Type

Public Sub LoadGuys()
Dim MyGuys(3) As Person
For i = 0 To 3
MyGuys(i).strFName = Cells(i + 5, 1)
MyGuys(i).strLName = Cells(i + 5, 2)
MyGuys(i).strAddress = Cells(i + 5, 3)
MyGuys(i).strCity = Cells(i + 5, 4)
MyGuys(i).strState = Cells(i + 5, 5)
MyGuys(i).strZip = Cells(i + 5, 6)
Next i
End Sub

=======================

After I run Loadguys Macro (which loads variables from my ws

in the immediate window I enter:

? MyGuys(2).strAddress

But get r/t 9 - Subscript out of range..
Can someone tell me why? What am I omitting not
to be able to bring up mytext

TIA,
JIM

 

[Andy Pope]

Hi,

The problem is scope.
Although you declared MyGuys as global you also declared an array with
the same name in the procedure. So the code would run and if you used
the command in the immediate window whilst still in that procedure you
would have seen correct value.

try this revision.
'--------------------
Global MyGuys() As Person

Public Type Person
strFName As String
strLName As String
strAddress As String
strCity As String
strState As String
strZip As String
End Type

Public Sub LoadGuys()
ReDim MyGuys(3) As Person

For i = 0 To 3
MyGuys(i).strFName = Cells(i + 5, 1)
MyGuys(i).strLName = Cells(i + 5, 2)
MyGuys(i).strAddress = Cells(i + 5, 3)
MyGuys(i).strCity = Cells(i + 5, 4)
MyGuys(i).strState = Cells(i + 5, 5)
MyGuys(i).strZip = Cells(i + 5, 6)
Next i

End Sub
'--------------------

Not sure what the class connection is as this all appeared to be in a
standard code module.

Cheers
Andy

 

[JMay]

Thanks Andy,

This is as far as I've EVER gotten into user/defined classes, arrays, etc....
ooooooowwwww!!
Hope to use this as building block to further my ed
Jim

 

[JMay]

Andy, Thought of just one-more Q;
Over the years in the VBE - I've inserted only User-Forms OR Modules
(Standard).
In my crude-simple example - would or could it be realed to using the one
option I've never used -- the (insert) Class Module option?

 

[Andy Pope]

Here is a very quick working of you Type into a class.
In the class I used public variables for all but ZIP, which demonstrates
the Let/Get properties.

class code module named CPerson.
'-------------
Option Explicit

Public FirstName As String
Public LastName As String
Public Address As String
Public City As String
Public State As String
Private m_strZip As String
Public Property Let Zip(RHS As String)
m_strZip = RHS
End Property

Public Property Get Zip() As String
Zip = m_strZip
End Property
'-------------

Standard code module
'-------------
Option Explicit

Public m_colMyGuys As Collection

Public Sub LoadGuys()
Dim i
Dim clsPerson As CPerson

Set m_colMyGuys = New Collection
For i = 0 To 3
Set clsPerson = New CPerson

With clsPerson
.FirstName = Cells(i + 5, 1)
.LastName = Cells(i + 5, 2)
.Address = Cells(i + 5, 3)
.City = Cells(i + 5, 4)
.State = Cells(i + 5, 5)
.Zip = Cells(i + 5, 6)
End With

m_colMyGuys.Add clsPerson, CStr(m_colMyGuys.Count + 1)
Next i

End Sub

Sub DisplayTest()
' just to show usage of collection and class
Dim clsItem As CPerson

For Each clsItem In m_colMyGuys
Debug.Print clsItem.FirstName, clsItem.LastName, clsItem.Zip
Next

End Sub
'-------------

Personally I would create another class to handle the tasks currently
done by the collection but that is complicated for a 1st look see into
classes

Cheers
Andy

 

[JMay]

Again, Thank you VERY much..
Jim

Here is a very quick working of you Type into a class.
In the class I used public variables for all but ZIP, which demonstrates
the Let/Get properties.

class code module named CPerson.
'-------------
Option Explicit

Public FirstName As String
Public LastName As String
Public Address As String
Public City As String
Public State As String
Private m_strZip As String
Public Property Let Zip(RHS As String)
m_strZip = RHS
End Property

Public Property Get Zip() As String
Zip = m_strZip
End Property
'-------------

Standard code module
'-------------
Option Explicit

Public m_colMyGuys As Collection

Public Sub LoadGuys()
Dim i
Dim clsPerson As CPerson

Set m_colMyGuys = New Collection
For i = 0 To 3
Set clsPerson = New CPerson

With clsPerson
.FirstName = Cells(i + 5, 1)
.LastName = Cells(i + 5, 2)
.Address = Cells(i + 5, 3)
.City = Cells(i + 5, 4)
.State = Cells(i + 5, 5)
.Zip = Cells(i + 5, 6)
End With

m_colMyGuys.Add clsPerson, CStr(m_colMyGuys.Count + 1)
Next i

End Sub

Sub DisplayTest()
' just to show usage of collection and class
Dim clsItem As CPerson

For Each clsItem In m_colMyGuys
Debug.Print clsItem.FirstName, clsItem.LastName, clsItem.Zip
Next

End Sub
'-------------

Personally I would create another class to handle the tasks currently
done by the collection but that is complicated for a 1st look see into
classes

Cheers
Andy

 

[Chip Pearson]

I have what I think is a rather good introduction to classes at
http://www.cpearson.com/excel/Classes.aspx

It should get you started down the right path.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)