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)



On Sat, 15 Nov 2008 07:36:01 -0800, JMay
wrote:

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?

"JMay" wrote:

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

"Andy Pope" wrote:

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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info