Reputation: 848
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
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
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
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