Get the name of an Excel worksheet containing code

Question

I have a code that is on the page and refers to the contents of the book. When executed on another worksheet, I would like to get the name of a specific worksheet containing the code.

I have sheets containing data. Code is added to this worksheet and the run that creates the Summary worksheet. On the Summary worksheet, I would like to run the code on the data sheet. This means that I cannot use ActiveSheet , and I would have to reference the data sheet by name.

How can I get the name of the worksheet containing the code without the need for a hard code name?

Answer 1

There are 2 properties of the application that interest you.

Application.ThisWorkbook Property (Excel)

Returns a workbook object that represents the workbook in which the current macro is running. Only for reading.

and

Application.ThisCell Property (Excel)

Returns the cell in which the function called by the user is called from the Range object.

Answer 2

Use the "Me" object.

Me.Name is the property you are looking for that will give you the name of the sheet containing the code, regardless of the active sheet.

Answer 3

To request the actual code structure of your project, you need to allow access to the VBA project object model (Excel options> Trust Center> Macro options, and then add a link to Microsoft Visual Basic for vX extensibility), where vX is version 5.3. You can use the objects in this to determine which sheets have some kind of code inside them.

However, I would recommend doing it differently.

Instead, iterate through the worksheets in your workbook, and then run the macro in Application Shell using Application.Run

Please note that it would be better to process your code and put it in a standard module, and then pass it to the worksheets as arguments (see my second example)

eg:.

 'With code distributed in each worksheet Sub blah() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets On Error Resume Next Application.Run ws.CodeName & ".CollectInfoMacro" If Err.Number = 1004 Then Debug.Print "Skipping "; ws.Name; ", No macro defined" On Error GoTo 0 Next ws End Sub 'Otherwise, better practice would be to refactor 'and not have code on each sheet, instead in a standard module: Sub blahblah() Dim ws As Worksheet Dim results As Collection Set results = New Collection For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then 'or whatever results.Add getYourInfoForTheSummary(ws), ws.Name End If Next ws 'Process your results (ie dump to the summary worksheet etc) ... End Sub Function getYourInfoForTheSummary(ws As Worksheet) As Collection 'or return whatever Dim results As Collection Set results = New Collection With ws 'do something End With Set getYourInfoForTheSummary = results 'or whatever End Function