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
SumEvenValues = sum
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.