当从 ProtectedView“启用编辑”时，Excel 的 VBA ActiveWorkbook 是“Nothing”

Excel's VBA ActiveWorkbook is "Nothing" when "Enable Editing" from ProtectedView

提问by Christian Fries

I have a VBA macro which is called from a spreadsheet function (user defined function, UDF). When the spreadsheet is downloaded from the internet and the user has set "Trust Center" settings accordingly, the spreadsheet will open in so the called "Protected View". The function will not be called. A button "Enable Editing" is shown. If the button is pressed, the spreadsheet is "trusted" and reopened normally, starting calculation, and hence calling the user defined function.

我有一个从电子表格函数（用户定义函数，UDF）调用的 VBA 宏。当电子表格从 Internet 下载并且用户相应地设置了“信任中心”设置时，电子表格将在所谓的“受保护视图”中打开。该函数不会被调用。显示“启用编辑”按钮。如果按下按钮，则电子表格是“受信任的”并正常重新打开，开始计算，从而调用用户定义的函数。

However, in that VBA function the value of Application.ActiveWorkbookis Nothing. This can be verified in the debugger.

然而，在VBA函数的值Application.ActiveWorkbook是Nothing。这可以在调试器中验证。

Since I just need to read some properties (like path name) of the spreadsheet, I could alternatively inspect the availability of Application.ActiveProtectedViewWindowwhich should reference to the protected version of the workbook. In the debugger, this object can be inspected. However, running in release (without debug) the value of Application.ActiveProtectedViewWindowis also Nothing.

由于我只需要读取电子表格的一些属性（如路径名），我也可以检查Application.ActiveProtectedViewWindow哪些属性的可用性应该引用工作簿的受保护版本。在调试器中，可以检查该对象。但是，在发行版中运行（没有调试）的值Application.ActiveProtectedViewWindow也是Nothing.

Both behaviors - especially the first one - appears to be a bug present in Excel 2010 and 2013 (see also a post at the MSDN forum).

这两种行为 - 尤其是第一种 - 似乎是 Excel 2010 和 2013 中存在的错误（另请参阅MSDN 论坛上的帖子）。

Question: Is there a way to get hold of properties of the active workbookafter it has been enabled for editing?

问题：有没有办法在启用编辑后获取活动工作簿的属性？

PS: As a follow up to the nice observation of Siddharth Rout, that "ThisWorkbook" might work: In my case, the macro is not part of the Workbook being openend. The UDF is defined in an XLA. Hence, ThisWorkbook would reference the XLA. I do need to get the ActiveWorkbook (= the workbook calling the UDF) instead of ThisWorkbook (= the workbook running the UDF).

PS：作为对 Siddharth Rout 很好的观察的后续行动，“ThisWorkbook”可能会起作用：在我的情况下，宏不是开放式工作簿的一部分。UDF 在 XLA 中定义。因此，ThisWorkbook 将引用 XLA。我确实需要获取 ActiveWorkbook（= 调用 UDF 的工作簿）而不是 ThisWorkbook（= 运行 UDF 的工作簿）。

IMPORTANT REQUIREMENT:

重要要求：

1. My function is called as a user defined function, i.e., execution order is determined by Excel updating the cell.

2. The function is not part of the workbook being opened. It is part of an XLA.

3. I cannot add any code to the workbook which is opened.

1. 我的函数被称为用户定义函数，即执行顺序由 Excel 更新单元格决定。

2. 该函数不是正在打开的工作簿的一部分。它是 XLA 的一部分。

3. 我无法向打开的工作簿添加任何代码。

回答by Siddharth Rout

Summary:The problem can be replicated and there are some possible workarounds. The most promising one - resulting from a chat - is to use ActiveWindow.Parentinstead of ActiveWorkbook.

摘要：该问题可以复制，并且有一些可能的解决方法。最有希望的 - 来自聊天 - 是使用ActiveWindow.Parent代替ActiveWorkbook。

---

---

I was able to replicate the problem.

我能够复制这个问题。

I tried

我试过

Private Sub Workbook_Open()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ActiveWorkbook Is Nothing)
End Sub

And I got True

我得到了 True

However, then I tried this and it gave me False

但是，然后我尝试了这个，它给了我 False

Private Sub Workbook_Open()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ThisWorkbook Is Nothing)
End Sub

Now answering your question...

现在回答你的问题...

Question: Is there a way to get hold of properties of the workbook after it has been enabled for editing?

问题：是否有办法在启用编辑后获取工作簿的属性？

Yes. Use ThisWorkbookinstead of ActiveWorkbook

是的。使用ThisWorkbook代替ActiveWorkbook

Followup From Comments

来自评论的跟进

Once the workbook completely loads after you exit the Protected Mode, you would be able to access the ActiveWorkbookobject. To test this, put this code in the protected file.

一旦退出后工作簿完全加载Protected Mode，您就可以访问该ActiveWorkbook对象。要对此进行测试，请将此代码放在受保护的文件中。

Private Sub Workbook_Activate()
    MsgBox "Application.ActiveWorkbook Is Nothing = " & _
    CStr(Application.ActiveWorkbook Is Nothing)
End Sub

You will notice that you get a False

你会注意到你得到一个 False

So once your workbook loads, your add-in can use ActiveWorkbookto interact with the opened file.

因此，一旦您的工作簿加载，您​​的加载项就可以用于ActiveWorkbook与打开的文件进行交互。

Here is another test

这是另一个测试

Private Sub Workbook_Activate()
    MsgBox ActiveWorkbook.Path
End Sub

This is what I got the moment, I exit the Protected Mode

这就是我得到的时刻，我退出 Protected Mode

FOLLOWUP FROM CHAT

聊天跟进

Using ActiveWindow.Parent.Pathinstead of ActiveWorkbook.Pathwould solve the problem.

使用ActiveWindow.Parent.Path而不是ActiveWorkbook.Path可以解决问题。

回答by Charles Williams

Try using Application.Caller.Parent.Parent instead of Application.Activeworkbook

尝试使用 Application.Caller.Parent.Parent 而不是 Application.Activeworkbook

回答by Christian Fries

This is not a complete answer to the original question, but a (dirty) workaround for a problem related to this.

这不是原始问题的完整答案，而是与此相关的问题的（肮脏）解决方法。

I needed ActiveWorkbook to infer the workbooks path, that is ActiveWorkbook.Path.

我需要 ActiveWorkbook 来推断工作簿路径，即 ActiveWorkbook.Path。

An alternative to using ActiveWorkbook.Path is to check for Application.RecentFiles(1).Pathwhich is the path of the most recently opened file. In many cases this will be the workbook for which the user just has "Enabled Editing". However, of course, this method may fail: In the case the used opened another sheet, then enabling the previously opened sheet.

使用 ActiveWorkbook.Path 的替代方法是检查Application.RecentFiles(1).Path哪个是最近打开的文件的路径。在许多情况下，这将是用户仅具有“启用编辑”功能的工作簿。但是，当然，这种方法可能会失败：如果用户打开了另一个工作表，则启用之前打开的工作表。

(Note: ActiveWorkbook.Path give the path of the folder, while Application.RecentFiles(1).Path gives the complete path of the file, so there has to be some post-processing).

（注意：ActiveWorkbook.Path 给出了文件夹的路径，而Application.RecentFiles(1).Path 给出了文件的完整路径，所以需要做一些后期处理）。

回答by Maciej Los

I know it's old thread, but i came across the same issue and i found solution ;)

我知道这是旧线程，但我遇到了同样的问题，我找到了解决方案；)

The only way to go around it, is to use variable type Workbook

解决它的唯一方法是使用变量类型工作簿

Dim wbk as Workbook
Set wbk = Application.ProtectedViewWindows(index).Workbook

Warning:
ActiveSheetreturns Nothingwhen active window is protected too.

警告：当活动窗口也受到保护时，
ActiveSheet返回Nothing。

Dim wsh As Worksheet
Set wsh = wbk.Worksheets(index)

回答by Programmer Dan

I had this same issue today, and neither the accepted answer nor any other answer that I could find on this page or through searching the Google-verse worked for me. I'm using the version of Excel within Office 365, and I figured that was at the root of the problem.

我今天遇到了同样的问题，无论是接受的答案还是我在此页面上或通过搜索 Google-verse 都能找到的任何其他答案都对我有用。我在 Office 365 中使用 Excel 版本，我认为这是问题的根源。

I eventually came to a solution after finding a Microsoft Excel 2010 resourceand hitting the old try-fail cycle for a few hours. Here's what I got:

在找到Microsoft Excel 2010 资源并在几个小时内遇到旧的尝试失败循环后，我最终找到了解决方案。这是我得到的：

Option Explicit
Public WithEvents oApp As Application
Private bDeferredOpen As Boolean
Private Sub Workbook_Open()
    Set oApp = Application
End Sub
Private Sub oApp_WorkbookActivate(ByVal Wb As Workbook)
    If bDeferredOpen Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(Wb)
    End If
End Sub
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
    Dim oProtectedViewWindow As ProtectedViewWindow
    On Error Resume Next
    'The below line will throw an error (Subscript out of range) if the workbook is not opened in protected view.
    Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(Wb.Name)
    On Error GoTo 0
    'Reset error handling
    If oProtectedViewWindow Is Nothing Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(Wb)
    Else
        'Delay open actions till the workbook gets activated.
        bDeferredOpen = True
    End If
End Sub
Private Sub WorkbookOpenHandler(ByVal Wb As Workbook)
    'The actual workbook open event handler code goes here...
End Sub

The difference between the 2010 solution and mine is that I had to call Workbook_Open and explicitly set the oApp variable there, because without that assignment neither the oApp_WorkbookActivate nor oApp_WorkbookOpen functions would fire when I opened the file.

2010 解决方案和我的解决方案之间的区别在于，我必须调用 Workbook_Open 并在那里显式设置 oApp 变量，因为如果没有该分配，则打开文件时 oApp_WorkbookActivate 和 oApp_WorkbookOpen 函数都不会触发。

Figured that someone else might be able to benefit from this, so I posted it, despite the fact that the most recent update to this thread is better than 2 years old.

认为其他人可能会从中受益，所以我发布了它，尽管该线程的最新更新比 2 年前要好。

Best.

最好的事物。

回答by Yathish

Try this code it works.

试试这个代码它的工作原理。

If (UCase(ActiveWorkbook.Name) = ucase("<YOUR XLA NAME WITH EXTENSION>")) Then 
    End
End If
Set wbObj = ActiveWorkbook

First time when you run the macro, it just ends without doing anything. Second time it picks up the proper file.

第一次运行宏时，它只是结束而不做任何事情。第二次它选择正确的文件。