Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF only works when focus is on a specific sheet - Help!!
Hi Niek,
Just to clarify - do you mean essentially change the formular from Holavail() to Holavail(Range1,Range2)? Thanks Andi "Niek Otten" wrote in message ... 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. -- Kind regards, Niek Otten "Andibevan" wrote in message ... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF only works when focus is on a specific sheet - Help!!
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 -- Kind regards, Niek Otten "Andibevan" wrote in message ... Hi Niek, Just to clarify - do you mean essentially change the formular from Holavail() to Holavail(Range1,Range2)? Thanks Andi "Niek Otten" wrote in message ... 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. -- Kind regards, Niek Otten "Andibevan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF only works when focus is on a specific sheet - Help!!
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. "Niek Otten" wrote in message ... 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 -- Kind regards, Niek Otten "Andibevan" wrote in message ... Hi Niek, Just to clarify - do you mean essentially change the formular from Holavail() to Holavail(Range1,Range2)? Thanks Andi "Niek Otten" wrote in message ... 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. -- Kind regards, Niek Otten "Andibevan" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF only works when focus is on a specific sheet - Help!!
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 "Andibevan" wrote in message ... 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. "Niek Otten" wrote in message ... 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 -- Kind regards, Niek Otten "Andibevan" wrote in message ... Hi Niek, Just to clarify - do you mean essentially change the formular from Holavail() to Holavail(Range1,Range2)? Thanks Andi "Niek Otten" wrote in message ... 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. -- Kind regards, Niek Otten "Andibevan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP!! Unhide Sheet with Macro and focus on other sheet | Excel Discussion (Misc queries) | |||
Set Focus to Worksheet and then a specific ROW | Excel Programming | |||
Code Only works when sheet is in focus | Excel Programming | |||
Statement to give focus to a specific control on a UserForm? | Excel Programming | |||
Focus sheet | Excel Programming |