UDF only works when focus is on a specific sheet - Help!!

[Andibevan]

Hi All,

I have been struggling with the following UDF that I keep nearly get working
but then it gives up.

It is a stand-alone UDF that needs no variables as it determines the date it
should work on from the 2nd row of the column it is called from, and the
name to look up from column A of the row it is called from.

The problem is that it works fine when calculate is hit (F9) from one
worksheet but it only generates #REF! when called (or forced to calculate
using F9) from another.

I am completely stumped:-

Function HolAvail()

Application.Volatile

On Error GoTo Err:

Dim Var_Name As Range 'Location of Name

Dim Var_Date As Range 'Location of Date

Dim Var_Name_Row As String 'Name row

Dim Var_Date_Column As String 'Date Column

Dim Var_SheetZZ As String ' as worksheets?

Dim STR_RNG_Date As String

Dim STR_RNG_Name As String

Var_SheetZZ = Application.Caller.Parent.Name

Var_Date_Column = Application.Caller.Column 'Column where date is



'Converts Column number into Column Letter

If Var_Date_Column > 26 Then

Var_Date_Column = Chr(Int((Var_Date_Column - 1) / 26) + 64) & _

Chr(((Var_Date_Column - 1) Mod 26) + 65)

Else

' Columns A-Z

Var_Date_Column = Chr(Var_Date_Column + 64)

End If





Var_Name_Row = Application.Caller.Row 'Row where name is



Set Var_Date = Range(Var_Date_Column & "2")

Set Var_Name = Range("A" & Var_Name_Row)



STR_RNG_Name = "'" & Var_SheetZZ & "'!" & Var_Name.Address

STR_RNG_Date = "'" & Var_SheetZZ & "'!" & Var_Date.Address



HolAvail = Evaluate("SUMPRODUCT((" & STR_RNG_Date & _

">=Hol_Start)*(" & STR_RNG_Date & _

"<=Hol_End)*(" & STR_RNG_Name & _

"=Hol_Name)*(Hol_Type_Code))")





If (Evaluate("SUMPRODUCT((" & STR_RNG_Date & _

">=Hol_Start)*(" & STR_RNG_Date & _

"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))"))

= 2 Then



HolAvail = 2

Else

HolAvail = HolAvail

End If



'Workdays are Blank

HolAvail = IIf(HolAvail = 0, "", HolAvail)



'Weekends are W (Weekdays 7 & 1)

HolAvail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _

"W", HolAvail)

Err:

'HolAvail = ""

HolAvail = "E"

End Function

 

[Niek Otten]

Not easy to understand immediately what you're trying to do, but in general:

All inputs to the function should be in the argument list. Don't rely on
Application.Volatile.
Excel just doesn't know which cells to recalculate and, more important, in
what sequence, if you don't tell it which cells/ranges the function depends
on.
There are some descriptions of how Excel tries to find out what extra cells
to recalculate, but why take the risk? Do the obvious, declare your
arguments and supply them with the function call.

 

[Tom Ogilvy]

Is the code in a general module as opposed to the worksheet module. If not,
try putting it in a general module.

 

[Charles Williams]

Hi Andi,

A little-known quirk of Application.evaluate (which is the default for
Evaluate) is that unqualified range references (range references without a
worksheet qualifier) are always assumed to refer to the active worksheet.

Probably the simplest solution is to use Worksheet.evaluate instead:

HolAvail = Application.caller.parent.Evaluate("SUMPRODUCT((" & STR_RNG_Date
& _

">=Hol_Start)*(" & STR_RNG_Date & _

"<=Hol_End)*(" & STR_RNG_Name & _

"=Hol_Name)*(Hol_Type_Code))")


Alternatively you can construct the string to use range references including
the worksheet qualifier.

See http://www.decisionmodels.com/calcsecretsh.htm for an example and some
limitations of the evaluate method.

There is also a Chart.Evaluate method, but I have never used it so I can
only guess that it might be useful for Series formulae, maybe when the sheet
is a chartsheet?

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Andibevan]

Hi Niek,

Just to clarify - do you mean essentially change the formular from
Holavail() to Holavail(Range1,Range2)?

Thanks

Andi

 

[Andibevan]

Tom - Thanks but It is already in the general module.

 

[Andibevan]

Charles,

I thought I had qualified my ranges using:-

STR_RNG_Name = "'" & Var_SheetZZ & "'!" & Var_Name.Address
STR_RNG_Date = "'" & Var_SheetZZ & "'!" & Var_Date.Address

Where Var_SheetZZ = Application.Caller.Parent.Name (returns the name of the
worksheet that called the function I think).

So If I have qualified my ranges (I think I have) I still have the same
problem. Bizarely The function works on one sheet but on the other sheet it
now gets errors of
#Value!.

Thankx

Andi

 

[Niek Otten]

Hi Andi,

Yes. And change the code of the UDF accordingly, of course, to use Range1
etc instead of accessing them directly via Application.caller

 

[Andibevan]

Thanks for your help on this Niek but I think I will go back to the drawing
board as my attempts are probably not the easiest solution.

Essentially I am trying to create a simple worksheet to translate a list of
holiday dates for resources (in a table with resource name, hol start, hol
end) into a calander format that is spread accross multiple worksheets.

 

[Andibevan]

I thought I would post back my final working code. It shows how a function
can be built that takes all inputs required for the function from the
positions relative to the cell.

The following 2 lines are used to determine the worksheet, row, column and
workbook that are related to the cell where the function is located:-
Var_WorkBK = Application.Caller.Parent.Parent.Name 'Workbook
Var_SheetZZ = Application.Caller.Parent.Name 'Sheet
Var_Date_ColNum = Application.Caller.Column 'Column
Var_Name_Row = Application.Caller.Row 'Row

Function Hol_Avail()
On Error GoTo Err
Dim Var As String
Dim Var_WorkBK As String
Dim Var_SheetZZ As String
Dim strHol_Start As String, strHol_End As String
Dim strHol_Type As String, strHol_Type_Code As String
Dim str_Date As String, str_Name As String
Dim Var_Date_ColNum As Integer, Var_Date_ColLet As String
Dim Var_Name_Row
Dim Var_Date
Dim strHol_Rsrc_NM As String
Var_WorkBK = Application.Caller.Parent.Parent.Name 'Workbook
Var_SheetZZ = Application.Caller.Parent.Name 'Sheet
Var_Date_ColNum = Application.Caller.Column 'Column where date is
Var_Name_Row = Application.Caller.Row 'Row where name is

If Var_Date_ColNum > 26 Then
Var_Date_ColLet = Chr(Int((Var_Date_ColNum - 1) / 26) + 64) & _
Chr(((Var_Date_ColNum - 1) Mod 26) + 65)
Else
' Columns A-Z
Var_Date_ColLet = Chr(Var_Date_ColNum + 64)
End If

strHol_Start = "'" & Var_WorkBK & "'!Hol_Start"
strHol_End = "'" & Var_WorkBK & "'!Hol_End"
strHol_Type = "'" & Var_WorkBK & "'!Hol_Type"
strHol_Type_Code = "'" & Var_WorkBK & "'!Hol_Type_Code"
strHol_Rsrc_NM = "'" & Var_WorkBK & "'!Hol_Name"

str_Date = "'" & Var_SheetZZ & "'!$" & Var_Date_ColLet & "$2" 'Date of
Cell
str_Name = "'" & Var_SheetZZ & "'!$A$" & Var_Name_Row 'Name of Cell"

Hol_Avail = Evaluate("SUMPRODUCT((" & str_Date & ">=" & strHol_Start & ")" &
_
"*(" & str_Date & "<=" & strHol_End & ")*" & _
"(" & str_Name & "=" & strHol_Rsrc_NM & ")*(" & strHol_Type_Code & "))")

If (Evaluate("SUMPRODUCT((" & str_Date & ">=" & strHol_Start & ")" & _
"*(" & str_Date & "<=" & strHol_End & ")*" & _
"(" & strHol_Type & "=""Public Holiday"")*(" & strHol_Type_Code & "))")) = 2
Then
Hol_Avail = 2
Else
Hol_Avail = Hol_Avail
End If

'Workdays are Blank
Hol_Avail = IIf(Hol_Avail = 0, "", Hol_Avail)

'Weekends are W (Weekdays 7 & 1)
Var_Date = Worksheets(Var_SheetZZ).Range(Var_Date_ColLet & "$2").Value
Hol_Avail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _
"W", Hol_Avail)

Exit Function
Err:
Hol_Avail = "Error"
End Function