Visual Basic for Applications Tips #1



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

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

Proudly presents:
Visual Basic for Applications

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


*1.   VBA IN THE NEXT MILLENNIUM                 
        
With all the hub-bub about the Y2K bug, you might be wondering 
how VBA handles the turn of the century date issue. Fortunately, 
you should experience few, if any, problems if you prepare for 
the millennium properly. You see, VBA interprets two-digit year 
values from 00 and 29 as years in 2000. That means, if you enter
the date 1/1/00, VBA thinks you mean January 1, 2000. You might 
not expect this interpretation. Two-digit year values from 30 to
99 remain in the 20th century for now. So, the date 1/1/98 is 
January 1, 1998. The easiest way to avoid this problem is to 
specify the century yourself, using four-digit year values 
instead of two.


*2.   TURNING OFF THE SYNTAX ERROR DIALOG BOX                 
        
When entering code, VBA will warn you when the statement syntax 
is incorrect. By default, VBA displays the syntax error dialog 
box and then displays the offending statement in red. If you 
find the dialog box annoying and unnecessary, you can turn it 
off. In Access, select Options... from the Tools menu, click the 
Module tab and uncheck the Auto Syntax Check item in the Coding 
Options section. In Word and Excel, do the same, except you'll 
select the Editor tab instead of the Module tab. VBA will still 
display the statement in red until you correct it, but you won't 
have to dismiss the syntax error dialog box before doing so.


*3.  CREATING A DYNAMIC LIST OF OBJECTS TO FILL A COMBO OR LIST BO                 
        
In Access, we use a combo or list box to offer a user a choice 
from a set list of items. You can enter that list manually, or 
you can create a dynamic list from a table or query. You can also
use VBA to fill the control with a list of objects. For instance,
if you wanted to list all the existing reports for a batch print 
utility, you might use the following code 

Private Sub cboReport_GotFocus() 
Dim db As Database, cnt As Container, doc As Document 
Dim strHold As String, strReports As String 

Set db = CurrentDb() 
Set cnt = db.Containers!Reports 

For Each doc In cnt.Documents 
    strHold = doc.Name 
    Debug.Print strHold 
    strReports = strReports & strHold & ";" 
Next doc 

Forms!frmPrintReport!cboReport.RowSource = strReports 

Set db = Nothing 
Set cnt = Nothing 
Set doc = Nothing 

End Sub 

where your form is frmPrintReport and the name of your combo box 
is cboReport. Be sure to set the combo box's Row Source Type to 
Value List. 

If you'd rather see a list of forms, replace the 
Set cnt = db.Containers!Reports statement with 

Set cnt = db.Containers!Forms


*4. BREAKING UP IS EASY TO DO                 
        
If you need to display a message, you probably use the MsgBox()
function. Unfortunately, you can't control how VBA displays it.
For instance, if your message is long, VBA--not you--will 
determine where to wrap your text. However, you can take back 
control by inserting a Chr() function in the text. 

To experiment, open the Debug window or the Immediate window 
(depending on the application you're using) and enter 
the statement 

MsgBox("This message is too long so" & Chr(13) & "we added a 
Chr(13) function to wrap the text.") 

and then press Enter. VBA will return a message box displaying 
two lines of text. Furthermore, the Chr(13) function will insert 
a line break between the words so and we. Without the Chr(13) 
function, VBA will decide where to wrap the text.


*5.   COUNTING FROM WORD                 
        
Did you know you could run the Windows Calculator accessory from 
inside Word? You can by executing this macro: 

Sub Calc() 
If Tasks.Exists("Calculator") = True Then 
    Tasks("Calculator").Activate 
Else 
    Shell "Calc.exe" 
End If 
Tasks("Calculator").WindowState = wdWindowStateNormal 
End Sub 

The first condition in the If statement checks to see if the 
calculator is already running. If it is, the first action gives 
focus to the already running Calculator. If the Calculator isn't 
running, the Else statement launches the appropriate program. 
The last statement simply uses the constant wdWindowStateNormal 
to set the WindowState property.


*6.   WHY YOU SHOULD CARE ABOUT A VARIABLE'S DATA TYPE                 
        
An important aspect of programming with VBA is capturing errors. 
Often, an application simply doesn't continue as you expect it 
to--a drive door may be open or your user may simply press the 
wrong button. At any rate, if you don't account for these errors 
in your code, they can, and often do, lock up your application. 

To protect your application from these errors, you'll need to 
know what they are and plan for them. The easiest way to 
determine an error is right after it occurs. Simply open the VB 
Editor or the Debug window (in Access) by pressing Ctrl-G. Then, 
enter into the Immediate pane the statement 

?Error(Err) 

and press Enter. VBA will display a descriptive message that 
describes the current (just-occurred) error. Once you know what 
the error is, you can add code that will direct your application 
when the error occurs.


*7. USING ERROR()                 
        
An important aspect of programming with VBA is capturing errors. 
Often, an application simply doesn't continue as you expect it 
to--a drive door may be open or your user may simply press the 
wrong button. At any rate, if you don't account for these errors 
in your code, they can, and often do, lock up your application. 

To protect your application from these errors, you'll need to 
know what they are and plan for them. The easiest way to 
determine an error is right after it occurs. Simply open the VB 
Editor or the Debug window (in Access) by pressing Ctrl-G. Then, 
enter into the Immediate pane the statement 

?Error(Err) 

and press Enter. VBA will display a descriptive message that 
describes the current (just-occurred) error. Once you know what 
the error is, you can add code that will direct your application 
when the error occurs.


*8. WHY USE $?                 
        
Are you wondering what the $ sign means in string functions such 
as Trim$, Str$, and InStr$? These functions have seemingly 
identical corresponding functions--Trim, Str, and InStr. How do 
you know which to use? The difference is the data type of the 
returned value. The plain versions (the functions without the $) 
return a Variant data type. If you've declared the string, then 
VBA must convert the returned Variant to a String data type. 
However, the $ version will return a String, without first having
to convert from the Variant. You might not save much time, but if
you're working with lots of strings, the time saved can make a 
difference. In addition, the Variant data type requires more 
memory than the String data type.


*9. TO USE OR NOT TO USE PARENTHESES?                 
        
Have you ever noticed that sometimes functions and methods 
contain parentheses and sometimes they don't? For instance, 
you've probably seen statements that resemble the following: 

MsgBox "Do you wish to continue?", 3 
iResponse = MsgBox("Do you wish to continue?",3) 

The first statement doesn't use parentheses to enclose its 
arguments, but the second does. The reason is simple. If you're 
just executing the function, as we did in the first statement, 
you don't need the parentheses. If you're assigning the result 
of a function to a variable, as we did in the second statement, 
you enclose the arguments in parentheses.


*10.  JUMPING TO FUNCTIONS                 
        
It's common to find function calls in code. When VBA encounters
a function call, it routes the flow to that function and returns
to the calling function once the task is complete. If you want to
view the function quickly, simply right-click the function name 
in your code. Then, select Definition from the resulting shortcut
menu. VBA will give focus to the selected function--even if it's 
in a different module. (You can also choose Definition from the 
View menu.) 

If you'd rather use keystrokes, you can. Simply position the 
cursor in the function name and press Shift-F2. To return to the 
calling function, press Ctrl-Shift-F2.

