Visual Basic for Applications Tips #8



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

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

Proudly presents:
Visual Basic for Applications

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


*1. USING DRAG-AND-DROP IN MODULES                 
        
If you're still using cut-and-paste to copy code from one 
procedure to another, stop. Use the Windows drag-and-drop feature 
instead. Simply select the code you want to copy or move. If you 
want to copy the code, hold down the Ctrl key while you drag the 
code to the waiting procedure. When the code is in position, 
release the mouse button to drop the code in place. If you want 
to move the code, don't hold down the Ctrl key while dragging 
the selected code.


*2. OMITTING THE STATUS BAR                 
        
If the status bar is taking up too much room on screen, get rid 
of it and free up that space. To turn off the status bar, choose 
Options from the Tools menu. Then, select the View tab. Next, 
deselect the Status Bar item in the Show options. If you'd like 
to automate the process using VBA, use the Application object in 
the following form: 

Application.DisplayStatusBar = Not (Application.DisplayStatusBar) 

We suggest you attach this code to a command button and use that 
control as a type of toggle switch. The Not operator will reverse 
the current state of the status bar. If it's displayed, clicking 
the command button will turn it off, and vice versa.


*3. SETTING PROPERTIES                 
        
Most properties have a list of fixed settings, available from 
that property field's drop-down list. To set a property, you 
probably click that field's drop-down arrow to open the field's 
list, then select one of the settings from the list. A quicker 
way to access a setting is to click to the left of the property 
field (in the gray area). VBA will cycle through the settings as 
you click. Simply stop clicking when you reach the setting 
you want.


*4. RECORDSETS AND BOOKMARKS                 
        
If you create a recordset in Access, you can use bookmarks to 
return to specific records. This isn't true if you're working 
with foreign recordsets--say from Paradox. Not all foreign 
tables support primary keys and therefore don't contain 
bookmarks. If you don't know the source of your recordset, you 
should test for bookmarks before relying on them. To do so, 
simply check the recordset's Bookmarkable property by using 
the form 

booTest = rst.Bookmarkable 

where booTest is a Boolean variable and rst represents the 
recordset object. If booTest equals True, the recordset 
supports bookmarks.


*5. QUICKLY ROUNDING DECIMAL VALUES                 
        
You can take many approaches to rounding decimal values. If you 
want to round a decimal value to the appropriate integer, you 
don't really need special expressions and rounding functions. 
Simply assign the value to an Integer data type. For instance, 
the following function will return a rounded integer value: 

Public Sub RoundValues(value As Integer) 
Debug.Print value 
End Sub 

You can test this procedure by opening the Immediate window and 
typing the statement 

RoundValues(1.5) 

The function will return the value 2. If you replace the value 
1.5 with the value 7.3, the procedure will return the value 7. 
This procedure doesn't care how many decimal values you enter, 
either. While this isn't the typical programming solution, it is 
a quick solution in the right situation.


*6. SHORTCUT KEYS FOR THE IMMEDIATE WINDOW                 
        
There are several shortcut keys you can use in the Immediate 
window. Some, such as pressing the Enter key, you already know 
about--pressing Enter runs a line of code. In addition, pressing 
Ctrl-C copies selected text to the Clipboard, and pressing Ctrl-V 
pastes the contents of the Clipboard into the Immediate window. 
You can use Ctrl-X to delete the selected text (to the Clipboard). 
More shortcut keys you may not be aware of include the following: 

- Ctrl-L displays the Call Stack dialog box. 
- Ctrl-Enter inserts a carriage return. 
- Ctrl-Home moves the cursor to the top of the window. 
- F2 displays the Object Browser. 
- F5 continues to run an application. 
- Shift-F5 restarts an application. 
- Alt-F5 runs the error handler code or returns the error. 
- F6 switches between the Immediate and Watch windows. 
- F8 initiates Single Step mode (executes code one line at a time). 
- Shift-F10 displays the shortcut menu.


*7. READ-ONLY TEXT BOX                 
        
The easiest way to create a read-only text box control is to set 
that control's Locked property to True. A locked control will 
display bound data, but it won't let you modify that data or 
enter new data. This property is generally all you'll ever need. 

However, there is another way--there always is. Attach the 
following statement to the control's KeyPress event: 

KeyAscii = 0 

KeyAscii is an intrinsic constant that returns the value of the 
pressed key. The above statement overrides the pressed key's 
value, and since 0 equals nothing, the control accepts and 
displays no character. 

We suggest that you use this method sparingly, though, and only 
when the Locked property isn't appropriate for some reason. 
Furthermore, this method won't prevent you from deleting data,
because it won't capture the Delete or the Backspace key.


*8. ADDING MONTHS TO A COMBO BOX                 
        
A combo box is one way to display the months of the year. If 
you're using Access, you can simply fill the box with a value 
list or enter the months in a table. If you're using the other 
applications, you'll need an AddItem method for all 12 months. 
Or, you can use this simple procedure: 

Private Sub UserForm_Activate() 
For i = 1 To 12 
ComboBox1.AddItem Format("28/" & i & "/1999", "mmmm") 
Next 
End Sub 

The For loop cycles through the integer representations for each 
month--1 through 12. Then, the Format() function uses that value 
to return the appropriate month by name.


*9. USING NOTHING                 
        
An object variable requires little memory, until you assign an 
object to it. Then, the variable can significantly drain your 
resources--especially if you have lots of object variables. We 
recommend that you deplete these variable drains when you're done 
with them. To do so, simply use the Nothing keyword in the form 

Set var = Nothing 

where var represents the object variable. This statement frees up 
the resources previously consumed by var. You haven't deleted the 
variable; you've just deflated its size.


*10. PRINTING THE ACTIVE SLIDE                 
        
You can easily print any slide in a PowerPoint presentation by 
adding an action button to the master slide and then attaching 
the following procedure to the button: 

Sub PrintSlide() 
Dim lNum as Long 
lNum = SlideShowWindows(1).View.Slide.SlideNumber 
With ActivePresentation.printOptions 
.RangeType = ppPrintSlideRange 
.Ranges.ClearAll 
.Ranges.Add lNum, lNum 
End With 
Active.Presentation.Printout 
End Sub 

The With structure tells PowerPoint to print only a portion of 
the slide show--that portion being a specific slide number. That 
slide number is, of course, the current slide. The variable lNum 
doesn't have to be a Long Integer--if your presentation is small, 
you can use an Integer or even the Byte data type.
