Excel VBA Test

Contents
Chapter 1 INTRODUCING VBA ....................................................................................................................................... 6
A BRIEF HISTORY OF VBA ........................................................................................................................................... 7
DON’T CONFUSE VB WITH VBA! ................................................................................................................................ 7
WHAT VBA can do for you? ....................................................................................................................................... 7
Controlling other office Applications from Excel ........................................................................................................ 9
Chapter 2 Getting Started with Macros ........................................................................................................................10
COMPOSING YOUR FIRST MACRO ............................................................................................................................10
ACCESSING THE VBA ENVIRONMENT........................................................................................................................10
Using the Macro recorder ........................................................................................................................................14
Hey, My stop recording button disappeared! ...........................................................................................................16
Running A macro......................................................................................................................................................16
The Macro dialog Box ...............................................................................................................................................17
INTRODUCING THEVISUAL BASIC EDITOR .................................................................................................19
WHAT is THE VBE? ...................................................................................................................................................19
HOW TO GET INTO THE VBE .....................................................................................................................................19
Understanding THE VBE ...........................................................................................................................................20
The project Explorer Window ...................................................................................................................................21
The code Window ....................................................................................................................................................21
The properties Window............................................................................................................................................21
The immediate Window ...........................................................................................................................................22
Using THE object browser ........................................................................................................................................22
Exiting THE VBE ........................................................................................................................................................23
CHAPTER 4 WORKING IN THE VBE ................................................................................................................................24
Toolbars in THE VBE .................................................................................................................................................24
Macros And modules ...............................................................................................................................................24
Locating your Macros ...............................................................................................................................................24
Editing a Macro with comments and improvements to the code ..............................................................................26
Deleting a Macro......................................................................................................................................................28
Inserting a Module ...................................................................................................................................................29
Renaming a Module .................................................................................................................................................30
INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT

P A G E |1
RESERVED
Deleting a Module....................................................................................................................................................31
Locking And protecting THE VBE...............................................................................................................................31
CHAPTER 5 OBJECT-ORIENTED PROGRAMMING ..........................................................................................................33
WHAT “object-oriented programming” means .........................................................................................................33
THE object model .....................................................................................................................................................33
Properties ................................................................................................................................................................34
Methods ..................................................................................................................................................................36
COLLECTIONS ...........................................................................................................................................................36
Chapter 6 Variables, Data Types, and Constants ...........................................................................................................37
WHAT is A variable? .................................................................................................................................................37
Assigning values To variables ...................................................................................................................................38
Why you need variables ...........................................................................................................................................38
Data types ................................................................................................................................................................39
Understanding the different data Types ...................................................................................................................39
Declaring a Variable for dates and Times ..................................................................................................................41
Declaring a Variable with the proper data Type ........................................................................................................41
Forcing variable declaration .....................................................................................................................................42
Understanding A variable’s scope.............................................................................................................................44
Local Macro level only ..............................................................................................................................................44
Module level ............................................................................................................................................................44
Application level ......................................................................................................................................................46
Constants .................................................................................................................................................................46
CHOOSING THE SCOPE AND LIFETIME OF YOUR CONSTANTS ....................................................................................47
CHAPTER 7 UNDERSTANDING OBJECTS AND COLLECTIONS ..........................................................................................49
worksheets ..............................................................................................................................................................50
Cells And ranges .......................................................................................................................................................51
Specialcells...............................................................................................................................................................52
Chapter 8 Making Decisions with VBa ..........................................................................................................................55
UNDERSTANDING LOGICAL OPERATORS...................................................................................................................56
And ..........................................................................................................................................................................56
or .............................................................................................................................................................................57
Not ..........................................................................................................................................................................58
P A G E |2
RESERVED
CHOOSING BETWEEN THIS OR THAT ........................................................................................................................58
if…Then…Else ...........................................................................................................................................................60
IF…THEN…ELSEIF ......................................................................................................................................................61
SELECT CASE.............................................................................................................................................................61
GETTING USERS TO MAKE DECISIONS.......................................................................................................................62
INPUT BOXES ...........................................................................................................................................................63
CHAPTER 9 REPEATING ACTIONS WITH LOOPS ............................................................................................................66
WHAT is A loop?.......................................................................................................................................................66
for…next ..................................................................................................................................................................68
FOR…EACH…NEXT ....................................................................................................................................................69
EXITING A FOR… LOOP .............................................................................................................................................70
LOOPING IN REVERSE WITH STEP .............................................................................................................................70
DO…WHILE ..............................................................................................................................................................71
DO…UNTIL ...............................................................................................................................................................71
DO…LOOP…WHILE ...................................................................................................................................................73
DO…LOOP…UNTIL ....................................................................................................................................................73
While…Wend ...........................................................................................................................................................73
NESTING LOOPS .......................................................................................................................................................74
CHAPTER 10 WORKING WITH ARRAYS ..........................................................................................................................75
WHAT is An ArrAy?...................................................................................................................................................75
What Arrays can do for you ......................................................................................................................................77
DECLARING ARRAYS .................................................................................................................................................78
THE OPTION BASE STATEMENT ................................................................................................................................78
BOUNDARIES IN ARRAYS ..........................................................................................................................................80
DECLARING ARRAYS WITH FIXED ELEMENTS.............................................................................................................80
DECLARING DYNAMIC ARRAYS WITH REDIM AND PRESERVE ....................................................................................81
CHAPTER11 ..................................................................................................................................................................83
AUTOMATING PROCEDURESWITH WORKSHEET EVENTS ..............................................................................................83
WHAT IS AN “EVENT”? .............................................................................................................................................83
WORKSHEET EVENTS — AN OVERVIEW ....................................................................................................................84
ENABLING AND DISABLING EVENTS ..........................................................................................................................86
EXAMPLES OF COMMON WORKSHEET EVENTS ........................................................................................................87

P A G E |3
RESERVED
Worksheet_selectionchange Event ..........................................................................................................................88
Worksheet_Beforedoubleclick Event ........................................................................................................................88
Worksheet_Beforerightclick Event ...........................................................................................................................88
Worksheet_followHyperlink Event ...........................................................................................................................88
WORKSHEET_DEACTIVATE EVENT ............................................................................................................................89
WORKSHEET_CALCULATE EVENT ..............................................................................................................................90
CHAPTER12 ..................................................................................................................................................................91
AUTOMATING PROCEDURES WITHWORKBOOK EVENTS ...............................................................................................91
WORKBOOK EVENTS — AN OVERVIEW.....................................................................................................................91
WHERE DOES THE WORKBOOK EVENT CODE GO? ....................................................................................................91
Entering Workbook Event code ................................................................................................................................92
EXAMPLES OF COMMON WORKBOOK EVENTS .........................................................................................................93
WORKBOOK_OPEN EVENT .......................................................................................................................................93
Workbook_Beforeclose Event ..................................................................................................................................94
Workbook_Activate Event ........................................................................................................................................94
Workbook_deactivate Event ....................................................................................................................................94
Workbook_sheetchange Event .................................................................................................................................95
Workbook_sheetselectionchange Event ...................................................................................................................95
Workbook_sheetBeforedoubleclick Event ................................................................................................................95
WORKBOOK_SHEETBEFORERIGHTCLICK EVENT ........................................................................................................96
WORKBOOK_BEFOREPRINT EVENT ...........................................................................................................................96
WORKBOOK_SHEETACTIVATE EVENT .......................................................................................................................96
WORKBOOK_SHEETDEACTIVATE EVENT ...................................................................................................................97
WORKBOOK_BEFORESAVE EVENT ............................................................................................................................97
CHAPTER 13 USING EMBEDDED CONTROLS .................................................................................................................97
WORKING WITH FORMS CONTROLS AND ACTIVEX CONTROLS .................................................................................98
CHOOSING BETWEEN FORMS CONTROLS AND ACTIVEX CONTROLS .........................................................................98
THE FORMS TOOLBAR ..............................................................................................................................................98
USING APPLICATION.CALLER WITH FORMS CONTROLS ...........................................................................................100
COMMANDBUTTONS .............................................................................................................................................102
characteristics of user defined functions ................................................................................................................110
UDF EXAMPLES THAT SOLVE COMMON TASKS .......................................................................................................112

P A G E |4
RESERVED
EXTRACT NUMBERS OR LETTERS FROM AN ALPHANUMERIC STRING......................................................................113
UDFS AND ERRORS .................................................................................................................................................114
VOLATILE FUNCTIONS ............................................................................................................................................114
THE NAME OF THE ACTIVE WORKSHEET AND WORKBOOK .....................................................................................115
UDFS WITH CONDITIONAL FORMATTING ...............................................................................................................116
CALLING YOUR FUNCTION FROM A MACRO ...........................................................................................................116
ADDING A DESCRIPTION TO THE INSERT FUNCTION DIALOG ..................................................................................117
Chapter 17:Debugging Your Code ..............................................................................................................................119
WHAT CAUSES ERRORS? ........................................................................................................................................121
WEAPONS OF MASS DEBUGGING ...........................................................................................................................122
THE DEBUGGING TOOLBAR ....................................................................................................................................122
USING THE STEP INTO COMMAND .........................................................................................................................124
USING THE STEP OUT COMMAND ..........................................................................................................................127
Chapter 18 Creating userForms ..................................................................................................................................136
WHAT IS A USERFORM? .........................................................................................................................................137
CREATING A USERFORM.........................................................................................................................................137
DESIGNING A USERFORM .......................................................................................................................................138
ADDING CONTROLS TO A USERFORM .....................................................................................................................139
SHOWING A USERFORM.........................................................................................................................................143
WHERE DOES THE USERFORM’S CODE GO? ............................................................................................................144
CLOSING A USERFORM ...........................................................................................................................................144
UNLOADING A USERFORM .....................................................................................................................................144
Hiding A Userform..................................................................................................................................................145
CHAPTER 19 USERFORM CONTROLS AND THEIR FUNCTIONS......................................................................................147
UNDERSTANDING THE FREQUENTLY USED USERFORM CONTROLS .........................................................................147
labels .....................................................................................................................................................................148
TEXTBOXES ............................................................................................................................................................150
LISTBOXES ..............................................................................................................................................................152
COMBOBOXES........................................................................................................................................................153
CHECKBOXES..........................................................................................................................................................155
OPTIONBUTTONS ...................................................................................................................................................157
FRAMES .................................................................................................................................................................159
P A G E |5
RESERVED
CHAPTER20 ADVANCED USERFORMS ....................................................................................................................162
THE USERFORM TOOLBAR ......................................................................................................................................162
MODAL VERSUS MODELESS ...................................................................................................................................163
DISABLING THE USERFORM’S CLOSE BUTTON ........................................................................................................164
MAXIMIZING YOUR USERFORM’S SIZE....................................................................................................................164
SELECTING AND DISPLAYING PHOTOGRAPHS ON A USERFORM ..............................................................................165
PRE-SORTING THE LISTBOX AND COMBOBOX ITEMS ..............................................................................................166
POPULATING LISTBOXES AND COMBOBOXES WITH UNIQUE ITEMS........................................................................167
DISPLAY A REAL-TIME CHART IN A USERFORM .......................................................................................................169
Chapter 1 INTRODUCING VBA

P A G E |6
RESERVED
P A G E |7
RESERVED
P A G E |8
RESERVED
P A G E |9
RESERVED
Chapter 2 Getting Started with Macros

P A G E | 10
RESERVED
P A G E | 11
RESERVED
➪ ➪

P A G E | 12
RESERVED
P A G E | 13
RESERVED
P A G E | 14
RESERVED
P A G E | 15
RESERVED
➪ ➪

P A G E | 16
RESERVED
❘

P A G E | 17
RESERVED
P A G E | 18
RESERVED
INTRODUCING THEVISUAL BASIC EDITOR

P A G E | 19
RESERVED
P A G E | 20
RESERVED
➪
The Code window is where the code for macros and VBA procedures is located. The VBE provides separate code
windows for each module. A good way to think of this is, for every object (worksheet, module, and so on) you see
listed in the Project Explorer, the VBE has provided a code window. You will note that the drop-down in the upper
right-hand corner of Figure 3-3 displays the name of the macro that is currently showing in the Code window
(mySort). As you create multiple macros, you can use this drop-down to quickly move from one macro to another.

P A G E | 21
RESERVED
➪
Understanding modules

P A G E | 22
RESERVED
P A G E | 23
RESERVED
CHAPTER 4 WORKING IN THE VBE
➪ ➪
➪ ➪

P A G E | 24
RESERVED
1 . The first thing you did was select column A.
2 . Next, you inserted a new column at column A.
3.
7.8. The Range object of A1:D10 can have its cells’ contents cleared with the
From ClearContents method:
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
P A G E | be
25
blinki RESERVED
ng in
the
large
white
pane
P A G E | 26
RESERVED
P A G E | 27
RESERVED
P A G E | 28
RESERVED
➪

P A G E | 29
RESERVED
P A G E | 30
RESERVED
P A G E | 31
RESERVED
➪
➪ ➪

P A G E | 32
RESERVED
CHAPTER 5 OBJECT-ORIENTED PROGRAMMING

P A G E | 33
RESERVED
P A G E | 34
RESERVED
➤➤
➤➤
➤➤
➤➤
➤➤
➤➤

P A G E | 35
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
P A G Eblinki
| 36
RESERVED
ng in
the
large
white
pane
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
Chapter 6 Variables, Data Types, and Constants
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
The Dim statement (VBA’s abbreviation for “Dimension”), which all variable declarations start with.
. A
cursor INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E |will
37
RESERVED
be
blinki
ng in
the
large
The name of your variable, which you create, such as myValue.
The word As.
The type of data being stored.

P A G E | 38
RESERVED
P A G E | 39
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
1 . white
The first thing you did was select column A.
2 . pane
Next, you inserted a new column at column A.
on the
3.
right,
The Range object of A1:D10 can have its cells’ contents cleared with the
7 . 8 . which
ClearContents method:
Fromis the
the new
menumodule
bar, .
clickPress
InsertCtrl+V
➪ to
Module p➤➤
. A From
cursor the
will menu
be bar,
blinkiclick
ng inInsert
the ➪
largeModule
white . A INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 40
panecursor RESERVED
on the will
right, be
whichblinki
is theng in
new the
P A G E | 41
RESERVED
➪

P A G E | 42
RESERVED
P A G E | 43
RESERVED
P A G E | 44
RESERVED
P A G E | 45
RESERVED
P A G E | 46
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
➤➤ menu
bar,
➤➤click
Insert
➤➤ ➪
Module
. A
cursor
will
be
blinki
➪
ng in
the
large
P A white
G E | 47
pane RESERVED
on the
right,
which
is the
P A G E | 48
RESERVED
CHAPTER 7 UNDERSTANDING OBJECTS AND
COLLECTIONS

P A G E | 49
RESERVED
P A G E | 50
RESERVED
P A G E | 51
RESERVED
P A G E | 52
RESERVED
➪

P A G E | 53
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
P A G E menu
| 54
bar, RESERVED
click
Insert
➪
Module
. A
Chapter 8 Making Decisions with VBa

P A G E | 55
RESERVED
P A G E | 56
RESERVED
P A G E | 57
RESERVED
P A G E | 58
RESERVED
You may recall from your experience with Excel’s WEEKDAY worksheet function that to Excel, weekday number 1 is
Sunday, weekday number 2 is Monday, and so on. VBA would look at this line of code and display the message box
only if the line of code is being executed on a Friday because Friday is weekday number 6. If the weekday is any day
other than Friday, VBA bypasses this line of code.

P A G E | 59
RESERVED
P A G E | 60
RESERVED
P A G E | 61
RESERVED
P A G E | 62
RESERVED
Sub ConfirmExample() Select Case MsgBox( _ “Do you really want to copy this worksheet?”, _ vbYesNo +
vbQuestion, _ “Please confirm...”) Case vbNo MsgBox _ “No problem, this worksheet will not be copied.”, _
vbInformation, _ “You clicked No.” Exit Sub Case vbYes MsgBox _ “Great - - click OK to run the macro.”, _
vbInformation, _ “Thanks for confirming.” ActiveSheet.Copy Before:=Sheets(“Sheet1”) End Select End Sub
As you look at the MsgBox line, note that the message box arguments are contained within parentheses. A
message box has two mandatory arguments: the prompt, which is the text you place in the body of the message
box, and the button configuration. Other combinations of buttons include OKCancel, YesNoCancel, and
AbortRetryIgnore. The title of the message box is optional, but I always enter it to offer a more customized
experience for the user.

P A G E | 63
RESERVED
P A G E | 64
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 65
white RESERVED
pane
on the
right,
which
is the
CHAPTER 9 REPEATING ACTIONS WITH LOOPS

P A G E | 66
RESERVED
Types of loops

P A G E | 67
RESERVED
P A G E | 68
RESERVED
Sub ForNextExample2() ‘Declare your Integer or Long variables. Dim MoreSheets As Integer, intCounter As Integer
‘Define the MoreSheets variable with an InputBox. MoreSheets = InputBox( _ “How many worksheets do you want
to add?”, _ “Enter a number”) ‘Open the For loop structure. For intCounter = 1 To MoreSheets ‘Enter the
command(s)that will be repeated. Worksheets.Add ‘Loop to the next iteration. Next intCounter End Sub

P A G E | 69
RESERVED
P A G E | 70
RESERVED
P A G E | 71
RESERVED
P A G E | 72
RESERVED
P A G E | 73
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 74 large
RESERVED
white
pane
on the
right,
which
CHAPTER 10 WORKING WITH ARRAYS

P A G E | 75
RESERVED
P A G E | 76
RESERVED
P A G E | 77
RESERVED
P A G E | 78
RESERVED
P A G E | 79
RESERVED
P A G E | 80
RESERVED
Sub ArrayWeekdays()
‘Declare the array variable for seven elements (from 0 to 6). Dim myDays(6) As String
‘Declare an Integer type variable to handle the seven indexed elements. Dim intDay As Integer
‘Start to loop through each array element starting at the default 0 lower boundary. For intDay = 0 To 6
‘For each array element, define the myDays String variable ‘with its corresponding day of the week. ‘There is no
such thing as “Weekday 0”, because Excel’s Weekday function ‘is numbered from 1 to 7,so the “+ 1” notation adds 1
to the intDays Integer ‘variable which started at the lower bound of 0. myDays(intDay) = Format(Weekday(intDay +
1), “DDDD”)
‘Cells in range A1:A7 are populated in turn with the weekday. Range(“A” & intDay + 1).Value = myDays(intDay)
‘The loop is continued through to conclusion. Next intDay
‘End of the macro.
End Sub

P A G E | 81
RESERVED
P A G E | 82
RESERVED
3.
the
menu
bar,
click
Insert
➪
CHAPTER11
Module
. A
cursor
AUTOMATING PROCEDURESWITH WORKSHEET EVENTS

will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
➤➤
Module
➤➤ . A
cursor
will
be
blinki
ng in
the
large
white
P A G E |pane
83
RESERVED
on the
right,
which
is the
new
➤➤
➤➤

P A G E | 84
RESERVED
P A G E | 85
RESERVED
P A G E | 86
RESERVED
3.
ClearContents method:
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
WORKSHEET_CHANGE
right, EVENT
which
is the
new
module
. INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 87 Press
RESERVED
Ctrl+V
to
p➤➤
From
the
P A G E | 88
RESERVED
P A G E | 89
RESERVED
3.
the
menu
bar,
click INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 90
Insert RESERVED
➪
Module
. A
cursor
will
CHAPTER12
AUTOMATING PROCEDURES WITHWORKBOOK

EVENTS

P A G E | 91
RESERVED
P A G E | 92
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar, INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 93
click RESERVED
Insert
➪
Module
. A
cursor
P A G E | 94
RESERVED
P A G E | 95
RESERVED
P A G E | 96
RESERVED
CHAPTER 13 USING EMBEDDED CONTROLS

P A G E | 97
RESERVED
➪ ➪

P A G E | 98
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 99
p➤➤ RESERVED
From
the
menu
bar,
click
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G Eright,
| 100
RESERVED
which
is the
new
module
.
P A G E | 101
RESERVED
➪ ➪

P A G E | 102
RESERVED
P A G E | 103
RESERVED
P A G E | 104
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
P A G E➪| 105
RESERVED
Module
. A
cursor
will
be
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
P A G E | 106
RESERVED
From
the
menu
bar,
click
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
The first thing you did was select column A.
1 . menu
2 . bar,
click 3.
7.8. Insert
The Range object of A1:D10 can have its cells’ contents cleared with the
➪
theModule
menu . A
bar,cursor
click will
Insert be
➪ blinki
Module ng in
. A the
cursor large
will white INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
107 pane
P A G E |be
RESERVED
blinkion the
ng inright,
the which
largeis the
white new
P A G E | 108
RESERVED
Chapter 16 User Defined
P A G E | 109
RESERVED
Functions

P A G E | 110
RESERVED
1. The first thing you did was select column A.
1
2. The first
Next, you thing youadid
inserted newwas select
column at column
column A.
A.
2. Next, you inserted a new column at column A. 3.
7.8. The Range object of A1:D10 can have its cells’ contents cleared with the 3.
7 From
.8. The Range object
ClearContents of A1:D10 can have its cells’ contents cleared with the
method:
From
the ClearContents method:
the
menu
menu
bar,
bar,
click
click
Insert
Insert ➪
Module➪
Module
. A
.
cursor A
cursor
will
willbe
blinkibe
blinki
ng in
ng the
in
the
large
large
white
white
pane
onpane
the INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 111
on the
right, RESERVED
right,
which
which
is the
is the
new
new
module
module.
P A G E | 112
RESERVED
Function SumColor(RangeToSum As Range) As Long ‘Declare the necessary variables. Dim ColorID As Integer, ColorCell
As Range, mySum As Long ‘Identify the ColorID variable so you know what color to look for. ColorID =
Range(Application.Caller.Address).Interior.ColorIndex ‘Loop through each cell in the range. For Each ColorCell In
RangeToSum ‘If the cell’s color matches the color we are looking for, ‘keep a running subtotal by adding the cell’s
number value ‘to the mySum variable. If ColorCell.Interior.ColorIndex = ColorID Then mySum = mySum +
ColorCell.Value Next ColorCell ‘The cells have all been evaluated, so you can define the SumColor function ‘by setting it
equal to the mySum variable. SumColor = mySum End Function

P A G E | 113
RESERVED
P A G E | 114
RESERVED
P A G E | 115
RESERVED
P A G E | 116
RESERVED
P A G E | 117
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right, INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A Gwhich
E | 118
RESERVED
is the
new
module
.
Press
❘

3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
Chapter 17:Debugging Your

click
Insert
➪
Module
. A
Code cursor
will
be
blinki
ng in
the
large
white INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 119
pane RESERVED
on the
right,
which
is the
new
P A G E | 120
RESERVED
P A G E | 121
RESERVED
➪ ➪

P A G E | 122
RESERVED
P A G E | 123
RESERVED
P A G E | 124
RESERVED
P A G E | 125
RESERVED
P A G E | 126
RESERVED
P A G E | 127
RESERVED
P A G E | 128
RESERVED
➪

P A G E | 129
RESERVED
P A G E | 130
RESERVED
P A G E | 131
RESERVED
P A G E | 132
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
➤➤
P A G E |p133
RESERVED
From
the
menu
bar,
click
P A G E | 134
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
P A G E | 135 pane
RESERVED
on the
right,
which
is the
new
Chapter 18 Creating userForms

P A G E | 136
RESERVED
➪

P A G E | 137
RESERVED
➪

P A G E | 138
RESERVED
➪

P A G E | 139
RESERVED
P A G E | 140
RESERVED
P A G E | 141
RESERVED
P A G E | 142
RESERVED
P A G E | 143
RESERVED
P A G E | 144
RESERVED
P A G E | 145
RESERVED
3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
2 .which
is the
3.
7 . 8 . newThe Range object of A1:D10 can have its cells’ contents cleared with the
module
the .
menuPress
bar,
Ctrl+V
click to
Insertp➤➤
➪ From
Module the
. A menu
cursor bar,
willclick
be
Insert
blinki ➪
ng in
Module
the . A
large
cursor
white will
pane be
on the
blinki
right,ng in
which the
is thelarge
newwhite
module INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 146 pane
. the RESERVED
on
Press
right,
Ctrl+Vwhich
to
is the
p➤➤ new
From
CHAPTER 19 USERFORM CONTROLS AND THEIR FUNCTIONS

P A G E | 147
RESERVED
P A G E | 148
RESERVED
P A G E | 149
RESERVED
P A G E | 150
RESERVED
P A G E | 151
RESERVED
P A G E | 152
RESERVED
P A G E | 153
RESERVED
P A G E | 154
RESERVED
P A G E | 155
RESERVED
P A G E | 156
RESERVED
P A G E | 157
RESERVED
P A G E | 158
RESERVED
P A G E | 159
RESERVED
P A G E | 160
RESERVED
❘

3.
the
menu
bar,
click
Insert
➪
Module
. A
cursor
will
be
blinki
ng in
the
large
white
pane
on the
right,
which
is the
new
module
.
Press
Ctrl+V
to
p➤➤
From
the
menu
bar,
click
Insert
➪
Module
. A INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT
P A G E | 161
cursor RESERVED
will
be
blinki
ng in
the
CHAPTER20 ADVANCED USERFORMS
➪ ➪
➤➤
➤➤ ➤➤
➤➤ ➤➤
➤➤ ➤➤

P A G E | 162
RESERVED
➤➤

P A G E | 163
RESERVED
P A G E | 164
RESERVED
P A G E | 165
RESERVED
P A G E | 166
RESERVED
P A G E | 167
RESERVED
P A G E | 168
RESERVED
P A G E | 169
RESERVED
➪

P A G E | 170
RESERVED
P A G E | 171
RESERVED
P A G E | 172
RESERVED

Excel VBA Test

Uploaded by

Copyright:

Available Formats

Excel VBA Test

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel VBA Test

Uploaded by

Copyright:

Available Formats

Contents

Chapter 1 INTRODUCING VBA ....................................................................................................................................... 6

A BRIEF HISTORY OF VBA ........................................................................................................................................... 7

DON’T CONFUSE VB WITH VBA! ................................................................................................................................ 7

WHAT VBA can do for you? ....................................................................................................................................... 7

Controlling other office Applications from Excel ........................................................................................................ 9

Chapter 2 Getting Started with Macros ........................................................................................................................10

COMPOSING YOUR FIRST MACRO ............................................................................................................................10

ACCESSING THE VBA ENVIRONMENT........................................................................................................................10

Using the Macro recorder ........................................................................................................................................14

Hey, My stop recording button disappeared! ...........................................................................................................16

The Macro dialog Box ...............................................................................................................................................17

INTRODUCING THEVISUAL BASIC EDITOR .................................................................................................19

WHAT is THE VBE? ...................................................................................................................................................19

HOW TO GET INTO THE VBE .....................................................................................................................................19

Understanding THE VBE ...........................................................................................................................................20

The project Explorer Window ...................................................................................................................................21

The code Window ....................................................................................................................................................21

The properties Window............................................................................................................................................21

The immediate Window ...........................................................................................................................................22

Using THE object browser ........................................................................................................................................22

Exiting THE VBE ........................................................................................................................................................23

CHAPTER 4 WORKING IN THE VBE ................................................................................................................................24

Toolbars in THE VBE .................................................................................................................................................24

Macros And modules ...............................................................................................................................................24

Locating your Macros ...............................................................................................................................................24

Editing a Macro with comments and improvements to the code ..............................................................................26

Inserting a Module ...................................................................................................................................................29

Renaming a Module .................................................................................................................................................30

INTELIGENES TECHNOLOGIES|ALL CONTENT RESERVED|COPYRIGHT

Locking And protecting THE VBE...............................................................................................................................31

CHAPTER 5 OBJECT-ORIENTED PROGRAMMING ..........................................................................................................33

WHAT “object-oriented programming” means .........................................................................................................33

THE object model .....................................................................................................................................................33

Chapter 6 Variables, Data Types, and Constants ...........................................................................................................37

WHAT is A variable? .................................................................................................................................................37

Assigning values To variables ...................................................................................................................................38

Why you need variables ...........................................................................................................................................38

Data types ................................................................................................................................................................39

Understanding the different data Types ...................................................................................................................39

Declaring a Variable for dates and Times ..................................................................................................................41

Declaring a Variable with the proper data Type ........................................................................................................41

Forcing variable declaration .....................................................................................................................................42

Understanding A variable’s scope.............................................................................................................................44

Local Macro level only ..............................................................................................................................................44

Module level ............................................................................................................................................................44

Application level ......................................................................................................................................................46

CHOOSING THE SCOPE AND LIFETIME OF YOUR CONSTANTS ....................................................................................47

CHAPTER 7 UNDERSTANDING OBJECTS AND COLLECTIONS ..........................................................................................49

Cells And ranges .......................................................................................................................................................51

Chapter 8 Making Decisions with VBa ..........................................................................................................................55

UNDERSTANDING LOGICAL OPERATORS...................................................................................................................56

GETTING USERS TO MAKE DECISIONS.......................................................................................................................62

INPUT BOXES ...........................................................................................................................................................63

CHAPTER 9 REPEATING ACTIONS WITH LOOPS ............................................................................................................66

EXITING A FOR… LOOP .............................................................................................................................................70

LOOPING IN REVERSE WITH STEP .............................................................................................................................70

NESTING LOOPS .......................................................................................................................................................74

CHAPTER 10 WORKING WITH ARRAYS ..........................................................................................................................75

What Arrays can do for you ......................................................................................................................................77