Chapter 4 – Functions and Sub-Procedures
In this chapter, we will understand definition of Functions and Sub-Procedures. Also we will learn how to call a Function and Sub-Procedure.
Function Definition
What is a Function or Sub-Procedure?
In VBA, a set of commands written to perform specific task is called a Function or Sub-Procedure (also known as Subroutine). While the objective of Function and Sub-Procedure is same however there is small difference them. A function may return a result however Sub-Procedure does not.
What is a Parameter?
Both Function and Sub-Procedure can accepts arguments (also known as parameters). Parameter is a way to pass information to Function and Sub-Procedure. For example, if you want to add two numbers then you can pass those numbers as parameters.
Valid Function or Sub-Procedure Name
- Name of the Function or Sub-Procedure must begin with a letter
- Name cannot be more than 255 character long
- Function or Sub-Procedure name cannot contain space and periods, you can use underscore (_) in place of space and periods
- Function or Sub-Procedure names in VBA are non-case sensitive that means you cannot declare two Function or Sub-Procedure with names AddNumbers and ADDNUMBERS
Function Definition:
Function Scope FunctionName (parameter list) as type
‘Function procedure code is listed here
FunctionName = Return value
End Function
Sample Function:
Function AddNumbers(iNum1 As Integer, iNum2 As Integer) As Integer
Dim iSum As Integer
iSum = iNum1 + iNum2
AddNumbers = iSum
End Function
Calling a Function
myVar = FunctionName(parameter list)
Example 1: Simple Function
Function Add30Days()
Dim dteDate As Date
'Set the variable to today's date
dteDate = Date
'Add 30 days
dteDate = dteDate + 30
MsgBox (dteDate)
End Function
Example 2: Function with Parameters
Sub Main()
'Call function
Call AddDays(Date, 100)
End Sub
Function AddDays(dteDate As Date, iDays As Integer)
Dim dteNewDate As Date
dteNewDate = dteDate + iDays
MsgBox (dteNewDate)
End Function
Explanation: Here we are calling AddDays function from Main Sub-Procedure. We have supplied today’s date as first parameter and number of days to be added as second parameter. The function adds given days in the date and show the result in message box.
Example 3: Function with Return
Sub Main()
Dim dteFinal As Date
'Call function as store returned value in the variable
dteFinal = AddDays(Date, 100)
MsgBox dteFinal
End Sub
Function AddDays(dteDate As Date, iDays As Integer) As Date
Dim dteNewDate As Date
dteNewDate = dteDate + iDays
'Return new date
AddDays = dteNewDate
End Function
Explanation: Here we are calling AddDays function from Main Sub-Procedure. We have supplied today’s date as first parameter and number of days to be added as second parameter. The function adds given days in the date and returns the new date to the Main Sub-Procedure. The returned value is stored in dteFinal date variable in Main Sub-Procedure and finally shown in message box.
Sub-Procedure Definition
Sub-Procedure Definition:
Sub Scope SubName (parameter list)
‘Sub procedure code is listed here
End Sub
Sample Sub-Procedure:
Sub AddNumbers(iNum1 As Integer, iNum2 As Integer)
Dim iSum As Integer
iSum = iNum1 + iNum2
MsgBox iSum
End Sub
Calling a Sub-Procedure
SubName(parameter list)
Example 1: Simple Sub-Procedure
Sub GetUserName()
Dim strFirstName As String
Dim strLastName As String
Dim strFullName As String
strFirstName = "Kailash"
strLastName = "Sharma"
strFullName = strFirstName & " " & strLastName
MsgBox strFullName
End Sub
Example 2: Sub-Procedure with Parameters
Sub Main()
'Call Subroutine
Call GetUserName("Kailash", "Sharma")
End Sub
Sub GetUserName(strFirstName As String, strLastName As String)
Dim strFullName As String
strFullName = strFirstName & " " & strLastName
MsgBox strFullName
End Sub
Explanation: Here we are calling GetUserName Sub-Procedure from Main Sub-Procedure. We have supplied two string parameters to the Sub-Procedure. The Sub-Procedure concatenates both strings along with a space in-between and shows the result in message box.