Visual Basic for Applications Tips #44


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

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

Proudly presents:
Visual Basic for Applications

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


*1. RETURN THE SELECTED ITEM IN A LIST BOX  
  
by Susan Sales Harkins  
  
Patricio G. submitted this quick and easy method for returning 
the selected item in a list box (or combo box) control as a 
value. Most of the time you'll return the actual item, but 
occasionally you'll need to know the position of that item in 
the list. This simple procedure adds 1 to the selected item's 
index value and then prints it in the Immediate window. You'll 
want to enhance the procedure to suit your particular needs, 
but this procedure supplies the heart of the task. 
Thanks, Patricio!

Private Sub lstname_DblClick(Cancel As Integer) 
    Debug.Print Me! lstname.ItemsSelected.Item(0) + 1 
End Sub


*2. CHECK FOR NOTHING

by Susan Sales Harkins

Most of us are familiar with the habit of clearing an object 
variable when we're done with it, using Nothing. Although 
checking objects for their current state is less common, 
sometimes it's necessary. However, you can't use the equals 
operator to check for Nothing, the same way you use it to 
assign Nothing. For instance, the statement

Set obj = Me

assigns the current form to the object variable, obj, and uses 
the equals operator to do so. When checking an object, you must 
use the Is operator in the form

If obj Is Nothing Then ...


*3. CANCEL EXCEL PRINT TASK

by Susan Sales Harkins

You won't always want users to be able to print 
data--confidential data is an example. One possible solution 
is to remove the Print button from the toolbars and the Print 
command from the File menu, but there's also a VBA solution. 
Add the procedure

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
  Cancel = True 
End Sub

to the ThisWorkbook module. This procedure also inhibits the 
Print Preview feature.

A user can disable this macro by opening the workbook with 
macros disabled.


*4. VALIDATING DATA

by Susan Sales Harkins

Validating data is a big part of any application, but you might 
have trouble deciding when to validate entries. You might 
consider using the Change event for each control that accepts 
new data. There's nothing wrong with this choice, but keep in 
mind that every time the user changes the entry, VBA will 
trigger the Change event. Ultimately, this setup 
can be inefficient.

A better choice might be the LostFocus event. Attaching your 
validation procedures to this procedure guarantees that your 
application will check the entry only when the user tries to 
leave the control, instead of every time the user changes the 
contents of the control.


*5. READING FOR THE OBJECT IMPAIRED

by Susan Sales Harkins

Many people are using VBA and other (almost) object-oriented 
languages without knowing what object-oriented design is. That 
lack of knowledge can inhibit your ability to advance your 
programming skills. If you're a beginner in this area, there's 
an introduction to the subject at

http://compsci.about.com/science/compsci/library/weekly/
aa111300a.htm


*6. COPY AN ENTIRE FOLDER

by Susan Sales Harkins

Jim D. submitted this excellent tip for copying the contents 
of an entire folder--avoid VBA code and use the Microsoft 
Script Runtime library. The following procedure requires just 
a few short lines to accomplish what once was a 
fairly complex task:

Function CopyTest(source As String, destination As String) 
Dim obj As Scripting.FileSystemObject 
Set obj = New Scripting.FileSystemObject 
obj.CopyFolder source, destination 
End Function

If the destination folder doesn't exist, the CopyFolder method 
creates it, but the procedure will return an error if the source
folder doesn't exist.


*7. STANDARD DIALOG BOXES

by Susan Sales Harkins

Several dialog boxes are standard across the Office suite. For 
instance, Open, Save, and Print dialog boxes are practically 
identical throughout the applications. Besides being easy to 
use, they're also readily available programmatically by calling 
the Dialogs object. A simple example is offered in the procedure 
below. This procedure opens the Show dialog box to the default 
folder and automatically limits the task to the .htm and .html 
files in that folder. At this point, you carry on as you 
normally would by specifying a file and clicking Open.

Function ShowOpenDialog() 
Application.Dialogs(xlDialogOpen).Show "*.htm" 
End Function

Our example works in Excel. You'll need to check your 
application for the appropriate dialog constants.


*8. USING NAMED ARGUMENTS

by Susan Sales Harkins

One of the more difficult aspects of working with functions 
(and sometimes procedures) is remembering all the arguments and 
the order in which you must include them. In addition, when 
reviewing the statement, you can't really tell just what the 
arguments mean, so debugging can also be difficult unless you 
have a photographic memory. That's why named arguments are such 
a great addition to VBA. Instead of writing

DoMyWork arg1, arg2, arg3, arg4

you can use named arguments in the form

DoMyWork arg1: value1, arg2: value2, arg3: value3, arg4: value4


Of course, in a real example, argx would be fairly descriptive 
of the argument's purpose.

If you omit an argument, you don't have to accommodate that 
omission by remembering an extra comma to denote its absence. 
For instance, the function

DoMyWork arg1, , , arg4

where arg2 and arg3 are optional becomes

DoMyWork arg1: value1, arg4: value4

In addition, order no longer matters. The next statement is 
just as valid as the previous:

DoMyWork arg4: value4, arg1: value1


*9. CLOSING THE ACTIVE WINDOW

by Susan Sales Harkins

You can close the active window in several ways. If you're 
working in a userform, you can click the appropriate Close or 
Exit command button. You can also click the Windows Close 
button (the X character) in the right corner of the window's 
title bar. These methods work in your application or in the 
Visual Basic Editor.

If you prefer, there's a keyboard shortcut, which comes in 
handy for those users who find switching back and forth between 
the keyboard and the mouse a nuisance. If you're in that group, 
try pressing Ctrl-F4 to close the active window--even in the 
Visual Basic Editor.


*10. TOGGLING IN A BOOLEAN STATE

by Susan Sales Harkins

Boolean states are common in code--whether you're working with 
Yes/No, On/Off, True/False, or -1/0. Almost anytime you're in 
this type of either/or situation, you can probably use toggling 
code. In other words, you can use one statement to express both 
states by adding a Not operator. For instance, if you want to 
switch back and forth between enabling a control, you might use 
a statement in the form

control.Enabled = Not control.Enabled

The result of executing the statement is the reversal of the 
button's enabled state before executing the code. In other 
words, if the button is enabled before you run the statement, 
it will be disabled after you run the statement, and vice versa.

This type of toggling code will work with all kinds of 
situations--just make sure you're working in an 
either/or situation.
