How to create a function and call it from within a procedure or macro. Instead of writing a long subroutine procedure or macro, we can divide the VBA procedure into a subroutine or macro and call the created function macro from within the procedure. This can make the maintenance of the procedure easy. A function declaration is similar to a sub declaration. We use the word ‘Function’ followed by the name of the function with the type of data it will return. Therefore, a function is nothing but a subroutine or procedure that returns a value. For example:
Function myDate () as String
mydate = Format (date, “ddd mm yyyy”)
End Function
Now let’s call this function from a subroutine or macro:
Sub enterdate()
Range(“A2”) = myDate(Now)
End Sub
If you run the macro ‘enterdate’ it will display today’s date in Range(“A2”) of your active worksheet like this 04-Jan-18, for example. You notice also that in the function declaration myDate there is nothing inside the brackets. We express this situation by saying the function requires no ‘parameters’. However if you write the function as follows, then you need to supply a parameter as a date:
Function myDate (dateToformat As Date) as String
mydate = Format (dateToFormat, “ddd mm yyyy”)
End Function
Now you can use the same procedure as above to call the function:
Sub enterdate()
Range(“A2”) = myDate(Now)
End Sub
You could also call the function using the following procedure:
Sub enterdate()
Range(“A2”) = myDate(#05/20/2017#)
End Sub
The date displayed in Range(“A2”) would now be 20-May-17.
In the above function we could also display the time value along with the date by adding another parameter like this:
Function myDate (dateToformat As Date, myTime As Boolean) as String
myDate = Format(dateToFormat, “dd mmm yyyy hh:mm:ss”)
End Function
Now we could call this function from within a procedure like this:
Sub enterDate3()
Range(“A2”) = myDate(Now, True)
End Sub
The result of running the above procedure is today’s date followed by the time like this, for example, in Range(“A2”):
04-01-2018 7:27:27 AM
If instead of the Boolean value ‘True’ we would write ‘False’ then we would get the date only.
We can also make the second parameter ‘optional’ by writing the word ‘optional’ before the parameter like this:
Function myDate (dateToformat As Date, Optional myTime As Boolean) as String
myDate = Format(dateToFormat, “dd mmm yyyy hh:mm:ss”)
End Function
Note: The ‘optional’ parameter always comes after the first required parameter.
The Optional parameter need not be used and this is how the situation would look when you write the code in the calling procedure:

The required parameter is the first parameter shown in bold. The second parameter, if optional, need not be entered in the procedure and it is indicated by the rectangular brackets.
Watch the video below to learn how to create an interesting function to assign grades to students and call it from within a subroutine or procedure:
Watch this video on Youtube.
Here’s the complete accompanying code:
Function grade(totMarks As Single) As String
If totMarks > 90 Then
grade = “A”
ElseIf totMarks > 80 Then
grade = “B”
ElseIf totMarks > 70 Then
grade = “C”
ElseIf totMarks > 60 Then
grade = “D”
ElseIf totMarks > 50 Then
grade = “D”
ElseIf totMarks > 40 Then
grade = “Pass”
Else
grade = “Fail”
End If
End Function
Sub assignGrades()
Sheet2.Activate
Range(“A2”).Select
Do While ActiveCell.Value <> “”
‘totMarks = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(0, 7).Value = grade(ActiveCell.Offset(0, 6).Value)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub assignGrades2()
Dim percentageMarks As Single
Sheet2.Activate
Range(“A2”).Select
Do Until ActiveCell.Value = “”
percentageMarks = ActiveCell.Offset(0, 6).Value
If percentageMarks > 90 Then
ActiveCell.Offset(0, 9).Value = “A”
ElseIf percentageMarks > 80 Then
ActiveCell.Offset(0, 9).Value = “B”
ElseIf percentageMarks > 70 Then
ActiveCell.Offset(0, 9).Value = “C”
ElseIf percentageMarks > 60 Then
ActiveCell.Offset(0, 9).Value = “D”
ElseIf percentageMarks > 50 Then
ActiveCell.Offset(0, 9).Value = “E”
ElseIf percentageMarks > 40 Then
ActiveCell.Offset(0, 9).Value = “Pass”
Else
ActiveCell.Offset(0, 9).Value = “Fail”
End If
ActiveCell.Offset(1, 0).Select
‘Range(“A9”).Select
Loop
End Sub
Further reading:
Hello Sir,
I want the data (Vessel Name) will be filtered automatically as per cell Date (04-Dec-2017) based on Data sheet.
Data Sheet
Vessel Name Arrival Date Departure Date
Vessel-1 01-Dec-2017 04-Dec-2017
Vessel-2 01-Dec-2017 02-Dec-2017
Vessel-3 01-Dec-2017 04-Dec-2017
Vessel-4 01-Dec-2017 08-Dec-2017
Vessel-5 02-Dec-2017 03-Dec-2017
Vessel-6 02-Dec-2017 03-Dec-2017
Vessel-7 02-Dec-2017 05-Dec-2017
Vessel-8 02-Dec-2017 02-Dec-2017
Report Sheet
Available vessels on the Date of 04-Dec-2017
Vessel Name
Vessel-1
Vessel-3
Vessel-4
Vessel-7
Thank you in advance for all who helps and suggest.