Odd issue with custom Excel VBA resetting values

Question

I'm doing a few things at once which I believe may be causing issues. I've tested this VBA in Office 2013 and Office 2016 under Windows 10.

I have a multitude of worksheets, each of which is titled based on the month and year (ex: "November 2018", "December 2018", etc.). I'm using VBA to do two (separate) things:

Get the Active Worksheet's name
Traverse a previous worksheet's data

VBA Code:

Public Function RelSheet(iPos As Integer, zRange As String)

    'Relative Worksheet Reference Facility
    'eg: =RelSheet(-1,"A3") = Cell A3 in Previous (Left) WSheet
    'eg: =RelSheet(1,"A3") = Cell A3 in Next (Right) WSheet
    'eg: "#Error" when reference does not exist
    'eg: Can do maths =RelSheet(1,"A3")*2

    Dim shtActive As Worksheet
    Application.Volatile True
    Set shtActive = Application.Caller.Worksheet
    On Error GoTo BadSheetReference
    RelSheet = Sheets(shtActive.Index + iPos).Range(zRange).Value

    GoTo ExitFunction

BadSheetReference:
    RelSheet = "#Error"

ExitFunction:
End Function

Function TabName()
  TabName = ActiveSheet.Name
End Function

In my worksheets, I'm tallying a summation month over month until a near year is created, then the tally begins again at 0 (or whatever value Jannuary contains). Cell C8 is the current month's value, cell C9 is the summation of the previous month's value (C9 of the previous worksheet) + the current worksheet cell's value in C8. The formula for that cell (C9) is as follows: ==IF(ISNUMBER(SEARCH("January", TabName())), C8, RelSheet(-1, "C9")+C8)

Unfortunately, once a worksheet name does contain the text of "January", all previous worksheets are also reverted to a sum of 0. I believe it is related to the RelSheet function and essentially recursively checking itself, but when I step through the code logically on paper, I don't see how it's doing this. My current workaround is just to manually set the value of the particular cell in a January worksheet to 0, and continue the formula on successive spreadsheets.

By default Sheets() will refer to the active workbook - do you have multiple workbooks open? Also TabName = ActiveSheet.Name would probably be a problem on inactive sheets — Tim Williams
Sheets is an enumerable, and there's no guarantee that the the index is ordered in any way what-so-ever. You'd be much better off iterating the Worksheets collection and checking the names. What happens if the use re-orders the sheets? — Comintern
@Comintern - are you sure? I've never had a case where (eg) Sheets(2) didn't reference the second tab in the workbook (assuming there are at least two tabs and no hidden ones) — Tim Williams
Note "active sheet" is misleading here - it's not the ActiveSheet, it's whatever worksheet invoked the UDF: whether that sheet is "active" or not makes no difference. I'd suggest renaming shtActive to e.g. callerSheet, and the unqualified Sheets collection is, as Tim correctly warns above, implicitly referring to whatever the ActiveWorkbook is, which means the UDF will evaluate differently depending on whether the expected book is currently active or not. Use ThisWorkbook.Worksheets instead, to guarantee deterministic results. — Mathieu Guindon
There isn't - but the Sheets collection contains non-worksheet sheets; use the Worksheets collection when you know you're getting a Worksheet object (as opposed to, say, a Chart sheet). The important part is the ThisWorkbook qualifier, without which what sheet you get depends on what workbook is currently active, or (in other contexts) even where in the VBA project the code is written: identical code written in a standard module may not behave the same way when written in a worksheet module, unless it's properly/explicitly qualified with a Workbook object reference. — Mathieu Guindon

Tim Williams Tim Williams · Accepted Answer · 2019-02-04T20:29:40

Couple of suggestions:

Public Function RelSheet(iPos As Integer, zRange As String)

    'Relative Worksheet Reference Facility
    'eg: =RelSheet(-1,"A3") = Cell A3 in Previous (Left) WSheet
    'eg: =RelSheet(1,"A3") = Cell A3 in Next (Right) WSheet
    'eg: "#Error" when reference does not exist
    'eg: Can do maths =RelSheet(1,"A3")*2

    Dim shtActive As Worksheet
    Application.Volatile True
    Set shtActive = Application.Caller.Worksheet
    On Error GoTo BadSheetReference
    '##added workbook qualifier
    RelSheet = ThisWorkbook.Sheets(shtActive.Index + iPos).Range(zRange).Value

    GoTo ExitFunction

BadSheetReference:
    RelSheet = CVErr(xlErrRef)

ExitFunction:
End Function

Function TabName()
  '## not ActiveSheet
  TabName = Application.Caller.Parent.Name
End Function

Odd issue with custom Excel VBA resetting values

1 Answers