Visual Basic for Applications Tips #30



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

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

Proudly presents:
Visual Basic for Applications

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


*1. NAMING PROJECTS                 
        
Word, Excel, and PowerPoint allow you to open more than one 
document at a time. (You can open only one database in Access.) 
For every document you open, the Visual Basic Editor will display 
a project in the Project Explorer. Every project has its own 
collection of host objects, so it can get crowded in the Project 
Explorer. It's important to make sure that you're working with 
the right project in the VB Editor. Therefore, we recommend that 
you give your objects unique and descriptive names. 

To change a project's name, select Project Properties from the 
Tools menu (in the VB Editor) and enter a name in the Project 
Name control. Or select the Project in the Project Explorer, 
open the Properties window (if necessary), and enter the name in 
the Name property field. To rename documents, userforms, and 
modules, use the Properties window.


*2. A SQL DUH MOMENT                 
        
No product brings a developer to his or her knees quicker than 
SQL. There are so many delimiter rules that even the experts 
have temporarily lapses of judgment. Here's one that is more 
common than you might think--you can't use the Access RunSQL 
method to create a SELECT query. 

At first you might think I've made a mistake. After all, the 
RunSQL method runs SQL statements, right? Yes, but this method 
runs only Action queries. Why? Well, what would you do with the 
result of the SELECT query once you had it? The RunSQL method 
doesn't create a recordset to manipulate--it acts on the existing 
records. When you need to create a recordset without taking any 
action, rely on the Recordset object.


*3. CONCATENATING SPACES                 
        
We often concatenate variables using VBA. Most of the time we're 
working with SQL statements, but not always. An easy mistake when 
concatenating is to omit necessary spaces between the string text 
and the variables. For instance, if you wanted to concatenate a 
simple WHERE clause and a variable, you might try the syntax 

"WHERE [fieldname] = " & variable 

which would work just fine, because SQL anticipates the spaces 
around the equal sign. However, the syntax 

DoCmd.RunSQL "SELECT * INTO" & variable & "FROM tablename WHERE 
tablename.fieldname" & strCriteria & ";" 

doesn't work, although the statement appears to be correct. The 
problem is the missing space after the INTO clause and before 
the variable. There's also a missing space after the variable 
and before the FROM clause. The correct syntax would be 

DoCmd.RunSQL "SELECT * INTO " & variable & " FROM tablename WHERE 
tablename.fieldname" & strCriteria & ";"


*4. QUITTING AN APPLICATION                 
        
In our previous tip, we showed you how to initiate a new instance 
of an Office application from Visual Basic. Of course, opening 
another application consumes a lot of memory. That's why you 
should also follow any Automation task with the Quit method in 
the form 

ObjApp.Application.Quit 

This way, you'll free up the memory previously used by the 
application. Don't forget to also set the object variable 
to Nothing.


*5. MORE ON CONCATENATING SPACES                 
        
In our last tip, we showed you a common mistake with 
concatenation--forgetting space characters. In our solution, 
we simply included the space characters as part of our text 
string. But that's certainly not the only way. You can 
concatenate a space character using the form 

& " " & 

but that's often unnecessary. For instance, the statement 

DoCmd.RunSQL "SELECT * INTO " & variable & " FROM tablename 
WHERE tablename.fieldname" & strCriteria & ";" 

is just as correct as 

DoCmd.RunSQL "SELECT * INTO" & " " & variable & " " & "FROM 
tablename WHERE tablename.fieldname" & strCriteria & ";" 

To make your statements more readable, you can define a constant 
to represent space characters using the statement 

Const constSpace = " " 

Including such a constant as follows 

DoCmd.RunSQL "SELECT * INTO" & constSpace &  variable & 
constSpace & "FROM tablename WHERE tablename.fieldname" & 
strCriteria & ";" 

isn't necessary, as we've already shown. However, it might make 
your statements easier to decipher, but only if you use the 
constant consistently.


*6. QUICKLY LEARNING THE VALUE OF A VARIABLE                 
        
When you run code for debugging purposes, you can quickly learn 
the value of a variable in the Debug or Immediate window. Simply 
position the cursor over any variable (that's been run), and VBA 
will display that variable's value in a ToolTip control. This 
feature can be extremely helpful when a variable changes its 
value or if a procedure is returning erroneous data. You can set 
a breakpoint right after the statement that contains the variable 
so you can check the variable's value. (A Watch expression works 
in a similar manner.)


*7. VIEWING THE OBJECT HIERARCHY                 
        
The Object Browser is a great tool for learning about properties 
and objects. But the browser can't provide an overall view of 
your application's object model. The browser lists all the 
objects in the Classes list, but it doesn't display that list 
as a hierarchy. 

Fortunately, you can find a diagram of the application's object 
model in the Help system. First, choose Contents And Index from 
the Help menu. Next, click the Contents tab, open the entry for 
Visual Basic reference, and double-click the Shortcut entry. 
Then, open the Visual Basic Reference entry and select Microsoft 
ApplicationName Objects.


*8. A SHORTCUT FOR REFERRING TO CONTROLS                 
        
When referring to a control on a form or userform, you probably 
use the syntax 

userform1.Controls("commandbutton1") 

or the syntax 

userform1!commandbutton1 

In the first statement, we use the dot identifier (.) to separate 
the form object from the Controls collection and name the specific 
control. In the second statement, we use the bang identifier (!) 
to separate the specific control from the Forms collection. What 
you might not realize is that the statement 

userform1.commandbutton1 

is also acceptable. In this case, the dot identifier is still 
separating the Controls collection from a specific form. However, 
we don't have to specify the Controls collection because it is 
the default. If we omit it, VBA assumes we're referring to the 
Controls collection.


*9. FINDING AN OBJECT'S DEFAULT PROPERTY                 
        
In our last tip, we showed you a shortcut for referencing objects. 
Specifically, you can omit the object's default reference. At this 
point, you may be wondering how you know what an object's default 
property is. There's an easy way to find out. Just open the Object 
Browser and select the object in the Classes list. The Members 
list will update accordingly, and the default property will 
display a small blue circle right above it.  

Let's look at a quick example. If you're not in the VB Editor, 
press Alt-F11. Once you open the VB Editor, press F2 to launch 
the Object Browser. Next, select any object in the Classes list. 
For instance, if you're using Word, select the Cell object (not 
the Cells collection object). Then, scroll down the Members list 
to the Range property. See the little blue circle just above that 
property? That means Range is the Cell object's default property.


*10. UNDERSTANDING A COMMON BUT OBSCURE MESSAGE                 
        
Have you ever seen error 91: 

Object variable or With block variable not set? 

If so, you know how frustrating it can be to figure it out. It's 
that With reference that's the problem. It makes you think 
there's something wrong with your With syntax. But the real 
culprit is probably just a missing Set statement. 

You see, you must use the Set statement to define an object 
variable. If you forget, VBA will return error 91. For instance, 
the following code declares an object variable and then refers 
to it without defining that variable properly: 

Dim frm As Form 
frm = Forms!MyForm 
DoCmd.OpenForm frm 

Remember, a form is an object, so frm represents an object 
variable. To properly define that variable, you need to remember 
the Set statement as follows: 

Dim frm As Form 
Set frm = Forms!MyForm 
DoCmd.OpenForm frm
