Access VBA Macro Programming

Calling Methods

As explained earlier, methods effectively are subroutines based on objects that take certain

actions, sometimes dependent on parameters passed to them. The method is effectively a

shortcut to an action, but you may need to specify parameters to define to VBA exactly what

it is you want to do.

An example is using theTransferSpreadsheetmethod in theDoCmdobject (see Chapter 16

for more information). You have to pass parameters, such as the TransferType, TableName, and

FileName so VBA knows what it is required to transfer. For example, in the following code,

DoCmd.TransferSpreadsheet acExport, , "MyTable", "C:\temp\MyTable"

C:\temp\MyTable defines the location of the file to be exported to; this is a mandatory

parameter for this method. The argument acExport defines that you are exporting data, and

MyTable is the source for the data. Other optional parameters can be passed, too, such as a

spreadsheet type if required and Has Field Names. Optional parameters are shown in the tip

text for the method with square brackets around them, such as [HasFieldNames]. The tip text

appears as you type in the VBA statement. This tip text box has a yellow background and

shows all available parameters.

Sometimes it is unnecessary for a method to have arguments, such as when you use the

DoCmdobject to close a form or report. If the code is being run from the form or report

itself, you do not need to specify the object type or the name of the object, since they are

already there by default.

DoCmd.Close

This will close the form or report it is being run from, as if the user had clicked the “x” at the

top-right corner of the window.

When we used the example earlier to transfer data to a spreadsheet, the second argument

for the spreadsheet type was left blank because it will default to the current version of Excel

installed on your machine.

DoCmd.TransferSpreadsheet acExport, , "MyTable", "C:\temp\MyTable"

This is called passing by order because the parameters are being passed in the order in

which they are defined in the function, separated by commas.

When you enter the opening parenthesis, a list of parameters will appear, highlighted in

bold as you enter each one. You have to stick to the order shown. If this is a function that

assigns the result to a variable, you do not need to include the parentheses. You will get an

error in some cases if you include them.

Some methods, such asTransferSpreadsheet, have a large number of parameters, and

many of them are optional. Optional parameters are shown with square brackets ([ ]). Passing

by order becomes more complicated with optional parameters because you may be using a

180 Microsoft Access 2010 VBA Macro Programming