Find worksheet name mapping in Excel

0
votes

I have inherited an Excel workbook with several worksheets, all with named tabs. I have some VBA code that runs depending on buttons and inputs. When I compare the VBAProject and the list of Excel Objects, the sheet's names match the tabs. All good so far.

However, within the VBA code and on the spreadsheets itself, it refers to other worksheets. They are still part of the workbook, and I think that the code was created, and then tabs were renamed. But I don't know how Excel keeps the connection, or, more importantly, how I am supposed to figure out the connections.

So, for instance, I have a VLOOKUP that refers to MiscDataRange 

=IF((VLOOKUP(E4,MiscDataRange,4,0))="Y"...

I don't know what MiscDataRange is, but within the VBA code I find one reference. Right after specifying worksheet "Misc Interrupt", which is NOT any of the named tabs.

    Worksheets("Misc Interrupt").Range("H2:H47") = "N" 'Reset to N at noon.

    UpdateData

    Range("MiscDataRange").ClearContents

I do have a sheet called MiscInt, and it appears to be the sheet that "Misc Interrupt" is using and MiscDataRange is referencing.

My problem is there is a hole in my knowledge - I can guess that MiscInt and Misc Interrupt are the same worksheet, that the VLookup is referring to data on the MiscInt sheet. But I am reduced to guessing. I cannot find anything in the file that maps those two as the same. Where would I look to find that?

This question seems related, except he simply has a VLookup, and isn't looking at the VBA code: Non-existent Excel Worksheet, but Formulas and Defined names still work?. In addition, from the VBA code, I can see hidden and visible worksheets.

2
vbaexcelexcel-2016
Range("MiscDataRange").ClearContents does not reference any particular sheet. it will use any sheet that is active at the moment the code executes. - jsotola
what happens if you execute Range("MiscDataRange").ClearContents or Range("MiscDataRange").Select ? - jsotola
unhide all the worksheets - jsotola
You can find exactly where Named ranges are from Immediate Window, e.g. Range("MiscDataRange").Address(External:=True) - PatricK
You can also find out where Named Ranges refer to by going to Formulas / Name Manager on the Excel menu. - YowE3K

2 Answers

4
votes

You can open the Names manager by going to the Formula Tab and clicking Names Manager or pressing ctrl+F3 or you can paste a list of Names and what the names reference by pressing F3. It may be necessary to unhide the names first.

enter image description here

Sub ShowAllNames()
    Dim n As Name
    For Each n In ThisWorkbook.Names
        n.Visible = True
    Next
End Sub
2
votes

If I understand your question, you're looking to find where those named ranges are.

You can use a sub like this:

Sub t()
Debug.Print "Sheet: " & Range("testNamedRange").Parent.Name
Debug.Print "Full Location: " & Range("testnamedrange").Name
Debug.Print "File path: " & Range("testnamedrange").Worksheet.Parent.Path
End Sub

Does that help?