Visual Basic for Applications Tips #2



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

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

Proudly presents:
Visual Basic for Applications

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


*1.   QUICKLY VIEWING CONSTANTS                 
        
A constant is a type of variable--generally a numeric or string 
value that doesn't change. You can define your own constants by 
declaring the variable using the Const keyword. Or, you can use 
one of VBA's intrinsic (built-in) constants. 

To display a list of intrinsic constants, launch the VB Editor 
(or open a module in Design View if you're using Access) and 
press Ctrl-J to open the Object Browser (or press F2). Next, 
choose the globals library (or the library that's specific to 
your application). Then, choose Constants from the Classes 
control to display a list of constants for the library 
you've selected.


*2. WRITING A CGI                 
        
Often the first Internet programming a person attempts is writing
a CGI. CGIs can be written in virtually any computer language, 
from shell scripts to Cobol to C to Perl. They can be a compiled 
or interpreted language. However, the examples are often limited 
to a few selected languages. 

The most language-agnostic introduction to CGI can be found at 

http://www.w3.org/CGI/

13. DISPLAYING THE ACTIVE PRINTER                 
        
Displaying or changing printer information in Access can be 
rather difficult. However, in Word, displaying the active 
printer is as easy as executing one property--the ActivePrinter 
property. To display the active printer, simply use the 
ActivePrinter property as the text argument in a MsgBox 
function as follows: 

MsgBox Application.ActivePrinter 

You can attach this to a command button or most any event. When 
you fire the event, VBA will display the name of the active 
printer in a message box. This is a good check to include before 
printing when your system is networked to several printers.


*4. BEWARE OF CASCADING EVENTS                 
        
When writing a sub (or event) procedure, you need to consider 
all the possibilities. In particular, you need to avoid code 
that conflicts with the event itself. For instance, if you try 
to run a Maximize statement in a Resize event, you'll create 
what's called a cascading event, in which the code either 
directly or indirectly fires the event. The Resize event runs 
the Maximize statement, which in turns executes the Resize 
event, which again runs the Maximize statement, and so on. You 
end up with a terminal loop.


*5. UNDERSTANDING OPTION EXPLICIT                 
        
By default, every VBA module contains two statements: Option 
Compare Database and Option Explicit. The Option Compare 
statement determines the module's sort order. Option Explicit 
determines the declaration mode--explicit. This means you must 
declare a variable to use it. Option Explicit helps you catch 
errors before they crash your code. 

If you misspell a variable, explicit mode will point it out 
because (most likely) that variable hasn't been declared. For 
instance, if you declare the variable iValue, but then refer to 
that variable in your code as iVale, VBA will stop and highlight
the line of code that contains the misspelled variable when you 
attempt to compile your code. At this point, VBA won't tell you 
exactly what's wrong with the code, but it's generally easy to 
detect. Without VBA to point out the line of code (implicit 
declaration), you might spend some serious debugging time trying 
to find the problem. 

You can turn off Option Explicit mode. Simply select Options from 
the Tools menu, click the Module tab, and then deselect the 
Require Variable Declaration option in the Coding Options 
section. If you prefer not to turn on Option Explicit mode, but 
you want to take advantage of the Option Explicit environment, 
you can enter the statement in a module yourself.


*6. MODIFYING MULTIPLE CONTROLS                 
        
Often, you'll want to perform the same task on several controls.
Fortunately, doing so can be done with one simple routine--if 
you plan for it. The trick is to assign similar names to all the 
controls you'll want to affect with this one routine. For 
instance, the following instructions will create a form with 
multiple checkboxes. You can toggle all of them between a 
disabled and enabled state, with just one procedure. In Access,
follow these steps: 

1. Open a blank form in Design View. 
2. Add five check box controls and name them chk1, chk2, chk3,
   chk4, and chk5. 
3. Add a command button and name it cmdToggle. 
4. Attach the following code to the button's Click event: 
 
Private Sub cmdToggle_Click() 
Dim i As Integer 
For i  = 1 To 5 
Me("chk" & I).Enabled = Not (Me("chk" & I).Enabled) 
Next i 
End Sub 
 
When you run the form the first time, all five check box controls
are enabled. Click the command button and the above VBA procedure
will disable all five controls. Click the button again, and the 
code will enable them once more.


*7. JUSTIFYING TEXT USING FORMAT()                 
        
Did you know that you can use the Format() function to justify 
text? You can, by limiting the number of characters in the 
underlying field and including the ! character. For instance, if 
the field has a width of seven characters, the function 

Format$("abc", "@@@@@@@") 

Would right-justify the string abc. If you want to left-justify 
a string, simply add the ! character to your format code in 
the form 

Format$("abc", "!@@@@@@@")


*8. A POINT OF CAUTION WHEN DECLARING AN EXCEL VARIABLE                 
        
A common Excel programming task might include defining a 
variable to track a row count. You might think an Integer data 
type sufficient for the task, but you'd be making a mistake. 
That's because an Integer handles values up to 32,767 while an 
Excel spreadsheet has 65,536 rows! You could make the same 
mistake with Access if a table holds more than 32,767 records. 
In this case, a Long Integer is the correct data type. Remember, 
always consider all the possibilities when defining a variable. 
Don't go overboard, but you must allow for the extremes.


*9. TALKING TO EXCEL FROM ACCESS                 
        
Automation is the technology that allows one application to 
interact and manipulate another. Fortunately, most of the Office
applications expose their objects (Automation objects) to this 
technology. For instance, you can talk directly to Excel from 
Access. This would be useful to take advantage of functionality 
native to Excel that Access doesn't have. However, to make it 
possible, you have to follow a few steps. First, the Object 
Library you want to reference must be set. Second, use VBA to 
create an instance of Excel (the Automation server). Use VBA to 
transfer data from Access (the Automation controller). Let's look
at a quick example. 

1. Open a new form in Access and add two text box controls and
   one command button. 
2. Name the controls txtName, and txtURL, and cmdTransfer. 
3. Attach the following code to cmdTransfer's Click event: 
 
Private Sub cmdTransfer_Click() 
 
Dim objExcel As Excel.Application 
Dim objWorkbook As Excel.Workbook 
Dim objSheet As Excel.Worksheet 
 
Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
 
Set objWorkbook = objExcel.Workbooks.Add 
Set objSheet = objWorkbook.Worksheets.Add 
 
objSheet.Cells(1, 1) = txtName 
objSheet.Cells(1, 2) = txtURL 
 
End Sub 
 
4. Before running the form, make sure the Excel Object Library 
is exposed to Access. Choose References from the Tools menu 
and select Microsoft Excel 8.0 Object Library, if necessary. 
Click OK. 
5. Run the form and enter TipWorld in the first text box, 
www.tipworld.com in the second text box, and then click the 
Transfer command button. 

The VBA code will create an instance of Excel and then transfer 
the data from your Access form controls into the first two cells 
of an Excel worksheet. If you want to close the instance of 
Excel, add the following statements to your procedure: 
 
objExcel.Quit 
Set objExcel = Nothing


*10. SETTING LIBRARY PRIORITIES                 
        
In our last tip, TALKING TO EXCEL FROM ACCESS, you learned how 
to enter data directly into Excel cells while working in an 
Access form. To do so, you referenced the Microsoft Excel 8.0 
Object Library and referred to the Application class. 

Once you've referenced more than one library, it's possible to 
create conflict when using a class--if more than one library uses 
that same class. For instance, both Access and Excel have an 
Application class. 

You can resolve a class conflict in two ways. First, when setting 
a reference to a library, you choose References from the Tools
menu. In that dialog box, along with the different library 
listings, you'll see two Priority buttons. You can use these to 
assign priorities to the different libraries. However, this 
method isn't always adequate. 

A safer method is to define the library in your code, as we did 
in our last tip. If you remember, we used the statement 

Dim objExcel As Excel.Application 

to declare our Excel object. If you omit the Excel reference and 
use the statement 

Dim objExcel As Application 

and Access has the priority (which is the most likely 
circumstance, since you're in Access), VBA will look for the 
Workbooks object in the Access library. Of course, VBA won't find 
it and will return an error. So, when working with common class 
names, it's a good idea also to declare the library explicitly.
