Chapter 10.

Creating Function
Procedures and User Forms
 Table of Content
1. Why Create Custom Functions?
2. Function Procedures
3. Function Arguments
4. Insert Function Dialog Box
5. Using Add-Ins to Store Custom Functions
6. Advanced Input Boxes
7. Avanced Message Boxes
8. Introduction to User Forms

2
 List of Practice
1. Practice 10.1
2. Practice 10.2
3. Practice 10.3
4. Practice 10.4
5. Practice 10.5
6. Practice 10.6
7. Practice 10.7
8. Practice 10.8

3
1. Why Create Custom Function?
▪ With all the functions available in Excel, you might wonder why
you’d ever need to create new functions. The answer: to simplify
your work. With a bit of planning, custom functions are useful in
worksheet formulas.
▪ Custom function that can significantly shorten your formulas. And
shorter formulas are more readable and easier to work with. The
trade-off, however, is that custom functions are usually much
slower than built-in functions. And, of course, the user must enable
macros to use these functions.

4
1. Why Create Custom Function?
▪ A VBA Function is a procedure that performs calculations and
returns a value. You can use these functions in your Visual Basic
for Applications (VBA) code or in worksheet formulas.
▪ Sub procedure is a command that either the user or another
procedure can execute. Function procedures, on the other hand,
usually return a single value (or an array), just like Excel worksheet
functions and VBA built-in functions

5
 2. Function Procedure
▪ The syntax for declaring a function is as follows:

▪ Public: Optional. Indicates that the Function procedure is accessible

to all other procedures in all other modules in all active Excel VBA
projects. This is default.
▪ Private: Optional. Indicates that the Function procedure is
accessible only to other procedures in the same module.
▪ Static: Optional. Indicates that the values of variables declared in
the Function
6
 2. Function Procedure
▪ Function: Required. Indicates the beginning of a procedure that
returns a value or other data.

▪ name: Required. Any valid Function procedure name, which must

follow the same rules as a variable name.

▪ arglist: Optional. A list of one or more variables that represent

arguments passed to the Function procedure. The arguments are
enclosed in parentheses. Use a comma to separate pairs of
arguments.

7
 3. Function Arguments
▪ Keep in mind the following points about Function procedure
arguments:
▪ Arguments can be variables (including arrays), constants,
literals, or expressions.
▪ Some functions don’t have arguments.
▪ Some functions have a fixed number of required arguments
(from 1 to 60).
▪ Some functions have a combination of required and optional
arguments.

8
 3. Functions With No Argument
▪ Excel, for example, has a few built-in functions that don’t use
arguments, including RAND, TODAY, and NOW. You can create
similar functions.
▪ Here’s a simple example of a function that doesn’t use an argument.
The following function returns the UserName property of the
Application object.
Function USER()
' Returns the name of the current user
USER = Application.UserName
End Function
▪ When you enter the following formula, the cell returns the name of
the current user: = USER()
9
 3. Functions With No Argument - Practice
▪ Practice 10.1:

▪ Create function SheetsCount() that counts the number of

worksheet in an active workbook.

▪ Create function SheetName() that returns name of an active

worksheet.

▪ Create function ListSheet() that returns names of all

worksheets starting from active cell.

10
 3. Functions With One Argument
▪ This section describes a function for sales managers who need to
calculate the commissions earned by their sales forces. The
calculations in this example are based on the following table:

▪ If you use a formula:

11
 3. Functions With One Argument
▪ Using formula is sometimes bad for a couple of reasons. First, the
formula is overly complex, making it difficult to understand.
Second, the values are hard-coded into the formula, making the
formula difficult to modify.

▪ You can create a custom function for that problem.

12
 3. Functions With One Argument
Function COMMISSION(Sales)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
' Calculates sales commissions
Select Case Sales
Case 0 To 9999.99: COMMISSION = Sales * Tier1
Case 10000 To 19999.99: COMMISSION = Sales * Tier2
Case 20000 To 39999.99: COMMISSION = Sales * Tier3
Case Is >= 40000: COMMISSION = Sales * Tier4
End Select
End Function

▪ After you enter this function in a VBA module, you can use it in a
worksheet formula or call the function from other VBA procedures.
13
 3. Functions With One Argument – Practice
▪ Practice 10.2:
Monthly assessable income PIT rate
▪ Create function Grade(score) that (million VND) (%)
0 to 5 5
converts score (9, 10...) to A, B, C
5 to 10 10
format. 10 to 18 15

▪ Create function Tax_Bracket 18 to 32 20

32 to 52 25
(taxable_income) that return tax
52 to 80 30
bracket based on assessable More than 80 35
income.

14
3. Functions With Two Arguments – Practice

▪ Practice 10.3: Create a function PIT(taxable_income,

number_dependents) that return the amount of personal income
tax. PIT is calculated as follow:
Assessable income = Taxable income – Taxpayer deduction (11
million/month) – Dependent deduction (4.4
million/month/dependent)
PIT amount = Assessable income × (tax rate under progressive tax
table)

15
3. Functions With An Array Argument

▪ A Function procedure also can accept one or more arrays as

arguments, process the array(s), and return a single value. The
array can also consist of a range of cells.
▪ The following function accepts an array as its argument and
returns the sum of its elements:
Function SUMARRAY(List) As Double
Dim Item As Variant
SumArray = 0
For Each Item In List
If WorksheetFunction.IsNumber(Item) Then _
SUMARRAY = SUMARRAY + Item
Next Item
End Function
16
3. Functions With An Array Argument

▪ Practice 10.4: Create a function Payback(a list of cash flows) that

return the number of years needed to recover initial investment.
The number of years in the following example is 2.5 years.

Year 0 1 2 3

Cash Flow -1,000 300 500 400

17
3. Functions With an Indefinite Number of Arguments

▪ Some Excel worksheet functions take an indefinite number of

arguments. A familiar example is the SUM function.
▪ You can create Function procedures that have an indefinite number
of arguments. The trick is to use an array as the last (or only)
argument, preceded by the keyword ParamArray.
▪ Note: ParamArray can apply only to the last argument in the
procedure’s argument list . It’s always a Variant data type and
always an optional argument (although you don’t use the Optional
keyword) .

18
3. Functions With an Indefinite Number of Arguments

▪ Following is a function that can have any number of single-value

arguments. It simply returns the sum of the arguments.

Function SIMPLESUM(ParamArray arglist() As Variant) As Double

For Each arg In arglist
SIMPLESUM = SIMPLESUM + arg
Next arg
End Function

19
3. Functions With an Indefinite Number of Arguments

▪ To modify this function so that it works with multicell range

arguments, you need to add another loop, which processes each cell
in each of the arguments.

Function SIMPLESUM (ParamArray arglist() As Variant) As Double

Dim cell As Range
For Each arg In arglist
For Each cell In arg
SIMPLESUM = SIMPLESUM + cell
Next cell
Next arg
End Function

20
3. Functions With an Indefinite Number of Arguments

▪ Practice 10.5: Create a function Real_NPV(rate, a list of cash flows)

that return the net present value for given interest rate and cash
flows starting from year 0. With interest rate of 10%, the function
should return NPV of -136.7 for following investment.

Year 0 1 2 3

Cash Flow -1,000 300 500 600

21
4. Insert Function Dialog Box

▪ Excel’s Insert Function dialog box is a handy tool. When you’re

creating a worksheet formula, this tool lets you select a particular
worksheet function from a list of functions.
▪ These functions are grouped into various categories to make
locating a particular function easier. When you select a function
and click OK, the Function Arguments dialog box activates to help
insert the function’s arguments.

22
4. Insert Function Dialog Box

23
4. Insert Function Dialog Box

▪ The Insert Function dialog box also displays your custom

worksheet functions. By default, custom functions are listed under
the User Defined category. The Function Arguments dialog box
prompts you for a custom function’s arguments.
▪ You can use the MacroOptions method of the Application object to
make your functions appear just like built-in functions. Specifically,
this method enables you to do the following:
▪ Provide a description of the function.
▪ Specify a function category.
▪ Provide descriptions for the function arguments.
24
4. Insert Function Dialog Box

▪ If you don’t use the

MacroOptions method to specify
a different category, your custom
worksheet functions appear in
the User Defined category in the
Insert Function dialog box.

25
4. Insert Function Dialog Box
▪ The following is an example of a procedure that uses the
MacroOptions method to provide information about a function
Real_NPV:

▪ You need to execute the DescribeFunction procedure only one time.

After doing so, the information assigned to the function is stored in
the workbook

26
 4. Insert Function Dialog Box
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = "Real_NPV"
FuncDesc = "Calculate Net Present Value of an investment based on
interest rate and series of cash flows"
FuncCat = 1
Arg1Desc = "Enter the value of annual interest rate or select the cell
that contains annual interest rate"
Arg2Desc = "Select ranges that contain cash flows"

Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub
27
5. Using Add-Ins to Store Custom Functions

▪ Generally speaking, a spreadsheet add-in is something added to a

spreadsheet to give it additional functionality. Excel ships with
several add-ins. Examples include Analysis ToolPak, (which adds
statistical and analysis capabilities) and Solver (which performs
advanced optimization calculations).
▪ You may prefer to store frequently used custom functions in an
add-in file. A primary advantage is that you can use those functions
in any workbook when the add-in is installed.

28
5. Using Add-Ins to Store Custom Functions

▪ Steps:
1. Save the workbook that contains Function procedure as
Excel Add-In extension (*xlam).
2. Open a workbook ➪ Excel Options ➪ Add-Ins ➪ Go … ➪
Browse … ➪ Choose the file in the first step.
3. Now you can use all the functions in a new workbook.

29
6. Advanced Input Boxes

▪ Simple version of InputBox function:

InputBox(prompt,[title],[default])
▪ Prompt: Required. The text displayed in the input box.
▪ Title: Optional. The caption displayed in the title bar of the input
box. If you omit it, “Microsoft Excel” is displayed.
▪ Default: Optional. The default value to be returned by the function if
the user enters nothing.

30
6. Advanced Input Boxes

▪ Typical use of InputBox function:

Sub InputBox1()
Dim x As Variant
x = InputBox("Please enter something:")
MsgBox ("You entered " & x & "!")
End Sub

▪ When x is dimmed as a variant, if user just presss cancel or okay as

it is, it returns an empty quotations and is going to be a variant or a
string ➪ Use as input validation!

31
6. Advanced Input Boxes

▪ Example: Create a sub to which as user to enter the amount of

deposit. The default value is 1,000, and if user doesn’t enter
anything, it shows the message “You didn’t enter anything, please
try again!”.

Sub InputBox2()
Dim deposit As Variant
Do
deposit = InputBox("Please enter deposit amount:",
"Loan's Inputs", 1000)
If deposit <> "" Then Exit Do
MsgBox ("You didn't enter anything, please try again!")
Loop
MsgBox ("You entered " & deposit & "!")
End Sub
32
6. Advanced Input Boxes

▪ However, if we want user to unter an integer, we must dim deposit

as integer. Then if they press cancel or okay, then there's a type
mismatch because the return of empty is not an integer, and so we
have an error.

Sub InputBox3()
Dim deposit As Integer
Do
deposit = InputBox("Please enter deposit amount:",
"Loan's Inputs", 1000)
If deposit <> "" Then Exit Do
MsgBox ("You didn't enter anything, please try again!")
Loop
MsgBox ("You entered " & deposit & "!")
End Sub
33
6. Advanced Input Boxes

▪ The Excel InputBox method offers some advantages over InputBox

function:
▪ You can specify the data type returned (it doesn’t have to be
a String).

▪ Input validation is performed automatically.

▪ Simple version of InputBox method:
Application.InputBox(prompt,[title],[default],[type])
▪ Prompt, title and default: Same as InputBox function
▪ Type: A code for the data type returned, as listed in the next table. If
this argument is omitted, the dialog box returns text.
34
6. Advanced Input Boxes

▪ To allow more than one data type to be returned, use the sum of the
pertinent codes.
▪ For example, to display an input box that can accept text or
numbers, set type equal to 3 (that is, 1 + 2)

35
6. Advanced Input Boxes

▪ Example: Create a sub to which as user to enter the amount of deposit. The
default value is 1,000, and if user doesn’t enter anything, it shows the
message “You didn’t enter anything, please try again!”.
▪ For the Application.InputBox method, if you press cancel, the variable
(deposit) has been given a value of false.

Sub InputBox4()
Dim deposit As Variant
Do
deposit = Application.InputBox("Please enter deposit amount:",
"Loan's Inputs", 1000, Type:=1)
If deposit <> False Then Exit Do
MsgBox ("You didn't enter anything, please try again!")
Loop
MsgBox "You entered " & deposit & "!"
End Sub
36
7. Advanced Message Boxes

▪ The MsgBox function displays a message in a dialog box.

MsgBox(prompt,[buttons],[title])
▪ Prompt: Required. The message displayed in the pop-up display.
▪ Buttons: Optional. A value that specifies which buttons and which
icons, if any, appear in the message box. See the next table.
▪ Title: Optional. The text that appears in the message box’s title bar.
The default is Microsoft Excel.

37
7. Advanced Message Boxes

38
7. Advanced Message Boxes

▪ To get a response from a message box, you can assign the results of the
MsgBox function to a variable. In this situation, the arguments must be in
parentheses.
▪ In the following code, I use some built-in constants (described in the next
table) to make it easier to work with the values returned by MsgBox:
Sub MsgBox1()
Dim Ans As Integer
Ans = MsgBox("Continue?", vbYesNo)
Select Case Ans
Case vbYes
MsgBox "You clicked Yes!"
Case vbNo
MsgBox "You clicked No!"
End Select
End Sub
39
7. Advanced Message Boxes

▪ The following procedure is another way of working with the values returned by
MsgBox :

Sub MsgBox2()
If MsgBox("Continue?", vbYesNo) = vbYes Then
MsgBox "You clicked Yes!"
Else
MsgBox "You clicked No!"
End If
End Sub 40
8. Introduction to User Forms

▪ A custom dialog box is created on a UserForm, and you access UserForms in

Visual Basic Editor (VBE). Following is the typical sequence that you’ll
follow when you create a UserForm:
▪ Step 1. Insert a new UserForm into your workbook’s VB Project.

▪ Step 2. Add controls to the UserForm.

▪ Step 3. Adjust some of the properties of the controls that you added.

▪ Step 4. Write event-handler procedures for the controls.

▪ These procedures, which are located in the code window for the UserForm,
are executed when various events (such as a button click) occur.

41
8. Introduction to User Forms

▪ Step 5. Write a procedure that will display the UserForm.

▪ This procedure will be located in a VBA module (not in the code module for
the UserForm).
▪ You can add a button to a worksheet, create a shortcut menu command, and
so on.

42
8. Introduction to User Forms – Step 1. Add a New UserForm

▪ To insert a new UserForm, activate VBE (press Alt+F11), select your

workbook’s project from the Project window, and then choose Insert ➪
UserForm. UserForms have default names: UserForm1, UserForm2, and so
on.

43
8. Introduction to User Forms – Step 2. Adding Controls to a UserForm

▪ To add controls to a UserForm, use the Toolbox, as shown in Figure 11-2.

(VBE doesn’t have menu commands that add controls.) If the Toolbox isn’t
displayed, choose View ➪ Toolbox. The Toolbox is a floating window, so you
can move it to a convenient location.

▪ Click the Toolbox button that corresponds to the control that you want to
add and then click inside the dialog box to create the control (using its
default size). Or you can click the control and then drag in the dialog box to
specify the dimensions for the control. 44
8. Introduction to User Forms – Step 2. Adding Controls to a UserForm

▪ Renaming all the controls that you’ll be manipulating with your VBA code is
a good idea. Doing so lets you refer to meaningful names (such as
ProductListBox) rather than generic names (such as ListBox1). To change
the name of a control, use the Properties window in VBE. Just select the
object and change the Name property.

45
8. Introduction to User Forms – Step 2. Adding Controls to a UserForm

▪ Some common controls from toolbox:

▪ Label: A Label control simply displays text in your dialog box.
▪ CommandButton: Every dialog box that you create will probably
have at least one CommandButton control. Usually, your UserForms
will have one CommandButton labeled OK and another labeled
Cancel.
▪ TextBox: A TextBox control lets the user type text or a value.

46
8. Introduction to User Forms – Step 2. Adding Controls to a UserForm

▪ Some common controls from toolbox:

▪ OptionButton: OptionButton controls are useful when the user needs
to select one item from a small number of choices. OptionButtons
are always used in groups of at least two. When one OptionButton is
selected, the other OptionButtons in its group are deselected.
▪ CheckBox: A CheckBox control is useful for getting a binary choice:
yes or no, true or false, on or off, and so on. When a CheckBox is
checked, it has a value of True; when it’s not checked, the CheckBox
value is False.
▪ Image: You can use an Image control to display a graphic image,
which can come from a file or can be pasted from the Clipboard. The
graphics image is stored in the workbook.

47
8. Introduction to User Forms – Step 3. Adjust some of the properties

▪ In VBE, the Properties window adjusts to display the properties of the

selected item (which can be a control or the UserForm itself). In addition,
you can select a control from the drop-down list at the top of the Properties
window.
▪ Although each control has its own unique set of properties, many controls
have some common properties. For example, every control has a Name
property.
▪ Renaming all the controls that you’ll be manipulating with your VBA code is
a good idea. Doing so lets you refer to meaningful names (such as
ProductListBox) rather than generic names (such as ListBox1). To change
the name of a control, use the Properties window in VBE. Just select the
object and change the Name property.

48
8. Introduction to User Forms – Step 3. Adjust some of the properties

49
8. Introduction to User Forms – Practice

▪ Practice 10.6: Create a user form

named form_saving to calculate
future value as shown in the figure.
Make sure that you set name for
every control.

50
8. Introduction to User Forms – Step 4. Write event-handler
procedures

▪ Each UserForm control (as well as the UserForm itself) is designed to

respond to certain types of events, and a user or Excel can trigger these
events. For example, clicking a CommandButton generates a Click event for
the CommandButton ➪ You can write code that is executed when a
particular event occurs.
▪ To find out which events are supported by a particular control, do the
following:

▪ 1. Add a control to a UserForm.

▪ 2. Double-click the control to activate the code module for the

UserForm. VBE inserts an empty event-handler procedure for the
default event for the control.
51
8. Introduction to User Forms – Step 4. Write event-handler
procedures

▪ To find out which events are supported by a particular control, do the

following:
▪ 3. Click the drop-down list in the upper-right corner of the module
window. You see a complete list of events for the control. The next
figure shows the list of events for a CheckBox control.

▪ 4. Select an event from the list. VBE creates an empty event-handler

procedure for you.

52
8. Introduction to User Forms – Step 4. Write event-handler
procedures

53
8. Introduction to User Forms – Step 4. Write event-handler
procedures

▪ Example: Create event-

handler procedures associated
with Reset and Quit buttons
that can be used to reset and
quit (hide) the user form
Saving Calculator.

54
8. Introduction to User Forms – Step 4. Write event-handler
procedures
▪ How to hide a user form: You create a procedure that uses the Hide
method of the UserForm object.
▪ For example, to hide user form form_saving, the following
procedure hide the dialog box on that form:

Private Sub quit_Click()

form_saving.Hide
End Sub

55
8. Introduction to User Forms – Step 4. Write event-handler
procedures

▪ How to reset a user form: You create a procedure that uses the
Unload command combined with Show method. When a UserForm
is unloaded, its controls are reset to their original values.
▪ For example, to reset user form form_saving, the following
procedure resets the values on the user form:

Private Sub reset_Click()

Unload form_saving
form_saving.Show
End Sub

56
8. Introduction to User Forms – Step 4. Write event-handler
procedures

▪ Dim or do not dim variable in a user form?

▪ You don't have to dim variables on a user form (for example,
a text box on user form) because it’s thinking that is a new
variable inside the private sub.

▪ However, if you have new variables other than those on a

user form, then you would need to dim those variables.

57
8. Introduction to User Forms – Step 4. Write event-handler
procedures – Practice

▪ Practice 10.7. Create an event

handler procedure so that when
the Calculate button is clicked, it
returns the future value based
on the user’s inputs.

58
8. Introduction to User Forms – Step 5. Display User Form
▪ How to display a user form: You create a procedure that uses the
Show method of the UserForm object. After that, you can asign the
procedure to a button in Excel.
▪ For example, to display user form form_saving, the following
procedure displays the dialog box on that form:

Sub display_form_saving()
form_saving.Show
End Sub

▪ Note: This procedure must be located in a standard VBA module

and not in the code module for the UserForm.
59
8. Introduction to User Forms – Step 5. Display User Form
▪ How to reuse a user form: Start by creating a UserForm that
contains all the controls and customizations that you’d need to
reuse in other projects. Then make sure that the UserForm is
selected and choose File ➪ Export File (or press Ctrl+E). You’ll be
prompted for a filename.
▪ Then, when you start your next project, choose File ➪ Import File to
load the saved UserForm.

60
8. Introduction to User Forms – Practice

▪ Practice 10.8. Create the user form

form_PIT used to calculate personal
income tax based on user’s inputs.

61