Paperback
-
SHIP THIS ITEMChoose Expedited Shipping at checkout for delivery by Thursday, April 4PICK UP IN STORECheck Availability at Nearby Stores
Available within 2 business hours
Related collections and offers
Overview
- Its syntax, using standard code conventions
- Differences in the operation of the keyword in a macro environment (e.g., in Office) and in Visual Basic, if there are any
- A list of arguments accepted by the function or procedure, if any
- A description of the data type returned by a function
- The finer points of a keyword's usage that are often omitted from or blurred over by the documentation
- Tips and gotchas that include undocumented behaviors and practical applications for particular language elements a section particularly invaluable for diagnosing or avoiding potential programming problems
- A brief, nonobvious example that illustrates the use of the keyword
- Basic VBA programming concepts, such as its data types and its support for variables, constants, and arrays
- Error handling in VBA applications
- Object programming with VBA
- Using VBA with particular applications. Excel and Project are utilized to show how to work with an application's integrated development environment and to take advantage of its object model Regardless of how much experience you have programming with VBA, this is the book you'll pick up time and time again both as your standard reference guide and as a tool for troubleshooting and identifying programming problems.
Product Details
ISBN-13: | 9781565923584 |
---|---|
Publisher: | O'Reilly Media, Incorporated |
Publication date: | 10/28/1998 |
Series: | In a Nutshell (O'Reilly) |
Pages: | 650 |
Product dimensions: | 6.00(w) x 9.00(h) x 1.50(d) |
About the Author
Read an Excerpt
Chapter 7: The Language Reference
VBA is a high-level language and, like all high-level languages, it is, by its very nature, a large yet rich language. While this means that it takes time for new users to understand the intricacies of the many and varied functions and statements available to them, at the same time the language's syntax is straightforward, logical, and therefore easy to understand.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:
- The methods, properties, and events associated with VB forms and controls
- The methods, properties, and events associated with VBA UserForms and the VBA controls available from the Microsoft Forms library
- The objects, as well as their associated properties, methods, and events, that are provided by each application that hosts VBA
- The VBA DLL that provides VB with its language is slightly different than that used in the hosted version of VBA, since they were released at different times.
- VB includes several unique functions to support its ability to create standalone applications as either FXFs, DLLs, or OCXs.
Statements
Statements form the cornerstone of the language. You'll notice from Appendix A that the largest concentration of statements is in the program structure section. Statements are mainly used for such tasks as declaring variables or declaring procedures.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.
Functions
Functions are relatively new additions to the language, most dating back to the Version 4 rewrite of VB and VBA. In the main, functions return a value, although, as with any function, you can choose to ignore the return value.In addition to returning a value, there are other important benefits of functions over statements:
Dim iReturnVal As Integer
iReturnVal = MsgBox(Prompt:="Click OK",
Title:="Test")
Operators
An operator connects or performs some operation upon one or more language elements to form a single expression. For example, in the code fragment: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.
Object models
Object models are increasingly used to provide additional functionality in the VBA language. This is an ideal way of extending the language, since VBA itself is adept at handling object models.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 Directive
Named Arguments
No
Syntax
#Const constantname - expression
constantname
Name of the constant.
expression
Data Type: Literal
Any combination of literal values, other conditional compilation constants defined with the #Const directive, and arithmetic or logical operators except Is.
Description
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.
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.
Rules at a Glance
- 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.
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. . . .