Visual Basic for Applications Tips #11


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

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

Proudly presents:
Visual Basic for Applications

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

*1. MOVING AROUND                 
        
In December, we showed you how to move backward through a recordset 
using the Move method in the form: 

rst.Move -2 

where rst is the name of a recordset. When you specify a negative 
value, VBA moves the current record position backward through the 
recordset. 

What you might not realize is that this holds true even if your 
recordset is a forward-only type. The one stipulation is that the 
record you're trying to access must be in the current set of cached 
records. If the Move method tries to move to a position before the 
first cached record, VBA will return an error.


*2. A QUICK PRINT                 
        
You can quickly print a form and its code from the VB Editor. Simply 
select the form you want to print and then choose Print from the File 
menu on the VB Editor's Standard toolbar. The VB Editor will then 
display the Print dialog box, which will offer several options. You 
can print the form's image, its code, or both. You can even change the 
print quality (the default is High) or send the form to a print file.


*3. WORD BUG                 
        
The Is operator doesn't always work as expected in Word--specifically, 
it has trouble with the Range object. The Is operator compares two 
object variables. You use this operator in the form: 

object1 IS object2 

If the objects are the same, the statement is True. If they aren't the 
same, the statement is False. For instance, the following code 
compares the same worksheet to itself: 

Dim wks1 As Worksheet, wks2 As Worksheet 
Set wks1 = ActiveSheet 
Set wks2 = ActiveSheet 
MsgBox wks1 Is wks2 

The message box will display the value True, since the two sheets are 
the same. However, when used with the Range object, the Is operator 
runs into trouble. The following code will incorrectly return the 
False value: 

Dim rng1 As Range, rng2 As Range 
Set rng1 = ActiveSheet.Rows(2) 
Set rng2 = ActiveSheet.Rows(2) 
MsgBox rng1 Is rng2 

There is an easy workaround. When working with the Range object, use 
the equal operator in the form: 

MsgBox rng1 = rng2


*4. CHANGING A CONTROL'S NAME                 
        
It's a good idea to name a control as soon as you create it, if you 
plan to give it a name other than its default. If you decide to give 
the control a more descriptive name later, you'll have to update all 
the code that references that control. Most importantly, you'll need 
to update the control's event procedures. VBA won't update these for 
you, which explains why your control ceases to work if you change its 
name. Simply locate the original procedure in the module and update 
the name. For instance, if you rename a command button named 
CommandButton1 to cmdOpen, you should find the original procedure 
name: 

CommandButton1_Click() 

and rename it accordingly: 

cmdOpen_Click()


*5. UNIVERSAL CURRENCY                 
        
When referring to currency in your code, avoid strings. For instance, 
it might be tempting to use the following statement: 

strAmount = "$999.99" 

If you use this statement, there's no way to control the way your 
application displays this currency amount, which means it has no 
international appeal. Even though your application may never see the 
international market, it's just not a good idea to hard code formats 
unless it's absolutely necessary. Fortunately, you can avoid the 
problem altogether if you use the CCur function in the form: 

strAmount = CCur(amount) 

The CCur function will provide internationally aware conversions from 
any other data type to Currency.


*6. HIDING FORMS                 
        
You can use the Hide method to close the current form so that you can 
return your document or activate another form. If the form is modal, 
the method must be in an event procedure belonging to the form. When 
this is the case, you can run the hide method by simply adding it to 
your code--you don't need to specify the form, since VBA knows you 
mean the current form. Most likely, you'll add the Hide method to an 
OK, Cancel, or Close command button.


*7. RESETTING ARRAY ELEMENTS                 
        
Generally, the first element in an array is 0. You can force the first 
element to be 1 by using the Option Base statement. Specifically, 
enter the statement: 

Option Base 1 

in the General section of your module. As a result, the elements in 
your array will begin with the value 1 instead of 0. For instance, the 
following procedure's first element will be 1: 

Option Base 1 

Function TestArray() 
Dim iMyArray(3) As Integer 
iMyArray(1) = "x" 
iMyArray(2) = "y" 
iMyArray(3) = "z" 
MsgBox iMyArray(1) 
End Function


*8. MORE ON RESETTING ARRAY ELEMENTS                 
        
In a previous tip, we showed you a quick way to force an array's 
elements to begin with the value 1 by entering the statement Option 
Base 1 in the module's General section. You can accomplish the same 
thing when you declare your array. For instance, the statement:  

Dim iMyArray(3) As Integer  

declares an integer array with three elements. By default, the value 0 
will represent the first element, 1 will represent the second, and 2 
will represent the third. If you want to force the first value to be 
something other than 0, simply say so in the declaration statement in 
the form:  

Dim iMyArray(firstelement To lastelement) As Integer  

For example, if you wanted to start with the value 1, you'd use the 
statement:  

Dim iMyArray(1 To 3) As Integer 

CORRECTION: 
MOB FORMING...stop SEND REINFORCEMENTS... stop 
You transform a few values and suddenly they want to lynch you. All 
kidding aside, we did make a mistake in our division tip awhile back. 
We said the result of the expression 10 Mod 4 was 5. Of course, that's 
incorrect. The Mod operator always returns the remainder, which in 
this expression is 2. We apologize for any inconvenience. Thank you to 
everyone who gently pointed out our error.


*9. HOW FAST DOES YOUR APPLICATION LOAD?                 
        
Let's face it--appearances count, and if your application appears to 
load fast, it will make a great first impression. One way to improve 
load time is to trim down your startup form. Here are a few guidelines 
you can follow to streamline your startup forms and help your 
application load quickly:  
  - Use as little code in your startup form as possible.  
- Store all the code that your startup form requires in that form's 
module. (Don't call function procedures from a standard module.)  
- ActiveX controls require more time to load, so avoid using ActiveX 
controls in your startup form.  

  
If you follow these simple guidelines, your startup form should load 
quickly. Of course, other objects may still be loading, but perception 
is the key in this discussion. The faster that first form loads, the 
faster the user perceives he or she is getting to work.


*10. FORM EVENTS                 
        
There are several form (userform) events, but some of them can be 
confusing. Take the Initialize and Activate events. It's difficult to 
know just which one to use. In fact, there's a big difference between 
the two events: 
 - Initialize: Use this event when you want to run a task when VBA 
first loads the form. 
- Activate: You'll use this event when you want to run a task each 
time the form is displayed.

As you can see, choosing the right event can be critical to your 
form's success.