Visual Basic for Applications Tips #3



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

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

Proudly presents:
Visual Basic for Applications

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


*1.  SETTING THE DEFAULT VALUE AT RUNTIME                 
        
In Access, Word, and Excel, you can set a combo box control's 
default value in the control's property sheet. In Access, you 
use the Default Value property. You can enter a value or string, 
but the safest method is to use the expression  
  
=comboboxname.ItemData(x)  
  
where x represents the index value of the list item you want to 
display as the default. For instance, if the list consists of 
three items--cat, dog, and horse--cat's index value is 0, dog's 
is 1, and horse's is 2. So, the expression  
  
comboboxname.ItemData(0)  
  
would display cat in the control's textbox component. Similarly, 
in Excel, you can enter the default string or value in the 
control's Value property.  

If a set default isn't adequate, you'll probably want to set the 
default at runtime. Fortunately, you can easily do so by 
attaching one simple line of code to a form event. In Access, 
attach the following procedure to your form's Open event:  
  
Private Sub Form_Open(Cancel As Integer)  
Me!comboboxname.Value =  
Me!comboboxname.ItemData(0)  
End Sub  
  
In Excel and Word, attach this procedure to the userform's 
Initialize event  
  
Private Sub UserForm_Initialize()  
comboboxname.ListIndex = 0  
End Sub  
  
where 0 specifies the first item in the control's list.


*2. A MOVING COMMAND BUTTON                 
        
This tip is just for fun--it's a procedure that makes a command 
button move. This technique may not have any practical 
applications, but you might enjoy trying it out. 

If you're using Access, open a form and position a command button 
at the left margin. Name that button cmdMove. Then attach the 
following code to cmdMove's Click event: 
 
Private Sub cmdMove_Click() 
Dim iStart As Integer, i As Integer 
iStart = Me!cmdMove.Left 
For i = iStart To iStart + 500 
    Me!cmdMove.Left = i 
    Me.Repaint 
Next i 
End Sub 
 
Then, run the form and click cmdMove. The button will slowly 
creep to the right. 

If you're using Excel or Word, open the VB Editor by pressing 
Alt-F11, insert a userform, and add a command button named 
cmdMove. Then attach this procedure to cmdMove's Click event: 
 
Private Sub cmdMove_Click() 
Dim iStart As Integer, i As Integer 
iStart = cmdMove.Left 
For i = iStart To iStart + 50 
    cmdMove.Left = i 
    Repaint 
Next i 
End Sub 
 
You may need to adjust the values 500 and 50--the ones we add to 
iStart--to create just the effect you want. Try experimenting 
with the For loop's Step argument to change the effect.


*3. ELIMINATING UNNECESSARY IF STATEMENTS                 
        
If you're using an If statement to assign a True or False value, 
you're working too hard. For instance, the following If statement 
assigns either True or False to the variable booResult, depending 
on the value of iTest: 
 
If iTest = 3 Then 
 booResult = True 
Else 
 booResult = False 
End If 
 
But there's a much quicker way to do the same thing. Simply use 
the statement 
 
booResult = iTest = 3 
 
If iTest equals 3, the statement is True and booResult will equal 
True. If iTest doesn't equal 3, booResult will equal False.


*4. CHECKING FOR THE WEEKEND                 
        
Numerous tests can determine whether a date falls during the 
business week or the weekend. Here's a quick expression that 
returns True if the date is a weekend date and False if it's a 
weekday: 

WDay = WeekDay(dteDate) Mod 6 = 1 

Just remember: this function assumes that Sunday is the first 
day of the week.

*5. USING + TO COMBONE ICONS AND BUTTONS                 
        
The MsgBox() function has several arguments and constants. The 
function's type argument defines the buttons displayed in the 
resulting message box, and there are several constants you can 
use. Fortunately, you can combine constants using the + operator. 
For instance, if you want to use the Yes, No, Cancel button set, 
and you want No to be the default, you'd use the 
following function: 
 
MsgBox "message text", vbYesNoCancel + vbDefaultButton2 
 
If you wanted to add the exclamation icon, you'd use the 
following function: 
 
MsgBox "This is a test", vbYesNoCancel + vbDefaultButton2 + _
    vbExclamation 
 
Combining constants makes it easy to offer your users the 
options they need.


*6. MAXIMIZING A USERFORM                 
        
Access has three methods for sizing a form--Maximize, Restore, 
and Minimize. Unfortunately, neither Excel nor Word supports 
these methods. To maximize a userform, attach the following code 
to the userform's Activate event: 
 
Private Sub UserForm_Activate() 
 
Application.DisplayFullScreen = True 
 
    With UserForm1 
        .Top = 1 
        .Left = 1 
        .Height = Application.UsableHeight 
        .Width = Application.UsableWidth 
    End With 
 
Application.DisplayFullScreen = False 
 
End Su


*7. DATE CONTROLS                 
        
Mistakes are easy to make when entering dates. For the most part, 
as long as the user enters three components--a day, month, and 
year--the control doesn't object. You can add validation rules 
and even input masks, but these still won't cut down on typos. 
Instead of supplying a single text box for entering a date, try 
offering a collection of combo boxes. Then, the user is forced 
to select the appropriate month, day, and year from the combo 
box. You can use code to assemble the components for storage. 

This solution has two drawbacks. First, you'll want to consider 
this solution only when the year values can be narrowed down to 
a reasonably controlled list. Second, it will take your users 
more time to work with three individual controls instead of one 
date control. However, when accurate dates are critical to your 
application, it may be worth the extra time and effort.


*8. COMPILE BEFORE YOU START                 
        
Some errors, such as typos and syntax mistakes, will show up when 
you're entering code. But this process won't catch them 
all--debugging can be a laborious journey. You can catch a few 
more errors before you run your code by compiling it before you 
run it. Simply choose Compile from the Debug menu (in the VB 
Editor) before you try to run your code for the first time. It'll 
be much easier to catch these errors this way, than later when 
you're running the code.


*9. STORING API DECLARES                 
        
Many developers use APIs extensively, while others avoid them 
like the plague. Regardless of your opinion of APIs, they are 
the easiest solutions for some occasions. If you regularly 
include APIs in your work, you should consider setting aside one 
module for just API declarations. It'll make them easier to find 
and manage. In addition, you won't have to repeat them if you 
reuse the API in more than one module. Be sure to precede the 
declarations with the Public statement so that they're available 
to all modules.


*10. EXPORTING CODE                 
        
Errors found while debugging code not only slow you down, but 
they may cause you to lose data. For instance, if an error shuts 
you down, you stand to lose changes to your code that weren't 
saved. Of course, the simple solution is to save code before 
running it, but that isn't always the best choice when updating 
or debugging--you may not want to save until you know the changes 
are acceptable. 

A simple solution is to export your code. You can do this on a 
regular and consistent basis. Or, you can export before running 
your code. That way, you don't overwrite the old code, but you 
won't lose the new code if there's a problem.
