CODEWITHSUNDEEP
excelvba
Chris
Chris

Reputation: 848

Get name of an Excel worksheet containing code

I have code that resides on a page and references the content of the workbook. When executed from a different worksheet, I would like to get the name of the specific Worksheet containing the code.

I have worksheets that contain data. The code is added to that worksheet and the run - which produces a Summary worksheet. From the Summary worksheet, I would like to run the code on the data worksheet. This means I can't 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 having to hard-code the name?

Upvotes: 8

Views: 7268

Answers (3)

Sam73
Sam73

Reputation: 81

Use the "Me" object.

Me.Name is the property you seek, wich will give you the name of the sheet containing the code regardless the active sheet.

Upvotes: 8

Cor_Blimey
Cor_Blimey

Reputation: 3310

To query the actual code structure of your project you will need to allow access to the VBA project object model (Excel settings> Trust Center > Macro Settings then add a reference to Microsoft Visual Basic for Application Extensibility vX) where vX is the version like 5.3. You can use the objects in this to identify which sheets have what code within them.

However, I would recommend doing it another way.

Instead, iterate through the worksheets in your workbook and then, within an error wrapper, run the macro using Application.Run

Note that it would be better practice to refactor your code and put it all in a standard module, then pass in the worksheets as arguments (see my second example)

E.g.:

'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 (i.e. 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

Upvotes: 0

SeanC
SeanC

Reputation: 15923

There are 2 application properties that would interest you for this.

Application.ThisWorkbook Property (Excel)

Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.

and:

Application.ThisCell Property (Excel)

Returns the cell in which the user-defined function is being called from as a Range object.

Upvotes: 8

Related Questions