| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating working with Class
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
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 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
Again, Thank you VERY much..
Jim "Andy Pope" wrote: 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 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating working with Class
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating UDF by Class Module (Leo)? | Excel Discussion (Misc queries) | |||
| What is the advantage of creating this Class. | Excel Programming | |||
| Creating a collection in a class | Excel Programming | |||
| Class stops working | Excel Programming | |||
| Class module creating OLEObject | Excel Programming | |||
Hybrid Mode
