Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
I'm trying to learn about class modules. John Walkenach's book got me
started but left me wanting for much more. I created my own class module, MySheet. I want to store the name of each worksheet in my class module. I want to be able to do the following in a workbook that has sheets in it: Sub Example() Dim objMySheet as MySheet, x as Integer Set objMySheet = New MySheet objMySheet.Find 'Populates MySheet with the names of every worksheet For x = 1 to objMySheet.Count MsgBox objMySheet(x).Name 'Display a worksheet name Next End Sub What is the code for the class module so I can use the "objMySheet(x)." style of programming? Also, if you know of any GOOD web sites that go into detail about creating class modules I'd appreciate it :-) TIA! Toby Erkson http://www.bidata.net/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
I copy and pasted and type up some stuff so this is untested. But there is enough here to get you started with class modules.... 'Here's a class module ..... name it DBConnection: 'DatabaseConnectionInfo Option Explicit Dim propDatabase As String Dim propServer As String Dim propPort As String Dim propPrjName As String Dim propPassword As String Public Property Get database() As String database = propDatabase End Property Public Property Let database(newDatabase As String) propDatabase = newDatabase End Property Public Property Get server() As String server = propServer End Property Public Property Let server(newServer As String) propServer = newServer End Property Public Property Get Port() As String Port = propPort End Property Public Property Let Port(newPort As String) propPort = newPort End Property Public Property Get userName() As String userName = propPrjName End Property Public Property Let userName(newUserName As String) propPrjName = newUserName End Property Public Property Get password() As String password = propPassword End Property Public Property Let password(newPassword As String) propPassword = newPassword End Property Example use: Function GetConnectionInfo(book As Workbook, ID as Integer ) As DBConnection Dim db As New DBConnection Dim ID, DATABASE_COL, SERVER_COL, PASSWORD_COL, USERNAME_COL Dim sheet As Worksheet Set sheet = book.Sheets("DBConfiguration") DATABASE_COL = Utils.GetColumnNum(book.Sheets("DBConfiguration"), "DATABASE") SERVER_COL = Utils.GetColumnNum(book.Sheets("DBConfiguration"), "SERVER") PASSWORD_COL = Utils.GetColumnNum(book.Sheets("DBConfiguration"), "PASSWORD") USERNAME_COL = Utils.GetColumnNum(book.Sheets("DBConfiguration"), "USERNAME") db.database = sheet.Cells(ID, DATABASE_COL).Value db.server = sheet.Cells(ID, SERVER_COL).Value db.password = sheet.Cells(ID, PASSWORD_COL).Value db.userName = sheet.Cells(ID, USERNAME_COL).Value Set GetConnectionInfo = db End Function ' Use in a Collection context: Function DBConnectionCollection() as Collection Dim dbCollection As Collection Dim d1 as DBConnection Dim d2 as DBConnection Set d1 = Function GetConnectionInfo(ThisWorkbook, 1 ) Set d2 = Function GetConnectionInfo(ThisWorkbook, 2 ) dbCollection.Add d1 dbCollection.Add d2 Set DBConnectionCollection = dbCollection End Function Sub Test() Dim dbCollections as Collection Set dbCollection = DBConnectionCollection() Dim d1 as DBConnection Dim d2 as DBConnection Set d1 = DBConnections(1) Set d2 = dbCollections(2) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Toby,
Presumably, your class has a collection object in it, in which you store the sheets. Create a property called Item, as follows: Public Property Get Item(Ndx As Long) As Variant Item = pColl.Item(Ndx) End Property Where pColl is the collection object in your class module. If your collection contains object-type variables, use the Set keyword: Public Property Get Item(Ndx As Long) As Variant Set Item = pColl.Item(Ndx) End Property Then, go to the File menu in VBA, choose Remove MySheet and click "Yes" to the "Do you want to export?" dialog. Save the .cls file in some folder. Then, open that exported file in NotePad or your favorite text editor. Find your Item property, and add Attribute Item.VB_UserMemId = 0 as the first line in the procedu Public Property Get Item(Ndx As Long) As Variant Attribute Item.VB_UserMemId = 0 Item = pColl.Item(Ndx) End Property This sets the Item property as the default property of the class. Save and close the file in Notepad. Go back to VBA, File menu, and choose Import. The Attribute statement will not appear in the VBA Editor. Import your modified cls module to your project. You can then use code like For Ndx = 1 To 4 Cells(Ndx, "K").Value = YourClass(Ndx) Next Ndx If you want to add the ability to do a For Each loop for your class, add a procedure like the following. Public Property Get NewEnum() As IUnknown ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property where pColl is your class's collection object. Then, export the class, open it in NotePad, and add Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" as the first lines of the NewEnum procedu Public Property Get NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property Save and close the Notepad file, and then Import it back into your project. You can then do For Each loops: Dim V As Variant For Each V In YourClass Debug.Print V Next V For web pages, take a look at http://www.cpearson.com/excel/DefaultProperty.htm http://www.cpearson.com/excel/ClassModules.htm -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com (email address is on the web site) "Toby Erkson" wrote in message ... I'm trying to learn about class modules. John Walkenach's book got me started but left me wanting for much more. I created my own class module, MySheet. I want to store the name of each worksheet in my class module. I want to be able to do the following in a workbook that has sheets in it: Sub Example() Dim objMySheet as MySheet, x as Integer Set objMySheet = New MySheet objMySheet.Find 'Populates MySheet with the names of every worksheet For x = 1 to objMySheet.Count MsgBox objMySheet(x).Name 'Display a worksheet name Next End Sub What is the code for the class module so I can use the "objMySheet(x)." style of programming? Also, if you know of any GOOD web sites that go into detail about creating class modules I'd appreciate it :-) TIA! Toby Erkson http://www.bidata.net/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Thanks guys!
Chip, I looked at your first Property: Public Property Get Item(Ndx As Long) As Variant Item = pColl.Item(Ndx) End Property and thought, "That's it!". I'll go thru the code both of you posted and I'm confident I'll figure out what I need to do from there. Thank you both, I appreciate the help :-) -- Toby Erkson http://www.bidata.net/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Okay, I followed what you gave me and read your web pages. This now works:
For x = 1 To mySheet.Count MsgBox mySheet.Name Next However, this does not work: For x = 1 To mySheet.Count MsgBox mySheet(x).Name 'Doesn't work MsgBox mySheet.Item(x).Name 'What I'd like but doesn't work Next I borrowed a book, "Visual Basic 6", by Sybex, and read up on Class Modules but your web page was more helpful i.e. I don't recommend the book for class module beginners. What I have so far in my class module (this works): ----- Class Module ----- Private pItem As Long Private pName As String Public Property Get Item() As Long Let Item = pItem End Property Public Property Let Item(iIndex As Long) Let pItem = iIndex End Property Public Property Get Name() As String Let Name = pName End Property Private Property Let Name(stringName As String) Let pName = stringName End Property Public Sub Find() Dim WS As Worksheet, iIndex As Integer iIndex = 1 For Each WS In Worksheets If Left(WS.Name, 2) = "BI" Then 'Only get worksheet info where the first two sheet name characters are "BI" Item = WS.Index Name = WS.Name iIndex = iIndex + 1 End If Next Let Count = iIndex - 1 'I set up the Count property but didn't include here cuz it's not important End Sub ----- End ----- My testing code module. This will properly display the two worksheets out of 10 in my test workbook: ----- Code Module ----- Sub test() Dim mySheet As BISheets, x As Long Set mySheet = New BISheets BISheet.Find For x = 1 To mySheet.Count MsgBox mySheet.Name Next End Sub ----- End ----- The fact that the .Name and .Item even work was a wild leap on my part based upon Chip's web page example. Each one acts like an array, that is, they seem to 'remember' their value based on an invisible index -- from what I have gathered from my code and class modules. I'm very confused now. Suggestions? How would I get this line to work: MsgBox mySheet.Item(x).Name 'What I'd like but doesn't work -- Toby Erkson http://www.bidata.net/ "Chip Pearson" wrote in message ... Toby, Presumably, your class has a collection object in it, in which you store the sheets. Create a property called Item, as follows: Public Property Get Item(Ndx As Long) As Variant Item = pColl.Item(Ndx) End Property Where pColl is the collection object in your class module. If your collection contains object-type variables, use the Set keyword: Public Property Get Item(Ndx As Long) As Variant Set Item = pColl.Item(Ndx) End Property Then, go to the File menu in VBA, choose Remove MySheet and click "Yes" to the "Do you want to export?" dialog. Save the .cls file in some folder. Then, open that exported file in NotePad or your favorite text editor. Find your Item property, and add Attribute Item.VB_UserMemId = 0 as the first line in the procedu Public Property Get Item(Ndx As Long) As Variant Attribute Item.VB_UserMemId = 0 Item = pColl.Item(Ndx) End Property This sets the Item property as the default property of the class. Save and close the file in Notepad. Go back to VBA, File menu, and choose Import. The Attribute statement will not appear in the VBA Editor. Import your modified cls module to your project. You can then use code like For Ndx = 1 To 4 Cells(Ndx, "K").Value = YourClass(Ndx) Next Ndx If you want to add the ability to do a For Each loop for your class, add a procedure like the following. Public Property Get NewEnum() As IUnknown ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property where pColl is your class's collection object. Then, export the class, open it in NotePad, and add Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" as the first lines of the NewEnum procedu Public Property Get NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property Save and close the Notepad file, and then Import it back into your project. You can then do For Each loops: Dim V As Variant For Each V In YourClass Debug.Print V Next V For web pages, take a look at http://www.cpearson.com/excel/DefaultProperty.htm http://www.cpearson.com/excel/ClassModules.htm -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Oops, copied the wrong code. This is the correct code module I'm using:
----- Code Module ----- Sub test() Dim mySheet As BISheets, x As Long Set mySheet = New BISheets mySheet.Find For x = 1 To mySheet.Count MsgBox mySheet.Name Next End Sub ----- End ----- "Toby Erkson" <t o b y e a t b i d a t a d o t n e t wrote in message ... ... My testing code module. This will properly display the two worksheets out of 10 in my test workbook: ----- Code Module ----- Sub test() Dim mySheet As BISheets, x As Long Set mySheet = New BISheets BISheet.Find For x = 1 To mySheet.Count MsgBox mySheet.Name Next End Sub ----- End ----- The fact that the .Name and .Item even work was a wild leap on my part based... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Toby,
Here's some example code that works (I tested it): In a class module named CMySheet: '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Option Explicit Private Coll As Collection Public Sub Add(WhatSheet As Worksheet) On Error Resume Next 'ignore duplicate entries Coll.Add Item:=WhatSheet, key:=WhatSheet.Name End Sub Public Property Get Item(Ndx As Integer) As Worksheet Set Item = Coll(Ndx) End Property Public Property Get Count() As Integer Count = Coll.Count End Property Private Sub Class_Initialize() Set Coll = New Collection End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Now, you MUST (!) export the class module, open it in NotePad, and add the Attribute statement. You don't add the Attribute statement in the VBA editor (nor will it appear in the editor). Use NotePad to add the statement. In NotePad, your Item property should look like Public Property Get Item(Ndx As Integer) As Worksheet Attribute Item.VB_UserMemId = 0 Set Item = Coll(Ndx) End Property This is not an optional step. Once you've modified the class in NotePad, import it back into VBA. Then, in a standard code module, use code like '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Option Explicit Public MySheets As CMySheets Sub AAA() Dim WS As Worksheet Dim Ndx As Integer If MySheets Is Nothing Then Set MySheets = New CMySheets End If For Each WS In ThisWorkbook.Worksheets MySheets.Add WhatSheet:=WS Next WS For Ndx = 1 To MySheets.Count MsgBox MySheets(Ndx).Name Next Ndx End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com (email address is on the web site) "Toby Erkson" <t o b y e a t b i d a t a d o t n e t wrote in message ... Okay, I followed what you gave me and read your web pages. This now works: For x = 1 To mySheet.Count MsgBox mySheet.Name Next However, this does not work: For x = 1 To mySheet.Count MsgBox mySheet(x).Name 'Doesn't work MsgBox mySheet.Item(x).Name 'What I'd like but doesn't work Next I borrowed a book, "Visual Basic 6", by Sybex, and read up on Class Modules but your web page was more helpful i.e. I don't recommend the book for class module beginners. What I have so far in my class module (this works): ----- Class Module ----- Private pItem As Long Private pName As String Public Property Get Item() As Long Let Item = pItem End Property Public Property Let Item(iIndex As Long) Let pItem = iIndex End Property Public Property Get Name() As String Let Name = pName End Property Private Property Let Name(stringName As String) Let pName = stringName End Property Public Sub Find() Dim WS As Worksheet, iIndex As Integer iIndex = 1 For Each WS In Worksheets If Left(WS.Name, 2) = "BI" Then 'Only get worksheet info where the first two sheet name characters are "BI" Item = WS.Index Name = WS.Name iIndex = iIndex + 1 End If Next Let Count = iIndex - 1 'I set up the Count property but didn't include here cuz it's not important End Sub ----- End ----- My testing code module. This will properly display the two worksheets out of 10 in my test workbook: ----- Code Module ----- Sub test() Dim mySheet As BISheets, x As Long Set mySheet = New BISheets BISheet.Find For x = 1 To mySheet.Count MsgBox mySheet.Name Next End Sub ----- End ----- The fact that the .Name and .Item even work was a wild leap on my part based upon Chip's web page example. Each one acts like an array, that is, they seem to 'remember' their value based on an invisible index -- from what I have gathered from my code and class modules. I'm very confused now. Suggestions? How would I get this line to work: MsgBox mySheet.Item(x).Name 'What I'd like but doesn't work -- Toby Erkson http://www.bidata.net/ "Chip Pearson" wrote in message ... Toby, Presumably, your class has a collection object in it, in which you store the sheets. Create a property called Item, as follows: Public Property Get Item(Ndx As Long) As Variant Item = pColl.Item(Ndx) End Property Where pColl is the collection object in your class module. If your collection contains object-type variables, use the Set keyword: Public Property Get Item(Ndx As Long) As Variant Set Item = pColl.Item(Ndx) End Property Then, go to the File menu in VBA, choose Remove MySheet and click "Yes" to the "Do you want to export?" dialog. Save the .cls file in some folder. Then, open that exported file in NotePad or your favorite text editor. Find your Item property, and add Attribute Item.VB_UserMemId = 0 as the first line in the procedu Public Property Get Item(Ndx As Long) As Variant Attribute Item.VB_UserMemId = 0 Item = pColl.Item(Ndx) End Property This sets the Item property as the default property of the class. Save and close the file in Notepad. Go back to VBA, File menu, and choose Import. The Attribute statement will not appear in the VBA Editor. Import your modified cls module to your project. You can then use code like For Ndx = 1 To 4 Cells(Ndx, "K").Value = YourClass(Ndx) Next Ndx If you want to add the ability to do a For Each loop for your class, add a procedure like the following. Public Property Get NewEnum() As IUnknown ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property where pColl is your class's collection object. Then, export the class, open it in NotePad, and add Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" as the first lines of the NewEnum procedu Public Property Get NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" ' ' Supports FOR EACH loops. ' Set NewEnum = pColl.[_NewEnum] End Property Save and close the Notepad file, and then Import it back into your project. You can then do For Each loops: Dim V As Variant For Each V In YourClass Debug.Print V Next V For web pages, take a look at http://www.cpearson.com/excel/DefaultProperty.htm http://www.cpearson.com/excel/ClassModules.htm -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an index with a class module?
Wow...I'm a total newb for this class module stuff! Thanks for your help,
Chip. Yes, I understand the necessary changes needed for the "Attribute" edits and have implemented them. I'll work your example into my code and keep experimenting -- I'm pretty good at that. I think I may be in over my head except for the most basic of class modules. Looks like I'm gonna need to self-edumicate myself on this subject. I can see the potential behind learning this concept, just need to pound it into my old head :-) Thanks again and have a good weekend, -- Toby Erkson http://www.bidata.net/ "Chip Pearson" wrote in message ... Toby, Here's some example code that works (I tested it): In a class module named CMySheet: '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Option Explicit Private Coll As Collection Public Sub Add(WhatSheet As Worksheet) On Error Resume Next 'ignore duplicate entries Coll.Add Item:=WhatSheet, key:=WhatSheet.Name End Sub Public Property Get Item(Ndx As Integer) As Worksheet Set Item = Coll(Ndx) End Property Public Property Get Count() As Integer Count = Coll.Count End Property Private Sub Class_Initialize() Set Coll = New Collection End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Now, you MUST (!) export the class module, open it in NotePad, and add the Attribute statement. You don't add the Attribute statement in the VBA editor (nor will it appear in the editor). Use NotePad to add the statement. In NotePad, your Item property should look like Public Property Get Item(Ndx As Integer) As Worksheet Attribute Item.VB_UserMemId = 0 Set Item = Coll(Ndx) End Property This is not an optional step. Once you've modified the class in NotePad, import it back into VBA. Then, in a standard code module, use code like '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' Option Explicit Public MySheets As CMySheets Sub AAA() Dim WS As Worksheet Dim Ndx As Integer If MySheets Is Nothing Then Set MySheets = New CMySheets End If For Each WS In ThisWorkbook.Worksheets MySheets.Add WhatSheet:=WS Next WS For Ndx = 1 To MySheets.Count MsgBox MySheets(Ndx).Name Next Ndx End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
Class module | Excel Programming | |||
Class module | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |