There are two types of worksheet functions in Excel: built-in Worksheet functions, and custom VBA functions. The custom functions that we write in the VBA Code Window can also be used in the Worksheet. Worksheet formulas can contain built-in Worksheet functions and custom VBA functions.
If you are not already there, press Alt-F11 to toggle to the Worksheet. Make A5 your active cell by clicking on it and type in the text Sum of 4,10, and 7=. Next click on C5. We will use a Worksheet function as an example. Type =SUM(4,10,7) and press Enter. The cell will now have the number 21 in it. That is all there is to a built-in Worksheet function.
You can also do addition by reference to cells. In cell A6 type Reference sum =, in A7 type 4, in B7 type 10, in C7 type 7. Then make C6 your active cell by clicking on it and, type =SUM(A7,B7,C7). Press Enter. Again the cell will have the number 21 in it. See the screenshot on the next page.
Important question! What is the difference between A1 and $A$1 in a worksheet formula? A1 is called a relative cell reference. When we copy a formula from one cell to another cell, the row and column references change relative to the new cell. By default, Excel creates relative cell references in formulas. Cell references with dollar signs are absolute references. In a formula, $A$1 is called an absolute cell reference. Absolute cell references do not change when a formula is copied to a new cell. They are constant.
You can also add numbers by reference to a range of cells. Make A8 your active cell and type in Range sum =. Next, in C8 type =SUM(A7:C7). Press Enter. Again you will see the number 21.
Complete lists of built-in Worksheet functions and built-in VBA functions are available on the internet. We will provide a quick reference to these built-in functions in later chapters. That is all you need to know about built-in functions.
You will use you own functions often while programming. After creating a custom VBA function it can also be used like a built-in function in the worksheet. The function general form is:
Function name (Arg1, Arg2,Arg3)
instructions
name = expression
[Exit Function]
[instructions]
[name = expression]
End Function
The function may have several or no arguments.
Important: The function name must be repeated inside the function and set equal to some value. This is the final value that the function is equal to after all instructions are performed.
We need to briefly describe subroutines, because a function must be placed in a subroutine. Subs can run on their own. A subroutine has the following general form:
Sub name()
instructions
[Exit Sub]
[instructions]
End Sub
After a function is defined outside the subroutine, it can be used in a subroutine. The expanded general form is:
Sub name()
instructions
variable = myFunction(a,b)
[Exit Sub]
[instructions]
End Sub
Function myFunction(Arg1,Arg2)
instructions
myFunction = expression with Arg1, Arg2
End Function
Notice that the values of a and b are substituted into the function to replace the values of Arg1 and Arg2.
Example of a VBA function in a subroutine. The subroutine Test13() uses a function to add three numbers. The function is declared as Long. (When you run Test13() be sure to place your cursor in the subroutine of the code window before you run it. If you get a dialog box with a selection of subroutines you can run, just select sub Test13() from the dialog box.) In the following program funcAddition(Arg1,Arg2) is used to calculate the sum of 1, 2, and 4.
Sub Test13()
Dim a As Long
Dim b As Long
Dim c As Long
a = 1
b = 2
c = funcAddition(a,b)
MsgBox “c = “ & c
End Sub
Function funcAddition(Arg1,Arg2) As Long
Dim myVariable As Long
myVariable = 4
funcAddition = Arg1 + Arg2 + myVariable
End Function
Notice that myVariable in the function must be declared or you will get the error message Variable not defined. Also note that you do not declare Arg1 or Arg2 or you will get the error message Duplicate declaration in current scope.
Note: The subroutine and the function can be placed anywhere and in any order on the Code Window.
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-Questions
