Calling Function from Sub

How to call a function from a sub routine in Excel VBA. Watch the video below:

How to call a function from a sub-routine

Today we will learn how to call a function from a sub routine or procedure. The complete VBA code for the sub-routine is given below:

Sub CallerSub()

Dim J As Long
Dim MyRange As Range
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range(“N:N”))
‘ If you wish to see the return value in a message use the next line of code. Remove the apostrophe.
‘MsgBox Average_of_J_Largest_Values(MyRange, J)
‘To get the data in an appropriate cell use the code below
Cells(lastrow, 14).Offset(0, 1) = Average_of_J_Largest_Values(MyRange, J)

End Sub

So instead of entering the function in a cell and getting the result, we have automated the process. The function is also in a module in the same workbook.

How to call a function from a sub routine in Excel VBA
How to call a function from a sub routine in Excel VBA

Published by

Dinesh Kumar Takyar

Welcome to! 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: For a structured Excel VBA training course online you can visit:

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.