Corel WordPerfect Office - X5 User Guide for VBA
?
s
;
W
0
g
6
t
=
9
}
3
*
&
m
7
A
z
#
%
!
@
92
2
“
5
+
User Guide for VBA
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
About VBA in WordPerfect Office X5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
About this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
About additional resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
About Corel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Understanding VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
What is VBA?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
What is automation? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Who is VBA designed for? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
How does VBA compare with other programming languages? . . . . . . . . . . . . . . 7
What are the main elements used in VBA? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
What is an object model? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
How is VBA code structured? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Getting started with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
Using the Visual Basic toolbar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Using the VB Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Using the Project Explorer in the VB Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Using the Properties window in the VB Editor . . . . . . . . . . . . . . . . . . . . . . . . . 22
Using the Code window in the VB Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Using the toolbars in the VB Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Using the Object Browser in the VB Editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Working with macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Creating macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Running macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Debugging macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Using the debugging windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Creating user-interfaces for macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41
Creating dialog boxes for macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Coding dialog boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Designing dialog boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Contents i
Providing help for macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Organizing and deploying macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Organizing macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Deploying macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Appendix: About the object models . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
About the WordPerfect object model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
About the Quattro Pro object model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
About the Presentations object model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
ii Contents
Introduction
This guide is intended as a resource for developing and distributing
Microsoft® Visual Basic® for Applications (VBA) solutions in
Corel® WordPerfect® Office X5.
This introductory section provides information about the following:
• VBA in WordPerfect Office X5
•this guide
• additional resources
•Corel
About VBA in WordPerfect Office X5
Although Corel® WordPerfect® X5, Corel® Quattro Pro® X5, and
Corel® Presentations™ X5 support VBA version 6.3, it is important to note
that VBA is not included with WordPerfect Office X5. You can use VBA in
WordPerfect Office X5 only if you have installed a software product that includes
VBA 6.3.
VBA comes with a fully integrated development environment (IDE) that provides
contextual pop-up lists, syntax highlighting, line-by-line debugging, and visual
designer windows. These helpful prompts and aids create a particularly friendly
learning environment for inexperienced developers.
About this guide
This guide was designed as a resource for the following:
• exploring the VBA IDE and many of its advanced features
• understanding the most important WordPerfect Office X5 functions and how to
use them
• examining how to package and deploy VBA solutions developed for
WordPerfect Office X5
This guide should be used by anyone who is interested in automating simple and
complex tasks in WordPerfect Office X5 or who is developing commercial solutions
Introduction 1
that integrate with WordPerfect Office X5. It is assumed that the reader already has
experience with at least one other procedural programming language, such as BASIC,
Microsoft Visual Basic, C, C++, Java™, Pascal, Cobol, or Fortran. This guide does not
describe the basics of procedural programming, such as functions, conditional
branching, and looping. Non-programmers should learn the basics of programming in
a language such as Visual Basic or VBA before using this document to develop
WordPerfect Office X5 solutions.
Contents
This guide is organized into the following chapters, which deal with specific aspects of
automating tasks and building solutions in WordPerfect Office X5:
• “Understanding VBA” on page 5 — provides a brief introduction to VBA.
• “Getting started with VBA” on page 19 — lets you explore the VBA workspace in
WordPerfect, Quattro Pro, and Presentations.
• “Working with macros” on page 33 — shows you how to create, run, and debug
macros.
• “Creating user-interfaces for macros” on page 41 — demonstrates how to provide
dialog boxes, toolbars and buttons, user interaction, and help for your macros.
• “Organizing and deploying macros” on page 53 — helps you organize and deploy
the macros you create.
This guide also provides an appendix (page 55), which provides information on the
object models for WordPerfect Office X5.
Finally, a glossary (page 57) defines the terms that are used in this guide.
Conventions
The following conventions are used in this guide:
When you see This is
A note — presents information such as conditions for performing a procedure
A tip — presents information such as procedure shortcuts, variations, or
benefits
In addition, this guide uses a few formatting conventions:
• User-interface items are displayed in boldface.
• Glossary items are displayed in italics.
2Introduction
• Information that can be substituted, such as a path or filename, is displayed in
<italics and between angle brackets>.
• Coding is formatted in a
monospace font.
About additional resources
The main Help files for WordPerfect, Quattro Pro, and Presentations provide basic
information on using VBA. To access these Help files, additional resources that install
with WordPerfect Office X5, and even more resources on the Web, please see the
Reference Center that installs with WordPerfect Office X5.
To access the Reference Center
1 Do one of the following:
• On the Windows® taskbar, click Start ` Programs ` WordPerfect Office X5 `
Reference Center.
• In WordPerfect, Quattro Pro, or Presentations, click Help ` Reference Center.
2 Click any tab to display the available documentation for that category.
3 Click any category entry to display its associated documentation.
About Corel
Corel is one of the world’s top software companies, with more than 100 million active
users in over 75 countries. We develop software that helps people express their ideas and
share their stories in more exciting, creative, and persuasive ways. Through the years,
we’ve built a reputation for delivering innovative, trusted products that are easy to learn
and use, helping people achieve new levels of productivity. The industry has responded
with hundreds of awards for software innovation, design, and value.
Our award-winning product portfolio includes some of the world’s most widely
recognized and popular software brands, including CorelDRAW® Graphics Suite,
Corel® Painter™, Corel DESIGNER® Technical Suite,
Corel® PaintShop Photo™ Pro, Corel® VideoStudio®, Corel® WinDVD®,
Corel® WordPerfect® Office, WinZip®, and the recently released
Corel® Digital Studio™ 2010. Our global headquarters are in Ottawa, Canada, with
major offices in the United States, United Kingdom, Germany, China, Taiwan, and
Japan.
Introduction 3
4Introduction
Understanding VBA
Before getting started with VBA in WordPerfect Office X5, it’s important to
understand a little bit about VBA in general.
This chapter answers the following questions:
•What is VBA?
• What is automation?
• Who is VBA designed for?
• How does VBA compare with other programming languages?
• What are the main elements used in VBA?
• What is an object model?
• How is VBA code structured?
What is VBA?
Visual Basic for Applications (more commonly known as VBA) is a built-in programming
language that can automate repetitive functions and create intelligent solutions in
WordPerfect, Quattro Pro, and Presentations.
VBA is both a language and an editor. It is not possible to have the language without
the editor, nor is it possible to edit VBA in anything but the VB Editor or to run VBA
programs without the VB Editor.
VBA is developed by Microsoft and is built into almost all of its desktop applications,
including Microsoft® Office. VBA is licensed by Microsoft to other companies,
including Corel (in CorelDRAW Graphics Suite, Corel DESIGNER Technical Suite,
and Corel WordPerfect Office), Autodesk, Inc. (in AutoCAD®), and IntelliCAD
Technology Consortium (in IntelliCAD®). This makes Corel applications compatible
with a wide array of applications that support VBA.
For a complete list of applications that support VBA, consult the Microsoft
Web site.
It is not necessary for an application to support VBA in order for the
WordPerfect Office X5 VBA engine to control that application. That means you can
Understanding VBA 5
build solutions in WordPerfect Office X5 that access databases specialized content
editors, XML documents, and more.
What is automation?
Most actions that you can do in WordPerfect Office X5 can be done programmatically
through VBA. This programmability of is called automation. Automating repetitive
tasks can save time and reduce effort, while automating complex tasks can make
possible the otherwise impossible.
In its simplest form, automation is simply recording a sequence of actions so that you
can play them back time and again. The term macro has come to include any code that
is accessible to VBA while running within the process, even though some of that code
might be far more advanced than a mere set of recorded actions. For the purposes of this
guide, a macro refers to VBA functions and subroutines (which are explained in “Building
functions and subroutines” on page 12).
While it is possible to record a sequence of actions in WordPerfect Office X5, the real
power of automation and VBA is that these recordings can be edited to provide
conditional and looping execution.
Who is VBA designed for?
VBA can be used by both non-programmers and programmers alike.
VBA for non-programmers
VBA is based on the successful Microsoft Visual Basic (VB) programming language.
The main difference between VBA and VB is that you cannot create stand-alone
executable (EXE) files using VBA, whereas you can with VB. That is to say, using VBA,
you can create only programs that run inside the host application (in this case,
WordPerfect, Quattro Pro, or Presentations).
VB is a “visual” version of the BASIC programming language. This means that it is a
very easy language to learn, particularly because it provides visual cues within the
editor. Microsoft has added a great deal to the original BASIC language, and it is now
a powerful and fast language (although not as powerful as Java or C++, nor as quick
as C).
The aim of this guide is not to teach you how to become a programmer but instead to
teach experienced programmers how to apply their skills to developing useful solutions
6Understanding VBA
within WordPerfect Office X5. If you are not a programmer, you may find it useful to
refer to the many books that have been written about VBA and VB before continuing
to read this guide.
VBA for programmers
VBA is an in-process automation controller. In other words, VBA can be used to control
the features of WordPerfect Office X5 that can be automated, and VBA runs efficiently
by bypassing the interprocess synchronization mechanisms. However, the automation
that the in-process VBA can access can also be accessed by the following:
• external out-of-process automation controllers (OLE clients)
• applications that are developed in programming languages (such as VB,
Visual C++®, Windows® Script Host, and C++) that can be used to develop
OLE clients
• the VBA engines of other applications
How does VBA compare with other programming languages?
VBA has many similarities with most modern, procedural programming languages,
including Java and JavaScript®, C and C++, and Windows Script Host. However,
VBA runs as an in-process automation controller, whereas the other languages (apart
from JavaScript) are used to compile stand-alone applications.
VBA compared with Java and JavaScript
VBA is similar to Java and JavaScript in that it is a high-level, procedural programming
language that has full garbage collection and very little memory-pointer support. (See
“Using memory pointers and memory allocation” on page 14 for more information.) In
addition, code developed in VBA — much like code developed in Java and JavaScript
— supports on-demand compilation and can be executed without being compiled.
VBA has another simil arity with JavaScrip t in that it cannot be executed as a standal o ne
application. JavaScript is embedded within Web pages, as a mechanism for
manipulating the Web browser’s document object model (or “DOM”). Likewise, VBA
programs are executed inside a host environment — in this case, WordPerfect,
Quattro Pro, or Presentations — so as to manipulate the host’s object model (which is
discussed in “What is an object model?” on page 9).
Most VBA applicat i ons can be com piled to P-code s o a s to make them r un more quickl y,
although the difference is hardly noticeable given the sophistication of today’s computer
hardware. Java can be similarly compiled; JavaScript, however, cannot.
Understanding VBA 7
Finally, whereas VBA uses a single equals sign (=) for both comparison and assignment,
Java and JavaScript use a single equals sign (
=) for assignment and two equals signs (==)
for Boolean comparison. (For more information on Boolean comparison and assignment
in VBA, see “Using Boolean comparison and assignment” on page 15.)
VBA compared with C and C++
Visual Basic — similarly to C and C++ — uses functions. In VB, functions can be used
to return a value but subroutines cannot. In C and C++, however, functions are used
regardless of whether you want to return a value. (For more information on functions
and subroutines, see “Building functions and subroutines” on page 12.)
VBA allocates and frees memory “transparently.” In C and C++, however, the
developer is responsible for most memory management. This makes using strings in
VBA even simpler than using the
CString class in C++.
Finally, whereas VBA uses a single equals sign (
C and C++ use a single equals sign (
=) for assignment and two equals signs (==) for
=) for both comparison and assignment,
Boolean comparison. (For more information on Boolean comparison and assignment in
VBA, see “Using Boolean comparison and assignment” on page 15.)
VBA compared with Windows Script Host
Windows Script Host (WSH) is a useful addition to Windows for doing occasional
scripting and automation of Windows tasks. WSH is an out-of-process automation
controller that can be used to control WordPerfect Office X5. However, because WSH
scripts cannot be compiled (and must be interpreted as they are executed) and must be
run out of process, they tend to be slow.
WSH is a host for a number of scripting languages, each of which has its own syntax.
However, the standard language used by WSH is a macro language resembling Visual
Basic, so for standard scripts, the syntax is the same as in VBA.
What are the main elements used in VBA?
If you’ve ever developed object-oriented code in C++, Borland® Delphi®, or Java,
you’re already familiar with the concepts of “classes,” “objects,” “properties,” and
“methods,” but let’s re-examine them in greater detail as they apply to VBA.
A class is a description of something. For example, the class “car” is a small vehicle with
an engine and four wheels.
8Understanding VBA
An object is an instance of a class. If we extend the car metaphor, then the actual, physical
car that you go out and purchase for the purposes of driving is an object (that is, an
instance of the class “car”).
Most classes have properties. For example, the properties of the class “car” are that it is
small, it has an engine, and it has four wheels. Every instance of the class “car” (that is,
every object in that class) also has properties such as color, speed, and number of seats.
Some properties, called “read-only” properties, are fixed by the design of the class; for
example, the number of wheels or seats does not (usually) vary from car to car. However,
other properties can be changed after the object has been created; for example, the
speed of the car can go up and down, and, with a bit of help, its color can be changed.
A method is an operation that the object can have performed on itself. In the example of
the class “car,” the car can be made to go faster and slower, so two methods for the class
are “accelerate” and “decelerate.”
Objects are often made up of other smaller objects. For example, a car contains four
objects of the class “wheel,” two objects of the class “headlight,” and so on. Each of these
child objects has the same properties and methods of its class-type. This parent/child
relationship of objects is an important one to recognize, particularly when referencing
an individual object.
Some classes “inherit” features from their parents.
What is an object model?
VBA relies on an application’s object model for communicating with that application and
modifying its documents. Without an object model, VBA cannot query or change an
application’s documents.
Object models in software provide a high level of structure to the relationship between
parent and child objects.
Remember, though, that the object model is the map that the VBA language uses to
access the various members — objects, methods, and properties — of a document, and
to make changes to those members. Without the object model, it is simply impossible
to gain access to the objects in the document.
Understanding VBA 9
Understanding object hierarchy
In any object model, each object is a child of another object, which is a child of another
object. Also, each object has child members of its own — properties, objects, and
methods. All of this comprises an object hierarchy that is the object model.
In order to “drill down” through the layers of hierarchy to get to the object or member
that you want, you must use a standard notation. In VBA, as in many object-oriented
languages, the notation is to use a period (
. ) to indicate that the object on the right is
a member (or child) of the object on the left.
It is not usually necessary to use the full hierarchical (or fully qualified) reference to an
object or its properties. Some of the object-syntax in the fully qualified reference is
mandatory or required; however, other syntax is optional (because a shortcut object for
it is available, or because it is implicit or implied), and so it can either be included for
clarity or omitted for brevity.
A shortcut object is merely a syntactical replacement for the long-hand version of the
object.
For detailed information on the WordPerfect Office X5 object models, see the
Appendix: About the object models.
How is VBA code structured?
Because VBA is a procedural language that shares much in common with all procedural
languages, your current knowledge should help you get off to a quick start with VBA.
This section examines the following topics on VBA structure and syntax:
• Declaring variables
• Building functions and subroutines
• Ending lines
• Including comments
• Using memory pointers and memory allocation
• Defining scope
• Using Boolean comparison and assignment
• Using logical and bitwise operators
• Providing message boxes and input boxes
10 Understanding VBA
The VB Editor formats all of the code for you (as discussed in “Formatting code
automatically” on page 23). The only custom formatting that you can do is to
change the size of the indentations.
VBA can create object-oriented classes, although these are a feature of the
language and are not discussed in detail in this guide.
Declaring variables
In VBA, the construction for declaring variables is as follows:
Dim foobar As Integer
The built-in data types are Byte, Boolean, Integer, Long, Single, Double, String,
Variant, and several other less-used types including Date, Decimal, and Object.
Variables can be declared anywhere within the body of a function, or at the top of the
current module. However, it is generally a good practice to declare a variable before it
is used; otherwise, the compiler interprets it as a
incurred at run time.
Variant, and inefficiencies can be
Booleans take
converting from a
value of
False to be zero and True to be any other value, although
Boolean to a Long results in True being converted to a
–1.
To get more information about one of the built-in data types, type it into the
code window, select it, and then press F1.
Data structures can be built by using the following syntax:
Public Type fooType
item1 As Integer
item2 As String
End Type
Dim myTypedItem As fooType
The items within a variable declared as type fooType are accessed using dot notation:
myTypedItem.item1 = 5
Understanding VBA 11
Declaring strings
Strings in VBA are much simpler than in C. In VBA, strings can be added together,
truncated, searched forwards and backwards, and passed as simple arguments to
functions.
To add two strings together, simply use the concatenation operator (
operator (
Dim string1 As String, string2 As String
string2 = string1 & " more text" + " even more text"
+):
&) or the addition
In VBA, there are many functions for manipulating strings, including InStr(),
Left(), Mid(), Right(), Len(), and Trim().
Declaring arrays
To declare an array, use parentheses — that is, the
Dim barArray (4) As Integer
( and ) symbols:
The value defines the index of the last item in the array. Because array indexes are zerobased by default, there are five elements in the preceding sample array (that is, elements
0 thru 4, inclusive).
Arrays can be resized by using
element to
ReDim Preserve barArray (6)
barArray, but preserves the existing contents of the original five elements:
ReDim. For example, the following code adds an extra
Upper and lower bounds for an array can be determined at run time with the functions
UBound() and LBound().
Multi-dimensional arrays can be declared by separating the dimension indexes with
commas:
Dim barArray (4, 3)
Building functions and subroutines
VBA uses both functions and subroutines (or “subs”). Functions can be used to return a
value, whereas subs cannot.
In VBA, functions and subs do not need to be declared before they are used, nor before
they are defined. In fact, functions and subs need to be declared only if they actually
exist in external system dynamic-linked libraries (DLLs).
Typical functions in a language such as Java or C++ can be structured as follows:
12 Understanding VBA
void foo( string stringItem ) {
// The body of the function goes here
}
double bar( int numItem ) { return 23.2; }
In VBA, however, functions are structured as in the following example:
Public Sub foo (stringItem As String)
' The body of the subroutine goes here
End Sub
Public Function bar (numItem As Integer) As Double bar = 23.2
End Function
To force a function or sub to exit immediately, you can use Exit Function or Exit
(respectively).
Sub
Declaring enumerated types
To declare an enumerated type, use the following construction:
Public Enum fooEnum
ItemOne
ItemTwo
ItemThree
End Enum
The first item in an enumerated type is assigned, by default, a value of zero.
Ending lines
In VBA, each statement must exist on its own line, but no special character is required
to denote the end of each line. (This is in contrast to the many programming languages
that use the semicolon to separate individual statements.)
To break a long VBA statement over two or more lines, each of the lines (other than the
last line) must end with an underscore (
newString = fooFunction ("This is a string", _
5, 10, 2)
Understanding VBA 13
_ ) preceded by at least one space:
It is also possible to combine several statements in a single line by separating them with
colons:
a = 1 : b = 2 : c = a + b
A line cannot end with a colon. Lines that end with a colon are labels used by
Goto statement.
the
Including comments
Comments in VBA — similarly to in C++ and Java — can be created only at the end
of a line. Comments begin with an apostrophe (
Each line of a multi-line comment must begin with its own apostrophe:
a = b ' This is a really interesting piece of code that
' needs so much explanation that I have had to break
' the comment over multiple lines.
To comment out large sections of code, use the following code (similarly to in C or
C++):
#If 0 Then ' That's a zero, not the letter 'oh'.
' All this code will be ignored by
' the compiler at run time!
#End If
' ) and terminate at the end of the line.
Using memory pointers and memory allocation
VBA does not support C-style memory pointers. Memory allocation and garbage
collection are automatic and transparent, just as in Java and JavaScript (and some C++
code).
Passing values “by reference” and “by value”
Most languages, including C++ and Java, pass an argument to a procedure as a copy
of the original. If the original must be passed, then one of two things can happen:
• a memory pointer is passed that directs to the original in memory
• a reference to the original is passed
The same is true in VB, except that passing a copy of the original is called passing by value
and passing a reference to the original is called passing by reference.
14 Understanding VBA
By default, function and subroutine parameters are passed by reference. This means that
a reference to the original variable is passed in the procedure’s argument, and so
changing that argument’s value within the procedure, in effect, changes the original
variable’s value as well. This is a great way of returning more than one value from a
function or sub. To explicitly annotate the code to indicate that an argument is being
passed by reference, you can prefix the argument with
ByRef.
If you want to prevent the procedure from changing the value of the original variable,
you can force the copying of an argument. To do this, prefix the argument with
as shown in the example that follows. This
ByRef/ByVal functionality is similar to the
ByVal,
ability of C and C++ to pass a copy of a variable, or to pass a pointer to the original
variable.
Private Sub fooFunc (ByVal int1 As Integer, _
ByRef long1 As Long, _
long2 As Long) ' Passed ByRef by default
In the preceding example, arguments long1 and long2 are both, by default, passed by
reference. Modifying either argument within the body of the function modifies the
original variable; however, modifying
int1 does not modify the original because it is a
copy of the original.
Defining scope
Yo u c a n d ef in e t h e scope of a data type or procedure (or even an object). Data types,
functions, and subs (and members of classes) that are declared as private are visible only
within that module (or file), while functions that are declared as public are visible
throughout all the modules; however, you may have to use fully qualified referencing if
the modules are almost out of scope — for example, if you are referencing a function in
a different project.
Unlike C, VBA does not use braces — that is, the
{ and } symbols — to define local
scope. Local scope in VBA is defined by an opening function or sub definition statement
(that is,
End Sub). Any variables declared within the function are available only within the scope
Function or Sub) and a matching End statement (that is, End Function or
of the function itself.
Using Boolean comparison and assignment
In VB, Boolean comparison and assignment are both performed by using a single equals
=):
sign (
Understanding VBA 15
If a = b Then c = d
This is in contrast to many other languages that use a double equals sign for a Boolean
comparison and a single equals sign for assignment:
if( a == b ) c = d;
The following code, which is valid in C, C++, Java, and JavaScript, is invalid in VBA:
if( ( result = fooBar( ) ) == true )
This would have to be written in VBA as the following:
result = fooBar( )
If result = True Then
For other Boolean comparisons, VBA uses the same operators as other languages
(except for the operators for “is equal to” and “is not equal to”). All the Booleancomparison operators are provided in the following table:
Comparison VBA operator C-style operator
Is equal to
Is not equal to
Is greater than
Is less than
Is greater than or equal to
Is less than or equal to
The result of using one of the Boolean operators is always either
===
<> !=
>>
<<
>= >=
<= <=
True or False.
Using logical and bitwise operators
In VBA, logical operations are performed by using the keywords And, Not, Or, Xor,
Imp, and Eqv, which perform the logical operations AND, NOT, OR, Exclusive-OR,
logical implication, and logical equivalence (respectively). These operators also perform
Boolean comparisons.
The following code shows a comparison written in C or a similar language:
if( ( a && b ) || ( c && d ) )
This would be written as follows in VBA:
16 Understanding VBA
If ( a And b ) Or ( c And d ) Then
Alternatively, the above could be written in the following full long-hand form:
If ( a And b = True ) Or ( c And d = True ) = True Then
The following table provides a comparison of the four common VBA logical and bitwise
operators, and the C-style logical and bitwise operators used by C, C++, Java, and
JavaScript:
VBA operator C-style bitwise operator C-style Boolean operator
And & &&
Not ~ !
Or | ||
Xor ^
Providing message boxes and input boxes
You can present simple messages to the user by using the MsgBox function:
Dim retval As Long
retval = MsgBox("Click OK if you agree.", _
vbOKCancel, "Easy Message")
If retval = vbOK Then
MsgBox "You clicked OK.", vbOK, "Affirmative"
End If
You can also get strings from the user by using InputBox function:
Dim inText As String
inText = InputBox("Input some text:", "type here")
If Len(inText) > 0 Then
MsgBox "You typed the following: " & inText & "."
End If
If the user clicks Cancel, the length of the string returned in inText is zero.
Understanding VBA 17
Loading...