ActiveWorkbook 与 ThisWorkbook

ActiveWorkbookThisWorkbook 有时可以被 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