Visual Basic for Applications Tips #43


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

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

Proudly presents:
Visual Basic for Applications

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


*1. WHERE IS ARRAY

by Susan Sales Harkins

Most of us use the Object Browser to locate a particular 
function, method, or property so we can learn more about it. 
As long as the appropriate library is referenced, you should 
be able to locate your information, right? Not always. There 
are a few pieces you won't find. For instance, the Object 
Browser doesn't display information on the Array method. 
That's because this method is a member of the HiddenModule 
module and it's hidden from view. You can still use the Array 
function--you just can't read about it in the Object Browser.


*2. CALL STATEMENT MAKES CODE EASIER TO READ

by Susan Sales Harkins

In our previous tip, we used the Call statement to pass control 
to that procedure. However, as you probably know, the Call 
statement is not required. So why did we use it? Because using 
the Call statement makes the code easier to read and decipher. 
It's easy to see with just a quick glance that the statement

Call MyProcedure

is passing control to MyProcedure and the keyword Call is 
the visual clue.

In the end, it's really up to you. If you're the only one 
viewing your code, you can easily omit quick clues such as this.


*3. CHOOSE() NOT CHOOSY

by Susan Sales Harkins

The Choose() function selects an item from a predefined list 
based on its position. For instance, the function

Choose(intValue, "one", "two", 3)

will return "one", "two", or 3 depending on the value of 
intValue. What you might not realize is that Choose() doesn't 
care whether the items in the list are of the same data type. 
That means you can mix them up. In our example, "one" and "two" 
are definitely strings, but 3 is a value. This behavior makes 
Choose() extremely flexible. 


*4. CALLING PROCEDURES FROM CHOOSE()

by Susan Sales Harkins

When we talked about the Choose() function in our previous tip, 
we said that this function accepts different data types in its 
list of choices. It's even more flexible than that, though. You 
can even call procedures from this function. 
For instance, the function

Choose(intValue, FunctionOne(), FunctionTwo())

will return the result of either FunctionOne() or FunctionTwo() 
as its result.


*5. ROUNDING NUMBERS WHEN USING CINT()

by Susan Sales Harkins

The CInt() function coverts an expression to an integer and 
rounds any decimal portion. This behavior is a little different 
from Int(), since that function simply truncates the decimal 
portion of a number. However, you need to be aware of how CInt()
rounds. Specifically, when the decimal portion is .5, CInt() 
always rounds to the nearest even number. For instance, .5 
rounds to 0, because 1 is an odd value; 1.5 rounds to 2.


*6. CINT() RETURNS INTEGER DATA TYPE

by Susan Sales Harkins

In our previous tip, we told you to be careful when using the 
CInt() function because when the argument's decimal portion is 
.5, this function rounds to the nearest even number. There's 
something else about CInt() you'll want to know. CInt() always 
returns an Integer data type. Other functions of this type, 
such as Fix() and Int(), return the same data type as 
the passed argument.


*7. DELETEFOLDER IS PERMANENT

by Susan Sales Harkins

We've talked about the FileSystemObject in past tips. One of
this object's methods, DeleteFolder, makes short work of 
cleaning up your directories by deleting a folder and all its 
file and subfolders. When executing this method, you need to 
be very sure of your action because this method permanently 
deletes folders and their contents. You can't retrieve them 
from the Recycle Bin.

In addition, if an error occurs, the DeleteFolder method exits 
immediately and there's no way to rebuild the partially affected
folder.


*8. QUICK MODULE LAUNCH

by Susan Sales Harkins

Peter T. sent this quick tip for launching the Visual Basic 
Editor (in Excel) to the current sheet's module. First, 
right-click the sheet tab. (You can right-click any 
tab--right-clicking a tab makes it the current sheet.) Next, 
choose View Code from the resulting submenu. When you do, 
Access launches the Visual Basic Editor and defaults to the 
active sheet's module.


*9. QUICK MODULE LAUNCH

by Susan Sales Harkins

Peter T. sent this quick tip for launching the Visual Basic 
Editor (in Excel) to the current sheet's module. First, 
right-click the sheet tab. (You can right-click any 
tab--right-clicking a tab makes it the current sheet.) Next, 
choose View Code from the resulting submenu. When you do, 
Access launches the Visual Basic Editor and defaults to the 
active sheet's module.


*10. RETURN TO THE LAST WINDOW

by Susan Sales Harkins  
  
If you frequently work with more than one window open, you 
might easily get lost. Fortunately, there's a keyboard shortcut 
for returning to the last active window. You can press 
Ctrl-Shift-F6 to select the last window.

This shortcut works equally well in the Visual Basic Editor. 
Simply press Ctrl-Shift-F6 to retrace your steps through 
the open modules.
