Recognizing VBA procedures

VBA has two types of procedures. One type is a Sub procedure. A Sub procedure is always contained within a pair of Sub...End Sub statements, as follows:

Figure 1-8:

Executing a procedure.

Figure 1-8:

Executing a procedure.

'Any VBA code here End Sub

The subName part of the example is the name of the procedure. The (...) part after the name could be empty parentheses or a list of parameters and data types. The 'Any VBA code here part stands for one or more lines of VBA code.

When looking at code that's already been written, you'll see that some Sub procedures have the word Public or Private to the left of the word Sub, as in these examples:

Private Sub subNamei...)

'Any VBA code here End Sub

Public Sub subNamei...)

' Any VBA code here End Sub

Public or Private defines the scope of the procedure. Neither is particularly important right now. All that matters right now is that you know that a Sub procedure is a chunk of VBA code that starts with Sub or Private Sub or Public Sub statement and ends at the End Sub statement.

For those of you who must know right now, a Public procedure has global scope (is available to all other objects). A Private procedure is visible to only the procedure in which it's defined. For example, Sub procedures in a class module are private to the form or report to which the class module is attached.

The second type of procedure that you can create in Access is a Function procedure. Unlike a Sub procedure, which performs a task, a Function procedure generally does some sort of calculation and then returns the result of that calculation. The first line of a Function procedure starts with the word

Function (or perhaps Private Function or Public Function) followed by a name. The last line of a Function procedure reads End Function, as illustrated here:

Function functionNamei..


'Any VBA code here

End Function

A module can contain any number of procedures. When you open a module, you might at first think you're looking at one huge chunk of VBA code. But in fact, you might be looking at several smaller procedures contained within the module, as illustrated in the example shown in Figure 1-9. Notice how each procedure within the module is separated by a black line that's the width of the page.

So that's the bird's-eye view of Microsoft Access and VBA from 30,000 feet. Just remember that VBA is a programming language that allows you to write instructions that Access can execute at any time. You can write different sets of instructions for different events. Each set of instructions is a procedure, which is a series of steps carried out in a particular sequence to achieve a goal. You write and edit VBA code in the VBA editor.



Figure 1-9:

A module containing three procedures.


1 VBA Dummies - Form Forml (Code)


Option Compare Database

Sub Magic_Click()

Dim Answer As Byte, Msg As String Answer = MsgBox("Do you eat meat?", vbYesNo, Msg = "You are" £ Ilf(Answer = vbNo, " not", Answer = MsgBox(Msg, vbOKOnly, "Info") .End Sub_

Private Sub FirstRecButton_Click() 'Go to first record. DoCmd.GoToRecord , , acFirst End Sub


Function IsOpen(strFormName As String) As Boolean Dim myObject As AccessObject

Set myObject = CurrentProject.AllForms(strFormName) If myObject.IsLoaded Then

If myObject.CurrentView <> acCurViewDesign Then

IsOpen = True End If End If End Function

The beauty of it all is that you can write lots of little procedures to handle some of your more mundane tasks automatically and effortlessly. You can also extend Access's capabilities by writing procedures that do the tasks Access can't do on its own.

0 0

Post a comment