Using an index with a class module?

[Toby Erkson]

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/

 

[gimme_this_gimme_that]

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

 

[Chip Pearson]

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 procedure:

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 procedure:

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]

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]

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/

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 procedure:

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 procedure:

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

 

[Toby Erkson]

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 -----

 

[Chip Pearson]

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)

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/

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 procedure:

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 procedure:

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

 

[Toby Erkson]

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,