PDA

View Full Version : Pass Range param from custom function



SVICER
03-26-2012, 02:39 AM
Hi
I have function:
Function MyOffset(r) As Range
With ActiveSheet
Set MyOffset = Range(r, r.Offset(10, 0))
End With
End Function

And I want to write in Excel (for example):
SUM(MyOffset(A1))
or
AVARAGE(MyOffset(A1))
and receive the answer, but I receive the error: #NAME?

Why? What's wrong?
Thanks

jwilder1
03-26-2012, 03:57 AM
Works fine in my workbook, other than Average being misspelled. Did you enter the formulas with an "="?

Aflatoon
03-26-2012, 04:30 AM
You must also have macros enabled, the code must be in the same workbook (otherwise you need to specify the workbook name in the function call) and it must be in a normal module, not a worksheet/ThisWorkbook module.

Paul_Hossler
03-27-2012, 10:47 AM
possibly simpler


Option Explicit
Function MyOffset(r As Range) As Range
Set MyOffset = r.Resize(10, 1)
End Function
Sub test()
MsgBox MyOffset(Range("A1")).Address
MsgBox MyOffset(Range("Z10")).Address
MsgBox MyOffset(Range("AA100")).Address
End Sub



You could make it a little more general if you wanted


Function MyOffset_1(r As Range, Optional nCols As Long = 1, Optional nRows As Long = 10) As Range
Set MyOffset = r.Cells(1, 1).Resize(nRows, nCols)
End Function


I added the .Cells() to be sure to key off the first cell in the range

Paul

Aflatoon
03-27-2012, 02:52 PM
Simpler still:
=SUM(Offset(A1,0,0,10,1))
sans VBA. ;)

If you are going to use VBA, you'll need to make the UDF volatile.