Microsoft Office Tutorials and References
In Depth Information
An Introductory Example Function Procedure
An Introductory Example Function Procedure
To get the ball rolling, I’ll begin with an example
Function
procedure. This function, named
REMOVESPACES
, accepts a single argument and returns that argument without any spaces. For
example, the following formula uses the
REMOVESPACES
function and returns
ThisIsATest
.
=REMOVESPACES(“This Is A Test”)
To create this function, insert a VBA module into a project, and then enter the following
Function
procedure into the code window of the module:
Function REMOVESPACES(cell) As String
‘ Removes all spaces from cell
Dim CellLength As Long
Dim Temp As String
Dim Characters As String
Dim i As Long
CellLength = Len(cell)
Temp = “”
For i = 1 To CellLength
Character = Mid(cell, i, 1)
If Character <> Chr(32) Then Temp = Temp & Character
Next i
REMOVESPACES = Temp
End Function
Look closely at this function’s code line by line:
h
The first line of the function is called the function’s
declaration line.
Notice that the
procedure starts with the keyword
Function
, followed by the name of the function
(
REMOVESPACES
). This function uses only one argument (
cell
); the argument name is
enclosed in parentheses.
As String
defines the data type of the function’s return value.
The
As
part of the function declaration is optional.
h
The second line is a comment (optional) that describes what the function does. The initial
apostrophe designates this line as a comment. Comments are ignored when the function
is executed.
h
The next four lines use the
Dim
keyword to declare the four variables used in the
procedure:
CellLength
,
Temp
,
Character
, and
i
. Declaring a variable is not necessary, but
(as you’ll see later) it’s an excellent practice.
h
The procedure’s next line assigns a value to a variable named
CellLength
. This
statement uses the VBA
Len
function to determine the length of the contents of the argument
(
cell
).
h
The next line creates a variable named
Temp
and assigns it an empty string.
