Visual Basic for Applications Tips #28



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

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

Proudly presents:
Visual Basic for Applications

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


*1. USING IF...THEN EFFICIENTLY                 
        
If an If...Then statement includes an Exit Sub or Exit Function 
statement, you should specify that evaluating condition first, 
if possible. That way, if the exiting condition is met, VBA 
doesn't have to evaluate the other conditions before finally 
evaluating the exit condition and exiting the procedure. Most 
likely, you won't notice any difference in performance, but your 
If...Then will be more efficient. This is especially true if your 
If...Then has several Else and/or ElseIf clauses.


*2. RUNNING MACROS IN THE VBE                 
        
Once you add a piece of code to a userform or a control, you can 
run the procedure to see if it works properly. To do so, select 
the form or control and then press F5. Or click the 
RunSub/RunForm button on the Visual Basic Editor's 
Standard toolbar. 

Sometimes when running code in this manner, the Macros dialog box 
appears. When this happens, there's nothing to worry about. It 
just means that you didn't select the form or control before 
trying to run it. To continue, simply close the Macros dialog 
box, select the form or control, and try again.


*13. TESTING FOR A BLANK STRING VARIABLE                 
        
There's little doubt that empty variables can wreak havoc on your 
code. Fortunately, there are several easy ways to test a variable 
before using it. For instance, the expression 

sVariable = vbNullString 

will return True if sVariable equals "" or False if sVariable has 
value. The vbNullString constant represents an empty string (""). 
Similarly, the expression 

sVariable = "" 

will return True if sVariable is empty and False if sVariable 
contains a string. However, there's an even faster expression. 
Consider the simple expression 

booResponse = Len(sVariable) = 0 

If sVariable is blank, its length equals 0, and the expression 
returns True. When sVariable equals a data string, booResponse 
equals False.


*4. PADDING NUMERICAL STRINGS WITH ZEROS                 
        
Adding leading zeros to a numerical string is a fairly common 
task and can be accomplished with formats. However, the procedure 
shown here is more flexible than a format, since you can change 
the number of zeroes at any time. 

Function ZeroPad(pad As String, places As Integer) 
While places > Len(pad) 
pad = "0" & pad 
Wend 
ZeroPad = pad 
End Function 

You'll pass the string you want to pad with zeros and the number 
of places you want the finished string to contain. Then, as long 
as the number of places is larger than the string you want 
padded, the procedure adds a zero. For instance, if you want to 
pad the value 2 with enough zeroes to make the final string 
three characters, you'd pass this function the values 2 and 3. 

The procedure will cycle through the While loop twice, each time 
adding a 0 character to the value 2. The final value will be 002.


*5. MOVE VERSUS TOP/LEFT                 
        
To move a control to the top-left corner of a form or userform, 
you might use the following combination of statements: 

control.Top = 0 
control.Left = 0 

There's nothing wrong with this code; it works just fine. But if 
you're working with lots of controls, there's a faster way--use 
the Move method instead. The following code performs faster and 
reduces your code from two statements to one: 

control.Move 0, 0


*6. DO YOU REALLY NEED THAT PUBLIC FUNCTION?                 
        
You probably see constants declared in the General Declarations 
section from time to time. This is particularly true when your 
code contains API calls. However, sometimes we find these 
constants are misplaced because they're used by only one function. 
When this is the case, you should consider moving your constant(s) 
to the procedure that uses it (or them). That's because Access 
will automatically release the memory used to declare those 
constants when the procedure ends. 

On the other hand, constants declared at the module level remain 
in memory for the lifetime of the application. As you can see, 
if the constant is used by only one function, you could be 
wasting resources. 

If you must leave the constant in the General Declarations 
section, you can still free up those resources. Simply reset the 
constants in the last procedure that refers to the constant. For 
instance, if your constant is a string variable, you'd use the 
statement 

strMyString = "" 

to free up any resources previously assigned to strMyString.


*7. RESETTING THE TAB INDEX PROPERTY                 
        
A control's TabIndex property determines that control's position 
in the tab order sequence. For instance, the control with a 
TabIndex of 0 is the first control to receive focus. When you 
press Tab, your form will select the control with the TabIndex 
of 1, and so on. Initially, this value is relative to the order 
in which you add controls to your form. This means the first 
control you create has a TaxIndex value of 0; the next control 
will receive a value of 1; and so on. 

However, it's common to move controls around during the design 
stage, so you'll probably need to update the TabIndex property 
for a few, if not all, of your controls once you've completed the 
form. A quick and easy way to reset each control is to select the 
control that should be last in the order and set its TabIndex 
property to 0. Then, select the next-to-the-last control in the 
sequence and repeat this process. Doing so will force the last 
control in the sequence (and the first control you set) to update 
its property to 1. Next, select the next-to-the-next-to-the-last 
control in the sequence and set its property to 0. When you do, 
the next-to-the-last control will reset itself to 1, forcing the 
last control to reset itself to 2. Continue in this manner until 
you reach the control that you want to be first in the order. At 
this point, all of the controls should be in order


*8. NONTRADITIONAL OBJECT REFERENCING                 
        
Usually, you use an explicit reference to a particular object or 
field, but doing so can create some rather lengthy code if you 
have several objects to reference. 

Fortunately, you can use variables to reference controls and 
fields, which is one reason we recommend that you use prefix tags 
when naming your controls. (In just a minute, this statement will 
make more sense.) Instead of using the ! identifier to explicitly 
reference controls, try the following syntax: 

For i = 0 to 3 
Me("txt" & i) = rst(i) 
Next i 

This short For loop can replace numerous lines of explicit 
references. However, your controls (or fields) must have the same 
prefix or name, such as txt0, txt1, txt2, and so on. 

Unfortunately, this loop raises an error if you have less than 
four fields (For i = 0 To 3) in your recordset. We'll have the 
solution to that problem in a future tip.


*9. RETURNING THE COMPUTER NAME                 
        
You can return the name of the current system by calling the 
GetComputerName API function. This function requires two 
arguments: a buffer that holds the returned name and the maximum 
size of the buffer. In addition, this function returns a 
Null-terminated string, so you must trim the extra characters 
from the name before you try to use it. 

You can create a quick example of this API by inserting a command 
button on a blank form or userform and adding the following code 
to the form's module: 

Private Declare Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal _ lpBuffer As String, nSize As Long) _
As Long 

Function GetComputer() As String 
Dim temp As Long 
Dim Name As String * 255 
Name = Space(255) 
temp = GetComputerName(Name, 255&) 
GetComputer = Left$(Name, InStr(Name, vbNullChar) - 1) 
End Function 

Private Sub Command1_Click() 
Dim Name As String 
Name = GetComputer 
MsgBox "The computer name is " & Name 
End Sub 

Be sure to attach the Sub function to the button's Click event. 
If you're new to APIs, the Declare statement goes in the form's 
General Declarations area. Once you've added the code, simply 
run the form and click the command button to return the 
computer's name.


*10. SAVING RESOURCES                 
        
In our previous tip, we showed you how to call an API to return 
the current system's name. In that tip, we used the statement 

Dim Name As String * 255 

to declare the Name variable and to limit its size to 255 
characters. You can save on resources if you know that the size 
of your computer name doesn't require that many characters. For 
instance, if you know for certain that the name will never be 
longer than 15 characters, you can limit the variable's size to 
15 using the statement 

Dim Name As String * 15
