Dynamic range based formula results' change every time I switch sheet Excel VBA

Question

I wrote a function that supposed to get a specific part of a specific Column, and then, by comparing each entry of the column to the value of the cell that is left to it, count the times a specific condition is met. It all works alright, except one problem.. if I use the function on "Sheet1", get a result and then switch to "Sheet2" and use the function on this sheet it changes the result on "Sheet1" for some reason.

Function countStable(rangeObj As Range) 'rangeObj that being passed is a namedRange(Synamic Range)
    Application.Volatile
    ActiveSheet.Select
    Dim entry, preEntryVal, entryVal As Variant
    Dim counters(1 To 5, 1 To 1) As Integer

    Dim cStable, cIncreased, cDecreased, cAdded, cLost

    cStable = 0
    cIncreased = 0
    cDecreased = 0
    cAdded = 0
    cLost = 0

    Set rangeObj = Intersect(rangeObj, rangeObj.Parent.UsedRange)

    For Each entry In rangeObj
    If Not IsEmpty(entry.Value) And Not IsEmpty(ActiveSheet.Range("A" & entry.Row)) Then
        entryVal = entry.Value
        preEntryVal = ActiveSheet.Cells(entry.Row, entry.Column - 1).Value

        If entryVal = preEntryVal Then
            cStable = cStable + 1
        ElseIf InStr(entryVal, "-") And Not (InStr(preEntryVal, "-")) Then
            cLost = cLost + 1
        ElseIf Not InStr(entryVal, "-") And InStr(preEntryVal, "-") Then
            cAdded = cAdded + 1
        ElseIf preEntryVal < entryVal Then
            cDecreased = cDecreased + 1
        ElseIf preEntryVal > entryVal Then
             cIncreased = cIncreased + 1
        End If

    End If

    counters(1, 1) = cStable
    counters(2, 1) = cIncreased
    counters(3, 1) = cDecreased
    counters(4, 1) = cAdded
    counters(5, 1) = cLost

    Next
    countStable = counters

End Function

As commented inside the code, rangeObj that is being passed as parameter was defined in the name manager and it is based on an Offset formula. I know it changes the values on both sheets because of the dynamic range, but not sure why.. I don't want it to be changed.

Help please?

Answer 1

In several places, the code references the ActiveSheet. Wherever the function appears, it will reflect the value of whatever sheet is active. You'll want to use the parent of the supplied range object instead.

Dim currentSheet as Worksheet
Set currentSheet = rangeObj.Parent

Then, search and replace ActiveSheet with currentSheet in the method.