Visual Basic for Applications Tips #20


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

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

Proudly presents:
Visual Basic for Applications

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


*1. AGGREGATE FUNCTIONS CAN BE SLOW                 
        
Aggregate functions grab values from tables based on criteria, and 
they're very useful in the right circumstances. If you're working with 
local tables or even small-networked tables, you can probably use 
aggregate functions without affecting the overall performance of your 
application. However, when you're working with large tables on a 
network, aggregate functions can slow things down significantly. Why? 
Because aggregates search your entire table without using an index. 
That means Access compares your criterion to every record in the 
table. 

An alternative is to open a recordset and return the value that 
way--be sure to assign an appropriate index. Using this method, Access 
will stop searching for the appropriate record and value once it 
matches the criteria. In addition, the procedure will perform what's 
known as a binary search, which means it won't look at each record. 
Instead, it makes spot checks and then, depending on the result, 
continues moving forward or backs up--much the same way you might find 
a name in a telephone book. 

Building a recordset, setting an index, and then searching for a 
matching value certainly requires more code, but sometimes more is 
better, and this is one of those cases. 


*2. A REFEDIT CONTROL ALTERNATIVE                 
        
In Microsoft Excel, you probably use the RefEdit control when you need 
to solicit a range from the user. If you'd rather not fool with a 
control, you can use the simple procedure below. This procedure takes 
advantage of the InputBox() function's range type (that's what the 
number 8 represents). 

Function UserRange() 
Dim MyRange As Range 
Set MyRange = Application.InputBox("Select a range of cells", , , , , 
, , 8) 
End Function


*3. THE FOR...EACH STATEMENT IN WORD                 
        
I make good use of the For...Each statement. In fact, I probably use 
it more than any other statement. For instance, if I want to find a 
particular word in a Microsoft Word document and apply some kind of 
formatting to it, I use the For...Each statement. The procedure below 
is a good example; it applies bold formatting to every occurrence of 
the word "vital" in the current document. 

For Each Wrd In Selection.Words 
  If Trim(Wrd) = "vital" Or Trim(Wrd) = "Vital" Then Wrd.Bold = True 
Next Wrd


*4. ADO CURRENT PROJECT                  
         
We've had a lot of people ask how to use ADO to connect to the current 
database. That's because ADO is typically used with foreign data 
(although we'll all be using it in the future, since Microsoft plans 
eventually to replace DAO with ADO). As a result, most examples and 
documentation stick with connections to files other than the current 
file. If you'd like to use ADO in the current database, use the 
following code to open a connection to the current database: 

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset 
Set cnn = CurrentProject.Connection 

Then, refer to your cnn variable when you open your recordset in the 
form 

rst.Open source, cnn, cursortype, locktype 

where source identifies your data source, cursortype is one of four 
constants that identify the position of your cursor, and locktype is 
also a constant that specifies your locking preference. As a rule, 
you'll use adOpenKeyset and adLockOptimistic, respectively. Now, let's 
suppose you want to open a recordset based on a table named tblMyWork 
in your .mdb file. To do so, you'd use the code 

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset 
Set cnn = CurrentProject.Connection 
rst.Open "tblMyWork", cnn, adOpenKeyset, adLockOptimistic 

You can replace the cnn variable with the connection reference in the 
form 

rst.Open "tblMyWork", CurrentProject.Connection, adOpenKeyset, 
adLockOptimistic

---By Susan Harkins


*5. NUMERIC NULLS                  
         
Technically, there's no such thing as a numeric null--if there's no 
value, the value equals 0. However, you probably encounter the 
annoying message 

Invalid Use of Null 

enough to know there really is a numeric null! More often than not, 
code returns this message because the function or expression you've 
run doesn't equal anything. For instance, the Dlookup() function will 
return a Null value if it doesn't encounter the field entry you've 
specified in the function's where argument. And that's where the error 
comes in--because VBA won't allow you to assign a Null value to a 
numeric variable. 

To avoid this error, wrap your functions and expressions that might 
return a Null value in an IsNull() function. The IsNull() function 
will return True when the function or expression returns a Null value 
instead of returning that annoying error message.

---By Susan Harkins


*6. USING CONTINUATION CHARACTERS                  
         
VBA allows you to enter long lines of code in a module, but they're 
hard to read because you can't see the entire line. When this is the 
case, you can use the continuation character (the underscore 
character) to break a line into multiple lines. For instance, you 
might consider breaking the statement 

MsgBox "This is a really long line, perhaps we should break it.", 
vbOKOnly, "Here's the title." 

between the arguments as shown here: 

MsgBox "This is a really long line, perhaps we should break it.",  _ 
  vbOKOnly, "Here's the title." 

You can break a single line many times, but you can't break a line in 
the middle of a string. We've indented the second line to make it more 
readable, but doing so isn't necessary. 

---By Susan Harkins


*7. LET'S PLAY HIDE THE CURSOR                  
         
There's an API that will hide the cursor for you, but you must use it 
carefully because it could be difficult to turn it back on. The 
function itself is simple. Just add the following statement to the 
General Declarations section of your module: 

Private Declare Function ShowCursor Lib "user32" (ByVal bShow As Long) 
As Long 

Then, to turn off the cursor, simply use the call 

ShowCursor 0 

To redisplay the cursor, use the call 

ShowCursor -1 

We'd like to note that when you use this API, the cursor is still 
active--you just can't see it.

---By Susan Harkins


*8. WHERE'S MIN AND MAX                  
         
Typically, you can't use the Min() and Max() functions in VBA. If 
you're working in Access, you can use the Dmax() and Dmin() functions, 
but even they aren't adequate in every case. If you need to learn the 
minimum or maximum value, you can try a Boolean data type in the form 

Dim boo As Boolean 
boo = a < b 

If boo equals True, you know that a is less than b. If boo equals 
False, you know a is greater than b. 

---By Susan Harkins


*9. DEBUGGING WITH BREAKPOINTS                   
          
One of my favorite debugging tricks uses breakpoints. After I set a 
breakpoint, VBA executes the code up to the breakpoint and then stops. 
This allows me to set a variable so I can test the code for certain 
conditions. Here's how it works. First, open the module, select the 
statement where you want VBA to stop executing, and click the Toggle 
Breakpoint button on the Debug toolbar. Next, run the code. When VBA 
stops the code, jump to the Immediate window and enter a statement in 
the form 

variable = newvalue 

Then click the Run Sub/UserForm button on the Standard toolbar. The 
remaining code will use newvalue. This is a great way to set a 
variable for testing when the available data doesn't meet the 
necessary conditions that newvalue represents.


*10. SETTING BREAKPOINTS

In our previous tip, we showed you how to set a breakpoint and reset a 
variable. To set a breakpoint, we told you to position the cursor 
within the line of code where you want VBA to stop executing and then 
click the Toggle Breakpoints button on the Debug toolbar. But there's 
a simpler way. Click the gray margin to the left of the appropriate 
statement, and the VB Editor will automatically insert a breakpoint. 
To remove the breakpoint, click the existing breakpoint icon (the 
little dot in the margin).
