Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    CLASS MODULE (Excel 2000)

    Hi all,

    If someone has the time, I want to understand Class Modules. I have read a bit about them, but I'm confused as to why we need them.
    Does anyone have a simple example, to show me how to use them?

    Thanks,
    Darryl.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    Chip Pearson's site has an example of how to use them for application events:

    http://www.cpearson.com/excel/AppEvent.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CLASS MODULE (Excel 2000)

    Getz & Gilbert's book, VBA Developer's Handbook will tell you more than you ever wanted to know about Class Modules. It has 3 chapters (150 pages) on class modules.

    Class modules are very useful. In a current project of mine, I have a ColorChooser class which takes a label control and when the user clicks on it, it displays a color pallet so that the user can select a color. Now, on any of my userforms if I want the user to be able to customize the color of something, I create a label without text and then create an instance of my ColorChooser class with that label. In this way I only have to setup the event handlers once in the class module instead of once each time that I create a label to setup a color.

    Class modules can also sometimes allow you to make major design changes without much code rewriting. Several years ago, I wrote a major Excel graphing template, that allowed a user to plot cross-sections of three-D objects that were described by csv files of measurments and metadata. I created a Class Module for these csv files that allowed me to get all of the information that the cross-sectional plotting modules needed. In this way I hid the structure of the input csv files from the plotting software. Just last week, I got an emergency phone call, they were going out in the field the next day to make measurements and they needed my cross-sectional plots, but the input csv files had a different format. Could I fix it? By this time I had forgotten that I had even written the code, but looking at it, I discovered that I only had to make several minor changes in the Class Module and the plot software never knew the difference.

    One last example: I use a Class Module as a repository for global information about a project. In this case, I only have one instance of the Class Module as a global object, but I use the Property get/let to do all of the error checking. So, if I want to know how many points wide a highway should be, it is oProject.HighwayWidth.

    Again, Getz & Gilbert's book does a much better job of describing Class Modules and as Jan Karel said, Chip Pearson's site has a useful example. I believe that the first Class Module that I wrote was based on his example. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    Thanks for your reply,

    The Book that I have now is Programming Excel 2003, Microsoft Press. I just finished a 3 day advanced course in Excel Programming, but the course didn't cover Class Modules. I did find that our instructors weakness pretty much the same as mine, as far as understanding VBA Programming. I have found that I have learned more in here than in any book, or course that I have taken, because it seems that users in here are in the real world using programming to fix real job related tasks. I tried Chip Pearsons example, thought it to be good example of a Class Module, but until I actually make my own, and use it, I am still a bit confused about Object variables. I am going to go out and buy that Getz & Gilbert's VBA Handbook, the book that I have now only has about 7 pages.

    Once again thanks.
    Darryl.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    If you want a good overview of Classes and Objects in VB6 generally, have a read through
    Object Oriented Programming in Visual Basic 6. There's about 60 pages of "good stuff" <img src=/S/free.gif border=0 alt=free width=30 height=15> online!

    Alan

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CLASS MODULE (Excel 2000)

    Here's example of using a Class module to combine controls. The UserForm generates five textboxes that are "linked" to spinners with the parameters in the workbook, but creates a Spinner object from the CSpinText Class Module to handle all of the events. Have Fun. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CLASS MODULE (Excel 2000)

    Found the question that got me started with Class Modules. It's in this thread. Note -- it the time, I called it a "gem," but now it sort of looks like a bad example, but it is interesting. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    I just finished a project where I used a class module. For my purposes I tend to use them to store data that is grouped and I want to keep this data together for later use. For instance I had information in several sheets that I need to track and later sort. I created a class called clsCustomer and gave it three public variables 'cName', 'cDept', 'cOrder'.
    <pre>public cName as String
    public cDept as String
    public cOrder as String
    ' these three go inside a class module called clsCustomer</pre>

    I can then create a variable of this class TYPE just like an integer, or string and use it to store my data. This is especially good for storing in collections. Example:
    <pre>' create a variable dataCustomer of type clsCustomer
    Dim dataCustomer as new clsCustomer
    ' now store data in this variable
    dataCustomer.cName = "Fred"
    dataCustomer.cDept = "Sales"
    dataCustomer.cOrder = "1890-A"</pre>

    So now I have all info about this customer in one variable (not three separate ones). I'll then store this in a collection and go fetch another customer list from my workbook. So let's say I fetch 10 customers and my collection is called colCustomer.
    <pre>Dim thisCustomer as clsCustomer ' new variable of TYPE clsCustomer
    for k=1 to colCustomer.count
    set thisCustomer = colCustomer(k)
    debug.print "Name: " & thisCustomer.cName
    debug.print "Department: " & thisCustomer.cDept
    debug.print "Order #: " & thisCustomer.cOrder
    ' do whatever you need to do with this data...
    next k</pre>

    The other neat thing is that intelli-sense will work for these new data types (the classes) that you created so when you use the dot notation (variable.field), it will pop up with the fields just like the built-in classes.

    So for me, I usually use a class when I need to accumulate multiple pieces of information about something. It's a way to organize the data inside one variable instead of having to create 10 variables for customer names, 10 for customer departments, and 10 more for customer orders. Instead of 30 total varibles, I'll create one collection that has 10 elements and each element is of type 'clsCustomer' which has 3 fields in it (in my example above).

    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: CLASS MODULE (Excel 2000)

    You could also use a custom type for this:

    Option Explicit

    Public Type Customer
    cName As String
    cDept As String
    cOrder As String
    End Type

    ' create a variable dataCustomer of type Customer
    Dim dataCustomer As Customer
    ' now store data in this variable
    With dataCustomer
    .cName = "Fred"
    .cDept = "Sales"
    .cOrder = "1890-A"
    End With

    Custom types provide IntelliSense too.

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    yes that's exactly what I was talking about. I do this in a class module (which makes sense to me as I've done a lot of C++ and Java). It's just creating your own custom data types which is all a class really is (in most basic terms). I think this is an easier to understand usage for beginners than showing them events.

    Deb

  11. #11
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: CLASS MODULE (Excel 2000)

    Umm....just gonna add another thanks

    Darryl.

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CLASS MODULE (Excel 2000)

    Deb
    You have just lit a light bulb for this programmer-hopeful. Thank you!
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •