How to create user defined or custom worksheet functions that can simplify our formulas. Watch the video below:
Let’s talk about functions in VBA. Most of the time we have been learning about subroutines and procedures and less about functions. We have created a few functions though like compound interest calculations.
How can find the average for the top three values from our Excel worksheet data? The easiest way without a function is to find out the three largest value one by one using the LARGE Excel function, add the values and finally divide the total value by the number 3 in this case to get the average of the top three values. Can we automate the process? Yes!
We will now create a function in a module because a VBA function is also a procedure that is stored in a VBA module. However, instead of the keyword ‘sub’ we use the keyword ‘function’. Below is the VBA code for the function:
Function Average_of_J_Largest_Values(DataRange, J)
‘ Will return the average of the highest N values in the named range DataRange
Dim Sum As Long, i As Long
Sum = 0
For i = 1 To J
Sum = Sum + WorksheetFunction.Large(DataRange, i)
Average_of_J_Largest_Values = Sum / J
How do we use the function? In any worksheet cell we write ‘=Average_of_J_Largest_Values’ and press enter. In fact, when you start typing the function, it will be offered as a choice in a standard manner. The major advantage of using such an approach is to make things simpler and much less error prone.
We can also call this function from another subroutine. We learn about this in a later training video.
We can use the insert function option as shown in the image below: