Excel 2013: Cells with calculations on specific sheet reset when calculating cells on different sheet

Question

Excel 2013

I have 3 worksheets in this workbook, its highly formatted and I used a custom formula I coded in VBA that utilizes Application.Volatile so it automatically refreshes the calculations every time you enter new data.

My team has formatted this workbook up and down and created a huge tracker that contains financials for our company. The problem is that now when we go to open the workbook and hit f9/load the calculate sheet function, only the selected worksheet will update and calculate based on its reference cells within that sheet.

It's supposed to do this, but the problem is inside of the other two tabs(mind you there are 3 total), the cells that have formulas will revert back to either all zeros or old data that is currently not applicable. When you select one of the other two tabs that initially were not selected and hit f9/load calculate sheet function the cells with functions that once had the zeros/old data inside them update based on the new values that the cell is referencing, and it works fine.

It keeps doing this as we switch tabs and reinitialize the f9/calculate sheet function, the other two tabs that are currently not selected reset and display either all zeros or old data. I have been googling and looking everywhere for a solutions and nothing has worked.

Function RedFinder(MyCellColumn As Integer, MyOffset As Integer, MonthCheck As Integer, YearCheck As Integer)
    Application.Volatile
'    Dim MyCellRow As Integer     'row I want to select
    Dim MyMoneyValue As Variant 'Single holds a decimal variable
    Dim MyAnswerString As String
'    Sheets("Sheet1").Activate  'activate sheet1 at cell script runs on
'    MyCellRow = 115              'set variable MyCellRow to row 1
    MyMoneyValue = CDec("0.0")


'    ActiveSheet.Cells(MyCellRow, MyCellColumn).Select    'select active cell based on input vars
    For MyCellRow = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row    'for loop used to go through all cells
        If IsDate(ActiveSheet.Cells(MyCellRow, MyCellColumn)) Then    'checks if cell is a date
            If Month(ActiveSheet.Cells(MyCellRow, MyCellColumn)) = MonthCheck And Year(ActiveSheet.Cells(MyCellRow, MyCellColumn)) = YearCheck Then    'checks if month and date match
                If IsNumeric(ActiveSheet.Cells(MyCellRow, MyCellColumn).Offset(0, MyOffset)) Then    'checks if corresponding column is a number
                    If ActiveSheet.Cells(MyCellRow, MyCellColumn).Offset(0, MyOffset).Font.Color = 255 Then    'checks if cell text color is red, 255 is the number Font.Color returns for RGB Red (255,0,0)
                        MyMoneyValue = MyMoneyValue + ActiveSheet.Cells(MyCellRow, MyCellColumn).Offset(0, MyOffset)    'adds cell value to MyMoneyValue
'                       MyAnswerString = MyMoneyValue
'                       MyCellRow = MyCellRow + 1
'                   Else
'                       MyCellRow = MyCellRow + 1
                    End If
                End If
'               Else
'               MyAnswerString = "False"
'               MyCellRow = MyCellRow + 1
            End If
        End If
    Next MyCellRow
'MsgBox MyCellColumnA
'RedFinder = Year(ActiveSheet.Cells(MyCellRow, MyCellColumn))
RedFinder = MyMoneyValue    'sets function to report total of MyMoneyValue
End Function

Answer 1

You need to remove all of the ActiveSheet references and replace them with a reference to the sheet containing the formula which calls your UDF

Function RedFinder(MyCellColumn As Integer, MyOffset As Integer, MonthCheck As Integer, YearCheck As Integer)
    Application.Volatile

    Dim MyMoneyValue As Variant 'Single holds a decimal variable
    Dim MyAnswerString As String
    Dim sht As Worksheet, c As Range, MyCellRow As Long

    Set sht = Application.Caller.Parent '<<<<    or use Application.ThisCell.Parent

    MyMoneyValue = CDec("0.0")

    For MyCellRow = 2 To sht.Cells(Rows.Count, 1).End(xlUp).Row
        Set c = sht.Cells(MyCellRow, MyCellColumn)
        If IsDate(c.Value) Then
            If Month(c.Value) = MonthCheck And Year(c.Value) = YearCheck Then    'checks if month and date match
                If IsNumeric(c.Offset(0, MyOffset)) Then
                    If c.Offset(0, MyOffset).Font.Color = 255 Then
                        MyMoneyValue = MyMoneyValue + c.Offset(0, MyOffset)
                    End If
                End If
            End If
        End If
    Next MyCellRow

    RedFinder = MyMoneyValue
End Function