Wondering where the information comes from...

For those of you who are obviously quite well versed in VBA, I only ask how
did you become so familiar with it? Was this brute force learning,
supervised college courses, on the job experience, or something else. As an
example, I am pretty sure that I need to start implmenting class modules, but
I honestly feel completely overwhelmed. If there is some resource that can
concisely and completely inform me as to their workings, I would appreciate
someone pointing the way for me.

wazooli

I am sure that everyone gets there by a different method, but the critical
factor IMO is actually using in a real-world situation. Courses, classes et
al are all very good to get you kick-started, but usage is where you develop
the real skills.

On the other point, what is it that makes you think you need classes? Whilst
very handy, and in some rare cases absolutely imperative, you can often
achieve your objective without them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
news:2C1728B7-1F1C-491C-8A03-EE68A58EB9C1@microsoft.com...
> For those of you who are obviously quite well versed in VBA, I only ask
how
> did you become so familiar with it? Was this brute force learning,
> supervised college courses, on the job experience, or something else. As
an
> example, I am pretty sure that I need to start implmenting class modules,
but
> I honestly feel completely overwhelmed. If there is some resource that
can
> concisely and completely inform me as to their workings, I would
appreciate
> someone pointing the way for me.
>
> wazooli

MSDN Link
http://msdn.microsoft.com/library/de...hatisclass.asp

Chip Pearson's site
http://cpearson.com/excel/codemods.htm

A previous post from Chip on the subject:-
There is far to much to say about Class Modules and Objects for a simple
newsgroup post, so I'll try to cover a few of the basics.

Basically, a Class is the definition of an Object. The word "object" is
deliberately vague. And object is anything that you want to design. It is
defined entirely (mostly) by its properties, methods, and events. In
Excel, there are hundreds of "built-in" objects, all defined by class
modules. The "class" is the definition of an "object". For example, a
Worksheet is an object. And there is a class module which defines just what
a Worksheet really is. There are various properties of a Worksheet object
(e.g., Visible). Properties simply define and set various attributes.
Think of properties as "Adjectives" which describe an object. An object
also has Methods. Methods are the "Verbs" of objects. For example, a
Worksheet object has an Activate method. This causes something to happen.
Finally there are Events. I can't think of a good grammatical analogy for
events. Essentially, Events are how an object tells the rest of the world
that something happened. For example, in a Worksheet object, there is a
Change event. This is the Worksheet object's way of telling the rest of
world, "Hey, look at me, I changed". The rest of the world can ignore that
event, or it may take action. But the world has been told that object has
done something (or had done something to it).

Now, you use Class Modules to create your own objects. Suppose you were
writing an application that was used for employee tracking. Using a class
module, you would define your own object called "Employee". This class
would define a single, generic, employee. With the DIM and SET statement,
you can create a specific employee, based on the "template" or "definition"
of a generic employee. The Employee class would have several Properties,
such as Name, Address, and Salary. It could also have methods, such as
Promote, GiveRaise, and Fire. In your application, the Promote method would
do the same things -- e.g., increasing the Salary property, updating a
central database, sending an email to another department to buy him a nicer
computer, etc. These actions are all the same whenever you Promote any
employee. By using a Class Module to define a "generic" employee, you only
have to write the code once. Then to work with a *specific* employee, you
just call the methods for that employee:

Dim ThisEmp As CEmployee
' more code
Set ThisEmp = New CEmployee
ThisEmp.Name = "John Smith"
' more code
ThisEmp.Promote

All of the code related to the Promote event is contained in the Class
modules (the definition of any employee), so you can simply call the Promote
method. Once you've defined the Class, you never have to worry about what
Promote actually does.

Here's another way to think about it. In the Worksheet object, there is a
PrintOut method. Within the PrintOut method, there is all the code that
actually formats the worksheet for printing, determines what printer you
have, and actually does all the work of printing the sheet. As a VBA
programmer, you don't have to worry about any of that. You simply call
PrintOut, and let that do all the work for you. You don't have to worry
about what sort of printer the user has, whether it can print color, and a
hundred other things. You just call PrintOut and let the Object do all the
work.

Class Modules let you create you own objects, or extend the functionality of
other, existing objects. They are very useful because they allow you to
write the code once, and then simply create new objects based on the class
(think of it like a blueprint for a house). It is write the code once, and
use it many times.

For example, I have a class module that extends the functionality of a
standard list box. The standard list box doesn't have a MoveUp method,
which simply moves the selected item one row up in the list. By using a
Class Module, I added a MoveUp method (as well as MoveDown, MoveToTop,
MoveToBottom, etc). I wrote that class one time. Now, whenever I need to
use "better" list boxes in my applications, I just use that Class. I don't
have to "re-invent the wheel" for every application I write.

This just scratches the surface of what a Class is and how to use them. If
you've ever heard the term "object oriented", Classes are the foundation of
this entire design philosophy.

-----------------------------------------------------
And from the same post, by Stephen Bullen:-
-----------------------------------------------------
Just to provide the opposite end of the spectrum to Chip's excellent
answer, class modules can also be though of as user-defined types (UDT) on
steroids. A simple UDT can be used to store related information about a
particular thing, such as Chip's employee:

Type Employee
Name As String
DOB As String
Grade As String
Salary As Double
End Type

If you wanted to do stuff with an employee, you'd use a normal procedure
somewhere:

Sub RaiseEmployee(uEmp As Employee, sNewGrade As String)
'Validate Grade
'...

uEmp.Grade = sNew Grade
'Do stuff to work out new salary etc.
'...
End Sub

Sub FireEmployee(uEmp As Employee)
uEmp.Grade = "F"
uEmp.Salary = 0
End Sub

Sub SetSalary(uEmp As Employee, dNewSalary As Double)
'Validate Salary
'...

'Does new salary mean a new grade?
'...
End Sub
etc.

That's fine as far as it goes and you can create some great programs
without ever using class modules. The main thing wrong with it is that the
*data* for the object (i.e. the contents of your UDT) is separated from the
*actions* that are performed on the data (the RaiseEmployee and
FireEmployee subs). Hence, you have to be very careful that the same
validation is performed in each sub and that one sub doesn't alter the data
in a way that will cause another sub to fail; this is often the cause of
some of the hardest bugs to find - logic problems.

If you use a class module instead, you can include the validation and other
functionality *with* the data; to the extent that the data can *not* be
changed unless it's valid. You can think of it as that the 'Grade'
property of the Employee (for example) can validate *itself* and can refuse
to be updated, or it can know *itself* that when it changes to a valid new
grade, it needs to change the salary too.

In the example above, with two simple procedures, think what would happen
if we had to add another check before changing the grade, or introduce a
new action to be performed if the grade is changed (such as notifying their
manager). In the procedural approach, we'd have to change two or three
routines to handle it - i.e. wherever the grade is set. In the clas module
approach, it is simply another action to be performed by the 'grade'
property *itself* - none of the other code needs to know about it.

i.e:

Dim msGrade As String 'Data that only code in the class can 'see'

'Property to read the grade
Public Property Get Grade() As String: Grade = msGrade: End property

'Property to set the grade
Public Property Let Grade(sNew As String)

If Not sNew Is Valid Then
Err.Raise "Not a valid grade"
Exit Property
End If

'Grade is valid, so we can safely store it
msGrade = sNew

'Now what else do we need to do when the grade changes?
Select Case sNew
Case "F"
'Being fired, better ask for a redundancy slip
Salary = 0
Case "M"
'Being made a manager, better ask for a better car
'Increase the Salary too
Case "D"
'Being demoted, schedule for more frequent reviews
'Decrease the Salary
'etc
End Select
End Property

Public Sub Fire()
Grade = "F"
End Sub

Now, everything that needs to be done when the grade is changed has been
made an *integral* part of changing the grade - there's no way that the
grade can be changed by anywhere else in the system without those checks
and actions happening.

Really, though, it just boils down to a different design and development
style, and one that hopefully takes us further down the road of improved
code reuse, more stability and fewer opportunities for bugs to creep in.

The hardest thing to work out, though, is to decide which functionality
should be 'in' the class module and which should be on the outside, but
using the class module. For example, do we have a '.Fire' method within
the class, or a Fire(oEmp As Employee) procedure outside that just sets the
grade to "F"?

I find that I'm using class modules more and more; it's almost at the stage
where if I'm asked "Why use a class module", my reply is "Why not?"

--------------------------------------------------------
And then just for the hell of it, Chip's addendum after reading Stephen's
comments:-
Chip Pearson May 18 2000, 12:00 am
--------------------------------------------------------

Newsgroups: microsoft.public.excel.programming
From: "Chip Pearson" <c...@cpearson.com> - Find messages by this author
Date: 2000/05/18
Subject: Re: Just what is a Class Module?
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

"Stephen Bullen" wrote

> class modules can also be thought of as user-defined types (UDT) on
> steroids

Excellent. You can think of Classes as "UDTs with verbs", or UDTs that "do
things".

> I find that I'm using class modules more and more; it's almost at the
stage
> where if I'm asked "Why use a class module", my reply is "Why not?"


Very true. I've found that I do most things now in classes. Write once,
instantiate the class, and it is all done. And once I figured out the
"Implements" business, things got even better. (The help files are rather
poor, here. You need to read MSDN msdn.microsoft.com) I'm sure there is
some overhead involved in creating new objects, but RAM is cheap and the
reliability and maintenance considerations exceed the resource usages.

> code reuse, more stability and fewer opportunities for bugs to creep in.


From a practical matter, this cannot be over-estimated or over-stated.. Far
too many bugs and problems are introduced by duplicated code. Classes
allow you to write the code once, debug it there, and only have to maintain
one procedure (or set of procedures). For "professional quality"
applications, this is absolutely essential. As Stephen points out, a
well-designed object model, accomplished with the use of well-thought-out
class modules, will tremendously reduce the potential for bugs in your
application.

If you have VB5 or 6, you have a Class Builder add-in. Learn it and love
it. It is not perfect, but can simplify the creation of your objects. You
can then Import these modules into your Excel VBProject.

> The hardest thing to work out, though, is to decide which functionality
> should be 'in' the class module and which should be on the outside, but
> using the class module. For example, do we have a '.Fire' method within
> the class, or a Fire(oEmp As Employee) procedure outside that just sets
the
> grade to "F"?


Indeed, that is a "design philosophy" question. The answer is based on the
developer's requirements, not purely technical considerations. There is no
simple "right" way to decide this. It depends on what "Firing" an employee
really means. Suppose you have three categories of employees: Executives,
Supervisors, and Hourly. The process of "Firing" each type of employee may
be quite different. In Excel2000 (VBA6), you can Implement the right class
to have either a generic "Fire" method if that is appropriate, or a
particular "Fire" method for each type of employee, and automatically have
the "Fire" method for the appropriate Type of worker. This functionality
was added in Excel2000 (VBA6) and is not available in Excel97 (VBA5).

Admittedly, it is a bit confusing until you get used to it. But then,
you've go a great tool at your disposal. Chapter 9 in the VB6 Programmer's
Guide has some nice basic info about all of this. But to really learn about
this, just experiment and play.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
news:2C1728B7-1F1C-491C-8A03-EE68A58EB9C1@microsoft.com...
> For those of you who are obviously quite well versed in VBA, I only ask
how
> did you become so familiar with it? Was this brute force learning,
> supervised college courses, on the job experience, or something else. As
an
> example, I am pretty sure that I need to start implmenting class modules,
but
> I honestly feel completely overwhelmed. If there is some resource that
can
> concisely and completely inform me as to their workings, I would
appreciate
> someone pointing the way for me.
>
> wazooli

I think I need classes because I would like to have a bunch of combo boxes
that all act the same, but get populated form different places. In case you
haven't been following along in my arduous and excruciating learning process,
here is a brief synopsis of what I am attempting:

I have a sheet with several columns, describing all of the experiments I
have performed thus far at the company I work at. Besides several columns
that contain dates, lot numbers, and application codes, I have a fourth
column that contains descriptors of the experiments. There is a subclass of
experiments that are rather complicated. I currently have combo boxes for
these complicated experiments that contain filenames (retrieved from an
auxiliary sheet in the same workbook). With the help of yourself, as well as
Tom Ogilvie, I have been able to write sub calls that enable me to open
whichever workbook is chosen in the drop down. What I would like to do now
is to have a secondary combo box open for any given first choice, that is
populated with a listing of sheets within the chosen workbook. The reason
for this is that some of these sheets have many thousands of rather tedious
caluclations, and they tend to bog my machine down. The need for classes is
the result of not wanting to have to 'hard code' in the names of sheets. I
want a class module that will create the sub-combo box, populate it based
upon the file name in the first combo box, and enable me to choose the
appropriate worksheet. I woudl then like the secondary combo box to
disappear. I hope this clears things up, and helps to shed some light on why
I feel so overwhelmed. I feel I am trying to bite off more than I can chew,
hence the call for guidance.

wazooli

"Bob Phillips" wrote:

> I am sure that everyone gets there by a different method, but the critical
> factor IMO is actually using in a real-world situation. Courses, classes et
> al are all very good to get you kick-started, but usage is where you develop
> the real skills.
>
> On the other point, what is it that makes you think you need classes? Whilst
> very handy, and in some rare cases absolutely imperative, you can often
> achieve your objective without them.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
> news:2C1728B7-1F1C-491C-8A03-EE68A58EB9C1@microsoft.com...
> > For those of you who are obviously quite well versed in VBA, I only ask
> how
> > did you become so familiar with it? Was this brute force learning,
> > supervised college courses, on the job experience, or something else. As
> an
> > example, I am pretty sure that I need to start implmenting class modules,
> but
> > I honestly feel completely overwhelmed. If there is some resource that
> can
> > concisely and completely inform me as to their workings, I would
> appreciate
> > someone pointing the way for me.
> >
> > wazooli
>
>
>

Wazooli,

I gave a response a few days ago that allows controlling multiple comboboxes
from a single control class. You can read that thread on Google at
http://tinyurl.com/6a6w7. There was also a previous related question
http://tinyurl.com/64oed at and http://tinyurl.com/3mje3.

However, I think you want dynamic comboboxes for what you describe. Check
out this web page http://www.xldynamic.com/source/xld.Dropdowns.html.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
news:B95065BE-8041-4067-AEF9-C1774784E0E8@microsoft.com...
> I think I need classes because I would like to have a bunch of combo boxes
> that all act the same, but get populated form different places. In case
you
> haven't been following along in my arduous and excruciating learning
process,
> here is a brief synopsis of what I am attempting:
>
> I have a sheet with several columns, describing all of the experiments I
> have performed thus far at the company I work at. Besides several columns
> that contain dates, lot numbers, and application codes, I have a fourth
> column that contains descriptors of the experiments. There is a subclass
of
> experiments that are rather complicated. I currently have combo boxes for
> these complicated experiments that contain filenames (retrieved from an
> auxiliary sheet in the same workbook). With the help of yourself, as well
as
> Tom Ogilvie, I have been able to write sub calls that enable me to open
> whichever workbook is chosen in the drop down. What I would like to do
now
> is to have a secondary combo box open for any given first choice, that is
> populated with a listing of sheets within the chosen workbook. The reason
> for this is that some of these sheets have many thousands of rather
tedious
> caluclations, and they tend to bog my machine down. The need for classes
is
> the result of not wanting to have to 'hard code' in the names of sheets.
I
> want a class module that will create the sub-combo box, populate it based
> upon the file name in the first combo box, and enable me to choose the
> appropriate worksheet. I woudl then like the secondary combo box to
> disappear. I hope this clears things up, and helps to shed some light on
why
> I feel so overwhelmed. I feel I am trying to bite off more than I can
chew,
> hence the call for guidance.
>
> wazooli
>
> "Bob Phillips" wrote:
>
> > I am sure that everyone gets there by a different method, but the
critical
> > factor IMO is actually using in a real-world situation. Courses, classes
et
> > al are all very good to get you kick-started, but usage is where you
develop
> > the real skills.
> >
> > On the other point, what is it that makes you think you need classes?
Whilst
> > very handy, and in some rare cases absolutely imperative, you can often
> > achieve your objective without them.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
> > news:2C1728B7-1F1C-491C-8A03-EE68A58EB9C1@microsoft.com...
> > > For those of you who are obviously quite well versed in VBA, I only
ask
> > how
> > > did you become so familiar with it? Was this brute force learning,
> > > supervised college courses, on the job experience, or something else.
As
> > an
> > > example, I am pretty sure that I need to start implmenting class
modules,
> > but
> > > I honestly feel completely overwhelmed. If there is some resource
that
> > can
> > > concisely and completely inform me as to their workings, I would
> > appreciate
> > > someone pointing the way for me.
> > >
> > > wazooli
> >
> >
> >