function that has ten possible parameters even if you want to use only two of them. Consider
the following example for importing data from a spreadsheet. This example assumes you
already have a current spreadsheet file called MyTable on the root directory of the C drive.
DoCmd.TransferSpreadsheet acImport, , "MyTable 1 ", "C:\MyTable", ,"A1:G10"
Notice that four parameters are being passed to open the file, although at least two
parameters are not being used, as shown by the Null values between the commas. This is
because these are optional parameters and do not have to be given a value. For example, a
spreadsheet type has not been given because the default is the current version of Excel
installed. Also, no HasFieldNames argument has been provided because the default is False.
The split between mandatory and optional parameters depends on what the method is
doing and how the code in the Excel Object Library has been written. Optional parameters
are always shown with square brackets ([ ]) around them.
It looks confusing, and anyone reading the code will not be able to immediately interpret
what is going on and what the parameters mean. If you are looking at a VBA application that
has been written by someone else, it may take more time to interpret what is going on than if
the passing by name method was used. Code should always be easy for another person to
understand in case they have to perform maintenance on it. If you have written a professional
application for commercial use and you are suddenly unavailable to maintain it, someone else
needs to be able to look at your work and quickly understand your code.
Passing by name is another way of passing parameters that makes it less confusing and
shows the names of the parameters being passed. Passing by name enables you to selectively
pass arguments without having to specify Null values for arguments you don’t want to use. It
also makes it easier to understand what is being passed to the method. If you pass by name,
the preceding example can be rewritten as follows. As before, this example assumes you
already have a spreadsheet file called MyTable on the root directory of the C drive.
DoCmd.TransferSpreadsheetTransferType:=acImport,TableName:="MyTable", _
FileName:="C:\MyTable",Range:="A1:G10"
You can define each parameter by naming the parameter and following it with a colon and an
equal sign (:=). When passing by name, you can pass the parameters in any order, unlike
passing by order, which strictly defines the order passed.
Collections Explained
In object-oriented programs, it is important to understand the concept of collections.
Collectionsare objects that contain a group of the same objects. An example is theTableDefs
collection, which contains all the table objects for a given database. All the TableDefs are like
objects because they have the same properties and methods. An object such as aQueryhas
different properties and methods and so cannot be part of theTableDefscollection, but it
would fit into theQueryDefscollection.
Chapter 14: The Access Object Models 181