Visual Basic for Applications Tips #37


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

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

Proudly presents:
Visual Basic for Applications

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


*1. SETTING WATCH EXPRESSION SCOPE

An often overlooked debugging feature is the ability to set watch 
expressions. You may want to watch a global variable. Or you may want 
to keep tabs on a local variable while its procedure is running. To 
add a watch expression, pull down the Debug menu and select Add Watch. 
At this point, you'll have to make some decisions:

- You can specify the subroutine in which you want to watch the 
variable.

- You can specify a break when the value changes or is True, which is 
helpful if you're watching a flag and want to trace how the value 
changes.


*2. PASSING PARAMETERS        

There's always a bit of confusion about passing parameters. When you 
pass a parameter to a function, VBA passes that variable by reference. 
That means if you change the value of the parameter within the 
subroutine or function, VBA will change the value of the original 
variable as well. This may or may not be what you want. If you want to 
prevent this behavior, pass the parameter as ByVal, as shown here:


Private Sub Test(ByVal var As String)


You don't have to use ByVal when you call the function.


*3. MSDN FAVORITES

As useful as the MSDN is, finding what you need can take several 
tries. The keywords that seem logical to me seem never to work. If you 
suffer with the same problem, we can't offer you any help in finding 
the Help information you need--the first time. But we can make it 
easier for you to access that data once you've finally located it the 
first time. After you find a topic you know you'll be reviewing again, 
simply create a bookmark to it. Then, the next time you want to see 
that topic, select it from the Favorites tab.


*4. DAO REFERENCES

You probably know that you can use DAO in an Access 2000 module. This 
flexibility is great for those who don't know ADO yet or are 
converting an Access 97 database to Access 2000. However, should you 
decide to use DAO in an Access 2000 database, be sure to reference the 
most recent version--DAO 3.6. Selecting the older version, 3.51, may 
cause problems in an Access 2000 module.

To create a reference, open any module and select Tools, References. 

by Susan Sales Harkins


*5. CLIPPIT SECURITY ALERT

Microsoft has announced a new security alert with the Office 2000 UA 
Control. This control provides the Show Me functionality in the Office 
Help system. This security hole is a little different from the viruses 
and worms we've become accustomed to. Malicious hackers can attach 
code to their Web sites that will later invade your system. For more 
information and a patch, visit

http://officeupdate.microsoft.com/2000/downloadDetails/Uactlsec.htm 

by Susan Sales Harkins


*6. SIZING UTILITY

Screen resolution is a common problem with working with Access because 
everyone has different preferences. Winsize is a free utility 
available from PC Magazine that displays the pixel dimensions of 
dialog boxes and pop-up menus. For more information, visit


http://www.zdnet.com/pcmag/stories/solutions/0,8224,2470110,00.html


As always, never download anything from the Internet unless you have 
updated virus software.

by Susan Sales Harkins


*7. IF YOU NEED A LITTLE HELP

Many developers create wizards and utilities for their own purposes 
and then make them available as shareware. You'll find such many files 
of this type at

http://www.colbyconsulting.com/

You'll also find free offerings and some demonstration files. For 
instance, you'll find the following: 

- A callback demo for list boxes 
- A demo of how to set and clear references from inside a project 
- A wizard for setting and resetting the custom properties of any 
  MDA/B/E from outside 
- A demo of how to build a back end, export a set of tables to the 
  back end, and link all those tables into the front end 
- A full-blown error handler insertion wizard

As always, when downloading and using shareware, be sure to pay the 
bill. Doing so ensures that developers continue to supply us with 
affordable and free utilities and wizards that make our work easier 
and our time more productive.

by Susan Sales Harkins


*8. FINDING CONSTANTS

If you know just one constant in an enumerated set, you can 
quickly display them all in the Object Browser. First, open the 
browser by pressing F2 (in an open module). Then, enter the 
known constant in the Search Text control and press Enter or 
click the Search tool. The Browser will display the constant in 
the Search Results control in the middle of the browser. In 
addition, the Classes control will update accordingly--showing 
the Class the constant belongs to. That means the Members of the 
control to the right will display all the constants that apply 
to that particular Class.

by Susan Sales Harkins


*9. WORD HEADERFOOTER OBJECT                    
           
Word's HeadFooter object is a bit odd in that it has no 
methods. However, this object has several properties:

- Application: Returns an Application object. 
- Creator: Returns a 32-bit integer that indicates the 
  application in which the specified object was created. 
- Exists: Determines whether a first-page or odd-page header or
  footer exists. (The primary header and footer always exist.) 
- Index: A read-only property that returns a number that 
  indicates the position of an item in a collection. 
- IsHeader: A read-only property that returns True if the 
  specified HeaderFooter object is a header; returns False 
  when the object isn't a header. 
- LinkToPrevious: Links the specified header or footer to the 
  corresponding header or footer in the previous section. 
- PageNumbers: Returns a PageNumbers collection, which holds all
  of the PageNumber objects for the header or footer. 
- Parent: Returns the Parent object. 
- Range: Returns a representative Range object for the portion of
  a document that's contained in the specified header or footer.
- Shapes: Returns a representative Shapes collection that 
  represents all the Shape objects in the specified header
  or footer.

by Susan Sales Harkins


*10. OPEN WORKBOOKS FROM THE VBE

When working in an Excel module, you might need temporary access
to an unopened workbook. You could stop what you're doing, 
return to the workbook environment, and use the File, Open 
command. Or you can enter the following command in the 
Immediate window: 

Workbooks.Open "workbookname" 

where workbookname identifies the workbook by name to open the 
workbook. Be sure to specify the complete path and the filename's 
extension.

by Susan Sales Harkins
