Visual Basic for Applications Tips #27


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

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

Proudly presents:
Visual Basic for Applications

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


*1. SORTING EXCEL WORKSHEETS                   
          
There's no built-in feature that alphabetically sorts Excel 
worksheets. But the following macro will:  

Sub AlphaSort() 
Dim iCount As Integer 
Application.ScreenUpdating = False 
iCount = Sheets.Count 
For i = 1 To iCount - 1 
For j = i + 1 To iCount 
If Sheets(j).Name < Sheets(i).Name Then 
    Sheets(j).Move Before:=Sheets(i) 
End If 
Next j 
Next i 
End Sub 

To create this macro, open the VB Editor by pressing Alt-F11. Choose 
Insert, Module and enter the above macro. Return to your worksheet and 
run the macro to sort the worksheets in the current workbook. To run 
the macro, press Alt-F8, select AlphaSort, and click Run.


*2. DISPLAYING THE MONTH                   
          
If you need to return the month component from a date, you can use the 
Month() function in the form 

Month(date) 

which will return a month's integer value. For instance, if date is 
April 12, 2000, the Month() function will return the value 4. Be sure 
to use the pound sign delimiters when date is a date string (rather 
than an expression that returns a date). For instance, you might use 
the statement 

Month(#August/21/2000#) 

to return the value 8.


*3. KEEP IT SIMPLE                   
          
The two conditional statements 

Do While Not rs.EOF  
Do Until rs.EOF  

are equivalent, but not necessarily the same. The first cycles through 
your looping code while the EOF property isn't False. The second loops 
until EOF is False. Is one preferable? We think so. We recommend you 
avoid using the Not operator when possible. There's nothing wrong with 
the Not operator; it just adds an extra step to interpreting the code. 
If it's not necessary, it's inefficient.  

Having said all that, we do recognize that you can't just replace 
every occurrence of the Not operator--and we're not suggesting you 
should. We're saying that you should evaluate your choice carefully 
and if there's an alternative, use it. Don't waste a lot of time on 
this quest, however. If using the Not keyword works but it takes you 
two hours to come up with a non-Not alternative, don't waste your time 
thinking about it. Go ahead and use the Not keyword.


*4. WORD TEMPLATES                   
          
Templates are common in Word and other Office applications because 
they reduce repetitive formatting tasks. Using VBA, you use the Open 
method to open a template for modification in the form  

Documents.Open templatename 

If you want to base a new document on a template, don't use the Open 
method. Instead, use the Add method in the form 

Documents.Add newdocumentname 

If you try to create a new document using the Open method, you'll 
simply make changes to the .dot file instead of creating a new .doc 
file.


*5. SQL DELETE                    
          
Most SQL statements allow you to work with specific fields, but not 
the DELETE statement. You must specify all the records. This mistake 
is easy to make when you're working with a JOIN. For instance, you 
might use the statement  

DELETE Orders.ID FROM Orders INNER JOIN Temp ON Orders.ID = 
OrderDetail.ID; 

to delete all the records in Orders where the ID value equals the ID 
value in OrderDetail. It won't work, however, because SQL won't delete 
just one field. Instead, use the statement  

DELETE Orders.* FROM Orders INNER JOIN Temp ON Orders.ID = 
OrderDetail.ID;


*6. INT() VERSUS CINT()                   
          
You're probably familiar with the Int() function, which returns the 
integer portion of a value. Similarly, the CInt() function does much 
the same thing. The main difference between the two functions is the 
data type of the value it returns:

- Int() returns the same data type as the value it's passed. 
- CInt() always returns an Integer data type.


*7. THE FRIEND KEYWORD                   
          
A relatively new addition to VBA (with version 5.0) is the Friend 
keyword. You'll use this keyword to determine a procedure's scope. 
Generally, sub functions are limited to the class module that contains 
them. Using the Friend keyword in the form  

Friend Sub name 

exposes name to other modules within the same project. However, you 
can't access the procedure from outside the project (as you can when 
using the Public keyword).  

You can use Friend with properties, sub, and function procedures.


*8. SWITCH EFFICIENCY--PART 1 OF 2                   
          
VBA's Switch() function evaluates a list of expression and returns an 
associated value upon finding the first expression that evaluates to 
True. This function works with two sets of elements--the expressions 
and a corresponding value for each expression in the form 

Switch(expression1, value1, expression2, value2, expression3, value3) 

When expression1 evaluates to True, the function returns value1; if 
expression2 equals True and expression1 is False, the function returns 
value2; and so on. If two expressions return True, Switch() considers 
the first in the list.  

With functions of this sort, you can sometimes improve performance a 
bit by putting the expressions that are most likely to be selected at 
the beginning of the expression list or function. However, that's not 
true with Switch(). All the expressions are evaluated, so you'll gain 
nothing by positioning the expressions in any special order.


*9. SWITCH EFFICIENCY--PART 2 OF 2                   
          
In our previous tip, we talked about the Switch() function. Because 
the Switch() function evaluates all the expressions listed, you 
increase the risk of raising a run-time error. An error can occur even 
if one of the expressions evaluates to True and the error will take 
precedence over the True expression. For this reason, you need to 
consider error handling an important part of any procedure that 
contains the Switch() function.


*10. ADDING ITEMS TO LISTS                   
          
The combo and list box controls allow you to enter a list of items. 
When you choose this route, you enter in the control's Row Source 
property a setting in the form of  

"item1";"item2";"item3" 

You must also specify the Value List setting for the Row Source Type 
property. The result is a list of items, one right after the other, in 
the same order as they appear in the Row Source property setting.  

You might not realize that you can display more than one column of 
items in a Value List control. To do so, you simply add a second item 
to the list in the form 

"item1a";"item1b";"item2a";"item2b";"item3a";"item3b" 

Access will display items 1a and 1b in the first row, items 2a and 2b 
in the second row, and items 3a and 3b in the third row. Just be sure 
to update the Column Count property to reflect the appropriate number 
of columns.
