Visual Basic for Applications Tips #48


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

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

Proudly presents:
Visual Basic for Applications

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


*1. COLOR MY CODE

by Susan Sales Harkins

The Visual Basic Editor displays the various parts of each VBA 
statement in different colors, which makes syntax errors stand 
out. For instance, a commented statement is green and keywords 
are blue. Any line that contains an error is red, so finding 
those errors shouldn't strain your eyes or your brain.

Objects, properties, functions, and methods are black. However, 
VBA helps you out here too by converting these components to 
proper case--ignoring how you entered them. If your statement's 
in proper case, you know VBA was able to interpret what you 
entered as a valid statement.

Wouldn't it be great if you could print your code in color? 
Unfortunately, VBA doesn't seem to have that capability just 
yet. There are add-ins that will print Visual Basic code in 
color, but I've yet to find one that works directly with VBA 
and the Office Visual Basic Editor. If you know of one, I'd 
love to hear about it.


*2. NEW PATH AND NAME PROPERTIES

by Susan Sales Harkins

Access 2000 has a new way to return the current database's path 
and name. To return the current file's complete path, use the 
CurrentProject's Path property in the form

strPath = CurrentProject.Path

Similarly, you use the CurrentProject's Name property to 
return the file's name:

StrName = CurrentProject.Name


*3. DEFAULT IN COMBO OR LIST BOX

by Susan Sales Harkins

You probably know that you can set a combo box's Default 
Value property to

=ctl.ItemData(x)

where ctl is the name of the control and x is the index value 
of the list item you want to display as the default.

You can handle this task through VBA when setting a permanent 
default isn't adequate. Simply attach this code to your form's 
Current event:

Private Sub Form_Current() 
Me!ctl = Me!ctl.ItemData(3) 
End Sub

where ctl is the name of the combo box control. Word and 
Excel users should attach the following code to the 
userform's Initialize event:

Private Sub UserForm_Initialize() 
ctl.ListIndex = x 
End Sub

where ctl identifies the combo box and x is the index value 
of the item you want to display as the default.


*4. SQL SERVER 2000 ONLINE DOCUMENTATION

by Susan Sales Harkins

It's not uncommon for Office users--especially Access users--to 
be connected to SQL tables. Fortunately, there's some fairly 
comprehensive online documentation for SQL Server 2000, so if 
you'd like to learn more, visit the link below to view the 
helpful Getting Started with SQL Server Books Online:

http://msdn.microsoft.com/library/psdk/sql/getstart_4fht.htm


*5. ALL UPPERCASE

by Susan Sales Harkins

When you need to ensure that all the characters in an entry are 
uppercase, you have several choices. One solution uses the 
following expression in the control's After Update event:

Me.ActiveControl=UCase(Me.ActiveControl)

This expression converts all characters into their uppercase 
counterparts. This easy expression requires no prior 
referencing and is flexible enough to work with any control.

The ActiveControl object isn't available in all Office 
applications. In those cases, try the following:

ctl.Value = StrConv(ctl.Value, vbUpperCase)

where ctl is an object variable.


*6. EASY SAVE

by Susan Sales Harkins

Lembit Soobik sent in this quick tip for saving changes when 
the user must press a Save or Cancel button to save the current 
entries. In such case, the following expression will force a 
quick save to an edited record:

Me.Dirty = False

Similarly, this next expression will undo any edits:

Me.Undo

Here's how it works: By setting the Dirty property to False, 
you force Access to save the current row. Just keep in mind 
that in the process, Access will also trigger Before Update 
and After Update events.

Thanks Lembit!


*7. REPLACING CHARACTERS

by Susan Sales Harkins

Office 2000 users can benefit from access to the new VBA 
function, Replace. This function replaces characters in one 
string with another string. For instance, to replace the 
character "b" with the letter "c" in the string "abcd", you'd 
use the function:

Replace("abcd","b","c")

This will return "accd."


*8. DELETING INSTEAD OF REPLACING

by Susan Sales Harkins

In the previous tip, we showed you how to replace characters in 
a string using VBA's Replace function. You can also delete 
characters by specifying an empty string "" for the third 
argument in the form

Replace("string", "delete","")

where "string" is the string or expression from which you're 
deleting characters, and "delete" is the character you want to 
delete. For example, the expression

Replace("abcd","b","")

returns the string "acd".


*9. WHEN OPTIONAL ARGUMENTS GO MISSING

by Susan Sales Harkins

In previous tips, we've discussed passing optional arguments 
using the Optional keyword. By declaring an optional argument, 
you can omit an argument when one isn't available. Simply use 
the form:

Function functionname (arg1 As datatype, Optional arg2 As 
datatype)

In this example, arg1 is not optional, but arg2 is.

Occasionally, you may need to know if an optional argument is 
passed. When this is the case, you can use the IsMissing 
statement in the form:

If IsMissing(arg2) = True Then ...

When using this solution, be sure to declare your optional 
argument as a Variant, because IsMissing works only with the 
Variant data type.


*10. OPTIONAL ARGUMENT DEFAULT

by Susan Sales Harkins

In the previous tip, we showed you how to use the IsMissing 
statement to determine whether an optional argument was passed. 
The problem with the IsMissing solution is the data type must 
be a Variant. Another solution is to apply a default value to 
the optional argument and then compare the argument to the 
default value.

To apply a default value to an optional argument use the form:

Function functionname (arg1 As datatype, Optional arg2 As 
datatype = defaultvalue)

For example, the following function declares an optional 
argument as a string and applies a default value of "NA" to the 
optional argument named status:

Function Status(name as String, status As String = "NA")

You can quickly determine whether an argument was passed to 
status by running this simple test

If status = "NA" Then ...

Of course, in most cases, assigning a default value to the 
optional argument negates the need for the test.
