Chapter 4 – Functions and Sub-Procedures
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.
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.
- 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 Scope FunctionName (parameter list) as type ‘Function procedure code is listed here FunctionName = Return value End Function
Function AddNumbers(iNum1 As Integer, iNum2 As Integer) As Integer Dim iSum As Integer iSum = iNum1 + iNum2 AddNumbers = iSum End 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 Scope SubName (parameter list) ‘Sub procedure code is listed here End Sub
Sub AddNumbers(iNum1 As Integer, iNum2 As Integer) Dim iSum As Integer iSum = iNum1 + iNum2 MsgBox iSum End Sub
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.