Visual Basic for Applications Tips #45


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic for Applications

----------------------------------------------


*1. MSGBOX AS ARGUMENT

by Susan Sales Harkins

Most of the time we see the MsgBox statement used alone 
in the form

MsgBox prompt

where prompt is the message the statement displays. However, 
you can also use MsgBox as the argument of another statement. 
For instance, the following statement displays a message box if 
the specified condition is False:

Iif(condition, trueaction, MsgBox("Sorry, that didn't work"))

You're not limited to the Iif statement, of course, but it's 
probably one of the more likely candidates for this 
type of nesting.


*2. FINDING FUNCTION DEFINITIONS

by Susan Sales Harkins

What would code be without function calls? The problem is, just 
because you recognize the call doesn't mean you know what the 
function does. For that, you'll need to see the actual function,
and that could prove difficult if you go about it the hard 
way--looking through all your modules. Instead, right-click the 
call in your code (in the name) and select Definition from the 
resulting shortcut menu. VBA will display (give focus) to the 
function, regardless of where it's stored (as long as it's in 
the same database, of course).

If you prefer, you can choose View, Definition, or you can press
Shift-F2.


*3. FIXING MISSING REFERENCES

by Susan Sales Harkins

Have you ever received the dreaded "Undefined Function" or the 
"Function not available in query expressions" errors? Most 
likely you've got a missing reference, and, unfortunately, the 
telling error messages aren't very helpful (surprise!).

The good news: This problem is usually easy to fix. First, open 
the Visual Basic Editor and select Tools, References. Once in 
the References dialog box, look for a library that's tagged as 
"Missing." When you find it, deselect it. Then, choose Debug, 
Compile, which should fix your reference. Be sure to reopen the 
References dialog box to make sure the library has been 
reselected. If it's not checked, be sure to select it.

Another symptom of a missing reference is a missing property or 
object that you know should be available.


*4. SCHEDULING NO WORK FOR THE WEEKEND

by Susan Sales Harkins

Many scheduling tasks include only weekdays when counting the 
number of working days between two dates. There are a lot of 
ways to determine whether a particular date falls during the 
week or on the weekend, but one of the easiest is the following:

dteWday = WeekDay(date) Mod 6 = 1

Keep in mind that this statement relies on VBA's first day of 
the week default, which is Sunday.


*5. INPUTBOX DEFAULTS

by Susan Sales Harkins

One of my favorite VBA shortcuts is the use of defaults in an 
InputBox statement. Since the InputBox is generally provided to 
solicit data from the user, it's a little odd to consider 
offering a default, but you can and you should, if possible. 
Your users will save some time, and you'll cut down on data 
entry errors. To specify a default value, use the form

response = InputBox(prompt, title, default)

When default is a string, be sure to enclose it in quotes.


*6. ARGUMENTS TO GO

by Susan Sales Harkins

User-defined functions are the meat and potatoes of most 
applications. As such, you'll probably take advantage of the 
capability to pass arguments from one procedure to another. 
However, just as built-in functions don't always require all 
the arguments, user-defined functions can also have optional 
arguments. To declare an optional argument, use the Optional 
keyword in the form

Function DoMyWork(Optional arg As datatypeofchoice)

That way, DoMyWork will accept the passed argument arg or 
will work just as well without it.

You can combine optional and required arguments in the same 
function; just be sure to declare your optional argument last, 
because every argument that appears after the Optional keyword 
will also be optional. For instance, the function

DoMyWork(arg1, Optional arg2)

has two arguments--arg1 is required and arg2 is not. 
But don't switch things around in the form

DoMyWork(Optional arg1, arg2)

expecting the function to require arg2, because it won't. 
In this case, both arguments are optional.


*7. IF ACTION EFFICIENCY

by Susan Sales Harkins

Using an If action to exit a sub or function procedure is fairly
common. When using this design strategy, specify the exiting 
condition first. That way, VBA won't even bother to evaluate the
other conditions--which is the most efficient setup. The truth 
is, you probably won't notice any improvement in the 
application's performance, but performance isn't always the 
only driving force. Efficient code is generally (almost always) 
easier to decipher and debug.


*8. LINKING TO TABLES WITH PASSWORDS

by Susan Sales Harkins

The TransferDatabase method is a quick way to import or link to 
tables in another Access database, but it doesn't work with 
password-protected files. If you need to connect to a protected 
database, use the OpenDatabase method instead, using the form

Set db = workspace.OpenDatabase (dbname, options, 
read-only, connect)

where connect equals the following string

"; pwd=mypassword"

You can omit the first part of the connection string because 
you're connecting to Jet database, but you must include the 
placeholder (;).


*9. NO RECORDCOUNT

by Susan Sales Harkins

The Recordset object does have a RecordCount property, but if 
you're using ADO you should know it doesn't always work. 
Specifically, if you define your Recordset object with a 
forward-only cursor (CursorType:=adForwardOnly), the RecordCount
property will always return -1, regardless of how many records 
it contains. If you think you might need a record count, create 
a keyset (CursorType:=adOpenKeyset) or static 
(CursorType:=adOpenStatic) recordset.


*10. DAO VS ADO TYPES

by Susan Sales Harkins

Last time we tipped you off to a limitation in ADO's 
forward-only cursor type. Specifically, the RecordCount 
property won't return the correct count when used on a 
forward-only table (CursorType:=adForwardOnly). If you're not 
familiar with ADO yet, you might not realize that the ADO 
cursor type is equivalent to the DAO record type. Here is a 
quick comparison of the two: 
 
ADO Constant (CursorType)        DAO Constant (RecordType) 
adOpenForwardOnly                dbOpenForwardOnly 
adOpenStatic                     dbOpenSnapshot 
adOpenDynamic                    dbOpenDynaset 
adOpenDynamic/adCmdTableDirect   dbOpenTable 
 
The ADO CursorType has one additional possibility--adOpenKeyset,
but there's no good DAO equivalent to this type.
