VB & VBA in a Nutshell: The Language
By Paul Lomax1st Edition October 1998
1-56592-358-8, Order Number: 3588
656 pages, $24.95
|
|
|
|
|
VB & VBA in a Nutshell: The LanguageBy Paul Lomax1st Edition October 1998 1-56592-358-8, Order Number: 3588 656 pages, $24.95 |
Implementing Custom Events p66-68 Chapter 4 Automation Examples p85-89 Chapter 5 Silent Reporting p106-107 Chapter 6 #Const Directive p113-117 Chapter 7 AddressOf Operator p121-123 Chapter 7 CallByName Function p142-146 Chapter 7 Declare Statement p214-218 Chapter 7 DoEvents Function p241-242 Chapter 7 Err.LastDLLError Property p259-261 Chapter 7 Filter Function p308-310 Chapter 7 GetObject p358-363 Chapter 7 WithEvents Keyword p576-577 Chapter 7
In the early versions of VB, programmers were limited to working with the built-in events. In VB5, however, three simple keywords--Event, RaiseEvent, and WithEvents--were added to the language to allow the programmer to define custom events or to trap events in external objects that would otherwise be inaccessible.
Custom events can be used for any of the following:
To report the progress of an asynchronous task back to the client application from an out-of-process ActiveX EXE component.
To pass through events fired by the underlying control in an ActiveX custom control.
As a central part of a real-time multiuser application in an n-tier client-server application. (Incidentally, events can't be fired from within a Microsoft Transaction Server Context.)
To receive notification of events fired in automation servers.
To query the user and receive further input.
The following are some of the rules and "gotchas" for defining custom events:
Events can be declared and fired only from within object modules (i.e., Form, User Control, and Class modules). You can't declare and fire events from a standard code module.
Events can be handled or intercepted only from within object modules. You can't handle any type of event from within a code module. This isn't really a limitation because you can simply include a call to a function or sub within a code module from within your event handler, to pass program control to a code module--just like you would write code in form and control event handlers.
The event declaration must be Public so that it's visible outside the object module; it can't be declared as Friend or Private.
You can't declare an object variable as WithEvents if the object doesn't have any events.
To allow the client application to handle the event being fired, the object variable must be declared using the WithEvents keyword.
VB custom events don't return a value; however, you can use a ByRef argument to return a value, as you will see in the next section, "Creating a custom event."
If your class is one of many held inside a collection, the event isn't fired to the "outside world"--unless you have a live object variable referencing the particular instance of the class raising the event.
To raise an event from within an object module, you first of all must declare the event in the declarations section of the object module that will raise the event. You do this with the Event statement using the following syntax:
[Public] Event eventname [(arglist)]
For example:
Public Event DetailsChanged(sField As String)
In the appropriate place in your code, you need to fire the event using the RaiseEvent statement. For example:
RaiseEvent DetailsChanged("Employee Name")
That is all you need to do within the object module. Simply declare an event using Event, and fire it using RaiseEvent.
The client code is just as simple. You declare an object variable using the WithEvents keyword to alert VB that you wish to be informed when an event is fired in the object. For example:
Private WithEvents oEmployee As Employee
This declaration should be placed in the Declarations section of the module. VB automatically places an entry for the object variable name in the Object drop-down list at the top left of your code window. When you select this, note that the events declared in the object are available to you in the Procedure drop-down list at the top right of your code window. You can then select the relevant event and its event handler. For example:
Private Sub oEmployee_DetailsChanged(sField As String) MsgBox sField & " has been changed" End Sub
In the earlier section "The Property Let procedure," we mentioned using a custom event to fire a warning to the client as part of a data-validation procedure. Unfortunately, though, events don't return a value. However, if you define one of the parameters of your event to be ByRef, you can examine the value of the variable once the event has been handled to determine the outcome of the event handling within the client application. Here's a simple example:
Server code:
Public Event Warning(sMsg As String, ByRef Cancel As Boolean)
Public Property Let ClaimValue(dVal As Double)
Dim blnCancel As Boolean
If dVal > 10000 Then
RaiseEvent Warning("The Claim Value appears high", _
blnCancel)
If blnCancel Then
Exit Property
End If
End If
mdClaimValue = dVal
End Property
Client code:
Private WithEvents oServer As clsServer
Private Sub oServer_Warning(sMsg As String, _
Cancel As Boolean)
Dim iResponse As Integer
iResponse = MsgBox(sMsg & " is this OK?", _
vbQuestion + vbYesNo, _
"Warning")
If iResponse = vbNo Then
Cancel = True
Else
Cancel = False
End If
End Sub
As you can see, this is a powerful technology. However, it also demonstrates another aspect of custom events that may not be desirable in certain circumstances: RaiseEvent is not asynchronous. In other words, when you call the RaiseEvent statement in your class code, your class code won't continue executing until the event has been either handled by the client or ignored. (If the client has not created an object reference using the WithEvents keyword, then it isn't handling the events raised by the class, and any events raised will be ignored by that client.) This can have undesirable side effects, and you should bear it mind when planning your application.
For more information on the custom event statements, see the entries for the Event, Friend, Private, Public, RaiseEvent, and WithEvents statements in Chapter 7.
So let's bring together all you've seen in this chapter with a few sample implementations of OLE automation servers.
This first application demonstrates how you can seamlessly use Microsoft Word to print output from your VB program without the user knowing that you have actually used Microsoft Word:
Private Sub cmdWordDoc_Click()
'create an error handler
On Error GoTo cmdWordDoc_Err
'create the local Early Bound object variables
Dim oWord As Word.Application
Dim oWordActiveDoc As Word.Document
Dim oWordSel As Word.Selection
'Create a new instance of Word
Set oWord = New Word.Application
'Create a new document object
Set oWordActiveDoc = oWord.Documents.Add
Set oWordSel = oWord.Selection
'Do some work with the Selection object
oWordSel.TypeText "This is some text from the VB app."
oWordSel.WholeStory
oWordSel.Font.Name = "Arial"
oWordSel.Font.Size = 12
oWordSel.Font.Bold = wdToggle
'Now print out the doc
oWordActiveDoc.PrintOut
'always tidy up before you leave
Set oWordSel = Nothing
Set oWordActiveDoc = Nothing
Set oWord = Nothing
Exit Sub
cmdWordDoc_Err:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf _
& Err.Source
End Sub
Because this example uses early binding, you'll have to use the References dialog to add a project reference to the Word 8 Object Model.
TIP: Note that this application appears seamless because the application's Visible property is
Falseby default. If you wanted to show the Word application window in operation (which may be required while debugging), simply set the property toTrue.
This application demonstrates how you can work with a late bound object. The OLE server in this instance is Windows MAPI. Using MAPI in this way uses Outlook sort of through the back door; you don't actually create an instance of Outlook, but this sample demonstrates how closely tied MAPI and Outlook are. In fact, the mail side of Outlook isn't much more than a nice GUI to the Windows MAPI. If you are connected to an Exchange server when this simple application runs, the mail is sent automatically; otherwise, the mail is placed in Outlook's outbox, ready for you to send. You may also have to change the profile name to match that on your own system.
The sample function shown below is called from a form containing a text box (txtDomain) that holds the domain name of the recipients, and a list box (lstEmails) that holds the individual addresses of the recipients. This example is in fact part of a working application used several times a day to send test messages to new email accounts:
Private Function SendReturnEMail() As Boolean
' create an error handler
On Error GoTo SendReturnEMail_Err
'set the default return value
SendReturnEMail = False
'we're using late binding for this app
Dim objSession As Object
Dim objMessage As Object
Dim objRecipient As Object
'declare some other utility variables
Dim i As Integer
Dim sSubject As String
Dim sText As String
Dim sName As String
'set up the email message text
sText = "This is an automatic test message, " & _
vbCrLf & _
"Please reply to the sender confirming receipt."
'and the subject
sSubject = "Test Message"
'start with the top of the mapi hierarchy --
'the session object
Set objSession = CreateObject("mapi.session")
'use the local Outlook default profile
objSession.LogOn profilename:="Microsoft Outlook"
'this application will send a number of test messages
'to the members of a particular domain
For i = 0 To lstEmails.ListCount - 1
'build the addresses from the names in the list
'and the given domain name
sName = Trim(lstEmails.List(i)) & "@" & _
Trim(txtDomain.Text)
'now create a new message object
Set objMessage = objSession.outbox.messages.Add
'feed in the required property values for the
'message
objMessage.subject = sSubject
objMessage.Text = sText
'create a new recipient for this message
Set objRecipient = objMessage.Recipients.Add
'and set it's properties
objRecipient.Name = sName
objRecipient.Type = 1
'make sure the email address is resolved
objRecipient.resolve
'now send the message
objMessage.Send showdialog:=False
'tidy up this message
Set objRecipient = Nothing
Set objMessage = Nothing
'and go round again for the next one
Next i
'all done so off we go
objSession.logoff
'tidying up as always
Set objSession = Nothing
'set the success return value
SendReturnEMail = True
Exit Function
SendReturnEMail_Err:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf _
& Err.Source
End Function
To finish with, here's an easy little application that places values from a VB application into an Excel spreadsheet. There are project-level (early bound) references created to both Excel and the ADODB 2.0 Reference Library. An ADO recordset has already been created and is passed as a parameter to the OutputToExcel function. The function creates an instance of a new Excel workbook and worksheet, then copies the values from the ADO recordset into the worksheet. Excel's functionality is used to perform a simple calculation on the data, the worksheet is saved, Excel is closed down, and all references are tidied up.
This example illustrates the power of a glue language such as Visual Basic. Here VB is acting as the glue between ADO, which is an ActiveX server, and Excel--controlling both to produce a simple yet patently powerful and seamless application:
Private Function OutputToExcel(oADORec As ADODB.Recordset) _
As Boolean
On Error GoTo cmdExcel_Err
'set up the default return value
OutputToExcel = False
' Declare the Excel object variables
Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet
'start at the top of the model
Set oXLApp = New Excel.Application
'and work your way down
Set oXLWBook = oXLApp.Workbooks.Add
'until you get to the worksheet
Set oXLWSheet = oXLWBook.Worksheets.Add
oXLWSheet.Cells(1, 1).Value = oADORec!FirstValue
oXLWSheet.Cells(2, 1).Value = oADORec!SecondValue
' do some stuff in Excel with the values
oXLWSheet.Cells(3, 1).Formula = "=R1C1 + R2C1"
' save your work
oXLWSheet.SaveAs "vb2XL.xls"
'quit Excel
oXLApp.Quit
' always remember to tidy up before you leave
Set oXLWSheet = Nothing
Set oXLWBook = Nothing
Set oXLApp = Nothing
OutputToExcel = True
Exit Function
cmdExcel_Err:
MsgBox Err.Description & vbCrLf & Err.Number & _
vbCrLf & Err.Source
End Function
Your efforts to resolve issues within an application are often frustrated by users not reporting errors. The user simply clicks past the message box reporting the error and continues. Either they forget or can't be bothered to contact the MIS department or the software developer to report the issue. There is a way you can store information about the error on the user's machine without having to go to the trouble of coding a file open/write/close routine that itself could cause a fatal error within the error handler.
The App object includes a method called LogEvent whose operation depends on the operating system being used. On NT the LogEvent method writes to the machine's event log, whereas in Windows 9x a log file is created or an existing log file appended to. Logging only takes place in compiled VB applications.
You can specify an event log file using the StartLogging method, which takes two parameters, the log filename and the log mode. (The App object's LogPath and LogMode properties, which you would expect to set before beginning logging, are read-only and can only be set by calling the StartLogging method.)
WARNING: Note that the log mode constants were missing from Version 5 of VB, so you either have to enter their literal values, or you have to define your own constants.
In Windows NT, if you call the StartLogging method but don't specify a log file, or in Windows 95, if you don't call the StartLogging method at all, VB creates a file called vbevents.log, which is placed in the Windows directory. To use event logging, you don't necessarily need to use the StartLogging method.
The LogEvent method itself takes two parameters. The first is a string containing all the detail you wish to store about the error or event. The second is an EventType constant, which denotes an error, information, or a warning. In NT, this event type value displays the correct icon in the event log, whereas in Windows 95, the word "Error," "Information," or "Warning" appears at the start of the item in the event log file.
TIP: In a previous section, "Error Handling in ActiveX Servers," you saw how to force MsgBox prompts to be automatically written to an event log by selecting the Unattended Application option. But which event log? The MsgBox function doesn't take a parameter to specify an optional event log, so VB will write the string contained within the Prompt parameter to the default vbevents.log in Windows 9x or to the application event log in Windows NT. However, you can place a call to the app object's StartLogging method in the class's Initialize event, thereby specifying a log file for all Msgbox and LogEvent calls.
Once you have an event log for your application, you can look back through the history of the application any time you choose. If you are networked to the user's machine, you can open the user's event log from your machine and detect problems without even leaving your desk.
No
#Const constantname = expression
constantname
expression
#Const directive, and arithmetic or logical operators except Is.
Defines a conditional compiler constant. By using compiler constants to create code blocks that are included in the compiled application only when a particular condition is met, you can create more than one version of the application using the same source code. This is a two-step process:
Defining the conditional compiler constant. This step is optional; conditional compiler constants that aren't explicitly defined by the #Const directive but that are referenced in code default to a value of 0 or False.
Evaluating the constant in the conditional compiler #If...Then statement block.
A conditional compiler constant can be assigned any string, numeric, or logical value returned by an expression. However, the expression itself can consist only of literals, operators other than Is, and another conditional compiler constant.
When the constant is evaluated, the code within the conditional compiler #If...Then block is compiled as part of the application only when the conditional compiler constant evaluates to True.
You may wonder why you should bother having code that is compiled only when a certain condition is met, when a simple If...Then statement could do the same job. The reasons are:
You may have code that contains early bound references to objects that are present only in a particular version of the application. You'd want that code compiled only when you know it wouldn't create an error.
You may wish to include code that executes only during the debugging phase of the application. It's often wise to leave this code in the application even after the application has been released, so that you can check back over a procedure if an issue arises. However, you don't want the code to be executed in the final application. The answer is to wrap your debugging code in a conditional statement. You can then provide a conditional constant that acts as a switch to turn debugging code on or off, as the example below demonstrates.
Although most operations performed with conditional compilation can be replicated with normal If...Then code blocks, conditional compilation reduces the size of the compiled application and thereby the amount of memory required for the application, making for a more efficient application.
Conditional compiler constants are evaluated by the conditional compiler #If...Then statement block.
You can use any arithmetic or logical operator in the expression except Is.
You can't use other constants defined with the standard Const statement in the expression.
According to the documentation, you can't use intrinsic functions in the expression; e.g., #Const MY_CONST = Chr(13) is illegal. In most cases, VBA displays a "Compile error : Variable not found" message if you try this. But there are numerous exceptions. For example, the use of the Int function in the following code fragment doesn't produce a compiler error, and in fact, successfully defines a constant ccDefInt whose value is 3:
#Const ccDefFloat = 3.1417 #Const ccDefInt = Int(ccDefFloat)
When using #Const, you can't use variables to assign the conditional constant a value.
Constants defined with #Const can be used only in conditional code blocks.
Constants defined with #Const have scope only within the module in which they are defined; i.e., they are private.
You can place the #Const directive anywhere within a module.
You can't use the #Const directive to define the same constant more than once within a module. Attempting to do so produces a "Compile Error: Duplicate Definition" error message.
Interestingly, you can define the same constant both through the VB or VBA interface (see the second item in the "Programming Tips & Gotchas" section) and using the #Const directive. In this case, the constant defined through the interface is visible throughout the application, except in the routine in which the #Const directive is used, where the private constant is visible.
The #Const directive must be the first statement on a line of code. It can be followed only by a comment. Note that the colon, which combines two complete sets of statements onto a single line, can't be used on lines that contain #Const.
#Const ccDebug = 1 'evaluates to true
Function testValue(sValue as String)
sValue = UCase(sValue)
testValue = sValue
#If ccDebug Then
'this code only executes if ccDebug evaluates to true
Debug.Print sValue
#End If
End Function
Conditional compiler constants help you debug your code, as well as provide a way to create more than one version of your application. You can include code that operates only when run in debug mode. The code can be left in your final version and won't compile unless running in the debugger. Therefore, you don't need to keep adding and removing debugging code.
You can also define conditional constants outside of the application's code. In the VBA Editor, enter the conditional compiler constant into the Conditional Compilation Arguments text box on the General tab of the Project Properties dialog. You can reach it by selecting the Project Properties option (where Project is the name that you've assigned to the project) from the Tools menu. In Visual Basic, the Conditional Compilation Arguments text box is found on the Make property sheet of the Project Properties dialog. It can be accessed by selecting the Project Properties option (again, where Project is the name that you've assigned to the project) from the Project menu. In Access, the Conditional Compilation Arguments text box is found on the Advanced property sheet of the Options dialog, which can be accessed by selecting the Options item from the Tools menu. Conditional compiler constants defined in this way are public to the project.
Constants Defined Through the VB/VBA Interface
|
In many cases, failing to properly define a constant doesn't produce an error message. When this happens (as it does, for instance, when you attempt to assign a variable's value to a constant), the default value of the constant is False. As a result, attempting to assign the value resulting from an invalid expression to a constant can lead to the inclusion of the wrong block of code in the compiled application.
Although it may be obvious, it's important to remember that the constant defined by #Const is evaluated at compile time, and therefore doesn't return information about the system on which the application is running. For example, the intent of the following code fragment is to test for a sound card and, if one is present, to include code taking advantage of the system's enhanced sound capabilities:
If waveOutGetNumDevs > 0 Then #Const ccSoundEnabled = True Endif ... #If ccSoundEnabled Then ' Include code for sound-enabled systems #Else ' Include code for systems without a sound card #End If
However, the code doesn't work as expected, since it includes or excludes the code supporting a sound card based on the state of the development machine, rather than the machine on which the application is running.
#If...Then...#Else Directive
No
AddressOf procedurename
Passes the address of a procedure to an API function. There are some API functions that require the address of a callback function as a parameter. (A callback function is a routine in your code that is invoked by the routine that your program is calling: it calls back into your code.) These callback functions are passed to the API function as pointers to a memory address. In the past, calling functions that required callbacks posed a unique problem to VB, since, unlike C or C++, it lacks a concept of pointers. However, the AddressOf operator allows you to pass such a pointer in the form of a long integer to the API function, thereby allowing the API function to call back to the procedure.
The callback function must be stored in a code module; attempting to store it in a class or a form module generates a compile-time error, "Invalid use of AddressOf operator."
The AddressOf operator must be followed by the name of a user-defined function, procedure, or property.
The data type of the corresponding argument in the API function's Declare statement must be As Any or As Long.
The AddressOf operator can't call one VB procedure from another.
The following example uses the EnumWindows and GetWindowText API calls to return a list of currently open windows. EnumWindows requires the address of a callback function as its first parameter. A custom function, EnumCallBackProc, is the callback function that populates the lstWindowTitles list box.
When the cmdListWindows command button is clicked, the list box is cleared, and a call to the EnumWindows API function is made, passing the AddressOf the EnumCallBackProc function and a reference to the list box control. EnumWindows then calls back to EnumCallBackProc, passing it the window handle of an open window and the reference to the list box. EnumCallBackProc then uses the GetWindowText API function to return the text in the titlebar of the window, passing it the window handle, a string buffer, and the length of that buffer. EnumCallBackProc is called by the API function as many times as is required, depending upon the number of open windows. The first portion of the example code must be stored in a code module, while the cmdListWindows_Click event handler can be stored in the form module containing the cmdListWindows button.
Option Explicit
Public Declare Function EnumWindows Lib "User32" _
(ByVal lpEnumFunc As Any, _
ByVal lParam As Any) As Long
Public Declare Function GetWindowText Lib "User32" _
Alias "GetWindowTextA" _
(ByVal hWnd As Long, _
ByVal lpString As String, _
ByVal cch As Long) As Long
Function EnumCallBackProc(ByVal hWnd As Long, _
ByVal lParam As ListBox) As Long
On Error Resume Next
Dim sWindowTitle As String
Dim lReturn As Long
sWindowTitle = String(512, 0)
lReturn = GetWindowText(hWnd, sWindowTitle, 512)
If lReturn > 0 Then
lParam.AddItem sWindowTitle
End If
EnumCallBackProc = 1
End Function
Private Sub cmdListWindows_Click()
Dim lReturn As Long
lstWindowTitles.Clear
lReturn = EnumWindows(AddressOf EnumCallBackProc, _
lstWindowTitles)
End Sub
Debugging calls containing AddressOf is at best very difficult and most of the time downright impossible.
It's possible to pass an AddressOf pointer from one VB procedure to another by creating a wrapper for the callback function. To do this, however, you must declare the pointer as either Long or Any. The following snippet shows how you could add such a wrapper function to the example used above:
Private Sub cmdListWindows_Click()
Dim lReturn As Long
lReturn = DoWindowTitles(AddressOf EnumCallBackProc, _
lstWindowTitles)
End Sub
Private Function DoWindowTitles(CallBackAddr As Long, _
lstBox As ListBox) As Long
'other stuff here
lstBox.Clear
DoWindowTitles = EnumWindows(CallBackAddr, lstBox)
End Function
Because you can't pass an error back to the calling Windows API function from within your VB callback function, you should use the On Error Resume Next statement at the start of your VB callback function.
Declare Statement
No
object, procedurename, calltype, _
argument1,..., argumentn])
object
procedurename
calltype
vbCallType constant
vbCallType constants are listed in the next table.
arguments
Constant |
Value |
Description |
|---|---|---|
vbGet |
2 |
The called procedure is a |
vbLet |
4 |
The called procedure is a |
vbMethod |
1 |
The called procedure is a method; this can be a Sub or a Function within object |
vbSet |
8 |
The called procedure is a |
Depends on the return value (if any) of the called procedure.
Provides a flexible method for calling a public procedure in a VB object module. Since procedurename is a string expression, rather than the hard-coded name of a routine, it's possible to call routines dynamically at runtime with a minimum of coding.
The return type of CallByName is the return type of the called procedure.
procedurename isn't case sensitive.
At last, VB allows you to create a call to a procedure using a string. This means that the call can be flexible at runtime.
The only drawback to the current implementation of CallByName is that the parameters to pass to the called function must be entered individually. This means that, when coding the CallByName function, you need to know in advance how many parameters are needed. You could work around this by coding your functions to accept only Variant arrays so that you only need to pass a single parameter.
Late binding is necessarily used to instantiate objects whose procedures are invoked by the CallByName function. Consequently, the performance of CallByName is inferior to that of method invocations in early bound objects. This degradation of performance is especially acute if CallByName is invoked repeatedly inside a looping structure.
The following example takes CallByName and the amendments to CreateObject to their logical conclusion: a variable procedure call to a variable ActiveX server in a variable location. In this example, the SQL Server pubs database is used as the source of the data. Two ActiveX objects on two separate machines are used to create two different recordsets: one from the Authors table, the other from the Titles table. However, nowhere in the program are the names of the ActiveX DLLs, the procedures, or the remote servers mentioned.
The middle tier of this application uses the registry to store these names, allowing fast alteration of the application without touching a single line of code or creating incompatibilities between components. The repercussions of this approach to enterprise-wide programming are wide-reaching, and the prospects very exciting.
Only when dealing with the user interface of the client component are the names of the required datasets and fields specified. The Form_Load event calls a standard function to populate combo box controls with the required data:
Private Sub Form_Load()
PopulateCombo cboAuthors, "Authors", "au_lname"
PopulateCombo cboTitles, "Titles", "title"
End Sub
The PopulateCombo function calls a GetRecordset function in the first middle tier of the model, passing in the recordset name required (either Authors or Titles in this case) and a search criteria string that is concatenated into the embedded SQL script to refine the recordset. GetRecordset returns an ADO recordset that populates the desired combo box:
Private Function PopulateCombo(oCombo As ComboBox, _
sRecords As String, _
sField As String) As Boolean
Dim adorRecords As ADODB.Recordset
Dim sSearch As String
If sRecords = "Authors" Then
sSearch = "contract = 1 AND state = 'CA'"
Else
sSearch = ""
End If
Set adorRecords = oAdmin.GetRecordset(sRecords, sSearch)
Do While Not adorRecords.EOF
oCombo.AddItem adorRecords(sField)
adorRecords.MoveNext
Loop
adorRecords.Close
Set adorRecords = Nothing
End Function
The GetRecordset method that sits on a central machine interrogates the registry (using the GetSetting function) to determine the names of the ActiveX server, the machine, and the procedure to call. I've also coded an alternative method of obtaining these names using a Select Case statement (which is commented out in the code sample). Finally, the CreateObject function obtains a reference to the appropriate ActiveX server on the appropriate machine and a call is made to the function in that server to obtain the correct recordset:
Public Function GetRecordset(sRecords As String, _
sCriteria As String _
) As ADODB.Recordset
Dim sServer As String
Dim sLocation As String
Dim sMethod As String
Dim oServer As Object
sServer = GetSetting(App.Title, sRecords, "Server")
sLocation = GetSetting(App.Title, sRecords, "Location")
sMethod = GetSetting(App.Title, sRecords, "GetMethod")
' An alternative method of obtaining the names of the
' elements of the remote procedure call is to hard-code
' them into the application as follows:
' Select Case sRecords
' Case Is = "Titles"
' sServer = "TestDLL.Titles"
' sLocation = "NTSERV1"
' sMethod = "GetTitles"
' Case Is = "Authors"
' sServer = "Test2DLL.Authors"
' sLocation = "NTWS2"
' sMethod = "getAuthors"
' Case Else
' Set GetRecordset = Nothing
' Exit Function
' End Select
Set oServer = CreateObject(sServer, sLocation)
Set GetRecordset = CallByName(oServer, _
sMethod, _
VbMethod, _
sCriteria)
End Function
The code to create the recordsets in TestDLL.Titles and Test2DLL.Authors isn't shown here, as it's straightforward database access code.
Now, imagine for a moment that the organization using this application wanted a minor alteration in the way the Authors recordset was presented to the client (a different sort order, for example). You can now make a change to the procedure, calling it getAuthorsRev ; compile a completely new ActiveX server; and place it on the remote server. Then with two quick edits of the registry, all the clients in the organization would instantly access the new procedure with a minimum of fuss, no loss of component compatibility, zero downtime, and an almost seamless transition.
Call Statement
No
Public
Private
Sub
Function.
Function
Sub.
Lib
Alias
Sub statement or Function statement.)
Function statement entry.)
Used at module level to declare references to external procedures in a dynamic-link library (DLL).
You can place a Declare statement within a code module, in which case it can be public or private, or within the declarations section of a form or class module, in which case it must be private.
Leaving the parentheses empty and not supplying an arglist indicates that the Sub or Function procedure has no arguments.
The number and type of arguments included in arglist are checked each time the procedure is called.
The data type you use in the As clause following arglist must match that returned by the function.
Option Explicit
Declare Function GetVersion Lib "kernel32"() As Long
Public Function WhereAmI() As Boolean
Dim lWinVersion As Long
Dim lWinMajVer As Long
Dim lWinMinVer As Long
Dim sSys As String
lWinVersion = GetVersion()
lWinMajVer = lWinVersion And 255
lWinMinVer = (lWinVersion And 65280) / 256
If lWinVersion And &H80000000 Then
sSys = "Windows 95"
Else
sSys = "Windows NT"
End If
Msgbox "Platform: " & sSys & vbCrLf & _
"Version: " & lWinMajVer & "." & lWinMinVer
If you don't specify a Public or Private keyword, the visibility of the external procedure is public by default. However, if the routine is declared in the declarations section of a form or a class module, a compiler error ("Constants, fixed length strings, arrays, and Declare statements not allowed as Public members of object modules") results.
Using an alias is useful when the name of an external procedure would conflict with a Visual Basic keyword or with the name of a procedure within your project, or when the name of the procedure in the code library isn't allowed by the Visual Basic DLL naming convention. In addition, aliasname is frequently used in the case of functions in the Win32 API that have string parameters, where the "official" documented name of the function is used in code to call either of two "real" functions, one an ANSI and the other a Unicode version. For example:
Declare Function ExpandEnvironmentStrings _ Lib "kernel32" Alias "ExpandEnvironmentStringsA" _ (ByVal lpSrc As String, ByVal lpDst As String, _ ByVal nSize As Long) As Long
defines the documented Win32 function ExpandEnvironmentStrings to a VB application. However, although calls to the function take the form:
lngBytes = ExpandEnvironmentStrings(strOriginal, _
strCopy, len(strCopy)
the actual name of the function as it exists in Kernel32.dll is ExpandEnvironmentStringsA. (Windows API functions ending in A are the ANSI string versions, and those ending in W (for W
ide) are the Unicode string versions.)
You can use the # symbol at the beginning of aliasname to denote that aliasname is in fact the ordinal number of a procedure within the DLL or code library. In this case, all characters following the # sign that compose the aliasname argument must be numeric. For example:
Declare Function GetForegroundWindow Lib "user32" _
Alias "#237" () As Long
Remember that DLL entry points are case sensitive. In other words, either name or, if it's present and doesn't represent a routine's ordinal position, aliasname must correspond in case exactly to the routine as it's defined in the external DLL. Otherwise, VB displays runtime error 453, "Specified DLL function not found." If you aren't sure how the routine name appears in the DLL, use QuickView to browse the DLL and scan for its export table.
libname can include an optional path that identifies precisely where the external library is located. If the path isn't included along with the library name, VB by default searches the current directory, the Windows directory, the Windows system directory, and the directories in the path, in that order.
If the external library is one of the major Windows system DLLs (like Kernel32.dll or Advapi32.dll ), libname can consist of only the root filename, rather than the complete filename and extension.
In some cases, a single parameter to an API function can accept one of several data types as arguments. This is particularly common when a function accepts a pointer to a string buffer if an argument is to be supplied and a null pointer if it doesn't; the former is expressed in Visual Basic by a string argument and the latter by a 0 passed to the function by value. It's also the case whenever an API function designates a parameter's data type as LPVOID, which indicates a pointer to any data type. To handle this, you can define separate versions of the DECLARE statement, one for each data type to be passed to the function. (In this case, name designates the name by which a particular API function is referenced in your program, while the ALIAS clause designates the name of the routine as it exists in the DLL.) A second alternative, rather than having to "strongly type" a parameter in arglist, is to designate its data type as As Any, indicating that the routine accepts an argument of any data type. While this provides you with a flexible way of partly overcoming the mismatch between VB and C data types, you should use it with caution, since it suspends Visual Basic's normal type checking for that argument.
Windows NT was built from the ground up using Unicode (two-byte) strings; however, it also supports ANSI strings. OLE 2.0 was built to use Unicode strings exclusively. Visual Basic from Version 4 onwards uses Unicode strings internally, but passes ANSI strings into your program. What does all this mean for you? Well, Windows NT and OLE 2.0 API calls that have string parameters require them to be passed as Unicode strings. Unfortunately, although Visual Basic uses Unicode strings internally, it converts strings passed to these DLLs back into ANSI. The remedy is to use a dynamic array of type Byte. Passing and receiving arrays of bytes circumvents Visual Basic's Unicode-ANSI conversion.
To pass a string to a Unicode API function, declare a dynamic byte array, assign your string to the array, and concatenate a terminating null character (vbNullChar) to the end of the string, then pass the first byte of the array (at element 0) to the function, as the following simple snippet shows:
Dim bArray() As Byte bArray() = "My String" & vbNullChar someApiCall(bArray(0))
Declare statement is to make routines in the Win32 API accessible to your programs. For more information on calling the Win32 API from Visual Basic, see Dan Appleman's The Visual Basic Programmer's Guide to the Win32 API,
published by Ziff-Davis Press. Sub Statement, Function Statement, StrConv Function
No
In VBA, DoEvents returns 0; in the retail version of VB, it returns the number of open forms.
Allows the operating system to process events and messages waiting in the message queue. For example, you can allow a user to click a Cancel button while a processor-intensive operation is executing. In this scenario, without DoEvents, the click event wouldn't be processed until after the operation had completed; with DoEvents, the Cancel button's Click event can be fired and its event handler executed even though the processor-intensive operation is still executing.
Control is returned automatically to your program or the procedure that called DoEvents once the operating system has processed the message queue.
The following example uses a UserForm with two command buttons to illustrate how DoEvents interrupts a running process:
Option Explicit
Private lngCtr As Long
Private blnFlag As Boolean
Private Sub CommandButton1_Click()
blnFlag = True
Do While blnFlag
lngCtr = lngCtr + 1
DoEvents
Loop
MsgBox "Loop interrupted after " & lngCtr & _
" iterations."
End Sub
Private Sub CommandButton2_Click()
blnFlag = False
End Sub
You may consider using the retail version of VB to create standalone ActiveX EXEs that handle very intensive or long processes. These can then be called from your VBA code. This allows you to pass the responsibility of time slicing and multitasking to the operating system.
Make sure that during the time you have passed control to the operating system with DoEvents, the procedure calling DoEvents isn't called from another part of the application or from another application, since the return from DoEvents may be compromised. For the same reason, you must not use the DoEvents function within VB in-process ActiveX DLLs.
While DoEvents can be essential for increasing the responsiveness of your program, it should at the same time be used judiciously, since it entails an enormous performance penalty. For example, the following table compares the number of seconds required for a simple For...Next loop to iterate one million times when DoEvents isn't called, on the one hand, and when it's called on each iteration of the loop, on the other:
without DoEvents |
0.3 seconds |
|---|---|
with DoEvents |
49.8 seconds |
If most of a procedure's processing occurs inside a loop, one way of avoiding far-too-frequent calls to DoEvents is to call it conditionally every hundred or thousand iterations of the loop. For example:
Dim lngCtr As Long
For lngCtr = 0 To 1000000
If lngCtr / 1000 = Int(lngCtr / 1000) Then
DoEvents
End If
Next
A read-only property containing a long data type representing a system error produced within a DLL called from within a VB program.
Only direct calls to a Windows system DLL from VB code assign a value to LastDLLError.
The value of the LastDLLError property depends upon the particular DLL being called. Your code must be able to handle the various codes that can be returned by the DLL you are calling.
Don't forget that a failed DLL call doesn't itself raise an error within your VB program. As a result, the Err object's Number, Description, and Source properties aren't filled.
The LastDLLError property is used only by system DLLs, such as kernel32.dll, and therefore errors that occur within DLLs you may have created in VB won't be assigned.
Obtaining accurate documentation about the return values of system DLLs can be a challenging experience. Most useful information can be found by studying the API documentation for Visual C++. However, you can use the Windows API FormatMessage to return the actual Windows error message string from within Kernel32.DLL, which incidentally is also in the correct language. The following is a brief example you can use in your applications to display the actual Windows error description:
Option Explicit Declare Function FormatMessage Lib "kernel32" _ Alias "FormatMessageA" _ (ByVal dwFlags As Long, lpSource As Any, _ ByVal dwMessageId As Long, _ ByVal dwLanguageId As Long, _ ByVal lpBuffer As String, ByVal nSize As Long, _ Arguments As Long) As Long Public Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000 Public Const FORMAT_MESSAGE_IGNORE_INSERTS = &H200 Function apiErrDesc(lErrCode As Long) As String Dim sErrDesc As String Dim lReturnLen As Long Dim lpNotUsed As Long sErrDesc = String(256, 0) lReturnLen = FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM _ Or FORMAT_MESSAGE_IGNORE_INSERTS, _ lpNotUsed, lErrCode, 0&, sErrDesc, _ Len(sErrDesc), ByVal lpNotUsed) If lReturnLen > 0 Then apiErrDesc = Left$(sErrDesc, lReturnLen) End If End FunctionHere's a snippet demonstrating how you can use this utility function:
lReturn = SomeAPICall(someparams) If lReturn <> 0 then Err.Raise Err.LastDLLError & vbObjectError, _ "MyApp:Kernel32.DLL", _ apiErrDesc(Err.LastDLLError) End IfNote that some API calls return 0 to denote a successful function call; others return 0 to denote an unsuccessful call. You should also note that some API functions don't appear to set the LastDLLError property. In most cases, these are functions that return an error code. You could therefore modify the snippet above to handle these cases:
lReturn = SomeAPICall(someparams) If lReturn <> 0 then If Err.LastDLLError <> 0 Then Err.Raise Err.LastDLLError & vbObjectError, _ "MyApp:Kernel32.DLL", _ apiErrDesc(Err.LastDLLError) Else Err.Raise lReturn & vbObjectError, _ "MyApp:Kernel32.DLL", _ apiErrDesc(lReturn) End If End If
Err Object, Chapter 6
No
Filter(SourceArray, FilterString[, Switch[, Compare]])
True or False) value. If True, the default value, Filter includes all matching values in result; if False, Filter excludes all matching values (or, to put it another way, includes all nonmatching values).
vbCompareMethod Enumeration
vbBinaryCompare, vbTextCompare, vbDatabaseCompare) that indicates the type of string comparison to use. The default value is vbBinaryCompare.
A String array of the elements filtered from SourceArray.
Produces an array of matching values from an array of source values that either match or don't match a given filter string. In other words, individual elements are copied from a source array to a target array if they either match or don't match a filter string.
The default Switch value is True.
The default Compare value is vbBinaryCompare.
vbBinaryCompare is case sensitive; that is, Filter matches both character and case. In contrast, vbTextCompare is case insensitive, matching only character regardless of case.
The returned array is always base 0, regardless of any Option Base setting.
The Filter function ignores zero-length strings ("") if SourceArray is a string array and ignores empty elements if SourceArray is a variant array.
The array you declare to assign the return value of Filter must be a dynamic, single-dimension String array or a variant.
Although the Filter function is primarily a string function, you can also filter numeric values. To do this, specify a SourceArray of type Variant and populate this array with numeric values. Although FilterString appears to be declared internally as a string parameter, a String, Variant, Long, or Integer can be passed to the function. Note, though, that the returned string contains string representations of the filtered numbers. For example:
Dim varSource As Variant, varResult As Variant
Dim strMatch As String
strMatch = CStr(2)
varSource = Array(10, 20, 30, 21, 22, 32)
varResult = Filter(varSource, strMatch, True, _
vbBinaryCompare)
In this case, the resulting array contains four elements: 20, 21, 22, and 32.
The Filter function is an ideal companion to the Dictionary object. The Dictionary object is a collection-like array of values, each of which is stored with a unique string key. The Keys method of the Dictionary object allows you to produce an array of these Key values, which you can then pass into the Filter function as a rapid method of filtering the members of your Dictionary, as the following example demonstrates.
Dim sKeys() As String
Dim sFiltered() As String
Dim sMatch As String
Dim blnSwitch As Boolean
Dim oDict As Dictionary
Set oDict = New Dictionary
oDict.Add "One Microsoft Way", "Microsoft"
oDict.Add "31 Harbour Drive", "AnyMicro Inc"
oDict.Add "The Plaza", "Landbor Data"
oDict.Add "999 Pleasant View", "Micron Co."
sKeys = oDict.Keys
sMatch = "micro"
blnSwitch = True
'find all keys that contain the string "micro" - any case
sFiltered() = Filter(sKeys, sMatch, blnSwitch, _
vbTextCompare)
'now iterate through the resulting array
For i = 0 To UBound(sFiltered)
Set oSupplier = oDict.Item(sFiltered(i))
With oSupplier
Debug.Print oSupplier.Address1
End With
Set oSupplier = Nothing
Next i
Yes
GetObject([pathname] [, class])
The class argument has these parts:
Appname.Objecttype.
Returns a reference to an ActiveX object.
Accesses an ActiveX server held within a specified file.
Although both pathname and class are optional, at least one parameter must be supplied.
In situations in which you can't create a project-level reference to an ActiveX object, you can use the GetObject function to assign an object reference from an external ActiveX object to an object variable.
GetObject is used when there is a current instance of the ActiveX object; to create the instance, use the CreateObject function.
If you specify pathname as a zero-length string, GetObject returns a new instance of the object--unless the object is registered as single instance, in which case the current instance is returned.
If you omit the pathname, the current instance of the object is returned.
An error is generated if pathname isn't specified, and no current instance of the object can be found.
The object variable you use within your program to hold a reference to the ActiveX object is dimensioned as type Object. This causes the object to be late bound; that is, your program knows nothing of the type of object nor its interface until the object has been instantiated within your program. To assign the reference returned by GetObject to your object variable, you must use the Set statement:
Dim myObject As Object
Set myObject = GetObject("C:\OtherApp\Library.lib")
The details of how you create different objects and classes are determined by how the server has been written; you need to read the documentation for the server to determine what you need to do to reference a particular part of the object. There are three ways you can access an ActiveX object:
GetObject("C:\OtherApp\Library.lib")
GetObject("C:\OtherApp\Library.lib!Section")Class parameter:
GetObject("C:\OtherApp\Library.lib", "App.Class")Pay special attention to objects registered as single instance. As their type suggests, there can be only one instance of the object created at any one time. Calling CreateObject against a single-instance object more than once has no effect; you still return a reference to the same object. The same is true of using GetObject with a pathname of ""; rather than returning a reference to a new instance, you obtain a reference to the original instance of the object. In addition, you must use a pathname argument with single-instance objects (even if this is ""); otherwise an error is generated.
You can't use GetObject to obtain a reference to a class created with Visual Basic.
When possible, you should use early binding in your code. For more details on early and late binding, see Chapter 4. You can use GetObject in early binding, as in:
Dim objExcel As Excel.Application Set objExcel = GetObject(, "Excel.Application")
The following table shows when to use GetObject and CreateObject :
Task |
Use |
|---|---|
Create a new instance of an OLE server |
CreateObject |
Create a subsequent instance of an already instantiated server (if the server isn't registered as single instance) |
CreateObject |
Obtain a further reference to an already instantiated server without launching a subsequent instance |
GetObject |
Launch an OLE server application and load an instance of a subobject |
GetObject |
Instantiate a class created with VB |
CreateObject |
Instantiate a class registered on a remote machine |
CreateObject |
CreateObject Function, Set Statement
No
Dim|Private|Public WithEvents objVarname As objectType
The WithEvents keyword informs VB that the object being referenced exposes events. When you declare an object variable using WithEvents, an entry for the object variable is placed in the code window's drop-down Object List, and a list of the events available to the object variable is placed in the code window's drop-down Procedures List. You can then write code event handlers for the object variable in the same way that you write other more common event handlers such as Form_Load.
An object variable declaration using the WithEvents keyword can be used only in an object module such as a Form or Class module.
An object variable declaration using the WithEvents keyword should be placed only in the Declarations section of the object module.
Any ActiveX object or class module that exposes events can be used with the WithEvents keyword. WithEvents is valid only when used to declare an object variable.
You can't use WithEvents when declaring the generic Object type.
Unlike other variable declarations, the As keyword is mandatory.
There is no limit to the number of object variables that can refer to the same object using the WithEvents keyword; they all respond to that object's events.
You can't create an array variable that uses the WithEvents keyword.
The following example demonstrates how to trap and respond to the events within an ADO recordset. An object variable is declared using the WithEvents keyword in the declarations section of a form module. This allows you to write event-handling code for the ADO's built-in events, in this case the FetchProgress event. (The FetchProgress event allows you to implement a Progress Bar control that shows progress in populating the recordset.)
Private WithEvents oADo As ADODB.Recordset
Private Sub oADo_FetchProgress(ByVal Progress As Long, _
ByVal MaxProgress As Long, _
adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)
ProgressBar1.Max = MaxProgress
ProgressBar1.Value = Progress
End Sub
Placing the object variable declaration that uses the WithEvents keyword in a procedure doesn't add the object variable name to the module's Object List. In other words, the events fired from the object would have scope only in the procedure and therefore can't be handled.
Even if you declare the object variable using the Public keyword, the events fired by the object have scope only in the module in which the object variable has been declared.
Because you can't use WithEvents to declare a generic Object type, WithEvents can be used only with early bound object references. In other words, objects must have been added to the project using the References dialog. Without this prior knowledge of the object's interface, VB has no chance of knowing how to handle events from the object.
If the object you are referencing doesn't expose any public events, you will generate a compile-time error, "Object doesn't source Automation Events."
You can't handle any type of event from within a code module. This isn't really a limitation, because to pass program control to a code module, you can simply call one of its functions or procedures from your event handler, just as you would from a form or control's event handler.
For information about generating your own custom events in form and class modules, see the "Implementing Custom Events" section in Chapter 4.
Dim Statement, Event Statement, Private Statement, Public Statement, RaiseEvent Statement
by Paul Lomax
Copyright ¨ 1998 O'Reilly & Associates, Inc. All rights reserved.
Printed in the United States of America.
Published by O'Reilly & Associates, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
Editor: Ron Petrusha
Production Editor: Mary Anne Weeks Mayo
Printing History:
October 1998: First Edition.
Nutshell Handbook and the Nutshell Handbook logo are registered trademarks of O'Reilly & Associates, Inc. The association of the image of a Newfoundland dog and the topic of VB & VBA is a trademark of O'Reilly & Associates, Inc.
IntelliSense, Microsoft, MS-DOS, PowerPoint, Visual Basic, Visual C++, Win32, Windows, and Windows NT are registered trademarks, and ActiveX, Outlook, and Visual Studio are trademarks of Microsoft Corporation. Visio is a registered trademark of Visio Corporation. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O'Reilly & Associates, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher assumes no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
This book is printed on acid-free paper with 85% recycled content, 15% post-consumer waste. O'Reilly & Associates is committed to using paper with the highest recycled content available consistent with high quality.
ISBN: 1-56592-358-8
© 2001, O'Reilly & Associates, Inc.