vba modules

[tim lee]

My lecture book says that there are 4 types of modules -
workbook,worksheet,form,standard (gen code recorded macros available all
projects) and class modules. But when I insert a module and a procedure, it
does not ask me those kind of questions and apart from this workbook, other
workbook and personal, there are no distinguising features that I can see -
nor does the help throw any light on the matter. Can anyone explain to me
please?

[Nick Hodge]

Tim

I don't think that is strictly true, certainly chart is missing from that
list, but I wouldn't see these as different modules.

If you open the VBE (Alt+F11) and go to the insert menu. You will see two
types of module, Class Module and Module (Standard). You also see UserForm,
but this does not insert a UserForm 'module', it inserts a UserForm
'Object'.

Class Modules allow you to define your own objects, properties, events, etc.
For example, using these you can program events for Excel itself.
(Application). These are not supplied by 'out of the box' with XL. You
should visit www.cpearson.com for a better explanation and examples on Class
Modules.

Standard Modules contain the 'general' code (Subs and Functions) that will
run the 'general' program. That is, not specific to any particular object.
Often, code here is the 'core' of a program, off which other code, events,
etc are run. Standard Modules are used, for example, when you record code
with the macro recorder.

Modules exist behind several objects and controls in XL, but these are
really 'Class modules', pre-programmed in XL. For example, there are
modules behind Workbooks, Worksheets, Charts, UserForms and most controls
that can be placed on them. These allow you to program to respond to events
the user triggers by working with the object. For example, the Worksheet
has a change event. This 'fires' each time the user changes something on
*that* sheet. You can then program, for example, to change changed to red,
if it's negative, the cell value entered.

To try this yourself, either right click on a sheet in the project window of
the VBE (Alt+F11, top left), or on the sheet tab in the workbook, right
click and select 'View Code...'. In the resultant main pane, there are two
drop downs at the top. In the left one, select worksheet. You will
immediately see a code 'template' appear. (In this case Private Sub
Worksheet_SelectionChange(ByVal Target As Range). This is because the
selection_change event is the worksheets 'default' event.). Now, in the
right drop down, you will see a list of the events that the worksheet
responds to. Select Change and enter the code as shown

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "You changed cell " & Target.Address & " to " & Target.Value
End Sub

The parameter, 'Target' is a range object 'pointing' to the cell that was
changed, so by using this object's 'Address' and 'Value' properties, I can
tell which cell was changed and to what value.

Hopefully this and a visit to Chip's site, will broaden your understanding.

--
HTH
Nick Hodge
Microsoft MVP (Excel)
Southampton, England
nick_...@lineone.net

"tim lee" <t...@timlee.com.au> wrote in message
news:9sl6qs$n3a$1...@yeppa.connect.com.au...

[Chip Pearson]

Tim,

I think your book is a bit misleading, if not completely wrong. Really, there
are only two types of modules -- class modules and standard code modules.
Classes, forms, ThisWorkbook, and the Sheet modules are all really just class
modules (the UserForms are coupled with something called a Designer, which is
the design-time, visual, representation of the form -- the thing where you drag
and drop controls).

You can insert into a project only a Class module, a Userform, or a standard
code module. The ThisWorkbook module is the class module behind the workbook
itself, and so you can't create an additional ThisWorkbook module, and you can't
remove it from the project. A Workbook has one, and only one, ThisWorkbook
module. Similarly, each sheet (Worksheet or Chart Sheet) has exactly one Sheet
module. You can't add new sheet modules (other than by adding a new sheet to the
workbook), and you can't remove a sheet module (other than by removing the sheet
itself).

There isn't much difference between the types of modules when you're writing
code. They all look and feel pretty much the same. The primary difference is
what happens at run time. You can execute code in a standard code module simply
by calling it by procedure name. E.g, Call MyProcedure (X, Y). But before you
can execute code within a class (a Class module, a Form, ThisWorkbook, or a
Sheet module), you must create an "instance" of the class. This loads a "copy"
of the class into memory. With standard class modules, you do this with code
like

Dim C As Class1
Set C = New Class1

The Set = New code is where the "instance" of the class is actually created.
Once the instance is created, you call its procedures, called methods and
properties, with code like

C.MyProc X, Y

If you tried to execute this line of code without first doing the Set/New
statement, you'd get an error 91, Object Not Set, because C hasn't been set to
an actual, running instance of the class Class1.

VBA will automatically create instances of ThisWorkbook, UserForms, and Sheet
classes whenever they are referenced in code.

Classes are different from standard code modules in that you can create multiple
instances of a single class, and these instances operate independently of one
another.

When you insert a module, you are inserting a standard code module. If you want
to insert a class or form, you choose that type from the Insert menu. As I said
before, you can't insert or delete the ThisWorkbook or Sheet modules, because
only (and exactly) one module is allowed for the workbook and for each sheet.

I hope this helps, and doesn't make things even more confusing.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"tim lee" <t...@timlee.com.au> wrote in message
news:9sl6qs$n3a$1...@yeppa.connect.com.au...

[Nick Hodge]

Chip

Or my explanation...which was more 'wordy' but I think arrived at pretty
much the same conclusion. (That's encouraging for me ;-))

--
HTH
Nick Hodge
Microsoft MVP (Excel)
Southampton, England
nick_...@lineone.net

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:eMKM#8raBHA.2080@tkmsftngp03...

[Myrna Larson]

The user can't insert workbook and worksheet event modules. Excel handles that
automatically. i.e. when you add a new worksheet, Excel creates an event
module for it.

For the other modules, you select the type of module when you make a selection
on the Insert menu. It lists UserForm, Module, and Class Module, which
correspond to form, standard, and class modules.