Visual Basic Tips #15


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

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

Proudly presents:
Visual Basic

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

*1. 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 the 
following 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 VB checks your object reference at compile time. With 
late-binding, VB checks the object each time you use it. So why would 
anyone use late-binding? You'll use 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.


*2. RESIZING AN ARRAY                 
        
In a previous tip, we discussed resizing arrays but apparently left 
out a few key facts. To create a resizable array, the array first has 
to be defined without dimensions, like so: 

<pre> 
Dim a_intValues() As Integer 
</pre> 

When you want to resize the last dimension of the array, you can use 
the Preserve keyword to preserve existing values, like so: 

<pre> 
Redim Preserve a_intValues(10) As Integer 
</pre> 

Of course, if you're making the array smaller, values that are in 
cells that are "downsized" won't be preserved. All other values will 
be kept.


*3. USING THE JET 4.0 PROVIDER                 
        
If you're using Visual Basic 6.0 and want to use Access 2000 
databases, the best ADO provider to use is the Jet 4.0 OLE DB 
Provider. This provider is automatically installed when you install 
Office 2000 on your machine. Within your VB program, be sure to 
reference the appropriate provider when making your data connections, 
whether you're doing it through code, a data control, or the data 
environment. If you don't use the Jet 4.0 provider with an Access 2000 
database, you'll get errors when you try to read data.


*4. CONVERTING FROM HEXADECIMAL                 
        
Here's a quick way to convert a hexadecimal number to a long (or 
whatever data type you need): 

Dim strNumber As String 
Dim lngNewNum As Long 
strNumber = "FFFF00" 
lngNewNum = CLng(Val("&H" & strNumber))


*5. USING THE JET 4.0 PROVIDER                 
        
If you're using Visual Basic 6.0 and want to use Access 2000 
databases, the best ADO provider to use is the Jet 4.0 OLE DB 
Provider. This provider is automatically installed when you install 
Office 2000 on your machine. Within your VB program, be sure to 
reference the appropriate provider when making your data connections, 
whether you're doing it through code, a data control, or the data 
environment. If you don't use the Jet 4.0 provider with an Access 2000 
database, you'll get errors when you try to read data.


*6. USING NAMED ARGUMENTS                 
        
If you've ever had to create a procedure with lots of parameters, you 
know it can get confusing when you have to call the procedure. Here's 
a quick example: 

Private Sub DoSomething(arg1 As Integer, arg2 As String, arg3 As Long, 
arg4 As String) 

When you call this, you can do like so: 

DoSomething value1, "value2", value3, "value4" 

However, this doesn't really document what you're passing to the 
subroutine. VB supports the use of named parameters, which allows you 
to specify what parameter goes with what value, like so: 

DoSomething arg1:=value1, arg2:="value2", arg3:=value3, arg4:="value4" 

The bonus with this method is that you can mix up the arguments and 
the call will still go through properly. This is especially helpful if 
you're using a procedure with optional arguments; in fact, it is 
required in most cases using optional arguments because the system 
won't know to skip arguments unless you leave blank spaces between 
commas.


*7. USING CONTINUATION CHARACTERS                 
        
Even though you can make extremely long lines of code, it's easier to 
read when the lines are narrower than the width of your screen. To do 
this, you can use the continuation character, which is the underscore 
character. You can break any line into multiple lines by using 
continuation characters between keywords, as shown here: 

MsgBox "http://www." & "microsoft & ".com" 

This breaks into these lines, as an example: 

MsgBox "http://www." _ 
   & "microsoft" _ 
   & ".com" 

The indentations on the second and third lines are not required, but 
they do make it easier to tell when you've separated lines like this. 
Also note that you can't use a continuation character in the middle of 
a string. You have to break the string into multiple, smaller strings, 
as we did in this example.


*8. ALWAYS PROVIDE A CASE ELSE                 
        
When using the Select Case statement, always have a Case Else that 
will pick up any cases not matched in your list. Even if you're sure 
that extraneous values can't be processed by your code, it's a good 
idea to include a Case Else, as shown here: 

Select Case intTest 
Case 1: 
   ' do something 
Case 2: 
  ' do something else 
Case Else: 
   ' this is probably an error, so  
   ' display an appropriate message 
End Select


*9. MULTIPLE KEYWORD SEARCHES                 
        
A reader asked how he could search a database using two LIKE clauses 
to allow for multiple word searches at the same time. This is easy to 
do using SQL. Here's an example of how to do it: 

Dim strWord1 As String 
Dim strWord2 As String 
Dim strSQL As String 

strSQL = "SELECT * FROM tblCustomers WHERE LastName LIKE '%" & 
strWord1 & "%' OR " _ 
   "LastName LIKE '%" & strWord2 & "%'" 

You can simply OR the conditions together, which will give you the 
union of the results from both parts of the query.


*10. USING DATE VALUES WITH SQL                 
        
If you have to store or select date values from Access or SQL Server, 
be sure to enclose the date/time values within pound signs (#) and 
single quote characters, like so: 

strSQL = "SELECT * FROM Emp WHERE HireDate = '#06/19/70#'" 

The pound signs indicate to the SQL engine that the date needs to be 
handled differently. Depending on your system, you may be able to 
leave the single quotes off; however, they won't hurt you if they are 
there.
