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:

Compound Interest Calculation in Excel

User Defined Function