Thursday, July 1, 2010

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

2 comments:

JP said...

You can use an array formula for this:

{=SUM(IF(MOD(A1:A10,2)=0,A1:A10,0))}

Vikas Bhandari said...

Hi,

Oh yeah, we can write an array formula. That I already knew, and if you see, I have mentioned that you can write an Excel Built in formula for this requirement. But there may be some requirements, where we cannot realistically use the Excel Formula. I took this example not because we don't have any alternative, rather, this example is an easy to understand. I had intentionally opted for a simple example.

Thanks,
Vikas