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.
 
Search JabSto ::




Custom Search