User Defined Function

[Gunnar]

When I try to use a UDF I get a #NAME error.
The function I tried with is:

Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

When I enter =SHEETOFFSET(1,A1) in any cell, it displays #NAME

I have since found this happens with all Functions.

What am I doing wrong? Or not doing what I should?



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

There are a couple of things that come to mind:

#1. If the code is in the same workbook, is the function in a General
module--not behind a worksheet, ThisWorkbook?

#2. Do you have macros enabled?

#3. Is that UDF in the same workbook or another workbook?

#4. If it's in another workbook, is that workbook open?

#5. And if that other workbook is a .xls, you'll either need a reference
to that project (inside the VBE) or enter the formula like:
=book1.xls!sheetoffset(1,a1)

#6. If you're using xl2002+, any chance that excel saw this workbook as
corrupted and asked if you wanted to disable it (and you answered yes)?
Help|About Microsoft Excel
click on the Disabled Items button and reenable it
(If it was really corrupted, you may have to restore from backups.)

 

[g forsberg]

Yep,

I had to put it in a new module. But why can't I use a work sheet or
ThisWorkbook

Many thanks

Gunnar

 

[Dave Hawley]

Hi Gunnar

ThisWorkbook is a Private Module of the Workbook Object and is used for
Events. Each Sheet also has it's own Private Module and is used to Sheet
Events.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Frank Kabel]

Hi
these modules are just not designed for storing user
defined functions but are mainly dedicated for event
procdures.

 

[Dave Peterson]

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

Those worksheet modules are also used to hold code for controls from the control
toolbox toolbar that are placed on the worksheet.