Visual Basic for Applications Tips #13


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

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

Proudly presents:
Visual Basic for Applications

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

*1. RESIZING AN ARRAY                 
        
VBA allows you to resize an array. By resize, we mean you can change 
the number of elements the array stores. For instance, the statement 

Dim iMyArray(3) As Integer 

declares an integer array with three elements. If you need to change 
the number of elements in an existing array, you should use the Redim 
statement in the form 

Redim arrayname(x) As datatype 

For instance, if we wanted to resize iMyArray to handle 10 elements, 
we'd use the statement 

Redim iMyArray(10) As Integer 

Be careful when you resize an array because the elements will lose 
their values.


*2. MORE ON RESIZING AN ARRAY                 
        
In our previous tip, we showed you how to use the Redim statement to 
resize an array. We also mentioned that when you resize an array, the 
elements lose their values. Fortunately, you can retain the element 
values using the Preserve keyword in the form 

Redim Preserve arrayname(x) As datatype 

If you use the Preserve keyword in your Redim statement, VBA will 
retain the value of each existing element in your array.


*3. A VBA ALTERNATIVE                 
        
VBA isn't always the most efficient solution. Occasionally, there are 
noncode solutions that are more efficient, but because we're 
accustomed to using code, we continue to use code. For instance, do 
you use command buttons to open other objects or files? If so, you 
should know that a hyperlink is often quicker and it's always easier. 

You should consider a hyperlink solution when using a command button 
to open another form or report. Here's what you do: In the VB Editor, 
open the form that would normally contain the command button that 
you're eliminating. Choose Insert, Hyperlink. In the Insert Hyperlink 
dialog box, skip the first text box and enter the name of the object 
you want to open in the Named Location In File (Optional) control. 
Finally, click OK. 

Unfortunately, you can't automatically insert a hyperlink in an Excel 
or Word userform.


*4. AVOIDING A WORD PRINTING PROBLEM                 
        
It's easy to print part or all of a Word document with VBA code. For 
instance, the following code prints the current page: 

ActiveDocument.PrintOut Range:= wdPrintCurrentPage 
ActiveDocument.Close 

But what if the document doesn't print? Generally, that can happen 
when Word prints the document in the background, and VBA doesn't pause 
long enough for Word to spool the document. VBA closes the document 
before the printer knows what it's supposed to print. 

There's an easy fix for this problem--simply set the PrintOut method's 
Background argument to True before you send the print parameter. For 
instance, the code below turns on the background printing, then tells 
VBA to print the current page before closing the document. As a 
result, Word and VBA both wait until the print job is complete before 
closing the document. 

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


*5. CHECKING EXCEL                 
        
Using VBA, you can make sure a specific sheet exists in your 
worksheet. First, open the VB Editor by pressing Alt-F11. Then, add a 
new module by choosing Insert, Module. Next, add this procedure: 

Public Function SheetExists(name As String) As Boolean 
SheetExists = False 
For Each sht In ThisWorkbook.Worksheets 
If sht.Name = name Then 
    SheetExists = True 
End If 
Next sht 
End Function 

When you call the function, you'll need to pass the name of the sheet 
you're looking for. If the For loop encounters that sheet, SheetExists 
will return a True value. If the function doesn't encounter the sheet, 
the function returns False. 

Be careful: This function is case-sensitive. If you're looking for 
Sheet1 and you enter sheet1, the function will return False even if 
Sheet1 exists.


*6. CHECKING BACKGROUND PRINTING                 
        
A few tips ago we discussed a problem that can occur when Word is 
printing in the background. You can check for this setting manually by 
choosing Tools, Options, clicking the Print tab, and reviewing the 
Background printing option in the Printing options section. You can 
also check the state of this option using the following VBA statement: 

booBackground = Application.Options.PrintBackground 

The function we gave you a few days ago will work regardless of your 
application's settings. However, at some point, you may need to check 
the user's current settings in this regard.


*7. A FASTER LOOP                 
        
All the loop statements need a way to know when to stop. The 
For...Next statement uses a value and Do...While uses a condition. You 
can specify a specific value, or you can use an expression. A good way 
to speed up your loop is to use variables instead of expressions as 
your loop's stop value. For instance, the following For loop uses the 
number of forms as its stop value: 

For lCounter = 0 To Forms.Count - 1 

Next lCounter 

Unfortunately, VBA must evaluate the Count property before executing 
each loop, which will slow things down a bit. 

A faster alternative is to assign the result of the Count property to 
a variable and then use the variable as the loop's stop value, as 
shown: 

lCount = Forms.Count - 1 
For lCounter = 0 To lCount 
...
Next lCounter 

Now, VBA evaluates the Count property only once. The result is a 
faster loop. (If your loop deletes or adds forms, you may need to 
reevaluate the Count property with each loop.)


*8. USING SPLIT                 
        
If you're still struggling with string parsing, you can relax. VBA 6 
introduces a new function--Split()--that makes parsing much easier. 
The Split() function returns a one-dimensional array containing a 
specified number of substrings. It uses the syntax 

Split(expression[, delimiter[, count[, compare]]]) 

where expression is a string containing substrings and delimiters. The 
delimiter argument is optional; if you omit it, the function will use 
the space character as the delimiter. The count argument is optional 
and represents the number of substrings to be returned; the value -1 
returns all substrings. Finally, the compare argument is also 
optional; it's a numeric value that determines the type of comparison 
when evaluating the substrings. 

The following function is a simple example of how you can use this new 
function to make short work of your parsing tasks: 

Function SplitString() 
Dim sSet As String, iCounter As Integer 
Dim arrSet() As String 
sSet = "We,parsed,this,string" 
arrSet = Split(sSet, ",") 
For iCounter = LBound(arrSet) To UBound(arrSet) 
    MsgBox arrSet(iCounter) 
Next iCounter 
End Function 

The Split() function parses the different substrings from the string 
"We,parsed,this,string"--using the comma character as the delimiter. 
Then, the MsgBox function displays each substring separately.


*9. LEADING ZEROS                 
        
There are several solutions for adding leading zeros to a value, and 
most of them are more convoluted than they need to be. One of the 
simplest methods for adding leading zeros isn't all that intuitive, 
but it's simple and it works. You see, we'll be using the Right 
function to add leading zeros. In a nutshell, you add the value to a 
10-based number that's one place larger than the number of characters 
you need for each entry. For instance, if you want all values to have 
five characters, using leading zeros to fill in as needed, you'd use 
the number 100000--that's one place more than five. The function 

Right(value + 100000, 5) 

will return five characters from the result of adding value to 
100000--including leading zeros. If value equals 30, the function will 
return 00030; a value of 4321 will return 04321, and so on.


*10. COMPILING DURING DEVELOPMENT                 
        
While you're still in the development stage of an application, don't 
use the Compile All Modules command. The first time you make changes 
to any of your code, you will undo the compile, so a Compile All 
Modules command at this stage of the game is a waste of time. When you 
need to compile code during the development stage, use the Compile 
Loaded Modules option. This command compiles only the modules that are 
called by the open module. (Not all VBA hosts offer a Compile All 
Modules command.)
