Visual Basic for Applications Tips #14


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

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

Proudly presents:
Visual Basic for Applications

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

*1. CLOSING OPEN MODULES                 
        
In our previous tip, we suggested you avoid using the Compile All 
Modules command during the development stage. There's something else 
you should be aware of: Using the Compile All Modules command loads 
all your modules into memory, and VBA won't automatically unload them 
once it's run the called procedure. That means modules continue to 
consume memory unnecessarily. We suggest that when you use the Compile 
All Modules command, you also close your file afterward to close all 
those open modules and free up the memory they're consuming. (Not all 
VBA hosts offer a Compile All Modules command.)


*2. MORE ON CLOSING ALL MODULES                 
        
In our previous tip, we suggested you close your file after running 
the Compile All Modules command. The truth is, you'll probably want to 
close your file a couple of times during every work session (if your 
sessions are long and your file is large). 

When you call a function, VBA opens the module that contains your 
code. Unfortunately, VBA doesn't offer a programmatic way to close 
that module once you've run the procedure. That means you eventually 
end up with lots of modules open--and consuming memory--long after you 
need them. To free up memory used by open modules, simply close and 
reopen your database. (You don't have to close the host application, 
just the current file.) Doing so will close all your modules and free 
up the memory they would otherwise be consuming. 

Although this tip is very similar to the previous tip, it's separate 
from the Compile All Modules command. So, if your application doesn't 
offer this command, this particular tip may still be helpful.


*3. PRINTING THE WORKBOOK PATH IN AN EXCEL HEADER                 
        
Some people find printing the workbook's name in a footer helpful. 
Doing so creates a quick reference for the file's location on your 
printout. There's no built-in feature for printing the file's name, 
but you can do so with a simple macro. To create this macro, open the 
VB Editor by clicking Alt-F11. Then, select the correct project in the 
Project window (which you'll find in the upper-left corner). Next, 
select Insert, Module. In that module, enter this procedure: 

Sub NameInFooter() 
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName 
End sub 

To close the VB Editor, simply click the Close button at the top-right 
corner of your screen. 

When you're ready to run the macro, choose Tools, Macro, then choose 
Macros in the resulting dialog box (or you can press Alt-F8). Next, 
select NameInFooter and click Run. You won't see the effects 
immediately. If you click the Print Preview button, you can see the 
filename in the footer.


*4. NAME THAT ERROR                 
        
Working with VBA returns errors, no matter how good you are at VBA. 
The thing is, often VBA just displays an error code, which isn't much 
help considering there are thousands of those values in the Office 
structure. Fortunately, VBA's Err function will return the value of 
the most recent runtime error. The combined expression 

Error(Err) 

will return an explanation of the most recent error (Err). You can use 
this expression in your code to display a descriptive message when 
your code returns an error. Or, after churning up an error, open the 
Debug or Immediate window and type 

?Error(Err) 

to learn immediately what the error was


*5. SPEEDIER OLE                 
        
You've probably heard the terms early-binding and late-binding, but 
you may not know what they mean. In a nutshell, they refer to when you 
declare a specific object type when working with OLE objects. For 
instance, you can use early-binding to declare a Word object using 
this code: 
 
Dim myWord as Word.Basic 

Or you can make a generic declaration, as follows: 

Dim myWord as Object 

This is called late-binding and eventually you will link myWord to a 
specific object. At that point, myWord will inherit the linked 
object's attributes. But until then, it's a generic object. 

Now, this tip is about speed, really, not early-binding and 
late-binding. The issue is, which is faster? Early-binding is faster 
because VBA checks your object reference at compile time. With 
late-binding, VBA checks the object each time you use it. 

So why would anyone use late-binding? You'll need late-binding when 
you don't have access to an object's type library, or when you're 
working with a server that doesn't support early-binding.


*6. ADDING FRONTPAGE TO THE SUITE                 
        
FrontPage is new to the Office suite and is available with Office 2000 
Premium. As you might expect, FrontPage supports VBA. However, 
FrontPage uses VBA differently than the other applications, as 
FrontPage (and Outlook) supports a single project. Other applications 
associate a project with each document. In other words, each Excel 
workbook can have a VBA project, but FrontPage allows only one VBA 
project--regardless of how many items are open. 

If you'd like more information, make sure you've installed the 
FrontPage Visual Basic Reference Help files (vbafp4.chm and 
vbafplm4.chm). In addition, this Microsoft site 

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vba1-19%2C00.html 

lists FrontPage resources that should help get you started in your 
joint venture between FrontPage and VBA.


*7. THE ADO MODEL                 
        
If you're still not familiar with ADO but you're ready to start, you 
might want to start with the ADO Object model. You can find a diagram 
of this model and an explanation of each object at 

http://www.microsoft.com/data/ado/prodinfo/progmod.htm 

After you've reviewed the model and read the documentation, pull down 
the Resources And Feedback menu and select ADO. You'll find resources 
for ADO available online listed.


*8. LEARNING NEW TRICKS                 
        
Each new version of VBA brings replacement actions and keywords. As a 
rule, when Microsoft updates an action or keyword, you can still use 
the previous version--at least for a while. However, we recommend that 
you make a habit of using the new replacements instead of their 
predecessors, even when those predecessors still work just fine. 

Once Microsoft updates a keyword or action with a new one, your time 
using the original version is limited. After a release or two, 
Microsoft usually drops replaced keywords and actions to make room for 
new features. That means that someday in the future, you'll try to use 
the old statement or action and you'll receive an error, and the 
reason might not be obvious. We recommend that you familiarize 
yourself with replacements when a new version is released, and start 
using those replacements right away.


*9. REMEMBER TO UPDATE COMMENTS WHEN YOU CHANGE CODE                  
        
Only one thing is worse than no comments in your source code and 
that's incorrect comments. Here's what happens--the developer makes a 
change to the code but forgets to note those changes in the comments. 
So instead of deciphering code because there are no comments to point 
the way, you're stuck trying to match comments to code when there 
simply is no match. It might take a while to figure out that there's 
nothing wrong with your logic and that the problem is with the 
comments. So whenever you change your code, don't forget to update the 
comments appropriately.


*10. DLL HELP                 
        
DLLs provide a tremendous amount of functionality, and they can also 
be a pain to work with--or without, depending on the situation. If 
you're tired of guesswork, read Rick Anderson's article online at  

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vb3-2%2C00.html 

"The End of DLL Hell" was originally published in Microsoft's MSDN 
News newsletter, and it provides a thorough look at just what DLLs 
are, how they work together, and why they can cause so much trouble. 
In addition, the article reviews a few third-party products that help 
you troubleshoot DLL problems.
