Custom Function

[mouse88]

I want to write a custom function which takes a range as a parameter such as the sum function:

SUM(A1, C56)

Do I just use a range as a parameter?

 

[anandvarma]

try this

Excel Workbook
	A	B	C	D
1	1			4
2	1
3	1
Sheet1

in the name manager put the name as suma1toc56
and put the following formula

=SUM(OFFSET(Sheet1!$A$1,,,56,3))

 

[mouse88]

Sorry don't think you understood.

I am writing a custom function and want to pass several ranges as parameters but dont know how.

This is what I have tried so far:

Public Function SUMIFNOTERROR(Cells() As Range) As Variant
Dim ReturnValue As Date
Dim Cell As Range
ReturnValue = TimeValue("00:00:00")
For Each Cell In Cells
    If Cell.Value <> "#N/A" Or Cell.Value <> "#DIV/0" Or Cell.Value <> "#REF!" Then
        ReturnValue = ReturnValue + Cell.Value
    End If
Next Cell
SUMIFNOTERROR = TimeValue(ReturnValue)
End Function

I want to be able to pass in several cells so I would use the formula like this:

=SUMIFNOTERROR(B2:B5)

With the current code I get a #VALUE! error. Im not sure if this is to do with any of the data types or because of the parameters in my function.

 

[mouse88]

Ok im getting somewhere with this now.

My question now is:

If I have a parameter in my function (Values As Variant), how can I pass an array of values to the function in a formula?