Visual Basic for Applications Tips #12


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

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

Proudly presents:
Visual Basic for Applications

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

*1. REPLACE WITH NOTHING                 
        
Most of you probably know that you can use the Replace() function to 
replace one string with another. To do so, you use the function in the 
form: 

Replace(string,findstring,replacementstring) 

where string is the string you're searching, findstring is the 
character or substring you want to replace, and replacementstring is 
the string you mean to substitute for findstring. 

What you might not realize is that you can use Replace() to delete a 
character or substring by specifying a zero-length string as 
replacementstring. For example, the following statement: 

Replace("Now you see it now you don't", "it", "") 

Returns the string "Now you see now you don't." We completely removed 
the pronoun "it." 

Be careful with that zero-length string though. The zero-length 
string, "", doesn't equal the string " " (where there's a space 
between the two apostrophe characters). These two string characters 
aren't interchangeable.


*2. ANOTHER CONSTANT TIP                 
        
A fairly common sight when working with strings is the expression: 

Chr(13) & Chr(10) 

This combination concatenates a carriage return and a line feed. 
However, there's an intrinsic constant you should use instead--vbCrLf. 
We recommend you use constants whenever possible. They're more 
readable and easier to remember (most of the time) than the value they 
represent.


*3. KEEP AN EYE ON YOUR VARIABLES                 
        
If you have room, you should keep the Locals window open when you're 
debugging. This window displays all the variables in the current 
procedure. Specifically, the window displays the variable names, 
values, and data types. If your procedure updates a variable, the 
window will reflect that change. To open the Locals window, click the 
Locals Window button on the Debug toolbar. Or, you can choose Locals 
Window from the View menu.


*4. ADDING AN ITEM TO A LIST BOX                 
        
To fill a list or combo box, you use the AddItem method. Did you know 
you could add an item to a specific position within the list? You can 
if you include the method's index value in the form: 

ListBox1.AddItem "One", 0 

where 0 represents the first position in the list. For instance, the 
following procedure will display the items "Two" and "Three" in a list 
box named ListBox1. 

Private Sub Form_Activate() 
ListBox1.AddItem "Two" 
ListBox1.AddItem "Three" 
End Sub 

This second procedure will add the item "One" to the beginning of that 
same list when you click the form. 

Private Sub CommandButton1() 
ListBox1.AddItem "One", 0 
End Sub 

(Just remember that the index values begin with 0 and not 1.)


*5. AN IMMEDIATE WINDOW SHORTCUT                 
        
When you want to run an expression, function, or variable in the 
Immediate window, you don't have to retype it. You can drag it from 
its module to the Immediate window and then run it. You may need to 
revamp it just a bit, but you'll save yourself a little time by not 
having to re-enter long expressions, etc. Dragging the statement also 
cuts down on typos.


*6. AN ARRAY TIDBIT                 
        
An array can store any type of data--strings, dates, currency values, 
or numbers. However, an array can hold only one type of data. You 
can't specify one array element as an integer and another as a string. 
Fortunately, there's a way around this limitation. Simply define your 
array as a Variant data type. 

As you probably know, a Variant can store any type of data. By 
declaring your array as a Variant, that array can store any type of 
data, which might come in handy when working with different fields in 
a database. 

You should be aware that a Variant array will consume more memory than 
the other data types. In the right circumstances, the additional 
memory can be an acceptable trade for the added flexibility the 
Variant supplies.


*7. CONCATENATION OPERATORS                 
        
If you convert older applications, you may run into the plus sign (+) 
used as a concatenation character. Older versions of VBA (VB and 
BASIC) used the plus sign before the ampersand became the prevalent 
concatenation operator. VBA continues to support the plus sign for the 
sake of backward compatibility. If you still use the plus sign as your 
operator of choice, we recommend that you begin using the ampersand 
instead, even though VBA still supports the plus sign. 

Each new version usually brings replacements, and as a rule, VBA will 
continue to support replaced keywords, operators, etc. for a while. 
Eventually, Microsoft usually drops the originals to make room for 
newer features. This means that some day, VBA may no longer support 
the plus sign as a concatenation operator. We recommend that you 
familiarize yourself with replacements when a new version is released 
and start using those replacements right away.


*8. AVAILABLE FONT SIZES                 
        
The Font Size tool on the Formatting toolbar lists sizes 8 to 72 (in 
most host applications), but you're not limited to just those sizes. 
If you want a smaller or larger font, use the VBA FontSize property. 
As a rule, you can specify a font size of 1 to 127 using VBA. However, 
just because VBA will allow you to specify a font size doesn't mean 
your printer can print it. When working with unusual font sizes, you 
should test the point size you choose to make sure your printer and 
font can accommodate that size.


*9. LEARNING ABOUT OBJECTS                 
        
If you're new to VBA, you may find learning the various objects, and 
the methods and properties that go with each object, a bit 
overwhelming. Fortunately, a tool in most Office applications can help 
you become more familiar with the object model. That tool is the Macro 
Recorder, and you'll find it in Word and Excel. You see, when you 
write a macro in Word or Excel, you're really creating a VBA 
procedure. 

To record a macro, first choose Tools, Macro, then select Record New 
Macro. Enter a name for the macro in the Macro Name control, or accept 
the default. Identify the document where you want to store the 
document in the Store Macro In drop-down list. If you don't, VBA will 
add the macro to your normal.dot template. Click OK, then perform the 
tasks you want the macro to repeat. Click Stop Recording in the Macro 
Recorder toolbar when you're done. 

Now you need to review the code, so you can learn more about the 
objects and properties you just manipulated. To do so, first choose 
Tools, Macro, then select Macros (or press Alt-F8). Select the macro 
you just recorded in the Macros dialog box and click Edit to display 
the procedure in the VB Editor. 

Although the recorder does have its limits, you can learn about the 
object model and the many properties and methods just by reviewing 
your macro code.


*10. A TIP FOR THE VBA BEGINNER                 
        
Almost everyone indents their code a bit, and if you want to know just 
when to add an indent or tab, several guidelines can help. If you 
don't indent as you enter the code, you can do so later. In fact, 
there are two ways to indent existing code. First, you can select the 
code you want to indent and choose Edit, Indent. The second way is 
simpler. Highlight the code you want to indent and press Tab. 

If you get carried away and add a few too many indentations, you can 
outdent your code as easily as you indented it. After selecting the 
code you want to outdent, you have two options: You can choose Edit, 
Outdent, or you can press Shift-Tab.
