Write Custom Functions in Excel.

Hi,

We all know that we can write Formulas in Excel. for example, =Sum(A1:A10) to sum the values present in range from A1 to A10. The summation is easy. Right??? But what if we want to tweak our requirements.

Let's assume we have a requirement, that we need to sum only those values which are divisible by 2. I have assumed a very basic example, although we can write an Excel Built in for this requirement, but I want to show to achieve this through Custom Function.

For example, I want to sum range A1 to A10. I will write a custom Function in Excel VBA. Open VBA editor, add Module 1 and add a function like following :

Public Function SumEvenValues(rng As Range) As Double
    Dim sum As Double
    Dim cell_ As Range
    
    sum = 0
    
    For Each cell_ In rng.Cells
        If (IsNumeric(cell_.Value)) Then
            'the value is numeric. Check the mod
            If (cell_.Value Mod 2 = 0) Then
                sum = sum + cell_.Value
            End If
        End If
    Next cell_
    
    SumEvenValues = sum
    
End Function

In your excel workbook, in Cell b1, type this : =SumEvenValues(A1:A10)

You will see that all the values which are divisible by 2 are summed up and the result is displayed in Cell B1. I have taken a very basic example to show it's use, which can be easily achived in Excel, but we may encounter to include those requirements which we may not be able to achieve within Excel only. So in such a case, VBA is used to handle all those requirements which Excel cannot handle.

HTH,
Vikas