In this tutorial we will provide an overview of the topic of VBA Functions and User-defined Functions (UDF). We will mention the practices in approaching macros without arguments, functions with one argument and functions with two arguments. We will see some examples of functions with each of these scenarios to help you understand how to work with them.
The example that contains two arguments, one of them will be optional. In case of optional arguments, I will show you how to evaluate whether the argument is entered or not, using the VBA.IsMissing function.
VBA Functions
Let’s review some topics on using functions in VBA for Excel. First, we need to provide a definition of a function.
A function is a procedure that will take arguments and return a value or an array of values.
Functions can be called from procedures or from cells.
There are functions without arguments such as TODAY or NOW.
Public functions are available to all procedures in the file and for use in cells.
Private functions are only available in procedures of the same module.
Custom functions UDF (User Defined Function)
A custom function can be public or private. If it is public we can invoke it from a cell in Excel, but if it is private, it can only be called from procedures.
These functions can be found in the Insert Function dialog box, in the User Defined category and we can have a graphical interface to insert the arguments.
When we enter the equals sign “=” in a cell, we will be able to visualize the UDF, as long as the file or add-in that contains them is open. Excel has more than 450 functions, plus the ones you develop.
Function without arguments
I share with you 3 examples of UDF functions where we do not require arguments to return a result. In the first one we return the name of the active sheet, in the next one the Excel version and in the last one we show the Excel user, which you can find in the Excel Options.
Option Explicit
Function SheetName()
Application.Volatile
SheetName = ActiveSheet.Name
End Function
Function Version()
Application.Volatile
Version = Application.Version
End Function
Function User()
Application.Volatile
User = Application.UserName
End Function
Function with one argument
In this function value we are going to have as argument the Sales value and depending on the quantity, we are going to return a discount. We will use the If-Then-Else statement to evaluate the quantities and MsgBox to show a well elaborated message.
Option Explicit
Function Discount(Sales)
Application.Volatile
If Sales < 10 Then
Discount = 0
ElseIf Sales < 20 Then
Discount = 0.1
Else
Discount = 0.2
End If
End Function
Sub CalculateDiscount()
Dim SalesValue As Integer
Dim Message As String
SalesValue = InputBox(“Enter sales”, “Sales”)
If SalesValue = 0 Then Exit Sub
Message = “Sales are: ” & vbTab & SalesValue
Message = Message & vbNewLine & “The discount is:” & vbTab & SalesValue
Message = Message & vbTab & VBA.Format(Discount(SalesValue), “0%”)
MsgBox Message, vbInformation, “EXCELeINFO”
End Sub
Function with two arguments. One optional
The following function will be valid only for use in an Excel cell. We will achieve that if the user enters the value 1, the entered text will be converted to UPPERCASE, if we enter 2, to lowercase, and if the Type parameter is not entered, the text will be returned as is. The Type parameter is optional, so we will evaluate with VBA.IsMissing if the parameter is entered or not.
Option Explicit
Function CText(Text As String, Optional Type As Variant)
If VBA.IsMissing(Type) Then
Type = 0
CText = Text
Else
Select Case Type
Case 1
CText = VBA.UCase(Text)
Case 2
CText = VBA.LCase(Text)
Case Else
CText = VBA.CVErr(xlErrValue)
End Select
End If
End Function