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:
My function is called as a user defined function, i.e., execution order is determined by Excel updating the cell.
The function is not part of the workbook being opened. It is part of an XLA.
I cannot add any code to the workbook which is opened.