1

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:

  1. Get the Active Worksheet's name
  2. 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.

Improve this question
12
  • 1
    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 Commented Feb 4, 2019 at 20:22
  • 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? Commented Feb 4, 2019 at 20:23
  • @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) Commented Feb 4, 2019 at 20:25
  • 2
    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. Commented Feb 4, 2019 at 20:50
  • 1
    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. Commented Feb 4, 2019 at 23:40

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Upvoted, although I'd warmly recommend returning CVErr(xlErrRef) (an actual #REF! worksheet error value) instead of a string that says #Error and that Excel doesn't understand as a worksheet error value. That would make the UDF work with IFERROR and ISERROR functions.
I would also suggest shtActive.Parent rather than ThisWorkbook to make it a bit more general. And changing the variable name to avoid the missleading inference it's working on the Active Sheet (eg shCaller)
Brilliant. Wish I knew/understood VBA's object hierarchy a bit better to have known my stumbling blocks. I'll have to read up on the difference between Sheets and ThisWorkbook.Sheets, as well as ActiveSheet vs Application.Caller.Parent. Thank you so much for your expertise here (everyone!)!
Just realized when I wrote those functions oh-so-long ago, I had already looked up and used Application.Caller...dang. Just proves you lose it if you don't use it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.