- Shopping Bag ( 0 items )
The online documentation of VB/VBA language components seems to follow the 80/20 rule: the basic facts that you need to use a language statement are provided in the documentation. But the additional 20 percent that you need to use it effectively or to apply it to special cases is conspicuously absent. To a professional VB/VBA programmer, though, this missing 20 percent of the language's documentation isn't a luxury, it's a necessity. And in VB & VBA in a Nutshell: The ...
The online documentation of VB/VBA language components seems to follow the 80/20 rule: the basic facts that you need to use a language statement are provided in the documentation. But the additional 20 percent that you need to use it effectively or to apply it to special cases is conspicuously absent. To a professional VB/VBA programmer, though, this missing 20 percent of the language's documentation isn't a luxury, it's a necessity. And in VB & VBA in a Nutshell: The Language, it finally is available.
The bulk of the book consists of an alphabetical reference to the statements, procedures, and functions of the VB/VBA language. Each entry has a standardized listing containing the following information:
Also included in VB & VBA in a Nutshell: The Language is a brief overview of the VB/VBA language, including:
Designed for programmers, this desktop quick reference covers Visual Basic 5 & 6 and the hosted language Visual Basic for Applications (VBA). VBA is widely used as the binding glue between Microsoft office products and application technologies. To derive the most benefit from this reference, you should be familiar with VB, the Windows environment, the object model, VB basic structure and VB syntax.
Perhaps the biggest obstacle to overcome is deciding which one of a number of functions and statements available to perform roughly the same task should be used. This situation has come about as the language has evolved over a number of years, and older statements have been left in the language to provide backward compatibility. If you always bear in mind that the language was originally made up of statements, that later versions introduced functions, and that only recently have object models been provided, then you can pinpoint the latest functionality within the language.
To help speed the process of finding the right function or statement to perform a particular task, you can use Appendix A, Language Elements by Category, to determine what language elements are available for the purpose you require. You can also make use of the VB or VBA Object Browser to search or browse the VBA library.
As I've stated several times already, this book concentrates on the VBA language, and therefore doesn't include the following components, which don't form a part of the VBA language:
There is another large concentration of statements in the file manipulation section of VB. Again, these statements predate object programming in VB. It's likely that the new File System object model released with VB6 will replace the intrinsic VBA file statements.
Some statements in VB are quite old, having their roots back in (and before) Version 1 of VB. (Remember that Version I of VB itself represented a graphical interface design program grafted onto the Basic language.) in some cases, statements have been replaced by newer and more flexible functions, but the statement remains in the language for compatibility. That said, there are literally only one or two instances where both a statement and function of the same name exist.
Most statements don't accept named arguments, since this concept didn't exist in the early days of VB. In the main, statements don't return a value. Therefore, you are often well-advised to wrap these statements in a custom function that can return a value you can use to determine if a task was successful or not.
Some newer statements are in fact methods of VBA class libraries. For example the DeleteSetting statement is a method of the VBA. Interaction class.
In addition to returning a value, there are other important benefits of functions over statements:
Dim iReturnVal As Integer
iReturnVal = MsgBox(Prompt:="Click OK",
strResult = 16 + int(lngVarl)
the addition operator (+) combines 16 and the value returned by int (lngVarl) into a single expression whose value is assigned to the variable strResult.
Interestingly, the VBA language is itself implemented as an object model, although very rarely is it used as such. Since it's a flat model (that is, it doesn't define a class hierarchy), it isn't actually thought of as an object model. Nevertheless, if you use the object browser to examine the VBA component, you'll see that it's an external library containing various classes.
Some of the latest additions to VBA have used object models. For example, the Scripting Runtime provides us with the File System object model and the Dictionary object. VB6 also includes the Data Binding and Data Formatting objects. As time goes by we'll find more and more object models augmenting and replacing statements and functions in VBA,
Because of their importance to VB6, I've included full descriptions of the File System, Dictionary, Data Binding, and Data Formatting object models in this language section. Note that as long as the object libraries are available and are registered in the system registry, their objects are available to all 32-bit VB and VBA applications and macros developed in the current version, as well as earlier versions of VB and VBA-hosted applications.
#Const constantname - expression
Name of the constant.
Data Type: Literal
Any combination of literal values, other conditional compilation constants defined with the #Const directive, and arithmetic or logical operators except Is.
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 the code compiled only when you know it would 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.
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 ccDef Int whose value is 3:
#Const ccDefFloat = 3.1417 #Const ccDefInt = int(ccDeFloat)
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. . . .
PrefacePart II: The BasicsChapter 1: Introduction Chapter 2: Program StructureChapter 3: VBA Variables and Data TypesChapter 4: Class ModulesChapter 5: AutomationChapter 6: Error HandlingPart III: ReferencePart IV: AppendixesAppendix A: Language Elements by CategoryAppendix B: Language ConstantsAppendix C: OperatorsAppendix D: What's New in VB6?Colophon
In its simplest form, Visual Basic for Applications is a glue language. This means that as a VB or VBA developer, you concentrate on the interface of and interaction between the objects and controls within the application, gluing the various elements of the application together by writing procedures to perform programmatic tasks and by adding code to handle events. Visual Basic programs are primarily event-driven. Some event or other - such as the user clicking a button - triggers most of the procedures you will write.
From a developer's point of view, one of the most important characteristics of an event-driven application is that, for the most part, the various elements of the program are not interdependent. Sections of your program can be written in complete isolation from the rest. Procedures can be added, removed, or disabled without necessarily having an adverse effect on the whole application. This isn't to say that a Visual Basic application is unstructured; far from it. Before starting to write your VB application, you should have a clear plan of how the various elements of your application are going to interact.
Over the past few years, VB developers have been empowered with a rapidly expanding development environment that can now create custom controls and ActiveX DLLs and EXEs that run either as client-side servers or as remote servers. This movement towards a more object-based ethos has forced a change in the programming style of most VB developers. For the majority of professional VB developers, the days when you could sit in front of a blank form and begin programming without a written plan - altering the architecture of your application on the fly - are long gone.
In this chapter, you will see how to structure a VB program, from starting your program, through the various procedure types at your disposal, and then how you can eventually end your VB program.
Getting a VB Program to Run
Regardless of the type of application you're writing and the development tool (hosted VBA or the retail version of VB) you're using, there has to be a starting point or an entry point for your program. Here there is a major difference between VB and VBA: a VB application is launched as an application in its own right, whereas the VBA program has to be launched by the host application. But in either case, the starting point you choose is decided by the type of application you are writing, as well as by the facilities offered by your development environment for launching applications. In this section, we'll look at the methods available to you for starting your application.
Because VBA is now hosted in a wide range of different applications, each of which has its own ways of launching an application or routine, it's impossible to describe here how to start your program running in each. Instead, we'll focus on the two most popular applications for hosted VBA, Word and Excel.
In discussing the launching of VBA programs in Word and Excel, I mention using the application's user interface to launch the program using a keyboard combination or a toolbar button. This can also be done programmatically. A discussion of how to do so, however, is beyond the scope of this book.
Running VBA Modules in Word
A Word/VBA program can take a multitude of forms, ranging from a small routine that accomplishes some utility function at one extreme to a complete application that handles every detail of the user's interaction with Word. Of course, you want the method that invokes your program to be consistent with its general purpose. Fortunately, Word provides several ways to launch a VBA application.
Storing your code
Whenever Word starts, it automatically loads the default global template file, normal.dot. It then loads all template (.dot) files in the Word startup directory, which (assuming the software is Word 97) is defined by the STARTUP-PATH value entry in the HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Word\Options key in the registry and can be customized by selecting the File Locations tab from the Options dialog (Tools --> options) and modifying the Startup entry. These also become part of Word's global layer, as do Word add-in (.wll) files, which are loaded last. So if your application is to affect the Word environment or multiple Word documents, you should place your code in a template that is loaded into the global layer. If your program is to be distributed to other users, you should store your code in a global template file other than normal.dot, where you're likely to overwrite customizations the user has made.
A global template file loaded during Word startup is displayed in the Project window visible in the development environment, but isn't viewable. To edit the file, you must open it in the Word environment. Note that you may have to close and reopen Word in order for modifications to take effect. in some cases, even if the file is open, you still may not be able to edit its code in the VBA IDE. In that case, you'll have to make modifications to a copy of the file stored in another directory and synchronize the two copies.
If your application applies to a set of documents that are based on a template (which is typically stored in the Office Template directory or one of its subdirectories), you can place your code in the template file. Each document created using that template maintains a reference to the template. So even though the code remains in the template and isn't copied to the document, the VBA code in the template can be executed as long as the reference is valid.
If your application applies only to a particular document, you can store the code in the document itself. You don't have to work with the templates loaded into Word's global layer.
If you are developing an application or routine that is responsible for initializing the Word environment, that provides some service expected to be available throughout a Word session, or that implements a customized interface that mediates between the user and Word, you want to have Word launch your application whenever Word itself is launched. Word provides two methods of doing this. Both are remnants of WordBasic and both require that you store your macro in a global template:
There is also a converse scenario - running a procedure when Word is closing - that operates in exactly the same way as AutoExec. You simply name the procedure AutoExit or include an AutoExit module with a Main procedure.
When a document loads
In many cases, your application should launch whenever a particular document (or a set of documents, or even all documents) is opened. Once again, Word offers several methods of executing code when an existing document is opened or a new one is created. All of them require, though, that the code be located either in the current template or in the document itself. The methods are:
You can also designate a cleanup routine to execute when a document closes. WordBasic recognizes either an AutoClose procedure or a Main routine in a code module named AutoClose. VBA fires the Document.Close event when a document closes.
In response to direct user action
Frequently, VBA/Word code is less an "application" as we typically understand it than a "macro" - i.e., a small piece of self-contained code that performs some useful function. For macros to be useful, there has to be a way for the user to run them easily from the Word interface. in this respect, Word provides a rich environment for the macro developer, since it supports so many ways of hooking a macro to the user interface. These include:
Intercepting Word's built-in commands
Most common Word operations are public procedures. This means that if you create a procedure of the same name and store it in a global template, in the current document's template, or in the active document itself, your procedure, rather than Word's built-in procedure, will execute. For example, when the user selects the Close option from the File menu, the FileClose procedure executes. Ordinarily, Word closes the active document. However, you can modify Word's behavior by substituting a FileClose routine like the following, which gives the user the option of closing all open documents:
Public Sub FileClose( )
Dim lngResponse As Long
Dim objDoc As Document
If Documents.Count = 1 Then
lngResponse = MsgBox("Close all open documents?", _
Select Case lngResponse
Assigning a macro to a toolbar button
You can add a button to a toolbar and assign a macro to it. To do this from Word's user interface, select the Customize option from the Tools menu, or right-click on any toolbar and select the Customize option. Word opens the Customize dialog. Make sure that the toolbar to which you add the button is checked in the Toolbars tab, then select the Commands tab. Select Macros in the Categories list box and the macro you want to add to the toolbar in the Commands list box. Then drag the macro object from the Commands list box, position it on the toolbar, and drop.
Assigning a macro to a key
To assign a macro to a keyboard combination, open the Customize dialog and click on the Keyboard button. Select Macros in the Categories list box, then select the macro to which you'd like to assign a key combination in the Commands list box. Move the cursor to the "Press new shortcut key" text box and select the key combination you'd like to activate your macro.
Running VBA Modules in Excel
An Excel/VBA application, like its Word counterpart, can be anything from a small routine that performs a useful service to a large application that completely shields the user from Excel's basic interface. Excel, like Word. provides a variety of ways to launch an application that's consistent with its overall purpose.
Storing your code
When Excel loads, it automatically loads all workbook (.xls) and add-in (.xla) files stored in the XLStart directory (and notably Personal.xls, a worksheet that can serve as a repository for code, and that Excel makes hidden by default) and in an alternate startup directory. XLStart is created by Excel during installation (it's typically a subdirectory of the Office directory) and can't be changed. The alternate startup directory, if one is defined, supplements the XLStart directory, and it's configurable. To define or change it, you can select the options option from the Tools menu, click on the General tab of the Options dialog, and enter the path in the "Alternate startup file location" text box.
Typically, to store global macros, you'd want to create your own add-in file or your own worksheet, which can be hidden and stored in the XLStart folder. Particularly since Personal.xls is a frequent target of Excel macro viruses, it's best not to use it as a repository for your code. Excel actually loads each of these startup files; consequently, it's important that they remain hidden. While Personal.xls and all add-in files are hidden automatically, other files aren't. To hide them, select the Hide option from the workbook's Window menu.
Like Word, Excel also supports the creation of documents from templates, which also can contain code. SO if Your VBA code applies only to a particular kind of workbook (that is to say, to all workbooks created from the same template), you can store the code in the template (.xlt) file. Excels behavior here, though, is somewhat different from Word's; whereas Word adds a reference to the template to the document, Excel actually embeds the template's code in a newly created workbook.
Finally, if VBA code applies only to a single document, the code can be added to the document, rather than to autoloaded workbooks, add-ins, or templates.
When Excel starts. it automatically loads all add-in and workbook files in its startup and alternate startup directories. Their Workbook.Open event is fired. Note that, although this is a document-level event (that is, it's fired by a workbook being opened, rather than by Excel starting), the fact that no document workbooks are open when the startup workbooks and add-ins are loaded makes these Workbook.Open event handlers functionally similar to the Word AutoExec procedure.
When a document loads
To execute code when a particular workbook is opened, that workbook must have been created using a template that included a Workbook.Open event handler, or a Workbook.Open event handler must have been added to the workbook itself. In fact, Excel workbooks support a rich event model; you can attach event handlers to such Workbook events as Activate, BeforeClose, Deactivate, NewSheet, and SheetActivate.
In response to direct user action
Like Word code, VBA code in Excel often consists of a set of macros. Sometimes, you can hook these to a Workbook event. But more commonly, you have to provide a way for the user to run your macro from the Excel interface. Although Excel lacks Word's ability to intercept basic procedures, it does offer two major options for "hooking" your macro into the Excel interface:
Assigning a macro to a toolbar button
You can add a button to a toolbar or submenu and assign a macro to it. To do this from Excel's user interface, select the Customize option from the Tools menu or right-click on any toolbar and select the Customize option. Excel opens the Customize dialog. Make sure that the toolbar to which you add the button is checked in the Toolbars tab, then select the Commands tab. Select Macros in the Categories list box and either Custom Menu Item or Custom Button from the Commands list box, depending on how you will attach your macro to the interface. Next, drag the object from the Commands list box, position it on the toolbar, and drop. Finally, right-click on the new menu item or button, select the Assign Macro option from the context menu, and select the routine you want to assign to the menu item or toolbar button.
Assigning a macro to a key
To assign a macro to a keyboard combination, open the Macro dialog (Tools --> Macro --> Macros), and select the macro you want to assign to a key combination from the Macro Name list box. Next, click on the Options button to open the Macro Options dialog. Finally, select a shortcut key to use along with the Ctrl key to activate your macro. Note that Excel doesn't inform you if you've chosen a key assignment already in use; it simply overwrites the old assignment with the new one.
Running VB Executables
An application that is to be compiled into an executable file with the retail version of Visual Basic and that contains forms can be started by the Visual Basic runtime loading a form, or by running a specially named sub procedure called Main. An application that is to be compiled into an ActiveX EXE, DLL, or OCX can only be started using a Sub Main procedure. You specify the startup method for the project in the General tab of the Project Properties dialog box (you open it by selecting the
Whether you specify a Form or a Sub Main procedure within a code module as the startup object for your program, the VB runtime module first loads into memory all Public or Global constants and variables in all code modules within the project. Therefore, you have instant access to these at startup. Beware, however, that publicly declared variables in form modules aren't loaded at startup time; they are only available while the form itself is loaded. This means that you can't assign a value to a Public variable in another form from that of your startup form or from a startup code module.
Using a Form at Startup
If you specify a form as the starting point for your project, the VB runtime module loads this form after loading project-level variables and constants but before executing any of your project code. When the form is loaded into memory, the form's Initialize event is fired, followed immediately by the Load event. Once the Form is displayed on screen, the Activate event is fired.
The Form Load and Initialize events
Until Version 4 of VB, the initialization code for a Form module was placed in the Form_Load event, and probably through habit - and possibly because it's still the default event - most VB developers continue to use the Form-Load event. However, in line with other object modules such as class modules, the Form module now contains an Initialize event, which is fired as the Form is loaded into memory. The Initialize event is immediately followed by the Form_Load event.
There is little operational difference between the Form's Load and Initialize events, and code to initialize the form - and the application if the form is the startup object - can be written in either. However, if you use both events to write initialization code, you may not always get the desired results. The reason for this is that controls contained on the form aren't completely loaded into memory when tile Initialize event is fired. Therefore, any code in the Initialize event handler that references a control on the form forces the rest of the form to load, which then fires the Load event. The following example illustrates this problem:
Private Sub Form_Initialize( )
Text1.Text "Hello "
Private Sub Form_Load( )
Text1.Text = Text1.Text & "World"
Given that the Initialize event fires before the Load event, you'd expect the code above to produce the tired old "Hello World" phrase in the text box. But you may be surprised to discover that when this form is run, only the word "Hello" appears. This is because when the Width property is set to 2000, execution branches to the Form Load event, and the string "World" is placed in the text box. Execution then passes back to the Initialize event and the string "Hello" is assigned to the text property, thereby overwriting the word "World."
Both the Form's Load and Initialize events are executed only once, each time the form is loaded into memory. Hiding the form and then reshowing it doesn't reexecute either event. However, another event, the Activate event, is executed in this situation. You shouldn't use the Activate event to write application initialization code because it executes every time the form regains the focus.
Using a Code Module at Startup
The preferred method of starting any Visual Basic application is to use a Sub Main procedure. In all but standard EXE projects, the Sub Main startup object is now mandatory.
The Sub Main procedure
To create a Sub Main, you need to include a code module in your project. Then simply type the following:
Sub Main( )
Visual Basic automatically adds an End Sub line for you. You can have only one Sub Main procedure in your project. A scope keyword - such as Private or Public - isn't required for the Sub Main procedure. While it's possible to call Sub Main from another procedure, it's definitely not recommended.
The Sub Main procedure doesn't necessarily have to contain any code. in fact, in projects such as ActiveX DLLs, EXEs, or OCXs, it's best not to write code in the Sub Main. If you are using a Sub Main to start up a project and require a form to be loaded on startup, you can use a Sub Main procedure similar to the following:
Sub Main ( )
Set oForm = Nothing End Sub
Here, an object variable is declared. A reference to a new instance of a Form object called f rmStartUp is then assigned to that object variable. The object variable can now be used to call the form's Show method. The form is shown modally, which means that the rest of the code in this procedure can't be executed until the form has completed its processing and is either hidden or unloaded. Finally, the object variable is set to Nothing, thereby unloading the form from memory. Using a Sub Main procedure in this way is now the recommended alternative to specifying a Startup form, since it allows you greater flexibility when initializing the application.
The Structure of a VB Program
Any VB program - whether a hosted VBA application or a VB executable - is a collection of modules containing code, graphical user interface objects, and classes. This book concentrates on the language elements of VBA as they relate to both hosted VBA and the retail version of VB. The VBA and VB user interfaces - whether Word, Excel, Project, or a VB form - all fire events that are handled by the code you create using the VBA language. Therefore the code modules within your program are of greatest concern to us here.
Visual Basic code can be split into three categories:
All Visual Basic language elements work equally well in all three types of procedure. For example, there are no restrictions placed on the type of code you can write within a particular type of procedure. It's left to you as the developer to decide what code goes where.
Events: The Starting Point
An event is always the starting point for your procedure. It can be a system-generated event, such as the Form Load event or a Timer control event, or it can be a user-generated event, such as the Click event on a command button.
To code an event handler for a control event, open the form's code window and select the control from the drop-down list of the available objects. Next, select the required event from the drop-down list of available events for that control. The Event handler definition is then automatically placed in the code window, and you can start coding the event handier.
If you are writing a small and simple application, YOU could program the whole thing within event handlers. However, the more complex your program becomes, the more you find yourself repeating code within these event handlers, and at this point you should start moving related blocks of code into their own separate functions.
Use Event Handlers to Call Functions and Methods
I would recommend that you keep the code in your event handlers to an absolute minimum, using them simply to call methods within a class or to call functions within the project. You will find that your code becomes easier to follow, code reuse is maximized, and maintenance time for the project is reduced.
The following Click event from a command button called cmdSave demonstrates this minimalist approach to event handling:
Private Sub cmdSave_Click( )
On Error GoTo cmdSave_Click_Err
If SaveDetails( ) Then
MsgBox Err.Description & vbCrLf & Err.Number
Because all the code to actually save the details is held within the SaveDetails function, this function can be called from anywhere in the form or project.
The move towards removing functional code from the user interface has been spawned by the n-tier client-server model, in which the user interface is purely a graphical device for displaying information and collecting user input. The middle tier or tiers enforce business rules and provide the main functionality of the application. Here's another example of the same Click event, this time using a SaveDetails method stored in a class module:
Private Sub cmdSave_Click( )
On Error GoTo cmdSave_Click_Err
Dim oObj As Business.BusinessObj
Set oObj = New Business.BusinessObj
If oObi.SaveDetails( ) Then
Set oObj = Nothing
MsgBox Err.Description & vbCrLf & Err.Number & vbCrLf
The following snippet, which provides the same functionality, demonstrates the power of reducing UI code to a minimum:
Function doSave( )
Set oObj = CreateObject("Business.Businessobj")
Set oObj = Nothing
So what's so special about this function? Well, this function is calling exactly the same method as the previous Click event, only this code has been taken from an Active Server Page used in a corporate intranet. Because the vast majority of code has been moved away from the front end of the application, the task of porting the application to an HTML/ASP user interface is made extremely easy. In this simple example, the SaveDetails method could care less who or what has called it; it doesn't matter whether it was a Win32 application or an ASP web server application - or both!
Writing Custom Procedures
Custom procedures can be written in any type of VB module. As a general rule, form modules should only contain procedures that need to refer to properties of the controls contained within the Form. Therefore, a procedure that doesn't refer to any form control properties should be placed in a code module.
To create a new procedure, you can use either the Add Procedure dialog, which is accessed from the Add Procedure option of the Tools menu, or you can move to the bottom of the code window and start typing the Function or Sub definition.
There are three types of custom procedures in Visual Basic:
A function is a collection of related statements and expressions that perform a particular task. When it completes execution, the function returns a value to tile calling statement. If you don't specify an explicit return value for the function, the default value of the return data type is returned. if you write a custom function in a class module and declare it as Public, it becomes a method of the class.
Here's a quick example of a function that's used to provide a minimum number:
Private Function MinNumber(ByVal iNumber As Integer) _
Because functions return a value, you can use them as part of an expression in place of a value. In the following snippet, the string passed to the VB Instr function is a custom function that returns a customer name given a customers code:
If InStr(l, GetCustomerName(sCustCode), "P") > 0 Then
For full details on the syntax and use of functions, see the Private, Public, and Friend statements in Chapter 7, The Language Reference. For details of how to pass values into a function, see Chapter 3, VBA Variables and Data Types.
A sub procedure is used exactly the same way as a function, the only difference being that it doesn't return a value and therefore can't be used as part of an argument. Sub procedures are used by Visual Basic to provide event handling.
In general you should use functions rather than subs to create custom procedures. Functions allow you to return a value, which at a minimum could be a Boolean True or False to inform the calling statement that the function has succeeded or failed. I have done some testing to determine whether there is a performance hit for using a function instead of a sub, and there is no appreciable difference between the two, even though the function has to return a value to the calling statement, and a sub procedure doesn't.
Like a function, if you write a custom sub in a class module and declare it as Public, it becomes a method of the class.
For full details of the syntax and use of Sub procedures, see the Private, Public, and Friend statements in Chapter 7. For details of how to pass values into a sub procedure, see Chapter 3.
Properly procedures are specialized procedures that assign and retrieve values of custom properties. They can be included only within object modules such as form or class modules. There are three types of property procedures:
Assigns a value to a property
Retrieves the value of a property
Assigns an object reference to a property
For a more in-depth look at using properties and property procedures, see Chapter 4, Class Modules.
Controlling Execution Flow
So you've got your event handlers that spring into life when the user clicks a button, or a form loads, or a Timer control fires its Timer event. You've written some neat functions to do all the work behind the scenes. How do you link the two?
Calling sub and function procedures
Sub procedures can be called in one of two ways. First, you can use the Call statement, like this:
Call DoSomething(sSomeString, isomeInteger)
If you use the Call statement, you must enclose the argument list in parentheses. The other method of calling a sub is by simply using its name, but if you don't use the Call statement, don't put parentheses around the argument list:
DoSomething sSomeString, iSomeInteger
If you aren't going to use the return value of a function, you can use either of the above methods to call the function. Otherwise, use the function name as part of an expression. For example:
If GetItNow(sSomeStuff) = 10 Then
Like the Call statement, when you use a function call as part of an expression, the argument list must be enclosed within parentheses.
For more information, see the entry for the Call statement in Chapter 7.
Explicitly calling event procedures
It's also possible to call an event handler front within your code. For example, to replicate the user clicking on a button called cmdOne, you can use the code:
Call cmdOne_Click( )
Because event handlers are private to the form in which they are defined, you can only explicitly call an event handler from code within the same form.
Ending Your VB Program
At some stage, most users want to exit from a program. OK, yours might be a really great program, but unfortunately the user may want to go off and do something else - like go home! You have to allow your application to both exit and tidy up before it ends. One advantage you have when building an application in a VBA-hosted environment is that you don't have to worry too much about finishing the program; the majority of the work is taken care of by the host application. You just have to ensure that any object references are cleaned up, and all database connections closed. You can place this type of code in the Close event. VB developers writing executables have to take care of unloading the application themselves, but in most cases this is no more onerous a task than in VBA; this section shows you how.
How to End Your Program
If you specified a form as the startup object, then you must unload this form to close the application. You can do this by including the following statement somewhere in the form, usually in the event handler of an Exit menu option or Exit command button:
If you specified a Sub Main procedure as the startup object, the program terminates when the Sub Main procedure is completed. For example, here's the Sub Main you saw earlier in this chapter:
Sub Main ( )
Because the form is shown modally, the Sub Main procedure doesn't continue until the form is either hidden - using the statement Me.Hide - or unloaded. Once this happens, program execution is handed back to the Sub Main procedure, which destroys the form object it created by setting the reference to Nothing. When the End Sub statement is executed, the whole application terminates.
If you are writing an ActiveX DLL or EXE, things are slightly different: you shouldn't place any code in your application to terminate the application. The termination should be handled by the operating system. Basically, when all references to your ActiveX component are set to Nothing, your component is unloaded from memory. You should, however, write code to destroy dependent objects in the Terminate event handler of any of your classes that have created dependent objects. You can find further information about this in Chapter 4.
How a Form Unloads
When a form is unloaded from memory, the following chain of events is triggered:
Allows you to cancel the unloading of a form. For example, you could use this event to check whether data in the form has been saved and, if it hasn't, prevent the form from unloading. The QueryUnload event passes a ByRef argument named Cancel to the event handler; if you set this to True, the unloading of the form is cancelled. This event is ideal for catching those users who insist on closing an application using the Close Window button - the one at the right of the titlebar - instead of using the nice Exit button or the menu option that you provided.
This event is fired as the form loses focus to another form. You have to be careful not to place termination code here, since this event being fired doesn't necessarily mean that the form is being unloaded from memory. If you have an application in which multiple forms are displayed at the same time and any can be selected, the Deactivate event is fired as you move away from one form to another.
This is the point of no return. From here on, your application is on its way out. Until Version 4 of VB, this was the end of the road. However, in the same way that the Form Load event is now preceded by an Activate event, so the Form Unload event is followed by a Terminate event.
This event brings form modules into line with Class modules, and should be used in place of the Unload event. As with the Form Load and Initialize events, you should only use either the form's Unload event or the Terminate event, not both. Use either the Unload or Terminate events to destroy any dependent objects you created during the lifetime of the form.
The End Statement
Visual Basic still supports an End statement, but this is purely for back-ward compatibility. In general, its use should be discouraged. In particular, its use in class modules and object-based VB applications is highly undesirable, since it has no concept of object cleanup. If You follow the previous procedure, you'll never need the End statement.
Posted August 31, 2001
Posted October 27, 2000
Posted January 24, 2000
This is my first review of a book because it has so inspired me! I have used this book for everything from programming Access to VB6 to VBScript. It is an excellent resource with plenty of examples. In fact, it covers areas that other (larger) books omit or just plain miss.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.