excel - Get name of the file from which the button was pressed on the toolbar

Question

I have a macro in the personal.xlsb file to format current sheet. That macro is used in 2 ways

1. by pressing a button on the toolbar from any open file
2. by being called from another sub/procedure

I'm trying to write the macro, without using SELECT, ACTIVATE, PASTE, etc. (so, by declaring all workbooks and sheets and using WITH...). To do that, I have to know the name of the file that called the macro and it has to work for both being called from the button and from a procedure.

The code below is not fully functional because it's not switching between the two workbooks. I didn't finish coding because I realized that I don't know how to get the file and sheet name.

Option Explicit

Public Sub FormatTheBasics()

    Dim CurLastColumn As Long, CurLastRow As Long
    
    Dim CurRowNum As Long, LastRow As Long, FirstRowOfSection As Long, LastRowOfSection As Long
    Dim CurCell As Variant, CurRange As Range
    Dim wbkM  As Workbook, wbkC  As Workbook
    Dim wksReplaceWords As Worksheet, wksFilesToExportEMail  As Worksheet, wksCopyFrom   As Worksheet, wksCopyTo   As Worksheet
    Dim rngCopyFrom As Range, rngCopyTo As Range
    Dim x     As Long
    Dim CurColumnLetter As String, CurColumnName As String, ReplaceFrom As String, ReplaceTo As String

    Application.EnableCancelKey = xlDisabled
 
    Set wbkM = Workbooks("Personal.xlsb")
    Set wksReplaceWords = wbkM.Sheets("ReplaceWords")


    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    
    Range("A1:" & MyColumnLetter(xlLastCol) & "1").Select
    
    Selection.Font.Bold = True
    
    With Selection.Interior
        .PatternColorIndex = 2
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    
    Selection.AutoFilter
    
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    
         
    For x = 1 To xlLastCol
         
        CurColumnLetter = MyColumnLetter(x)
        CurColumnName = StrConv(Range(CurColumnLetter & "1").Value, vbLowerCase)
        Range(CurColumnLetter & "1").Value = StrConv(CurColumnName, vbProperCase)
    
        CurRowNum = 2
    
        With wksReplaceWords
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

            Set CurRange = .Range("C" & CurRowNum & ":C" & LastRow)
            For Each CurCell In CurRange
                If CurCell <> "" Then
                
                    ReplaceFrom = .Range("B" & CurRowNum).Value
                    ReplaceTo = CurCell
 
                    If InStr(1, CurColumnName, ReplaceFrom, vbTextCompare) > 0 Then
Range(CurColumnLetter & "1").Replace what:=ReplaceFrom, replacement:=ReplaceTo, MatchCase:=True

                    End If
    
                End If
            Next CurCell
        End With
              
    Next x
    
    Cells.Select
    Cells.EntireColumn.AutoFit

    Columns("A:" & MyColumnLetter(xlLastCol)).Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes

    Range("A2").Select

End Sub

标签： excelvba

Answer 1

Within a VBA function, there is no means to find out where and why execution was started. To put it in programmer-speak, there is no way of introspecting call stack and execution context.

So how to solve this? Your best bet is to use ActiveSheet or ActiveWorkbook once only, assuming that the target workbooks' window was in front when the function was triggered. Store it into a variable and from then on use the Variable:

Public Sub DoStuff():
    Dim ws as Worksheet
    Set ws = ActiveSheet
    ws.Range("A1").value = "Hello World"
    ' ...
End Sub

This way, if you find flaws in your sheet-detection logic, you just need to improve the code right at the top.