How to call a function from a sub routine in Excel VBA. Watch the video below:
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:
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)
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.