Create Function and Call it

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:

optional-parameter-in-procedure
optional parameter in 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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Create Function and Call it”

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

Leave a Reply

Your email address will not be published. Required fields are marked *