Visual Basic for Applications Tips #47


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

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

Proudly presents:
Visual Basic for Applications

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


*1. WORD FILENAMES

by Susan Sales Harkins

You may know that the WindowName() function returns the name of 
the current document (in Word). It also includes the file's 
extension--.dot, which may not be what you want. If you'd like 
to omit the extension, use this code:

strName = WindowName() 
bytExt = InStr(strName, ".") 
strName = Left(strName, bytExt - 1) 
MsgBox strName ' display the name

The first line assigns the result of the WindowName() function 
to the strName variable. The next line finds the dot character 
in strName and assigns that character's position to bytExt. Now 
we can get rid of the extension by using the Left() function to 
return all the characters from the beginning of strName up to 
the dot character (bytExt - 1). The last statement simply 
displays the results.


*2. TAX TIME!

by Susan Sales Harkins

No, it's not April yet, but if you work with payroll 
withholding taxes, you might want to check out Boon Docks, 
Inc. at

http://www.taxupdate.com/software/index.html

You can download a free evaluation copy of TaxControls--an 
ActiveX DLL that you can drop into almost any VBA application. 
This software provides current tax tables and quick, easy access
to those tables. If you decide to purchase the software, updates
are sent automatically by email as required.

Don't download anything from the Internet unless you have 
current virus protection installed on your system.


*3. EXCEL CASE

by Susan Sales Harkins

When trying to determine the case of Excel text, you can use 
the LCase() and UCase() functions or the Proper method. You can 
also combine them in one toggling macro--as shown here:

Sub ToggleCase() 
Dim rng As Range 
For Each rng In Selection.Cells 
  Select Case True 
    Case rng = LCase(rng) 
      rng = UCase(rng) 
    Case rng = UCase(rng) 
      rng = Application.Proper(rng) 
    Case Else 
      rng = LCase(rng) 
  End Select 
Next 
End Sub

This macro changes the case of the text in the active range. If
it's lower case, the macro changes it to upper case. If it's 
upper case, the macro changes it to proper case. When neither 
lower nor upper case, the macro defaults to lower case. To use 
the macro, simply select the cell or range, press Alt-F8, 
highlight ToggleCase, and click Run. (Or create a macro button.)


*4. DOCUMENT FORMULAS

by Susan Sales Harkins

Would you like to document the formulas in your Excel 
workbooks? You can quite easily by running this macro:

Sub DocumentFormulas() 
Dim rng As Range 
Open "C:\Formulas.txt" For Output As #1 
For Each rng In Sheets("Sheet1").UsedRange.Cells 
   Print #1, rng.Address; Tab; rng.Formula 
Next 
Close #1 
End Sub

This macro will export the formulas in Sheet1 to a text 
document named Formulas.txt in the root directory. 


*5. OUTLOOK DEVELOPMENT

by Susan Sales Harkins

If you're trying to program Outlook using the application's 
object model, you're probably working harder than you need to. 
Instead of relying on VBA and VBScript, consider using 
Microsoft Collaboration Data Objects (CDO). CDO is a 
development model for interfacing to MAPI. You can view the 
model online at

http://msdn.microsoft.com/library/psdk/cdosys/
	_cdosys_cdo_for_windows_2000_object_model.htm


*6. HUNGARIAN NOTATION

by Susan Sales Harkins

We've always recommended that you adopt a naming convention and 
use it consistently. The Hungarian Notation is probably the 
most widely used and recognized by developers. For a 
comprehensive list of Hungarian tags, visit the VBStudio site at

http://www.vbstudio.com/codelibrary/hungarian.asp


*7. FIELD PROTECTION IN WORD FORMS

by Susan Sales Harkins

Did you know that protecting a form turns the macro recorder 
off? That makes it rather difficult to record a macro that 
protects your form's fields. Fortunately, the ActiveDocument 
property's Protect method can protect form fields:

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, 
noReset:=True

This statement protects the forms in the active document 
without resetting any of the fields' contents.


*8. EMPTY CELLS

by Susan Sales Harkins

Word tables often contain blank cells, which may be hazardous 
to your application. Interestingly, you can use the cell's 
end-of-cell character to identify an empty cell. That special 
character is two characters in length. Testing for the length 
of the text in the cell will return 2 when the cell is empty. 
That's what this macro bases its task on:

Function FindEmptyCells() 
Dim tbl As Table 
Dim c As Cell 
For Each tbl In ActiveDocument.Tables 
For Each c In tbl.Range.Cells 
If Len(c.Range.Text) = 2 Then 
    MsgBox "This cell is empty" 
End If 
Next c 
Next tbl 
End Function

You'll probably want to substitute the MsgBox statement with 
something more unique to your application.


*9. EXCEL RANGE NAMES

by Susan Sales Harkins

Giving names to Excel ranges is a powerful feature that most of 
us are familiar with and use frequently. If you should need a 
list of all the ranges in a sheet, you could use VBA to print a 
list to the Immediate window using the simple procedure 
shown here:

Sub RangeNames() 
Dim xlName As Name 
For Each xlName In Excel.Names 
    Debug.Print xlName.Name 
Next 
End Sub


*10. RANGE NAMES AND ADDRESSES

by Susan Sales Harkins

In our previous tip, we showed you how to use the Excel Name 
object to print the range names of the current sheet to the 
Immediate window. It might be helpful also to know each range's
exact location. When that's the case, you can use the RefersTo 
property, as shown here:

Sub RangeNames() 
Dim xlName As Name 
For Each xlName In Excel.Names 
    Debug.Print xlName.Name; " "; xlName.RefersTo 
Next 
End Sub
