I am having similar problems as described in the original question. I am
trying to create a UDF that runs on one worksheet and internal to the UDF it
collects values from another worksheet. I can make it work when everything
is on one worksheet. But, as soon as I introduce a reference to another
worksheet, the UDF has no result and returns #Value.

For example: I have:

Public Function MyFunction(iAIndex as Integer, iBIndex as Integer) as Variant

Dim a
Dim b

Set a = ThisWorkbook.Worksheets("MyFirstSheet").Range("The ARange") '
TheARange is a 1xN range
Set b = ThisWorkbook.Worksheets("MyReferenceSheet").Range( "TheBRange") '
TheBRange is a 1xM range

MyFunction = TheARange(1, iAIndex) * TheBRange(1,iBIndex)

End Function

Basically, what I have been able to figure out is that as long as the
function is calling a value from the worksheet where it is used, this works.
But, if it tries to touch another worksheet it is broke. Is this a
limitation on the UDF method or am I missing something?

Your help is greatly appreciated.

Best regards,
Mark Bower

"Tom Ogilvy" wrote:

Make sure the code is in a general module, not a sheet module, the
thisworkbook module or a userform module. in the VBE, Insert=Module. Put
it in that.

--
Regards,
Tom Ogilvy

"nougain" wrote in
message ...

Compilation error disappear, but when I use ss_weekly function it shows
#NAME? in my sheet's cell. I don't know where I should focus to correct
it. Here is the updated VBA code:

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
' ss: Schedule Slippage
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
Function ss_weekly(endDate As Date) As Double
' Data range
Dim dateRange As range
Dim dataRange As range

'CSR is sheet name
Set dateRange = Sheets("CSR").range("O20:O351")
Set dataRange = Sheets("CSR").range("AA20:AA351")

' Local variables to compute the average
Dim sumDataForAllDatesLessThanGivenDate As Double
Dim sumDataForAllDatesLessThanOneWeekBack As Double
Dim countDataForAllDatesLessThanGivenDate As Integer
Dim countDataForAllDatesLessThanOneWeekBack As Integer
Dim sumData As Double
Dim countData As Integer

' Input endDate is expected to be a Friday date. Therefor adding 2
days to get the Sunday and
' subtracting 4 to get the last Monday
sumDataForAllDatesLessThanGivenDate = Application.SUMIF(dateRange,
"<=" & (endDate + 2), dataRange) 'On Sunday
sumDataForAllDatesLessThanOneWeekBack =
Application.SUMIF(dateRange, "<" & (endDate - 4), dataRange) 'On
just passed Monday
countDataForAllDatesLessThanGivenDate =
Application.CountIf(dateRange, "<=" & (endDate + 2))
countDataForAllDatesLessThanOneWeekBack =
Application.CountIf(dateRange, "<" & (endDate - 4))

' Slippage
sumData = sumDataForAllDatesLessThanGivenDate -
sumDataForAllDatesLessThanOneWeekBack
countData = countDataForAllDatesLessThanGivenDate -
countDataForAllDatesLessThanOneWeekBack

If countData <= 0 Then
ss_weekly = 0
Else
ss_weekly = sumData / countData
End If
End Function


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826