Visual Basic for Applications Tips #46


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

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

Proudly presents:
Visual Basic for Applications

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


*1. INHIBIT CLOSE

by Susan Sales Harkins

Access forms display a Close button at the right border of the 
title bar. If you don't want users to close a form using this 
button, set the form's Close button property to No. This won't 
keep users from choosing Close from the File menu, which will 
also close the current form. This code allows a user to close 
a form only by clicking a command button:

Dim bolClose As Boolean

Private Sub commandbutton_Click() 
 bolClose = True 
 DoCmd.Close acForm, "formname" 
End Sub

Private Sub Form_Load() 
    bolClose = False 
End Sub

Private Sub Form_Unload(Cancel As Integer) 
    Cancel = Not bolClose 
End Sub

The declaration goes in the form's General Declaration section. 
The three event procedures work together. When you open the 
form, VBA sets bolClose to False. If you trigger the Unload 
event (by clicking the Windows Close button or choosing File, 
Close), VBA sets Cancel to the opposite of bolClose, which will 
evaluate to True. Because clicking the command button is the 
only way you can set bolClose to True:

Cancel = Not bolClose

it's also the only way to close the form.

Thanks to Virginia H. for submitting this code.


*2. FREE CONTROLS

by Susan Sales Harkins

You're not limited to the controls that come with your 
application. Any number of controls is available from 
third-party vendors. Next time you need just a bit more 
functionality, check out the Common Controls Replacement 
Project at

http://www.mvps.org/ccrp/

The controls are free, easy to register, and improve on the 
common controls and dialog boxes that come with many VBA 
host products.


*3. WRAPPING MACRO

by Susan Sales Harkins

Matt M. sent us this quick and easy wrapping macro. Simply 
select an Excel cell or range and run the macro to wrap text. 
By wrap, we mean the text stops at the right cell border and 
wraps to the next line instead of extending past the border. 
You'll find this format in the Alignment tab--right-click the 
cell or range, choose Format Cells, click the Alignment tab, 
and look for the Wrap Text option in the Text control section.

This macro toggles the format off and on. Once you've applied 
it, you can just as easily remove it by selecting the cell or 
range and running the macro a second time. In addition, we found
the macro also works on noncontiguous ranges.

Sub Wrap() 
Dim aCell As Range 
For Each aCell In Selection 
    aCell.WrapText = Not aCell.WrapText 
Next aCell 
End Sub

Thanks for sharing the macro, Matt!


*4. DATA INTEGRITY VERSUS RESOURCES

by Susan Sales Harkins

Visual Basic offers two ways to pass data between routines. You 
can pass the actual data, or you can pass a pointer to the 
data. The first method--ByVal--passes a parameter by value. The 
second--ByRef--passes a parameter by reference. Each has its 
advantages and disadvantages.

ByRef saves on resources, but you'll find that some developers 
consider the use of ByRef bad programming because ByRef passes 
only a pointer to the variable. While this option uses less 
memory, it doesn't protect the original variable, which can be 
altered. If you want the option to change the original value, 
then ByRef is an adequate choice, especially since it saves 
on resources.

On the other hand, ByVal passes the actual value. VB makes a 
copy of the variable for temporary use and maintains and uses 
this copy as long as the routine is running. So ByVal, while 
requiring more memory, protects the integrity of your data.

You must also conform to what the API function expects--so if 
you're working with API functions, stick with the 
declared reference.


*5. COPY A FOLDER

by Susan Sales Harkins

VBA can be rather clunky when it comes to file 
management--especially when you're copying folders and files. 
We've shown you a procedure that uses the Scripting Runtime 
Object Library instead. Be sure to add the library to your 
application--by opening the Visual Basic Editor; choosing 
Tools, References; selecting Microsoft Scripting Runtime; and 
clicking OK--before adding and running the procedure.

The following procedure quickly and easily handles the task 
of copying an entire folder (and its contents):

Function CopyFolder1(source As String, target as String) 
Dim fso As New Scripting.FileSystemObject 
fso.CopyFolder source, target 
Set fso = Nothing 
End Function

That's certainly much easier than any VBA alternative. Be 
careful, though; if the source string isn't a valid path, the 
function returns an error. If that's a possibility (and it 
probably is), try using this procedure:

Function CopyFolder2(source As String, target as String) 
Dim fso As New Scripting.FileSystemObject 
On Error GoTo errHandler 
fso.CopyFolder source, target 
Set fso = Nothing 
Exit Function

errHandler: 
MsgBox "Please enter a valid path" 
Set fso = Nothing 
End Function


*6. MORE ON SCRIPTING RUNTIME

by Susan Sales Harkins

In our previous tip, we showed you how to use the Scripting 
Runtime library to copy an entire folder to a new location. If 
you're not familiar with the library, you might want to learn 
more, since it's often easier to use than VBA (depending on the 
task, of course). To learn more, review the document at

http://msdn.micfosoft.com/scripting/default.htm?/scripting/
vbscript/doc/vsobjdictionary.htm


*7. HIDDEN MEMBERS

by Susan Sales Harkins

We've alerted you to the fact that the Array method is a hidden 
member and therefore not visible in the Object Browser. When 
Arthur H. read our tip, he reminded us that you can quickly 
expose hidden members by right-clicking the Object Browser pane 
and choosing Show Hidden Members from the resulting shortcut 
menu. The _HiddenModule Class will appear in gray text.

The Show Hidden Members option acts as a toggle switch--so the 
Object Browser will continue to display hidden members until 
you turn off the option. Thanks for the reminder, Arthur!


*8. WORK SLOWDOWN

by Susan Sales Harkins

You can suspend VBA code in several ways, but probably the 
simplest is to use the API Sleep function. Unlike many APIs, 
the Sleep function is easy to use. Simply drop this 
declaration into a module:

Private Declare Sub Sleep Lib "kernel32" (ByVal 
dwMilliseconds As Long)

Then, call the function in the form:

Sleep numberofmilliseconds

For instance, the statement

Sleep 1000

would suspend your code for 1,000 milliseconds.


*9. THE VERSION PROPERTY

by Susan Sales Harkins

Whether you need to know the current version of VBA or the 
actual application, you can depend on the Version property. 
Specifically, when checking for the current version of VBA, 
run the following statement in the Visual Basic Editor's 
Immediate window:

Debug.Print Application.VBE.Version

If you're running Office 2000, it should return 6.00. To check 
on the actual application, run the statement

Debug.Print Application.Version

Office 2000 applications will return 9.00.


*10. NAMING EXCEL SHEETS

by Susan Sales Harkins

Excel's guidelines for naming sheets differ a bit from the 
rules governing naming workbooks. You can use a few of the 
restricted characters when naming a sheet, but you're limited 
to just 31 characters. Gustav B. sent in a user-defined 
function for naming Excel sheets. After soliciting a name from 
the user, he calls this function to make sure the entry doesn't 
contain any forbidden characters:

Function TrimExcelSheetName(ByVal strSheetName As String) _
 As String 
' Replaces characters in strSheetName that are 
' not allowed by Excel in a sheet name. 
' Truncates length of strSheetName to bytSheetNameLen. 
' 2000-12-07. Gustav Brock, Cactus Data ApS, Copenhagen 
  On Error Resume Next 
  Const cstrInValidChars  As String = "\/:*?[]" 
  Const cstrReplaceChar   As String * 1 = "-" 
  Const cbytSheetNameLen  As Byte = 31 
  Dim bytLen    As Byte 
  Dim bytPos    As Byte 
  Dim strChar   As String 
  Dim strTrim   As String 
  strSheetName = Trim(strSheetName) 
  bytLen = Len(Left(strSheetName, cbytSheetNameLen)) 
  For bytPos = 1 To bytLen Step 1 
    strChar = Mid(strSheetName, bytPos, 1) 
    If InStr(cstrInValidChars, strChar) > 0 Then 
      strChar = cstrReplaceChar 
    End If 
    strTrim = strTrim & strChar 
  Next bytPos 
  TrimExcelSheetName = strTrim 
End Function

Basically, the function cycles through the first 31 characters 
in the user's entry, replacing each occurrence of the characters 
\, /, :, *, ?, [, and ] with a hyphen character (-). You can 
substitute any valid character for the hyphen character--even 
an empty string (""). Thanks for the help, Gustav!
