Visual Basic for Applications Tips #42


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

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

Proudly presents:
Visual Basic for Applications

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


*1. LET THE USERS KNOW THE APP IS BUSY

by Susan Sales Harkins

If an application is going to be busy for a while, you really 
should let the user know. The easiest way is to change the 
mousepointer to the readily understood hourglass icon. This 
quick-change act is easy to include in most procedures. 
Simply include the statement

Screen.MousePointer = vbHourglass

right before the code that will tie things up for a while. Once 
the task is complete, be sure to reset the mousepointer using 
the statement

Screen.MousePointer = vbDefault


*2. MOUSEPOINTER FOR ERROR-HANDLING ROUTINES

by Susan Sales Harkins

In our previous tip, we showed you how to inform your user 
visually when a task may take a few minutes by using the 
MousePointer property in the form

Screen.MousePointer = vbHourglass

Be sure to return your mousepointer to normal using the statement

Screen.MousePointer = vbDefault

at the end of a task. However, the end of the task isn't the 
only place you'll want to include this statement. Be sure to 
include it in error-handling routines. In addition, if you call 
an outside procedure or function, you might need to include a 
statement that resets the mousepointer in that function or 
procedure. The latter suggestion is a little tricky and isn't 
always necessary, but if there's any chance that this called 
function or procedure could be the last stop in your task, you 
should consider resetting the mousepointer--just in case.


*3. REMOVE A DLL FILE

by Susan Sales Harkins

DLLs make Office and VBA tick, but they also tend to blow things
up. Why? Because DLLs come in different versions and applications
sometimes call an older version that's obsolete. It's a good idea
to get rid of DLLs you no longer need. To do so, you'll run the 
regsvr32.exe utility from the Windows Run command in the form

Regsvr32.3x3 /u dllpath

where /u is the unregister switch and dllpath is the DLL's full
pathname.


*4. LET MACROS LIGHTEN YOUR LOAD

by Susan Sales Harkins

Generally, these tips are about VBA, but most people refer to 
VBA procedures as macros. That's why we thought some of you 
might be interested in a shareware program named Macro Anywhere,
which is available from

http://www.pcworld.com/fileworld/file_description/
0,1458,7419,00.html

This program lets you set a keyword or keystroke combination to 
produce text--from a short headline to an entire document.


*5. STABILIZING VBA

by Susan Sales Harkins

Working with VBA a lot has a tendency to destabilize Windows 
(specifically versions 95 and 98). If you start receiving 
General Protection Faults, it's time to reboot. There's really 
nothing else you can do. We recommend that you reboot a few 
times each day during any serious development sessions. Doing 
so should help you prevent those errors altogether.


*6. REPLACE WORD TEXT

by Susan Sales Harkins

Replacing text in a Word document is fairly easy using the Find 
and Replace feature, but this process take a while because Word 
scrolls through the entire document, highlighting each 
occurrence of the word. If you'd like a quicker search and 
replace, try the following procedure:

Function FindStuff() 
Dim strFind As String 
Dim strReplace As String 
strFind = InputBox("What are you replacing?") 
strReplace = InputBox("What is the replacement text?") 
With ActiveDocument.Content.Find 
    .ClearFormatting 
    .Replacement.ClearFormatting 
    .Text = strFind 
    .Replacement.Text = strReplace 
    .Execute Replace:=wdReplaceAll 
End With 
End Function

VBA will prompt you for the text you're replacing and the 
replacement text. Then, the procedure replaces the appropriate 
text without visibly looping through the document. Word allows 
you to undo these changes. Simply select Edit, Undo after you 
run the procedure and before you take any further action.


*7. REPLACE EXCEL TEXT

by Susan Sales Harkins

In our previous tip, we showed you a VBA procedure that replaces
text in a Word document. You can do the same in an Excel sheet 
using the following procedure:

Function FindStuff() 
Dim strFind As String 
Dim strReplace As String 
strFind = InputBox("What are you replacing?") 
strReplace = InputBox("What is the replacement text?") 
Cells.Replace What:=strFind, Replacement:=strReplace, LookAt:=xlPart, 
SearchOrder:=xlByRows, MatchCase:=False 
End Function

This procedure works only with the active sheet. You can't 
undo these changes.


*8. LIMITING REPLACEMENTS TO A RANGE IN EXCEL

by Susan Sales Harkins

Our past two tips have covered replacing text in Word and Excel.
When working in Excel, you can specify whether you want to 
replace text in the active sheet or a specific range. To limit 
replacements to a specific range, use the procedure

Function FindStuff() 
Dim strFind As String 
Dim strReplace As String 
strFind = InputBox("What are you replacing?") 
strReplace = InputBox("What is the replacement text?") 
Range("nameofrange").Replace What:=strFind, Replacement:=strReplace, 
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 
End Function

where nameofrange identifies a valid range.


*9. LIST FONTS

by Susan Sales Harkins

Word has a collection named FontNames that contains a list of 
all the available fonts. If you'd like to see a list of these 
fonts, you can run the following procedure:

Function FontNamesList() 
Dim varFont As Variant 
For Each varFont In FontNames 
    Debug.Print varFont 
Next 
End Function

This procedure will list all the available fonts in the Visual 
Basic Editor's Immediate window.


*10. USE STOP TO DEBUG

by Susan Sales Harkins

You might not realize it, but you can work error handling into 
a regular procedure. For instance, a Select Case or If 
statement is the perfect spot. Let's suppose a procedure 
includes a Select Case statement that accounts for every 
possible situation the statement might encounter. You might 
think you don't need an Else statement, but including one is a 
good idea. That way, if the unexpected happens, the code in the 
Else statement can warn you. For example, the Else action code 
might display a message box that clearly identifies the error. 
After receiving this message, you can press Ctrl-Break and 
examine the variables to discern just what triggered 
the Else clause.
