**
Using
Built-In
VBA
Functions**

VBA
has
a
variety
of
built-in
functions
that
simplify
calculations
and
operations.
Many
of
VBA’s
functions
are
similar
(or
identical)
to
Excel’s
worksheet
functions.
For
example,
the
VBA
function
UCase,
which
converts
a
string
argument
to
uppercase,
is
equivalent
to
the
Excel
worksheet
function
UPPER.

To
display
a
list
of
VBA
functions
while
writing
your
code,
type
VBA
followed
by
a

period
(.).
The
VB
Editor
displays
a
list
of
all
functions
and
constants
(see
Figure
24-1).

If
this
does
not
work
for
you,
make
sure
that
you
select
the
Auto
List
Members
option.
Choose
Tools➜Options
and
click
the
Editor
tab.
In
addition
to
functions,
the
displayed
list
also
includes
built-in
constants.
The
VBA
functions
are
all
described
in
the
online
help.
To
view
Excel
Help,
just
move
the
cursor
over
a
function
name
and
press
F1.

**
Figure
24-1:
**
Displaying
a
list
of
VBA
functions
in
the
VB
Editor.

Here’s
a
statement
that
calculates
the
square
root
of
a
variable
by
using
VBA’s
Sqr
function
and
then
assigns
the
result
to
a
variable
named
*
x*:

x
=
Sqr(MyValue)

Having
knowledge
of
VBA’s
functions
can
save
you
lots
of
work.
For
example,
consider
the

REMOVESPACES
Function
procedure
presented
at
the
beginning
of
this
chapter.
That
function
uses
a
For-Next
loop
to
examine
each
character
in
a
string
and
builds
a
new
string.
A
much
simpler
(and
more
efficient)
version
of
that
Function
procedure
uses
the
VBA
Replace
func-
tion.
The
following
is
a
rewritten
version
of
the
Function
procedure:

Function
REMOVESPACES2(cell)
As
String

‘
Removes all
spaces
from
cell

REMOVESPACES2
=
Replace(cell,
“
“,
“”) End
Function

You
can
use
many
(but
not
all)
of
Excel’s
worksheet
functions
in
your
VBA
code.
To
use
a
work-
sheet
function
in
a
VBA
statement,
just
precede
the
function
name
with
WorksheetFunction
and
a
period.

The
following
code
demonstrates
how
to
use
an
Excel
worksheet
function
in
a
VBA
statement.
Excel’s
infrequently
used
ROMAN
function
converts
a
decimal
number
into
a
Roman
numeral.

DecValue
=
2010

RomanValue
=
WorksheetFunction.Roman(DecValue)

The
variable
RomanValue
contains
the
string
MMX.
Fans
of
old
movies
are
often
dismayed
when
they
learn
that
Excel
does
not
have
a
function
to
convert
a
Roman
numeral
to
its
decimal
equiva-
lent.
You
can,
of
course,
create
such
a
function
using
VBA.
Are
you
up
for
a
challenge?

It’s
important
to
understand
that
you
can’t
use
worksheet
functions
that
have
an
equivalent
VBA

function.
For
example,
VBA
can’t
access
Excel’s
SQRT
worksheet
function
because
VBA
has
its
own
version
of
that
function:
Sqr.
Therefore,
the
following
statement
generates
an
error:

x
=
WorksheetFunction.SQRT(123)
‘error

Controlling
Execution

Some
VBA
procedures
start
at
the
top
and
progress
line
by
line
to
the
bottom.
Often,
however,

you
need
to
control
the
flow
of
your
routines
by
skipping
over
some
statements,
executing
some
statements
multiple
times,
and
testing
conditions
to
determine
what
the
routine
does
next.

This
section
discusses
several
ways
of
controlling
the
execution
of
your
VBA
procedures:

h
If-Then
constructs

h
Select
Case
constructs

h
For-Next
loops

h
Do
While
loops

h
Do
Until
loops

h
On
Error
statements

The
If-Then
construct

Perhaps
the
most
commonly
used
instruction
grouping
in
VBA
is
the
If-Then
construct.
This
instruction
is
one
way
to
endow
your
applications
with
decision-making
capability.
The
basic
syntax
of
the
If-Then
construct
is
as
follows:

If
condition
Then
true_instructions
[Else
false_instructions]

The
If-Then
construct
executes
one
or
more
statements
conditionally.
The
Else
clause
is
optional.
If
included,
it
enables
you
to
execute
one
or
more
instructions
when
the
condition
that
you
test
is
not
true.

The
following
Function
procedure
demonstrates
an
If-Then
structure
without
an
Else

clause.
The
example
deals
with
time.
VBA
uses
the
same
date-and-time
serial
number
system
as
Excel
(but
with
a
much
wider
range
of
dates).
The
time
of
day
is
expressed
as
a
fractional
value
—
for
example,
noon
is
represented
as
.5.
The
VBA
Time
function
returns
a
value
that
represents

the
time
of
day,
as
reported
by
the
system
clock.
In
the
following
example,
the
function
starts
out

by
assigning
an
empty
string
to
GreetMe.
The
If-Then
statement
checks
the
time
of
day.
If
the
time
is
before
noon,
the
Then
part
of
the
statement
executes,
and
the
function
returns
Good Morning.

Function
GreetMe() GreetMe
=
“”

If
Time
<
0.5
Then
GreetMe=
“Good
Morning” End
Function

The
following
function
uses
two
If-Then
statements.
It
displays
either
Good
Morning
or

Good
Afternoon:

Function
GreetMe()

If
Time
<
0.5
Then
GreetMe
=
“Good
Morning”

If
Time
>=
0.5
Then
GreetMe
=
“Good
Afternoon” End
Function

Notice
that
the
second
If-Then
statement
uses
>=
(greater
than
or
equal
to).
This
covers
the
extremely
remote
chance
that
the
time
is
precisely
12:00
noon
when
the
function
is
executed.

Another
approach
is
to
use
the
Else
clause
of
the
If-Then
construct:

Function
GreetMe()

If
Time
<
0.5
Then
GreetMe
=
“Good
Morning”
Else
_ GreetMe
=
“Good
Afternoon”

End
Function

Notice
that
the
preceding
example
uses
the
line
continuation
sequence
(a
space
followed
by
an
underscore);
If-Then-Else
is
actually
a
single
statement.

The
following
is
another
example
that
uses
the
If-Then
construct.
This
Function
procedure
calculates
a
discount
based
on
a
quantity
(assumed
to
be
an
integer
value).
It
accepts
one
argu-
ment
(quantity)
and
returns
the
appropriate
discount
based
on
that
value.

Function
Discount(quantity)

If
quantity
<=
5
Then
Discount
=
0

If
quantity
>=
6
Then
Discount
=
0.1

If
quantity
>=
25
Then
Discount
=
0.15

If
quantity
>=
50
Then
Discount
=
0.2

If
quantity
>=
75
Then
Discount
=
0.25

End
Function

Notice
that
each
If-Then
statement
in
this
procedure
is
always
executed,
and
the
value
for

Discount
can
change
as
the
function
executes.
The
final
value,
however,
is
the
desired
value.

The
preceding
examples
all
used
a
single
statement
for
the
Then
clause
of
the
If-Then
con-
struct.
However,
you
often
need
to
execute
multiple
statements
if
a
condition
is
TRUE.
You
can
still
use
the
If-Then
construct,
but
you
need
to
use
an
End
If
statement
to
signal
the
end
of

the
statements
that
make
up
the
Then
clause.
Here’s
an
example
that
executes
two
statements
if

the
If
clause
is
TRUE:

If
x
>
0
Then y
=
2

z
=
3

End
If

You
can
also
use
multiple
statements
for
an
If-Then-Else
construct.
Here’s
an
example
that
exe-
cutes
two
statements
if
the
If
clause
is
TRUE,
and
two
other
statements
if
the
If
clause
is
not
TRUE:

If
x
>
0
Then y
=
2

z
=
3

Else

y
=
–2

z
=
–3

End
If

The
Select
Case
construct

The
Select
Case
construct
is
useful
for
choosing
among
three
or
more
options.
this
construct
also
works
with
two
options
and
is
a
good
alternative
to
using
If-Then-Else.
The
syntax
for
Select
Case
is
as
follows:

Select
Case
testexpression

[Case
expressionlist–n

[instructions–n]]

[Case
Else

[default_instructions]] End
Select

The
following
example
of
a
Select
Case
construct
shows
another
way
to
code
the
GreetMe

examples
presented
in
the
preceding
section:

Function
GreetMe() Select
Case
Time

Case
Is
<
0.5

GreetMe
=
“Good
Morning” Case
0.5
To
0.75

GreetMe
=
“Good
Afternoon” Case
Else

GreetMe
=
“Good
Evening” End
Select

End
Function

And
here’s
a
rewritten
version
of
the
Discount
function
from
the
previous
section,
this
time
using
a
Select
Case
construct:

Function
Discount2(quantity)
Select Case
quantity

Case
Is
<=
5

Discount2
=
0

Case
6
To
24

Discount2
=
0.1

Case
25
To
49

Discount2
=
0.15

Case
50
To
74

Discount2
=
0.2

Case
Is
>=
75

Discount2
=
0.25

End
Select

End
Function

Any
number
of
instructions
can
be
written
below
each
Case
statement;
they
all
execute
if
that
case
evaluates
to
TRUE.

Looping
blocks
of
instructions

*
Looping
*
is
the
process
of
repeating
a
block
of
VBA
instructions
within
a
procedure.
You
may
know
the
number
of
times
to
loop,
or
it
may
be
determined
by
the
values
of
variables
in
your
program.
VBA
offers
a
number
of
looping
constructs:

h
For-Next
loops
h
Do
While
loops
h
Do
Until
loops

For-Next
loops

The
following
is
the
syntax
for
a
For-Next
loop:

For
counter
=
start
To
end
[Step
stepval]

[instructions]

[Exit
For]

[instructions] Next
[counter]

The
following
listing
is
an
example
of
a
For-Next
loop
that
does
not
use
the
optional
Step

value
or
the
optional
Exit
For
statement.
This
function
accepts
two
arguments
and
returns
the
sum
of
all
integers
between
(and
including)
the
arguments:

Function
SumIntegers(first,
last)

total
=
0

For
num
=
first
To
last total
=
total
+
num

Next
num

SumIntegers
=
total

End
Function

The
following
formula,
for
example,
returns
55
—
the
sum
of
all
integers
from
1
to
10:

=SumIntegers(1,10)

In
this
example,
num
(the
loop
counter
variable)
starts
out
with
the
same
value
as
the
first
vari-
able,
and
increases
by
1
each
time
the
loop
repeats.
The
loop
ends
when
num
is
equal
to
the
last

variable.
The
total
variable
simply
accumulates
the
various
values
of
num
as
it
changes
during
the
looping.

When
you
use
For-Next
loops,
you
should
understand
that
the
loop
counter
is
a
nor-

mal
variable
—
it
is
not
a
special
type
of
variable.
As
a
result,
you
can
change
the
value

of
the
loop
counter
within
the
block
of
code
executed
between
the
For
and
Next
statements.
this
is,
however,
a
*
very
bad
*
practice
and
can
cause
problems.
In
fact,
you
should
take
special
precautions
to
ensure
that
your
code
does
not
change
the
loop
counter.

You
also
can
use
a
Step
value
to
skip
some
values
in
the
loop.
Here’s
the
same
function
rewrit-

ten
to
sum
*
every
other
*
integer
between
the
first
and
last
arguments:

Function
SumIntegers2(first,
last)

total
=
0

For
num
=
first
To
last
Step
2

total
=
total
+
num

Next
num

SumIntegers2
=
Total

End
Function

The
following
formula
returns
25,
which
is
the
sum
of
1,
3,
5,
7,
and
9:

=SumIntegers2(1,10)

For-Next
loops
can
also
include
one
or
more
Exit
For
statements
within
the
loop.
When
this
statement
is
encountered,
the
loop
terminates
immediately,
as
the
following
example
demonstrates:

Function
RowOfLargest(c)
NumRows =
Rows.Count

MaxVal
=
WorksheetFunction.Max(Columns(c))
For r
=
1
To
NumRows

If
Cells(r,
c)
=
MaxVal
Then

RowOfLargest
=
r

Exit
For

End
If

Next
r

End
Function

The
RowOfLargest
function
accepts
a
column
number
(1–16,384)
for
its
argument
and
returns
the
row
number
of
the
largest
value
in
that
column.
It
starts
by
getting
a
count
of
the
number
of
rows

in
the
worksheet.
(This
varies,
depending
on
the
version
of
Excel.)
This
number
is
assigned
to
the
NumRows
variable.
The
maximum
value
in
the
column
is
calculated
by
using
the
Excel
MAX
func-
tion,
and
this
value
is
assigned
to
the
MaxVal
variable.

The
For-Next
loop
checks
each
cell
in
the
column.
When
the
cell
equal
to
MaxVal
is
found,
the

row
number
(variable
r,
the
loop
counter)
is
assigned
to
the
function’s
name,
and
the
Exit
For
statement
ends
the
procedure.
Without
the
Exit
For
statement,
the
loop
continues
to
check
all
cells
in
the
column
—
which
can
take
quite
a
long
time!

The
previous
examples
use
relatively
simple
loops.
But
you
can
have
any
number
of
statements

in
the
loop,
and
you
can
even
nest
For-Next
loops
inside
other
For-Next
loops.
The
following

is
VBA
code
that
uses
nested
For-Next
loops
to
initialize
a
10
x
10
x
10
array
with
the
value
–1.
When
the
three
loops
finish
executing,
each
of
the
1,000
elements
in
MyArray
contains
–1.

Dim
MyArray(1
to
10,
1
to
10,
1
to
10) For
i
=
1
To
10

For
j
=
1
To
10

For
k
=
1
To
10

MyArray(i,
j,
k)
=
–1

Next
k

Next
j

Next
i

Do
While
loops

A
Do
While
loop
is
another
type
of
looping
structure
available
in
VBA.
Unlike
a
For-Next

loop,
a
Do
While
loop
executes
while
a
specified
condition
is
met.
A
Do
While
loop
can
have
one
of
two
syntaxes:

Do
[While
condition]

[instructions]

[Exit
Do]

[instructions]

Loop

or

Do

[instructions]

[Exit
Do]

[instructions]

Loop
[While
condition]

As
you
can
see,
VBA
enables
you
to
put
the
While
condition
at
the
beginning
or
the
end
of
the
loop.
The
difference
between
these
two
syntaxes
involves
the
point
in
time
when
the
condition
is
evaluated.
In
the
first
syntax,
the
contents
of
the
loop
may
never
be
executed:
That
is,
if
the
con-
dition
is
met
as
soon
as
the
Do
statement
is
executed.
In
the
second
syntax,
the
contents
of
the
loop
are
always
executed
at
least
one
time.

The
following
example
is
the
RowOfLargest
function
presented
in
the
previous
section,
rewrit-

ten
to
use
a
Do
While
loop
(using
the
first
syntax):

Function
RowOfLargest2(c)
NumRows =
Rows.Count

MaxVal
=
Application.Max(Columns(c))

r
=
1

Do
While
Cells(r,
c)
<>
MaxVal r
=
r
+
1

Loop

RowOfLargest2
=
r

End
Function

The
variable
r
starts
out
with
a
value
of
1
and
increments
within
the
Do
While
loop.
The
looping
continues
as
long
as
the
cell
being
evaluated
is
not
equal
to
MaxVal.
When
the
cell
is
equal
to
MaxVal,
the
loop
ends,
and
the
function
is
assigned
the
value
of
r.
Notice
that
if
the
maximum
value
is
in
row
1,
the
looping
does
not
occur.

The
following
procedure
uses
the
second
Do
While
loop
syntax.
The
loop
always
executes
at
least
once.

Function
RowOfLargest(c)

MaxVal
=
Application.Max(Columns(c))

r
=
0

Do

r
=
r
+
1

Loop
While
Cells(r,
c)
<>
MaxVal

RowOfLargest
=
r

End
Function

Do
While
loops
can
also
contain
one
or
more
Exit
Do
statements.
When
an
Exit
Do
state-
ment
is
encountered,
the
loop
ends
immediately.

Do
Until
loops

The
Do
Until
loop
structure
closely
resembles
the
Do
While
structure.
The
difference
is
evi-

dent
only
when
the
condition
is
tested.
In
a
Do
While
loop,
the
loop
executes
*
while
*
the
condition

is
true.
In
a
Do
Until
loop,
the
loop
executes
*
until
*
the
condition
is
true.
Do
Until
also
has
two
syntaxes:

Do
[Until
condition]

[instructions]

[Exit
Do]

[instructions]

Loop

or

Do

[instructions]

[Exit
Do]

[instructions]

Loop
[Until
condition]

The
following
example
demonstrates
the
first
syntax
of
the
Do
Until
loop.
This
example
makes
the
code
a
bit
clearer
because
it
avoids
the
negative
comparison
required
in
the
Do
While
example.

Function
RowOfLargest4(c)
NumRows =
Rows.Count

MaxVal
=
Application.Max(Columns(c))

r
=
1

Do
Until
Cells(r,
c)
=
MaxVal r
=
r
+
1

Loop

RowOfLargest4
=
r

End
Function

Finally,
the
following
function
is
the
same
procedure
but
is
rewritten
to
use
the
second
syntax
of
the
Do
Until
loop:

Function
RowOfLargest5(c)
NumRows =
Rows.Count

MaxVal
=
Application.Max(Columns(c))

r
=
0

Do

r
=
r
+
1

Loop
Until
Cells(r,
c)
=
MaxVal

RowOfLargest5
=
r

End
Function

The
On
Error
statement

Undoubtedly,
you’ve
used
one
of
Excel’s
worksheet
functions
in
a
formula
and
discovered
that

the
formula
returns
an
error
value
(for
example,
#VALUE!).
A
formula
can
return
an
error
value
in

a
number
of
situations,
including
these:

h
You
omitted
one
or
more
required
argument(s).

h
An
argument
was
not
the
correct
data
type
(for
example,
text
instead
of
a
value).

h
An
argument
is
outside
of
a
valid
numeric
range
(division
by
zero,
for
example).

In
many
cases,
you
can
ignore
error
handling
within
your
functions.
If
the
user
does
not
provide

the
proper
number
of
arguments,
the
function
simply
returns
an
error
value.
It’s
up
to
the
user
to
figure
out
the
problem.
In
fact,
this
is
how
Excel’s
worksheet
functions
handle
errors.

In
other
cases,
you
want
your
code
to
know
if
errors
occurred
and
then
do
something
about
them.
Excel’s
On
Error
statement
enables
you
to
identify
and
handle
errors.

To
simply
ignore
an
error,
use
the
following
statement:

On
Error
Resume
Next

If
you
use
this
statement,
you
can
determine
whether
an
error
occurs
by
checking
the
Number
property
of
the
Err
object.
If
this
property
is
equal
to
zero,
an
error
did
not
occur.
If
Err. Number
is
equal
to
anything
else,
an
error
*
did
*
occur.

The
following
example
is
a
function
that
returns
the
name
of
a
cell
or
range.
If
the
cell
or
range

does
not
have
a
name,
an
error
occurs,
and
the
formula
that
uses
the
function
returns
a
#VALUE!

error.

Function
RANGENAME(rng)
RANGENAME =
rng.Name.Name

End
Function

The
following
list
shows
an
improved
version
of
the
function.
The
On
Error
Resume
Next

statement
causes
VBA
to
ignore
the
error.
The
If
Err
statement
checks
whether
an
error
occurs.

If
so,
the
function
returns
an
empty
string.

Function
RANGENAME(rng) On
Error
Resume
Next

RANGENAME
=
rng.Name.Name

If
Err.Number
<>
0
Then
RANGENAME
=
“” End
Function

The
following
statement
instructs
VBA
to
watch
for
errors;
if
an
error
occurs,
it
continues
execut-
ing
at
a
different
named
location
—
in
this
case,
a
statement
labeled
ErrHandler:

On
Error
GoTo
ErrHandler

The
following
Function
procedure
demonstrates
this
statement.
The
DIVIDETWO
function
accepts
two
arguments
(num1
and
num2)
and
returns
the
result
of
num1
divided
by
num2.

Function
DIVIDETWO(num1,
num2)

On
Error
GoTo
ErrHandler

DIVIDETWO
=
num1
/
num2

Exit
Function

ErrHandler:

DIVIDETWO
=
“ERROR” End
Function

The
On
Error
GoTo
statement
instructs
VBA
to
jump
to
the
statement
labeled
ErrHandler

if
an
error
occurs.
As
a
result,
the
function
returns
a
string
(ERROR)
if
any
type
of
error
occurs
while
the
function
is
executing.
Note
the
use
of
the
Exit
Function
statement.
Without
this
statement,
the
code
continues
executing,
and
the
error
handling
code
*
always
*
executes.
In
other
words,
the
function
always
returns
ERROR.

It’s
important
to
understand
that
the
DIVIDETWO
function
is
*
nonstandard
*
in
its
approach.
Returning
an
error
message
string
when
an
error
occurs
(ERROR)
is
not
how
Excel
functions
work.
Excel
functions
return
an
actual
error
value.

Chapter
25
contains
an
example
that
demonstrates
how
to
return
an
actual
error
value

from
a
function.

Using
Ranges

Many
of
the
custom
functions
that
you
develop
will
work
with
the
data
contained
in
a
cell
or
in
a
range
of
cells.
Recognize
that
a
range
can
be
a
single
cell
or
a
group
of
cells.
This
section

describes
some
key
concepts
to
make
this
task
easier.
The
information
in
this
section
is
intended

to
be
practical,
rather
than
comprehensive.
If
you
want
more
details,
consult
Excel’s
online
help.

Chapter
25
contains
many
practical
examples
of
functions
that
use
ranges.
Studying

those
examples
helps
to
clarify
the
information
in
this
section.

The
For
Each-Next
construct

Your
Function
procedures
often
need
to
loop
through
a
range
of
cells.
For
example,
you
may
write
a
function
that
accepts
a
range
as
an
argument.
Your
code
needs
to
examine
each
cell
in

the
range
and
do
something.
The
For
Each-Next
construct
is
very
useful
for
this
sort
of
thing.
The
syntax
of
the
For
Each-Next
construct
is

For
Each
element
In
group

[instructions]

[Exit
For]

[instructions] Next
[element]

The
following
Function
procedure
accepts
a
range
argument
and
returns
the
sum
of
the

squared
values
in
the
range:

Function
SUMOFSQUARES(rng
as
Range) Dim
total
as
Double

Dim
cell
as
Range total
=
0

For
Each
cell
In
rng

total
=
total
+
cell
^
2

Next
cell

SUMOFSQUARES
=
total

End
Function

The
following
is
a
worksheet
formula
that
uses
the
SumOfSquares
function:

=SumOfSquares(A1:C100)

In
this
case,
the
function’s
argument
is
a
range
that
consists
of
300
cells.

In
the
preceding
example,
cell
and
rng
are
both
variable
names.
There’s
nothing

special
about
either
name*;
*
you
can
replace
them
with
any
valid
variable
name.

Referencing
a
range

VBA
code
can
reference
a
range
in
a
number
of
different
ways:

h
Using
the
Range
property

h
Using
the
Cells
property

h
Using
the
Offset
property

The
Range
property

You
can
use
the
Range
property
to
refer
to
a
range
directly
by
using
a
cell
address
or
name.
The
following
example
assigns
the
value
in
cell
A1
to
a
variable
named
Init.
In
this
case,
the
state-
ment
accesses
the
range’s
Value
property.

Init
=
Range(“A1”).Value

In
addition
to
the
Value
property,
VBA
enables
you
to
access
a
number
of
other
properties
of
a
range.
For
example,
the
following
statement
counts
the
number
of
cells
in
a
range
and
assigns

the
value
to
the
Cnt
variable:

Cnt
=
Range(“A1:C300”).Count

The
Range
property
is
also
useful
for
referencing
a
single
cell
in
a
multicell
range.
For
example,
you
may
create
a
function
that
is
supposed
to
accept
a
single-cell
argument.
If
the
user
specifies

a
multicell
range
as
the
argument,
you
can
use
the
Range
property
to
extract
the
upper-left
cell

in
the
range.
The
following
example
uses
the
Range
property
(with
an
argument
of
“A1”)
to
return
the
value
in
the
upper-left
cell
of
the
range
represented
by
the
cell
argument.

Function
Square(cell
as
Range) CellValue
=
cell.Range(“A1”).Value
Square =
CellValue
^
2

End
Function

Assume
that
the
user
enters
the
following
formula:

=Square(C5:C12)

The
Square
function
works
with
the
upper-left
cell
in
C5:C12
(which
is
C5)
and
returns
the
value
squared.

Many
Excel
worksheet
functions
work
in
this
way.
For
example,
if
you
specify
a
multi-

cell
range
as
the
first
argument
for
the
LEFT
function,
Excel
uses
the
upper-left
cell

in
the
range.
However,
Excel
is
not
consistent.
If
you
specify
a
multicell
range
as
the
argument
for
the
SQRT
function,
Excel
returns
an
error.

The
Cells
property

Another
way
to
reference
a
range
is
to
use
the
Cells
property.
The
Cells
property
accepts
two
arguments
(a
row
number
and
a
column
number),
and
returns
a
single
cell.
The
following
statement
assigns
the
value
in
cell
A1
to
a
variable
named
FirstCell:

FirstCell
=
Cells(1,
1).Value

The
following
statement
returns
the
upper-left
cell
in
the
range
C5:C12:

UpperLeft
=
Range(“C5:C12”).Cells(1,1)

If
you
use
the
Cells
property
without
an
argument,
it
returns
a
range
that
consists
of

all
cells
on
the
worksheet.
In
the
following
example,
the
TotalCells
variable
contains
the
total
number
of
cells
in
the
worksheet:

TotalCells
=
Cells.Count

The
following
statement
uses
the
Excel
COUNTA
function
to
determine
the
number
of
nonempty

cells
in
the
worksheet:

NonEmpty
=WorksheetFunction.COUNTA(Cells)

The
Offset
property

The
Offset
property
(like
the
Range
and
Cells
properties)
also
returns
a
Range
object.
The
Offset
property
is
used
in
conjunction
with
a
range.
It
takes
two
arguments
that
correspond
to
the
relative
position
from
the
upper-left
cell
of
the
specified
Range
object.
The
arguments
can

be
positive
(down
or
right),
negative
(up
or
left),
or
zero.
The
following
example
returns
the
value
one
cell
below
cell
A1
(that
is,
cell
A2)
and
assigns
it
to
a
variable
named
NextCell:

NextCell
=
Range(“A1”).Offset(1,0).Value

The
following
Function
procedure
accepts
a
single-cell
argument
and
returns
the
sum
of
the
eight
cells
that
surround
it:

Function
SumSurroundingCells(cell)
Dim Total
As
Double

Dim
r
As
Long,
c
As
Long

Total
=
0

For
r
=
–1
To
1

For
c
=
–1
To
1

Total
=
Total
+
cell.Offset(r,
c) Next
c

Next
r

SumSurroundingCells
=
Total
–
cell

End
Function

This
function
uses
a
nested
For-Next
loop.
So,
when
the
r
loop
counter
is
–1,
the
c
loop
counter
goes
from
–1
to
1.
Nine
cells
are
summed,
including
the
argument
cell,
which
is
Offset(0,
0).
The
final
statement
subtracts
the
value
of
the
argument
cell
from
the
total.

The
function
returns
an
error
if
the
argument
does
not
have
eight
surrounding
cells
(for
example,

if
it’s
in
row
1
or
column
1).

To
better
understand
how
the
nested
loop
works,
following
are
nine
statements
that
perform
exactly
the
same
calculation:

Total
=
Total
+
cell.Offset(–1,
–1)
‘
upper
left

Total
=
Total
+
cell.Offset(–1,
0)
‘left

Total
=
Total
+
cell.Offset(–1,
1)
‘upper
right

Total
=
Total
+
cell.Offset(0,
–1)
‘above

Total
=
Total
+
cell.Offset(0,
0)
‘the
cell
itself

Total
=
Total
+
cell.Offset(0,
1)
‘right

Total
=
Total
+
cell.Offset(1,
–1)
‘lower
left

Total
=
Total
+
cell.Offset(1,
0)
‘below

Total
=
Total
+
cell.Offset(1,
1)
‘lower
right

Some
useful
properties
of
ranges

Previous
sections
in
this
chapter
give
you
examples
that
used
the
Value
property
for
a
range.
VBA
gives
you
access
to
many
additional
range
properties.
Some
of
the
more
useful
properties

for
function
writers
are
briefly
described
in
the
following
sections.
For
complete
information
on
a
particular
property,
refer
to
Excel’s
online
help.

The
Formula
property

The
Formula
property
returns
the
formula
(as
a
string)
contained
in
a
cell.
If
you
try
to
access
the

Formula
property
for
a
range
that
consists
of
more
than
one
cell,
you
get
an
error.
If
the
cell

does
not
have
a
formula,
this
property
returns
a
string,
which
is
the
cell’s
value
as
it
appears
in
the

Formula
bar.
The
following
function
simply
displays
the
formula
for
the
upper-left
cell
in
a
range:

Function
CELLFORMULA(cell)

CELLFORMULA
=
cell.Range(“A1”).Formula

End
Function

You
can
use
the
HasFormula
property
to
determine
whether
a
cell
has
a
formula.

The
Address
Property

The
Address
property
returns
the
address
of
a
range
as
a
string.
By
default,
it
returns
the

address
as
an
absolute
reference
(for
example,
$A$1:$C$12).
The
following
function,
which
is
not
all
that
useful,
returns
the
address
of
a
range:

Function
RANGEADDRESS(rng)
RANGEADDRESS =
rng.Address

End
Function

For
example,
the
following
formula
returns
the
string
$A$1:$C$3:

=RANGEADDRESS(A1:C3)