Splitting a routine into parts

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I am writing a vba script to output Access data to a slideshow. I have this
working so far but in an effort to make th ecode reusable I want to break it
into sections and subroutines.

What is the prefered method of passing control from one routine to the other
as far as opening the database. And this is probably the wrong termanology.

I have

Dim DB as DAO.Database
Dim RS as DAO.Recordset
Dim ppObj as PowerPoint.Application
Dim ppPres as PowerPoint.Presentation

in the main routine. When I call a sub, these onjects are not found. Do I
need to redeclare these in each subroutine?
 
There are several ways to handle this actually.

The first way, is to create a class, put the declarations in the class, then
instantiate the class as a global. This is by far my favorite way, and
allows you to include any reusable code in the class.

The second way is to pass the objects to each subroutine as part of the
argument list. For example on subroutine would have the definition like:

public sub test(DB as DAO.Database,RS as DAO.Recordset, ppObj as
PowerPoint.Application, ppPres as PowerPoint.Presentation) as boolean
....
end sub

Then when you call the sub you would use

test(DB,RS,ppObj,ppPres)

A third method would be to declare the definitions as global variables, thus
allowing them to be used anywhere in your application.

In VBA it's up to the developer which method they'd prefer to use. I like
the first method because it's object oriented, where the other two aren't.
Plus i don't really like declaring a bunch of Global variables/objects when I
can substitute for a single class instantiation.

There may be a better method, but that's just what I can come up with off
the top of my head.
 
How do I cretae a class? I have done the second two and I actually have it
running now with a little trial and error, but am interested in learning new
methods. Thanks.
 
How do I cretae a class? I have done the second two and I actually have it
running now with a little trial and error, but am interested in learning new
methods. Thanks.
Click to expand...

While in the code editor, click on "Insert" then "Class Module". Write your
definitions and code in there. When you get done, save the class module.
Whatever name you save the module as, is how you will reference it in the
rest of your application.

For example, let's say I have a class named "Test", and the code for this
class is:

### Test Class Code Start ###

Option Compare Database

Private MyVariable as integer

Public Sub SayHello()
msgbox "Hello World."
End Sub

Public Property Get MyValue()
MyValue = MyVariable
End Property

Public Property Set MyValue(NewValue as Integer)
MyVariable = NewValue
End Property

### Test Class Code Stop ###

Then in your main function you would call it and use it like this:

### Main Code Start ###

Public Sub Main()
'First instantiate the class
Dim MyClass as New Test

' Use the "SayHello" subroutine in the Test Class
MyClass.SayHello

' Set the Test class's MyVariable value to 10
MyClass.MyValue = 10

' Display the value held by MyVariable in the Test class
MsgBox MyClass.MyValue
End Sub

### Main Code Stop ###

Ok, in the test class, we did several things, first we declared a private
variable of Integer. Being private means no one can access it without going
through the class.
Then we made the SayHello subrouting, fairly straight forward here. Then we
gave the class a property, and defined it's Get and Set methods.

You've probably been using classes this whole time, without even realizing
it. For instance, a form is actually a class defined in Access itself. That
form's Caption is a property of the form, just as MyValue is a propert of our
class. The form's caption probably isn't actually stored in the property,
instead the property is merely a way for us to interact and change a private
variable that actually stores the value. Opening and closing a form are
examples of the forms procedures, where SayHello was a procedure in our class.

Hope that helps you understand classes a bit more. I would definately
suggest doing some research to learn more about classes and object oriented
programming.
 
Back
Top