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

Question

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.

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

Since I just need to read some properties (like path name) of the spreadsheet, I could alternatively inspect the availability of Application.ActiveProtectedViewWindow which 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.ActiveProtectedViewWindow is also 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 ).

Question: Is there a way to get hold of properties of the active workbook after 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).

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.

Answer 1

Summary: The problem can be replicated and there are some possible workarounds. The most promising one - resulting from a chat - is to use ActiveWindow.Parent instead of 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

However, then I tried this and it gave me 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 ThisWorkbook instead of ActiveWorkbook

Followup From Comments

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

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

You will notice that you get a False

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

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

FOLLOWUP FROM CHAT

Using ActiveWindow.Parent.Path instead of ActiveWorkbook.Path would solve the problem.

Answer 2

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.

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

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.

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.

Best.