How to create a UDF Function?

[sangeesekar]

I just wanted to know that how to create a UDF. I have read some blogs and books. Every one described a simple example. I just wanted to know how to create a sum function using Range(by selecting range). Ofcourse please don't ask excel has this function already. why do you want. Just for the sake of understanding the code.

Thanks.: pray2:

Please help this. This would be helpful for me to perform any operation using Ranges.

[SamT]

PublicFunction MySum(InputRange As Range) As Long
'Only handles Long numbers
'Range must be a single column selection
Dim X As Long
Dim j As Long
For j = 1 to InputRange.Cells.Count
X = X + InputRange.Cells(j).Value
Next j
MySum = X
End Function

Paste it in a standard module

[sangeesekar]

Thanks Sam. :hi:

[mdmackillop]

Please post your multiplication UDF

[sangeesekar]

Function MyProd(Rng As Range) As Long
Dim A As Long
A = 1
Dim B As Long
For B = 1 To Rng.Cells.Count
A = A * Rng.Cells(B).Value
Next B
MyProd = A
End Function

[sangeesekar]

It is working

[sangeesekar]

Hi anyone please give me one example code and explain me about option explicit and implicit?

[Bob Phillips]

There is no such thing as option implicit (it is implicit). Example of what?

[sangeesekar]

Example of code?

[Bob Phillips]

Code that does what? WIthout a clear explanation we could give you anything, do you want some code that shows how to do a Monte Carlo simulatioin (I think not), how to select a cell (I think not). Be specific.

[Paul_Hossler]

As your UDFs get more elaborate and complex:

1. You don't need to restrict it to only Longs if you don's want to

2. You'll probably need to include Error Checking logig. For example, if there is text or something other that numbers in your input range, you'll get a #VALUE error. I just added a test using IsNumeric.





'see on line help for good explanation
'always use it
Option Explicit
Function MyProd(Rng As Range) As Double
Dim dRunningTotal As Double
Dim iCells As Long

dRunningTotal = 1
For iCells = 1 To Rng.Cells.Count
If IsNumeric(Rng.Cells(iCells).Value) Then
dRunningTotal = dRunningTotal * Rng.Cells(iCells).Value
End If
Next iCells

MyProd = dRunningTotal
End Function


Paul