General Programming Questions

[Luther Cifers]

A couple of general programming questions:

1) Why use more than one module? Is it only to keep the program better organized?
Are subs from different modules as easily accessible to one another as subs in
the same module?

2) What are class modules and what are they used for? How do they differ from
modules?

Thanks.

- Luther

[Chip Pearson]

> 1) Why use more than one module?

Simple organization is the primary reason. It makes it easier to
move code between projects. However, you may want to make only
some procedures visible outside the VBProject, and keep others
private to the project. In this case, you can put Option Private
Module at the top of the modules with private code, and put your
public procedures in another module. Also, the internal size of a
module is limited to 64K, so you may end up having to use
multiple modules if you have a large amount of code.

> Are subs from different modules as easily accessible to one
another as subs in
> the same module?

Yes, as long as they are not declared as Private, e.g., Private
Sub MySub(). In this case, they are accessible only from the
same module.

> 2) What are class modules and what are they used for? How do
they differ from
> modules?

Class modules are "templates" for your own objects. Below is
something I wrote yesterday in reply to another post with a very
similar question.

There are many reason that you might want to use a class module
in Excel. One is that a class module place in which you can use
the WithEvents keyword (you can use in the ThisWorkbook module, a
userform's code module or the Sheet modules because these are
themselves class modules). Using WithEvents, you can trap the
events raised by a control or another object. For example, the
Excel Application object will raise an event when a new workbook
is created. In order to receive that event, you have to declare
WithEvents a variable whose type is Application. E.g., in a class
named CAppEvents, put the following code:

Public WithEvents App As Excel.Application
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "You Created A New Workbook."
End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub

Then, in a standard code module, put code like

Dim AppEvents As CAppEvents
Sub Init()
Set AppEvents = New CAppEvents
End Sub

Once you've run the Init procedure, the App_NewWorkbook event
procedure will execute whenever a new workbook is created. While
you could put the WithEvents declaration and the event procedure
in the ThisWorkbook module, using a separate class module makes
it easy to move the code from one project to another.

Another reason to use a class module is that you can define your
own objects, which may make your project simpler and more
powerful. For example, suppose you were writing an employee
tracking project. You could create a class named CEmployee that
describes a generic employee. This may have properties like
Name, Address, Phone, and Salary. Unlike Type variables (called
Structures in other languages), a class property contains actual
code, so you can add validation to ensure that a variable has a
valid value, and take specific action when value changes. For
example,

Private pSalary As Single
Property Let Salary (Value As Single)
If Value <= 0 Then
' salary must be positive
Msgbox "Invalid Value!!"
Else
pSalary = Value
Adjust_Tax_Payments
End If
End Property
Public Sub PrintPaycheck()
' code to print a paycheck
End Sub

Here, you are ensuring that salary for the employee is greater
than zero, and when it is changed, some other values, such as
taxes, are changed accordingly. Because you can create new
instances of a class without declaring a specific variable for
each one, you can create as many Employees as you need, at run
time. For example, in a standard code module, you would do
something like,

Dim Employees As New Collection
Sub CreateEmployees()
Dim OneEmployee As CEmployee
Dim Rng As Range
For Each Rng In Range("A1:A10")
If Rng.Value <> "" Then
Set OneEmployee = New CEmployee
OneEmployee.Name = Rng
OneEmployee.Salary = Rng(1,2)
' other values
Employees.Add OneEmployee
End IF
Next Rng
End Sub

Now, you have a collection of employees, and you can loop through
that using a For Each loop anytime you need to do something with
Employees. E.g.,

Sub PrintChecks()
Dim Emp As CEmployee
For Each Emp In Employees
Emp.PrintPaycheck
Next Emp
End Sub

This only begins to describe what you can do with classes. They
may seem a bit tricky at first, but they are one of the most
powerful tools you have to create solid and well designed
applications.

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

"Luther Cifers" <luther...@cmcsg.com> wrote in message
news:026901c0e2c1$7460a470$9ae62ecf@tkmsftngxa02...