Visual Basic contains quite a few intrinsic constants -- that is, constants
that are now part of the VBA language but that you used to have to define in
your code either explicitly or by adding the Constant.Bas file to your
project. One of these is vbCrLf, which equates to the
carriage return/line feed -- or Chr$(13) & Chr$(10) - character combination.
But why would I suggest that you include this constant in an embedded SQL script? After all, the code means nothing to SQL Server; it could care less that you want a carriage return and line feed at the end of each line. The answer: debugging, plain and simple. Quite often, you'll find yourself creating embedded SQL scripts that run to 10, 20 or more lines. For example,
sSQL = "SELECT * FROM anytable" _
& " WHERE userid = " & lUser _
& " AND color = '" & sColor "'" If you define the SQL statement in this way, the SQL script is readable when viewed in the procedure, but what about when you want use the Immediate window to see the value of the sSQL variable at run time?
You enter
? sSQL
in the Immediate window, and a long, unbroken string is shown, running miles off into the distance.
However, if you simply suffix each line with vbCrLf, like this:
sSQL = "SELECT * FROM anytable" & vbCrLf _
& "WHERE userid = " & lUser & vbCrLf _
& "AND color = '" & sColor "'" then you can quickly and easily read your completed SQL code to find the problem.
The previous example illustrates another benefit of vbCrLf: if you don't use it, you must remember to start (or end) each line with a space; otherwise,
sSQL = "SELECT * FROM anytable" & _
& "WHERE userid = " & lUserbecomes
"SELECT * FROM anytableWHERE userid = 1"
and your code will generate a SQL syntax error.
One of the problems with testing and debugging a remote automation server is that the server application typically remains hidden, doing its work in the background. Hence, when something goes wrong, you frequently can't tell what has happened or where in your code your application is being derailed.
Consequently, when you're developing, testing, and debugging your application, it's a good idea to make sure that otherwise hidden automation server remain visible. You can do this by using code like the following to make sure that a new instance of an automation server (in this case Microsoft Word) is visible:
Dim objWord as Word.Application
Set objWord = New Application
#If ccDebug Then
objWord.Visible = True
#End IfYou can define the project-level conditional compiler constant ccDebug in either of two ways:
By including the line
#Const ccDebug = 1
in the declarations section of a form or code module.
By using the Make tab of the Project Properties dialog (select the Project Properties option from the VB Project menu) and entering the following in the Conditional Compilation Arguments text box:
ccDebug = 1Then, whenever your code runs in the design-time environment, you automation
server will be visible. When you are ready to create the final executable,
you should either set the ccDebug conditional compilation constant to 0 or,
if you've used the Conditional Compilation Arguments text box, remove its definition.
I often find this technique very useful, but it takes a little extra work when you're creating your object classes. On balance, despite this up front investment of time, it saves a good deal of programming time by making it easy to determining when an object has changed and therefore needs to be saved.
To implement an IsSaved or IsDirty property, each Property Let
procedure of a particular object must contain a line of code to determine if the value to be
assigned to the property is different than its current value (which should be
stored in a private member variable). If it is different, then the private
member variable that represents the IsSaved property is set to False. For
example:
Property Let CustStreetAddr(sVal as String)
If msCustStreetAddr <> sVal Then
msCustStreetAddr = sVal
mbIsSaved = False
End If
End Property(Of course, you can also implement this the other way round by having an IsDirty property that returns True when the object needs to be saved.)
Back at the client end you can check to see if the object needs saving quickly and easily as follows:
If Not myObject.IsSaved Then
SaveTheObject
End IfOn the server object, this is implemented as a simple Property Get procedure:
Property Get IsSaved() As Boolean
IsSaved = mbIsSaved
End PropertyAnother neat addition to this is to define an object event called something like ObjectChanged. Then the event can be fired whenever some attribute of the object changes:
Property Let CustStreetAddr(sVal As String)
If msCustStreetAddr <> sVal Then
msCustStreetAddr = sVal
mbIsSaved = False
RaiseEvent ObjectChanged()
End If
End PropertyOn the client form, you can then implement an event handler for the ObjectChanged event that enables the Save button when the object needs to be saved:
Sub MyObject_ObjectChanged()
cmdSave.Enabled = Not myObject.IsSaved
End SubThis code enables the Save button when the object is not saved and disables the button when the object has been changed.
I should add a major qualification to this tip: don't update your object property based on the Change event handler of a text box. The Change event is fired for each keystroke that the text box receives. Therefore typing a word like "Stupid" into the text box will fire off 6 Change events - and the final result is that the text box could contain the same word that it originally started with, so that in fact its contents haven't changed at all despite the firing of six unnecessary events.
Most of the time, we take for granted the For Each...Next loop, which
iterates the members of an array or a collection.
It's the fastest, most efficient
method of visiting all the members of the collection or array, and we could
care less that, as it enumerates the collection, the unseen code is actually
generating new references to members of the collection with each iteration of
the loop. However, as the provider of a collection class, it is up to you to
provide an interface that the
For Each...Next statement
can work with.
This may sound a little daunting, but you'll be pleasantly surprised how easy
it is to implement a property that enumerates members of the collection within
your class. First of all, you must create a Property Get procedure called NewEnum with the type of IUnknown. Its syntax is always the same:
Public Property Get NewEnum() As IUnknown
Set NewEnum = mCol.[_NewEnum]
End Propertywhere mCol is the name of your private collection object variable.
Second, set the Procedure ID for this Property Get
procedure to -4. To do this, select the Procedure Attributes option from the Tools menu, then click the Advanced button on the Procedure Attributes dialog. Enter a Procedure ID of
-4. You should also check the "Hide this member" option to prevent the property
from appearing in the IntelliSense drop down.
One of my long standing gripes about the Collection object is the complete absence lack of an easy method to determine whether the member you're looking for exists within the collection. Therefore, when I'm writing a wrapper class for a collection, I always include my own.
However, I add a little more to the method than simply determining if the member exists in the collection. If the member is not found within the collection, I attempt to add it to the collection. This way, I can simplify the code at the client end by always calling the Exists method prior to assigning the member to a local object variable. Therefore, I know that if the Exists method returns true, I can safely go on to assign the member to the local object variable.
The code for a typical Exists method is shown below:
Public Function Exists(sDomainName As String) As Boolean
On Error GoTo Exists_Err
Dim oTemp As Domain
Set oTemp = mcolDomains.Item(sDomainName)
Exists = True
Set oTemp = Nothing
Exit Function
TrytoGet:
If Not LoadDomain(sDomainName) Then
Exit Function
Else
Exists = True
End If
Exit Function
Exists_Err:
If Err.Number = 5 Then
Resume TrytoGet
Else
'further error handling here for other error types
End If
End FunctionAs you can see, the idea is to test for the presence of a particular member of the collection by attempting to assign it to a temporary local object variable. If the member is not present, then error 5 ("Invalid Procedure Call or Argument") is raised. Trapping this, program flow proceeds to the LoadDomain function, which attempts to load the member into the collection.
The new VB6 Dictionary object (found in the Scripting Runtime Library) contains its own built-in Exists property. The custom Exists method for the collection object can therefore be cut down dramatically but still achieve the same results, as the following method illustrates:
Public Function Exists(sDomainName As String) As Boolean
If mdicWebsites.Exists(sDomainName) Then
Exists = True
Else
Exists = GetWebsite(sDomainName)
End If
End FunctionWhether you're using the (now old fashioned) Collection object or the (new and fast) Dictionary object, your client code is identical, as the following fragment shows:
Private Sub cboDomainName_Click()
If moWebsites.Exists(cboDomainName.Text) Then
Set moWebsite = moWebsites.Website(cboDomainName.Text)
End If
End SubThere are many occasions where you need to reset the controls on a form, either in readiness for a new record, or perhaps when a record has been displayed and the user clicks the button to enter a new record.
You could, of course, reference each control on the form individually to reset its
display. However, I tend to whip through every control on the form and perform the
same function on each. Of course, not all controls have the same properties, so I
preface my procedure with an On Error Resume Next statement
that tells my code to ignore any errors and continue with the next line of code:
On Error Resume Next
For Each oControl In Controls
oControl.Text = "" 'mainly for text box controls
oControl.ListIndex = -1 'reset combo and list boxes.
NextYou've spent the last 17 years writing your new application and it works like a dream. You've got error handling coming out of your ears, belts, and braces. You've tested the application till you were blue in the face. Then, first day out in the field, a user contacts you and says, "It won't let me save this record."
Huh? What could be wrong? It can't be your code. No way!
You hunt through the code, and eventually find out from the user that the error message was a SQL syntax error that only reared its ugly head when the customers name was O'SomethingOrOther. Ahha!
Look at this embedded SQL Script:
sSQL = "UPDATE Customers Set CustName = '" & sCustName & _
"' WHERE CustID = " & custID"Given a customer name of "Lomax" and an ID of 100, then the string sSQL will contain a perfectly valid
"UPDATE Customers Set CustName = 'Lomax' WHERE CustID = 100"
However, change the customer name to "O'Reilly" and you generate a SQL syntax error:
"UPDATE Customers Set CustName = 'O'Reilly' WHERE CustID = 100"
The "O" is the string variable for CustName, but "Reilly" is a string that has no beginning (or is it the "O" that has no end? Hmmm..). So what's the answer? Well, there are a few ways of sorting out this little problem. One of the easiest is to replace all single quotation marks ("'") within strings with an apostrophe ("`" ). There are several ways to do this, but here's my favorite (for pre-VB6 code):
Mid(CustName, Instr(CustName, 1, "'"), 1) = "`"
This code fragment makes unusual use of Mid on the left
side of the statement. It replaces the single character "'" with the "`"
character. However, this single line of code can only replace the first
instance of a single quotation mark ("'") in a string. To replace all
instances, you'd have to implement a Do While loop like the following:
Do While Instr(CustName,1,"'") > 1
Mid(CustName, Instr(CustName, 1, "'"), 1) = "`"
LoopIf you're using VB6, you can use the new Replace function to substitute a single line of code for the code block:
CustName = Replace(CustName, "'", "`")
You'll also find that providing users with large, multi-line text boxes for entering free format text provides opportunities for them to enter words like "It's", "They're", and other apostrophe-containing, SQL script-breaking words. So be sure to remove the apostrophes here, too.
I've called this a mass assignation function. What's a mass assignation function? Let's say you have a collection class that contains 20 properties, which you populate by reading data from a database. You have a procedure that opens the database, creates a recordset, and then assigns the values from the recordset to each of the relevant properties in the class, something like this:
oClass.EmployeeNo = rsRecordset!EmpNo
oClass.FirstName = rsRecordset!FirstName
Etc...Using this method, you are calling the Property Let procedure of each property. If there is validation code within the Property Let procedure, this must execute too, most likely on data that has been validated before being saved in the database. A more efficient method of population is to create a function within the collection class, like this:
Friend Function Initialize(sEmpNo as String, _
sFirstName as String ...etc) As Boolean
msEmpNo = sEmpNo
msFirstName = sFirstName
...Etc...This single function assigns all the values for the object in one go by
assigning the values directly to the local variables, thus bypassing the
Property Let procedures and the redundant validation code. You can therefore pass all the values to populate the object in one go:
If oClass.Initialise( rsRecordset!EmpNo, _
rsRecordset!FirstName, _
etc...) ThenOf course you should only use this method within a class module - never from outside, and you should only employ this against data that you're certain has already been validated. You will find that a mass assignation function will dramatically improve the performance of your collection classes.
You can create your own timer class without needing a form and a Timer control
present. This solution is ideal for a remote server application where you don't
want to be cluttering up the server with forms. This example also shows how callback
functions and the AddressOf operator are used.
The following code forms two separate projects. The first is the automation server; this consists of a class module (clsRemTimer) and a code module. The code module is necessary to provide a callback procedure for the API functions used to initiate and destroy the Windows Timer.
TimerServer.vbp - clsRemTimer.cls
Option Explicit
Public Event Timer()
Private blnEnabled As Boolean
Private lTimerID As Long
Private lInterval As Long
Public Property Let Interval(lVal As Long)
lInterval = lVal
End Property
Public Property Get Interval() As Long
Interval = lInterval
End Property
Public Property Let Enabled(blnVal As Boolean)
If blnVal = False Then
StopTimer
Else
StartTimer
End If
End Property
Private Function StartTimer() As Boolean
If Not blnEnabled Then
lTimerID = TimerStart(Me, lInterval)
If lTimerID = 0 Then
Err.Raise 60000 + vbObjectError, "clsTimer", "Could not start Timer"
End If
blnEnabled = True
End If
StartTimer = True
End Function
Private Function StopTimer() As Boolean
If blnEnabled Then
lTimerID = TimerStop
If lTimerID = 0 Then
Err.Raise 60001 + vbObjectError, "clsTimer", "Could not stop Timer"
End If
blnEnabled = False
End If
StopTimer = False
End Function
Friend Function RaiseTimerEvent()
RaiseEvent Timer
End Function
Private Sub Class_Terminate()
Call TimerStop
End SubTimerServer.vbp - modTimer.bas
Option Explicit
Private oTimer As clsRemTimer
Private lTimerID As Long
Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
Public Sub TimerCallBack(ByVal hwnd As Long, _
ByVal uMsg As Long, _
ByVal idEvent As Long, _
ByVal dwTime As Long)
oTimer.RaiseTimerEvent
End Sub
Public Function TimerStart(ByRef oTmr As clsRemTimer, _
lInterval As Long) As Long
Set oTimer = oTmr
lTimerID = SetTimer(0, 0, lInterval, AddressOf TimerCallBack)
TimerStart = lTimerID
End Function
Public Function TimerStop() As Long
TimerStop = KillTimer(0, lTimerID)
Set oTimer = Nothing
End FunctionTimerClient.vbp - frmTimerClient.frm
Finally, here's a sample client, which is simply a form that uses the RemTimer class. Note that for this to function correctly, the client must declare the instance of the RemTimer class WithEvents. This sample client form contains one text box (named Text1) with its Locked property set to True and one command button (named Command1).
Option Explicit
Private WithEvents oTimer As TimerServer.clsRemTimer
Private Sub Form_Load()
Set oTimer = New TimerServer.clsRemTimer
oTimer.Interval = 1000
Text1.Text = 0
Command1.Caption = "Start"
End Sub
Private Sub Command1_Click()
On Error GoTo Command1_Err
If Command1.Caption = "Start" Then
oTimer.Enabled = True
Command1.Caption = "Stop"
Else
oTimer.Enabled = False
Command1.Caption = "Start"
End If
Exit Sub
Command1_Err:
MsgBox Err.Description
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set oTimer = Nothing
End Sub
Private Sub oTimer_Timer()
Text1.Text = Text1.Text + 1
End SubOne problem you always have when architecting a class hierarchy is implementing a method that allows the user of the class to populate the class. For example, you could implement a Load method that reads all relevant records into the class. However, I prefer to automatically populate classes using the Count property; this method produces quite elegant code within the client.
Here's an example of a Count property that automatically populates the class:
Public Property Count() As Long
If colMyCollection.Count = 0 Then
LoadRecordsIntoObject
End If
Count = colMyCollection.Count
End PropertyBeware, though, you may need to add a filter property to reduce the number of records automatically loaded into the object. Also, bear in mind that many times these days, the person writing the objects will not be the person who writes the client-side code. Therefore, you should make your class objects as easy to use as possible.
Here's an example of some client side code that utilizes the Count property shown above:
Set oDomains = New Domains
oDomains.Type = 1
If oDomains.Count > 0 Then
sNames = oDomains.Names
End If
Set oDomains = NothingThere is no operational difference between a sub and a function. A sub is the VB equivalent of a void function in C and C++. However, if you use a function, you have the choice of using or ignoring the function's return value.
If you were about to create a Sub because you don't necessarily need a particular return value, then create a function with a Boolean return value instead. This way, you can return True if all went well, or False if some error occurred. Your code will be much more robust as a result.
For example, the following code calls two sub procedures, one after the other:
Call DoFirstRoutine(sVal)
Call SecondRoutine(sOther)However, the call to the second may actually presuppose on the successful completion of the first. In this case, it is safer to implement the two routines as functions that return a Boolean value to denote the success or failure of the procedure, as follows:
If DoFirstRoutine(sVal) Then
SecondRouting sOther
End IfThe only time you should need to write code within a sub procedure is as an event handler; otherwise, use a function and return a value (at least a Boolean). The calling statement can then choose whether to use or ignore the return value from your function.
I love this story. It's true (and very recent). One weekend (like most in the summer), we were off racing and had to stay over in a hotel, part of a major UK chain. The kids are getting a bit older now, so we decided that they could have their own room. When I'd filled out the form for the first room and signed in, the lady behind the check-in desk said, "I'm sorry, but can we put your wife down as the occupier of the second room? It's just that our system won't let us use the same surname and initial for two records on the same day. We need a different initial."
Whose bright idea was it that the primary key on the day table should be last name and initial?
OK, perhaps it's unusual, but what about father and adult son, both paying for their rooms individually but both with the same first name? I knew a family once - a mother, father, and three sons - all of whom had the same initial and last name! Nora, Norman, Nigel, Nicholas, and Nathaniel. (OK, so I made the last name up because I could remember it...)
If someone says that something is "unusual," "unlikely," or "improbable," it does not mean that it's impossible. Unusual, etc., means that it can happen. And as sure as eggs are eggs, it's going to happen, and it'll happen to your system! Sound programming is not about gambling on probabilities, its about banking on certainties!
One of the neat things about NT is its event log. Wherever possible (and I can't think of a situation where it isn't), you should add a line of code (or two) into your error handling routine to write a line out to the event log.
Even if your application is unfortunate enough to have to run on Windows 9x, you can still specify that an error line be written to an event log file.
Once you are documenting errors via the event log, you can sit at your computer on the network and (if you have administrator rights) keep your eye on the event logs of your users for potential problems. No more having to rely on your users giving you the exact error message that was shown on the screen.
You will soon find that, with the use of the event log, once difficult to track errors are easy to trace, and even errors that may be caused by other sometimes "unseen" causes are pinpointed quickly and accurately.
One of the confusing aspects of developing a large project with Visual Basic is that code snippets tend to be in any of a variety of places, and keeping track of what's where - not to mention navigating to a particular routine when you want to see it - is often difficult. However, a little-known keyboard shortcut can help.
Simply highlight the property or procedure name in the code and press SHIFT+F2; you'll be transported as if by magic to the highlighted property or procedure. (If it's outside the project, you'll be taken to the Object Browser, where you can obtain further information about it.)
You can navigate back to where you came from just as easily. This time hit CTRL+SHIFT+F2 and you're back at your original place. This can save hours and hours of scrolling and jumping from one module to another in a large project.
One of the most common tasks we have to undertake when creating a user form is to provide a dropdown list of values based on property values within one of our objects. A combo control, for example, might contain the names of all the employees in a certain division of the company.
One of the most common methods of doing this (until VB6) was to populate the collection object, then enumerate each object in the collection and assign the particular value from each object into the combo control. The code looks something like this:
Set oEmployees = New Employees
For i = 1 to oEmployees.Count
If oEmployees.Exists(i) Then
Set oEmployee = oEmployees.Employee(i)
cboEmployees.AddItem oEmployee.Name
Set oEmployee = Nothing
End If
Next i
Set oEmployees = NothingThe amount of processing and (if an object is remote) the massive amount of network traffic that this simple procedure can create is staggering! So when I found that functions can return arrays in VB6, I was delighted. This means that you can now execute a simple SQL query that returns to the client only an array containing the values of that field that you want to display in the list or combo box. Here's the server side code:
Public Property Get Names() As String()
ReDim sTemp(0) As String
Dim oADORec As ADODB.Recordset
Dim lRecCount As Long
Dim sSQL As String
Dim iCounter As Integer
Set oUtil = New PowerUtils.DBUtils
sSQL = "SELECT SName from Employees"
Set oADORec = oUtil.GetRecordset(sSQL)
If oADORec.RowCount > 0 Then
ReDim sTemp(oADORec.RowCount)
Do While Not oADORec.EOF
sTemp(iCounter) = oADORec!SName
iCounter = iCounter + 1
oADORec.MoveNext
Loop
End If
Set oADORec = Nothing
Set oUtil = Nothing
Names = sTemp
End PropertyIf you can't get the RowCount property to work with your system, it is faster
to execute a SQL Query that returns the row count and then do a single
ReDim of the array, rather than to use ReDim Preserve.
Here's how the Names property shown above is used at the client end:
Dim sNames() As String
Dim vName As Variant
Dim oEmps As Employees
Set oEmps = New Employees
sNames = oEmps.Names
For Each vName In sNames
cboEmployees.AddItem vName
Next
Set oEmps = NothingNote that only an array of string values is passed from the server object to the client. Therefore, the amount of traffic between the two objects is negligible. Also note that to assign an array from a function in VB6, your local variable must be dimensioned as a dynamic array.
Copyright © 2009 O'Reilly Media, Inc.