Visual Basic for Applications Tips #6



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

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

Proudly presents:
Visual Basic for Applications

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


*1. IF...THEN VERSUS IIF()                 
        
If you routinely assign the result of an Iif() statement to a 
variable, you should consider replacing those statements with an 
If...Then construct. Why? 

First, the Iif() statement is a little harder to decipher because 
of its one-line structure. 

Second, the If...Then construct is faster than the Iif() 
statement. That's right--an If...Then statement is more than 
twice as fast as an Iif() statement that performs the same task.


*2. FIXED-LENGTH VERSUS VARIABLE-LENGTH                 
        
People often ask what the difference is between a fixed-length 
string and a variable-length string. The difference is just as 
the name suggests. A fixed-length string is fixed in size. That 
means that it can handle only so many characters and no more. 
For instance, the statement 

Dim strFixed As String * 10 

limits strFixed to ten characters or fewer. On the other hand, 
the statement 

Dim strVariable As String 

allows strVariable to grow (or shrink) as necessary.


*3. DECLARING DLL FUNCTIONS                 
        
If you're new to VBA, you may also be new to working with DLLs. 
There are three main types of API libraries: GDI, User, and 
Kernel. GDI functions are related to line, text, and bitmap 
output. User functions cover messaging, dialog boxes, and 
controls. The last library, Kernel, applies to system-related 
information, such as memory allocation. Each library contains 
many functions. 

Once you've found an appropriate function in one of the libraries, 
you must declare that function in your application's code. In 
doing so, you'll need to know the name of the function, the 
number of arguments the function expects and their data types, 
and how the function will return the result and its data type. 
The general syntax for declaring an API function is 

Declare Function functionname Lib "libraryname" 
    [Alias aliasname][(argument list)] As resultdatatype 

It's important to note that 32-bit calls are case-sensitive.


*4. WORKING WITH TRUE/FALSE VARIABLES                 
        
You might not realize that you can assign a number or even a 
string to a Boolean variable. Let's take a look at a quick and 
easy example: 

Private Sub BooCheck() 
Dim bytOne As Byte 
Dim intTwo As Integer 

bytOne = True 
intTwo = True 
Debug.Print bytOne = intTwo 
End Sub 

In this case, the True Boolean value is assigned to both a Byte 
and Integer variable, which is quite legal. True simply 
represents the values 0 and -1.


*5. MORE ON BOOLEAN VARIABLES                 
        
In our previous tip, we showed you how the Boolean values True 
and False can be assigned to non-Boolean data type variables. 
To refresh your memory, we used the following procedure to 
assign the Boolean value True to a Byte and an Integer data type: 

Private Sub BooCheck() 
Dim bytOne As Byte 
Dim intTwo As Integer 

bytOne = True 
intTwo = True 
Debug.Print bytOne = intTwo 
End Sub 

If you tried this example, were you surprised to find that 
the expression 

bytOne = intTwo 

equals False? You probably expected the comparison to equal True, 
because bytOne and intTwo both equal True. Since they equal the 
same value, a simple comparison should equal True. But in this 
case, it doesn't. That's because bytOne and intTwo are different 
data types, so they aren't equal in the eyes of VBA--even if you 
assign the same value to both.


*6. WORKING WITH THE OBJECT BROWSER                 
        
By default, the Object Browser displays properties, methods, and 
events in alphabetical order. Sometimes this isn't 
convenient--especially if you're looking for something by type 
rather than by name. Fortunately, you can group both the Class 
and the Members lists by type. Simply right-click the 
appropriate window and choose Group Members. To return the 
grouping option to alphabetical, repeat this process and then 
deselect Group Members.


*7. SPECIFYING DEFAULTS WITH INPUTBOX()                 
        
You probably use the InputBox() function to solicit users for data. 
However, did you realize that you can specify a default value? 
When possible, you'll want to do so, for several reasons: 

- You'll save your users a little data-entry time. 
- You'll cut down on data-entry errors. 
- You'll avoid run-time errors that might occur because of
  missing data. 

To specify a default value, be sure to take advantage of the 
function's third argument--the Default argument--in the form 

InputBox("prompt", "title", default, xPos, yPos, HelpFile, Context) 

For instance, the following statement would offer an input box 
with a default value of "I'll take the really big one": 

InputBox "Please enter the jewel of your choice", "Gems To Go", 
"I'll take the really big one" 

If the default is a value, omit the quotes.


*8. CDBL() VERSUS VAL()                 
        
The Val() function converts digits stored as text to their 
numeric value. However, you need to be careful when using this 
function with formatted text (such as decimal and thousand 
separators). For instance, the function 

Val("1234") 

will return the value 1234. However, the function 

Val("1,234") 

will return the value 1. 

The Val() function truncates all the digits following the 
thousands separator (the comma character). That's because that 
function grabs only those values that fall to the left of the 
first text character in the string. The Val() function 
interprets the comma as a text character--ignoring its 
formatting potential as a thousands separator. 

If the text you need to convert may contain formatting, you 
should use the CDbl() function instead of the Val() function. 
CDbl() recognizes the different separators and responds 
accordingly. The functions 

CDbl("1234)" 

and 

CDbl("1,234") 

both return the value 1234.


*9. PASSWORD-PROTECTING YOUR CODE                 
        
Did you know that you can password-protect your Office VBA 
modules? You can, and, by doing so, you can protect your code 
from anyone who doesn't know the password. To set a password, 
open your spreadsheet, document, or database in its respective 
application. Then, open the VB Editor by pressing Alt-F11. Now, 
right-click the project's name--the first item in the 
hierarchy--and choose Properties from the shortcut menu. 
(Properties will be proceeded by the project's name.) 

Next, click the Protection tab in the resulting dialog box, 
select the Lock Project For Viewing option, and enter the same 
password in the Password and Confirm Password controls. Click 
OK to close the dialog box. If your passwords don't match, 
you'll hear about it now. Save and close your file. 

Reopen your file and try to open the VB Editor by pressing 
Alt-F11. VBA will prompt you to enter your password. If you 
know the password, you can enter it now to access your code. 
If you don't, VBA will not give you access to the VB Editor. 
That means that not only can't you modify existing code, you 
can't add new code. (Access versions before 2000 don't contain 
the VB Editor and therefore don't offer this feature.)


*10. AVOIDING A PRINT TRAP                 
        
A common Word task is to open a document, print it, and then 
close it. To accomplish this, you might use code similar to 

ActiveDocument.PrintOut Range:=wdPrintCurrentPage 
ActiveDocument.Close 

Unfortunately, this code may end up doing nothing if Word is 
printing in the background. When this is the case, VBA doesn't 
pause while the system is spooling the document. Instead, it 
immediately continues to the next statement, which closes the 
document. As a result, the spooling task is never completed and 
the document isn't printed. 

You can avoid this trap by specifying the Background property 
as follows: 

ActiveDocument.PrintOut Background:=True, Range:=wdPrintCurrentPage 
ActiveDocument.Close 

Specifying the Background property forces VBA to wait until the 
print task is complete before executing the remaining code.
