Visual Basic for Applications Tips #22


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

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

Proudly presents:
Visual Basic for Applications

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


*1. DECLARING A NEW CLASS--PART 1 OF 3                   
          
Once you've defined a new class, you'll need to create a new instance 
of that class by declaring the new object variable in the form 

Dim objNewClass As NewClass 

where NewClass identifies the custom class. The next step is to define 
the variable in the form 

Set objNewClass = NewClass 

If you forget the New keyword, VBA will generate a runtime error when 
you try to execute any of the new class's properties or methods.


*2. DECLARING A NEW CLASS--PART 2 OF 3                   
          
In our previous tip, we showed you how to declare and set a new 
instance of a custom class properly. If you're working in a simple 
application, you can save yourself a line of code by using the form 

Dim objNewClass As NewClass 

However, be careful where you use this shortcut. If you need to know 
exactly when VBA creates the object, you'll find including the New 
keyword in the Set statement much easier to follow.


*3. DECLARING A NEW CLASS--PART 3 OF 3                   
          
Once you've created a new class, you'll use the Property Set procedure 
to create custom properties. Then you'll use the Property Let 
procedure to set the current property and the Property Get procedure 
to retrieve the current property. However, you won't always use both, 
as a property can be read-only. In that case, you'll use only the 
Property Get procedure to retrieve the property. If you use only the 
Property Let procedure, than you'll create a write-only property--you 
can set it, but you can't retrieve it.


*4. CLICK EVENT ORDER                   
          
When you click on most controls, you produce a Click event. Right? 
Well, let's see. When you click a text box, the control fires its 
Click event--that much is true. However, if you click inside the text 
box component of a combo box, you don't trigger that control's Click 
event. So what's the difference? The combo and list box controls don't 
fire their Click event until the user actually selects an item from 
the control's list. This behavior is considerably different from other 
controls because they trigger their Click event as soon as the control 
receives the focus. So consider the outcome carefully when you attach 
code to a combo or list box's Click event.


*5. RESERVED WORDS                   
          
If you create a new class, you'll probably create a few methods to 
complete custom tasks. Just be careful when you name these methods. 
Remember, you can't use words reserved by VBA. For instance, if your 
method opens a file, you may be tempted to name that method Open. 
However, if you do so, VBA will report an error when you try to 
declare the method. That's because Open is a reserved word. If this 
happens, simply change the method's name to resolve the conflict.


*6. CONTINUING LONG LINES OF CODE--PART 1 OF 2                    
          
Long strings are fairly common in VBA code, but don't try to wrap a 
string onto multiple lines. The string must be complete on one line, 
or you must use the line continuation character to break a long line 
into multiple, smaller lines, as shown here:  

str = "This is a long string that will be very hard to read as it will 
eventually extend off screen."  

str = "This is a long string" _ 
& " that will be very hard to read" _ 
& " as it will eventually extend off screen."  

Be sure to include spaces because the continuation character won't 
automatically add them for you.


*7. CONTINUING LONG LINES OF CODE--PART 2 OF 2                    
          
In our previous tip, we showed you how to break up a long string into 
multiple lines. We also mentioned that you must remember to include 
your own space characters because the continuation character won't add 
them for you. We recommend that you adopt the habit of including space 
characters in one of two ways: 
 - Include the space at the end of a line and before the continuation 
character.  
- Include the space at the beginning of the next line. 

Choose one of the above and use it consistently. That way, you can 
avoid forgetting about the space character altogether or adding two 
space characters--one at both positions.


*8. AN EASY JULIAN DATE                   
          
A Julian date is the day of the year, beginning with January 1. For 
instance, January 1 is 1, obviously, February 1 is 32, and March 1 is 
61. Although most modern applications don't use Julian dates anymore, 
you may run into an occasion where you need to calculate a date's 
Julian date value. Fortunately, it's easy. Just use the simple 
expression 

strDate = Format(dte, "y")  

where dte represents the date you're converting. If you're converting 
the current date, replace the dte argument with the Date function.


*9. CUSTOM COLLECTIONS                   
          
You can create your own Collections in VBA, but there are a few points 
you should be aware of when you do. First, custom Collections are 
one-based. This means, the first object is object 1, the second is 
object 2, and so on. If you delete an object from the middle of the 
Collection, VBA will adjust the subsequent object values. Second, the 
Collection object variable is just like any other variable and subject 
to the procedure's scope and lifetime. If you declare a Collection 
object in a procedure, that object will disappear, along with all the 
objects it contains, as soon as the procedure terminates.  

The gist is, don't depend on a Collection's object position, and 
consider declaring Collection objects as module or global variables.


*10. SQL DELETE WARNING                   
          
The SQL DELETE clause sometimes refers to a single column, but don't 
assume that field's value is the only one being deleted. The SQL 
DELETE clause deletes an entire record; it makes no difference if the 
clause references only a single field. For instance, the SQL 
statements 

DELETE FROM tblMyStuff WHERE Flag = True; 

and  

DELETE FROM tblMyStuff.OrderID WHERE Flag = True; 

do the same thing--they both delete all the records in tblMyStuff 
where the Flag field contains the True value. The second statement 
won't delete just the value in the OrderID but the entire record.
