ActiveWorkbook 與 ThisWorkbook

ActiveWorkbook 和 ThisWorkbook 有時可以被 VBA 的新使用者互換使用而不完全理解每個物件與哪個相關，這可能會在執行時導致不希望的行為。這兩個物件都屬於 Application Object

ActiveWorkbook 物件是指當前位於執行時 Excel 應用程式物件的最頂層檢視中的工作簿。 （例如，在引用此物件時你可以看到並與之互動的工作簿）

Sub ActiveWorkbookExample()

'// Let's assume that 'Other Workbook.xlsx' has "Bar" written in A1.

    ActiveWorkbook.ActiveSheet.Range("A1").Value = "Foo"
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Foo"

    Workbooks.Open("C:\Users\BloggsJ\Other Workbook.xlsx")
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar"

    Workbooks.Add 1
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints nothing

End Sub

ThisWorkbook 物件是指程式碼在執行時所屬的工作簿。

Sub ThisWorkbookExample()

'// Let's assume to begin that this code is in the same workbook that is currently active

    ActiveWorkbook.Sheet1.Range("A1").Value = "Foo"
    Workbooks.Add 1
    ActiveWorkbook.ActiveSheet.Range("A1").Value = "Bar"

    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar"
    Debug.Print ThisWorkbook.Sheet1.Range("A1").Value '// Prints "Foo"

End Sub