Visual Basic for Applications Tips #31



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

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

Proudly presents:
Visual Basic for Applications

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


*1. RETURNING A BIMONTHLY INTEGER                 
        
Lots of us do business in bimonthly increments--which means we 
divide our year into two segments per month, for a total of 24 
segments. You can easily track these segments using the function 

Function bimonthly(datestring As Date) 
Dim d As Integer, m As Integer, s As Integer 
d = Format(datestring, "d") 
m = Format(datestring, "m") 
If d > 15 Then 
s = m * 2 
Else 
s = m * 2 - 1 
End If 
Debug.Print s 
End Function 

where datestring is the date value in question. Of course, you'll 
want to replace the Debug.Print statement with a more appropriate 
task for returning the results. 

The first two lines of this function assign the day and month 
values to the variables d and m. The If...Else statement then 
determines whether d is in the first or last half of the month. 
If d is in the last half, the first conditional action 

s = m * 2 

multiplies the month integer (1 through 12) by 2. If d is in the 
first half of the month, the Else action 

s = m * 2 - 1 

multiplies the month integer by 2 and then subtracts 1.


*2. USING WORD FORMATS                 
        
Applying professional-looking formats to a document is fairly 
easy. The following code will open a new document in Word from 
most Office applications: 

Dim objWord As New Word.Application 
objWord.Visible = True 
objWord.Documents.Add 

At this point, you can work in Word manually. Or you can send 
text from your application to Word using the statement 

objWord.Selection.TypeText "yourtext" 

where yourtext represents the text you want to format. If you 
want to select the entire document, use the statement 

objWord.Selection.WholeStory 

To modify the font size, use the statement 

objWord.Selection.Font.Size = sizeinteger 

where sizeinteger is the font size you require. 

Of course, for this code to work, you must reference the Word 
library. To do so, choose References from the Visual Basic menu 
and select Microsoft Word 8.0 Object Library (or 9.0 if you're 
using Office 2000). Once you do so, use the Object Browser to 
find the dozens of properties and methods you can use to alter 
your document. Be sure to close your function with the statement 

ObjWord = Nothing


*3. USING THE GLOSSARY                 
        
Do you have as much trouble finding information in the Help system 
as I do? Sometimes, no matter what I enter, I get nothing, but I 
know there's bound to be useful information--I'm just not asking 
the right questions and searching on the right words. If you have 
this problem too, try using the glossary. First, open the Help 
system and select the Contents tab. Enter the word  

glossary 

and then double-click glossary when the Help system displays it. 
At this point, Help displays a list of words in alphabetical 
order. Simply locate the most appropriate word for the subject 
you're searching and click it. You might have to click a few 
different words, but you're bound to find what you're looking for.


*4. USING ADO'S FIND METHOD                 
        
The Find methods in DAO and ADO aren't the same--although their 
task is still the same. The Find method searches a recordset for 
a record that matches specific criteria. In doing so, the DAO 
Find method allows you to combine multiple conditions using the 
And operator. However, the ADO method doesn't. The ADO method 
uses the form

rst.Find fieldname operand value 

where fieldname is the name of the field you're searching; 
operand is a logical operator, such as =; and value is the data 
you're trying to match. A simple ADO Find task might resemble 
the following: 

rst.Find "LastName = Smith"


*5. SO MUCH FOR COMPATIBILITY                 
        
VBA is specifically designed to work with SQL Server and Oracle. 
However, as you probably know, lots of Visual Basic applications 
access Jet databases. Theoretically, you should be able to switch 
from a SQL Server database to another database and experience no 
problems, as long as you change the OLE DB provider. 
Unfortunately, the Jet database has problems with the Jet OLE 
DB provider. 

This means you may run into bugs when connected to a Jet 
database. If you encounter problems, you should consider 
creating a new ODBC data source--one that uses the OLE DB 
provider for ODBC drivers and the Access ODBC driver. 

To create an ODBC data source, open the Windows Control Panel 
and double-click ODBC (32-bit). With the DSN tab selected, click 
Add. Next, select the appropriate Jet database driver and click 
Finish. Enter a name and description for the data source. Next, 
click Select and find the database you're connecting to. Doing 
so should display the path to your database. At this point, click 
OK, and Windows will display the Data Source Administrator dialog 
box with the new data source. Click OK. 

The verdict is out as to whether Office 2000's Access performs 
any better than before.


*6. PARSING WITH FORMAT()                 
        
Parsing is one of those tasks that most developers hate--mostly 
because you can't do it with a generic round of code. Invariably, 
you end up writing unique code for every new parsing task. One 
thing you might consider is using Format() instead of string 
functions. Doing so isn't always appropriate, but when it is, it 
can be simpler to write and apply. For instance, let's suppose 
you have a nine-character string and you need to insert a space 
character (or any other character for that matter) after every 
third character. To do so, you might consider a typical parsing 
expression such as 

results = Left$(9digitstring, 3) & " " & Mid$(9digitstring, 4, 3) & " 
" 

But the Format() function is more efficient and easier to read: 

results = Format$(9digitstring, "!@@@ @@@ @@@") 

This alternative to parsing isn't always available; you can't 
always avoid string functions. But when Format() can do the trick, 
you might as well use it.


*7. TO COMMENT OR NOT TO COMMENT                 
        
Comments are an important part of your code, and we've discussed 
whether to comment or not to comment in previous tips. What we 
haven't discussed, though, is what your comments should say. So, 
we took a short poll of a few developers and here's the gist of 
their responses: 

Comments should convey the purpose of the code, not how it works. 
You can decipher the code and figure out how it works. However, 
you might not know why it exists--that's why you use comments. 
For instance, you don't need comments to break down an expression 
by its mathematical importance. Instead, the comment needs to tell 
you that the following code figures each customer's discount, or 
each salesperson's commission, or a student's final grade. 

Comments shouldn't extend off the edge of the screen. Instead of 
one long line, enter several short lines, all of which are 
visible onscreen without scrolling. 

Comments can and probably should include notes about problems 
that you encountered while testing and the solutions you tried. 
These notes may keep you from trying the same thing (and failing 
again) a year or so down the road.


*8. RETURNING THE DEFAULT DIRECTORY                 
        
Need to know where Word will save your documents before you save 
them? If so, the Options object is just what you need. The 
following statement will return the destination (default) folder: 

Options.DefaultFilePath(wdUserTemplatesPath) 

To try it, press Alt-F11 to open the VB Editor and enter the 
statement in the Immediate window. Be sure to precede the 
statement with the ? character, so VBA will print the result.


*9. WHAT'S THE PATH SEPARATOR?                 
        
Do you ever concatenate file and directory names to create a 
complete path? Most of us do at some point or another. There's 
one problem though--if you hardcode the path separator, you 
could run into trouble. The path separator is the character 
used to separate the directories and files. Most of the time 
that character is the backslash character (\). 

If the system default isn't the backslash character and you 
hardcode the backslash character, your function will fail. 
Fortunately, if you're using Word, you can use the 
PathSeparator property in the form 

Application.PathSeparator 

It's that simple. Instead of hardcoding the separator character, 
simply concatenate the result of the PathSeparator property.


*10. COUNTING FORMS                 
        
If you need to check for loaded files during the testing and 
debugging stage, open the Immediate window (while the current 
form is paused) and enter the statement 

? Forms.Count 

This statement will return the number of loaded forms.
