In this issue we will be looking at some cool coding tips to build on the 'OBJECT' skills we looked at in the last issue.

Having created some cool code, what do you do with it. In Office 2000 Developer Edition, there is an inbuilt utility to build your own code library. ( More info here) However, it is possible to build your own code library by creating a code template, similar to the macro templates that come with OFFICE '97 (eg.'Macros8.dot'). Just create a code template called 'VBA_Library.dot'. To copy code into your code library, use the Project Explorer in the VB editor to drag and drop code modules from other projects. To give modules more appropriate names, use the View|Properties Window Menu Item to rename modules.
Pretty simple and it will eventually save you a lot of time in reinventing the 'wheel', so to speak!

If a macro takes a long time to run because if is continually updating the screen, you can turn off screen updating using the following code at the beginning and end or the macro code.

Sub runAmacro()
Application.ScreenUpdating=False
'code goes here
Application.ScreenUpdating=True
End Sub

The following tutorial, which extends the standard search and replace Menu Item to include a large selection of words imported into a 'Find.Replacement' object from a text file, uses this code to suppress screen updates as it highlights the selected words in red. Go to tut4.htm

~ COOL WORD RESOURCE ~
The Home Page for the Microsoft Office Developer WORD 'Tips'

In EXCEL it is important to specify the data type to avoid errors, so make sure you use the 'Application.InputBox()' method rather than the commonly used 'Inputbox' method. Using this method you can specify the type of value that can be entered by the user, rather than programming a loop to get the correct value. For example ...

'other code

YourValue = Application.InputBox(Prompt:="Enter a number:", Default:=0, Type:=1)

'rest of code

The Type values are ...

• 0 for formulas
  
• 1 for numbers
  
• 2 for text
  
• 4 for logical values (True or False)
  
• 8 for cell references
  
• 16 for error values
  
• 64 for array values
  

~ COOL EXCEL RESOURCE ~
A good listing of some intermediate EXCEL code from the 'mindspring' site. Some fun code, as well as an excellent look at using 'events' in EXCEL.

Using VBA in ACCESS is not one of our strengths, although we are working on it. So, in this section, we will just point you towards some excellent resources.

~ COOL ACCESS RESOURCE ~
Home page for 'ProgrammingMSAccess.com'
[ SKILL LEVEL: Intermediate to Advanced ]