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
• Calling a Function
• Sub-Procedure Definition
• Calling a 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.

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 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

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 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

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.