UDFs on different sheets causing eachother 2015 reverse error
I have 2 sheets with 3 UDFs in the first and 2 in the second.
-
Sheet 1 is a monthly 1-column matrix for each day that people put their watch on the rows below. There are UDFs in the 3 defined rows that concatenate the data in the column above, referencing the string as an argument. I am making a function call as shown below to avoid having to change the UDF (which significantly increases the computation time if I do this), so the UDF result is updated when something changes in the R column:
calculateOvertime(R:R)
-
On sheet 2, the days of the month are in rows (not columns) where you can detail your day if they were doing overtime. This is detected by one of the UDFs in sheet 1, so here 2 UDFs require data computed by the UDFs in sheet 1
I have some strange problems with this setup:
-
For some reason, when I switch tabs, all cells containing any of these features are shown as #VALUE !. I have to add "Application.CalculateFull" to an event handler that fires whenever a tab is activated:
Private Sub Worksheet_activate() Application.CalculateFull End Sub
-
A UDF referencing a cell containing another UDF in a different tab will always get "2015!" as a value referencing error 2015 (value error because cell contains #VALUE! when sheet is not active)
Obviously these 2 problems are related, because when I move the sheets the calculated UDF values in the inactive sheet are somehow lost.
My method of getting a value from a cell is as follows. I am determining which row the label in column A is used with using the Find () function
Dim compensationRowIndex As Integer
compensationRowIndex = CInt(othersheet.Range("A1:A250").Find("COMPENSATION").Row)
then i get the value and cast it to Single
Dim compensation As Single compensation = CSng(othersheet.Cells(compensationRowIndex , columnIndex).Value)
the variable "compensation" always retains the value 2015.
Is there a way to get around this? Also when I want to print the sheets, all cells containing the UDF are filled with #VALUE !. My guess is: if I can make problem 1 go away then 2 will exit.
ANY help on this is greatly appreciated. I searched for this problem for almost all day and did not find a solution that showed symptoms.
source to share
Problem solved!
"ActiveSheet" inside a UDF does not mean "Sheet containing a cell containing a UDF". It literally means "active leaf".
When you reference a UDF in an anothera sheet, things go horribly wrong. It's all about replacing the ActiveSheet with a variable defined in the If statement that decides where the UDF is called from. In my case, the second sheet always contains the word "Info". When on this sheet you should go one sheet to the left:
Dim ws As Worksheet
If InStr(ActiveSheet.Name, "Info") = 0 Then
Set ws = ActiveSheet
Else
Set ws = Worksheets(ActiveSheet.Index - 1)
End If
Not a 100% waterproof solution (like if someone is reordering the sheets), but close enough for my purposes.
source to share
The following piece of code (used in UDF) should do what you want:
Dim ws As Worksheet
If TypeOf Application.Caller Is Range Then
Set ws = Application.Caller.Parent
End If
The Caller
object property Application
points to the Range
enclosing cell (s) from which the UDF is called. ws
will point to the contained sheet. The proposal If TypeOf
avoids errors in case the function was called by something other than UDF (for example, another VBA procedure), where it Caller
may not point to an object Range
. In this case ws
, ( Nothing
) remains unassigned .
source to share