Passing a sheets codename as a parameter in the range function

user_19222916 0
Viewed 71

IN VBA

Function SNAME(number As String) As String
    Dim WORD$
    WORD = "Sheet" + number
'    SNAME = Worksheets(1).CodeName
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Worksheets(i).CodeName = WORD Then SNAME = Worksheets(i).Name
    Next i
End Function

IN EXCEL

=SUMIFS((SNAME(19)!$T$2:$T$9962),(SNAME(19)!$A$2:$A$9962),"=31",(SNAME(19)!$C$2:$C$9962),"<="&EDATE(TODAY(),-12))

Purpose:

The idea here is that I want to pass the sheets name to the range function based on its code name. the VBA function SNAME seems functional and returns the worksheet's name based on the VBA object code name. When I try to combine that with the range function in the equation, there seems to be an error I'm unsure how to resolve. When I replace SNAME(19) with the sheet name the Equation executes properly.

Goal:

I need to be able to reference the sheet using its code name to avoid future errors of individuals changing the name of the sheets. I'd really appreciate any advice someone might have on the issue.

range excel-formula vba excel reference
1 Answers

As far as I know, Excel automatically corrects the formula if any name used there has been changed. So I'm not sure whether you need this construction. Anyway, to make it work we have to change SNAME(19)!$T$2:$T$9962 to something like INDIRECT("'" & SNAME(19) & "'!$T$2:$T$9962").

To make this cleaner, let's put quoting inside the function SNAME. Also, I'd change the type of number to Variant and put Exit For on success:

Function SNAME(number) As String
Dim sh As Worksheet
Dim WORD$
    WORD = "Sheet" & number
    For Each sh In ThisWorkbook.Worksheets
        If sh.CodeName = WORD Then 
            SNAME = "'" & sh.Name & "'"
            Exit For
        End If
    Next sh
End Function

In the formula, I would use LET to ease editing the function call and to call it once:

=LET(sh, SNAME(19),
    SUMIFS(
        INDIRECT(sh & "!$T$2:$T$9962"), 
        INDIRECT(sh & "!$A$2:$A$9962"),
        "=31",
        INDIRECT(sh & "!$C$2:$C$9962"),
        "<=" & EDATE(TODAY(),-12)
    )
)
user_149096210
Related