Visual Basic for Applications Tips #9


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

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

Proudly presents:
Visual Basic for Applications

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

*1. EASY WAY TO GET CURRENT RECORD                 
        
When working with database tables, you may use the Move method to 
change the current record in a recordset. Specifically, this method 
has one argument, which allows you to specify the number of rows you 
want to move. For instance, to move forward two rows, you'd use the 
statement 

rst.Move 2 

Similarly, to move backward two rows, you'd use the statement 

rst.Move -2 

If you use 0 as the Move method's argument, VBA will retrieve the 
latest data from the current record. This behavior is helpful when you 
want to make sure that the current record is the most recent data.


*2. ACTIVEX LEGALITIES                 
        
Often, you don't have to purchase an ActiveX control to have it. You 
see, any application that uses an ActiveX control registers that 
control on your system. Once the control is registered, you're free to 
use it, even though you didn't acquire it by purchasing the control 
outright. You'll find these additional controls on your Toolbox. Just 
click the More Controls tool to view a list of all the registered 
nonnative ActiveX controls residing on your system. 

Although you can use these ActiveX controls in your own applications, 
you can't distribute them. If you decide you want to distribute one of 
these controls, you'll need to acquire a license to do so.


*3. ACTIVEX HELP                 
        
In our last tip, we told you that it's fine to use your registered 
ActiveX controls in your own applications, even if you didn't pay for 
it outright. (You did pay for these controls, when you purchased the 
hosting application.) Unfortunately, unless you have the documentation 
and Help files that you get when you actually purchase the control, 
taking advantage of the control may not be an easy task. You'll need 
to know about the control's properties and methods to use it. 

If you're lucky, the Help files may be installed. To find out, insert 
the control in a form and then open that control's property sheet. 
Select any property and press F1. If the files are there, pressing F1 
should access them and, of course, you're free to use them.


*4. OUTLOOK DEVELOPMENT                 
        
Outlook is one of the more difficult Office products to program. I say 
that not because it's complicated, but because it is limited. Outlook 
2000 forms finally support VBA, but automating Outlook can still be a 
challenge. 

If you're looking for information on VBA and Outlook (or Exchange), 
one of the best sites we've come across is OutlookExchange.com, 
sponsored by ECMS and Micro Eye. The URL for this site is 

http://www.outlookexchange.com/ 

You'll find plenty of code samples, tips, and documentation for the 
Outlook power user. If you're not a power user, take a look anyway, 
because there's a ton of documentation that just may help turn you 
into one.


*5. THE VALUE OF AN INTRINSIC CONSTANT                 
        
VBA offers several intrinsic constants--predefined values that can't 
be changed. For instance, the DAO model offers several constants that 
represent the different record status conditions. In addition, all of 
the Office applications have native intrinsic constants. 

Although we recommend that you use the constant instead of the value 
in your code, sometimes you need to know the value. To learn a 
constant's actual value, simply run it in the Immediate or Debug 
window. For instance, to learn the value for the dbRecordNew DAO 
constant, open the Immediate or Debug window and type the statement: 

?dbRecordNew 

and press Enter. The response is the value 2. You can use this 
technique with almost any constant, as long as you've referenced the 
appropriate library.


*6. OFFICE ERROR MESSAGES                 
        
If you're using a Microsoft Office program as your application's host, 
you might be interested in an Excel workbook that lists all the Office 
error messages and their corresponding values. The name of this file 
is Errormsg.xls, and it is available for download from 

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vba11-25%2C00.html 

Once you've downloaded the file--an EXE file that you should find in 
the 

Program Files\ORKTools\Download\Documents\Cstalert 

folder--run it. The EXE file will install several files, including 
Errormsg.xls. At that point, simply open Errormsg.xls in Excel. Each 
Office application has its own sheet--just click the corresponding tab 
to view the error messages for an application. Since you're working 
with an Excel workbook, you can easily add your own notes and 
information to each record for future reference.


*7. VIEWING MORE THAN YOU THOUGHT                 
        
Do you sometimes wish you had two monitors and two pairs of hands when 
working in a module window? If you need to view different parts of 
your code at the same time, simply split the worksheet into two panes. 
I find a horizontally split module particularly useful when I need to 
return to the beginning of a function or sub procedure to add a 
declaration. I just hop up to the top pane, add the declaration, and 
then hop back down to the bottom window and return to my code. This 
isn't the only use for a split module--it just happens to be my 
favorite. 

If you want a horizontal split, drag down the split box (the small 
rectangle that rests on top of the vertical scroll bar). You'll take 
similar steps to create a vertical split, except drag the split box 
that's to the right of the horizontal scroll bar. Once you've split 
your module into two panes, you can scroll either pane to find any 
section of the same module.


*8. DELETING A SPLIT SCREEN                 
        
In our last tip, we showed you how to split a module into two 
scrollable windowpanes. This tip is particularly useful when you're 
working with a large module. To return your view to just one pane, 
simply remove the split. When you're ready to return to a single 
screen, drag the split bar back to its originating split box. Perhaps 
the easiest way to eliminate a split module is simply to double-click 
the split bar.


*9. PRINTING HELP TOPICS                 
        
You can easily print Help topics by clicking the Print icon in the 
Help window. However, as you know, most Help topics are spread across 
several pages with many subheadings. That means that you must access 
each one and print it to get a set of the entire Help topic. Right? 
Not anymore.  

To print the entire topic, locate the appropriate book in the Contents 
tab. Click the Print button, and in the Print Topics dialog box, click 
Print The Selected Heading And All Subtopics. Then, click OK twice. 

Furthermore, your host application will print the topic continuously, 
rather than printing each heading on a separate page.


*10. NO SECRET CODE IN THOSE COMMENTS                 
        
All developers know that they're supposed to comment their code, and 
most do. However, some don't do a very good job. Too often developers 
want to take shortcuts with their code and write phrases and 
abbreviations. Do yourself a favor and use proper English in your 
comments. In addition, use whole sentences for your comments. As a 
final thought, avoid abbreviations unless they're universally known. 
If you end up being the application's maintenance developer, you'll be 
glad you were so thorough.