XLL AddIn does not load correctly if VBA is disabled

Mar 13, 2014 at 1:35 PM
Hi Govert

Firstly, thank you for a brilliant piece of work.

I was hoping you could assist with an issue we are facing with deploying ExcelDNA .XLL addins.

They do not work correctly if VBA is disabled in Excel.

Even with a simple add-in it our formulas we create with ExcelDNA are not available in Excel.

This is how I reproduce the issue:
  1. Install a clean windows machine with Excel 2010 only.
    During the install I choose “Not Required” for VBA
  2. I create a very simple xll with just one function that returns "Hello World"
  3. I then go into Excel to and in the Add-Ins section I browse to the sample .xll
  4. I then enter the formula in Excel and it says #NAME - it also doesn't appear in Intellisense
  5. I try the same .xll addin on another machine with Excel 2010 and VBA enabled. It works.
Do you know of any reason why this would occur?

Thank you
Kevin Naidu
Coordinator
Mar 13, 2014 at 1:46 PM
Hi Kevin,

If Excel is installed without the VBA support, that instance will not support any kind of add-ins (even though Excel-DNA and other native add-ins based on the C API / Excel SDK do not depend on VBA directly). No code in the .xll is run, so I don't think we can even show a message or anything.

If you have a MIcrosoft representative or other channel to them, you might like to suggest that Excel at least show a message or something in this case.

-Govert
Mar 13, 2014 at 1:54 PM
Hi Govert

We have already opened a support case with Microsoft.

They will look at it but have also pointed us to the snippet in this link: http://support.microsoft.com/kb/287567/EN-US
"Disabling VBA provides a high level of macro security because disabling VBA prevents macros from running in documents and in non-compiled add-ins such as Excel .xla add-in files. When VBA is disabled, you can still use add-ins that are compiled, such as Excel .xll add-in files, Word .wll add-in files, and Automation Add-ins. Automation to Office programs is still available. However, you may not be able to use certain methods or properties that use VBA. If you are developing an Automation client or an Automation Add-in that may be deployed to computers on which VBA may be disabled, you should test the code to make sure that the code works when VBA is disabled."

I was also under the same impression as you - that disabling VBA would prevent any add-ins from working.
However I try the Analysis ToolPak .xll addin from Microsoft that comes with Excel and that works. The NETWORKDAYS function etc are all available.

If you have any other ideas it would be a great help. I am hoping that Microsoft support are able to provide some info to me. I will post any useful from them on this forum.

Thanks very much for your help
Kevin
Coordinator
Mar 13, 2014 at 2:07 PM
Hi Kevin,

Ah - I see.
I'm sure that no add-ins are supported if VBA is not installed (I've tested this carefully under Excel 2010).
The article also talks about VBA being installed and then 'Disabled'. I've never tried this - I'll have a closer look when I get a chance.

In your case you've not installed VBA either.
Most of the Analysis ToolPak functions are now built into Excel, and no longer require that .xll to be loaded. It is no longer a reliable indication of whether .xlls can load.

For basic usage, the Excel-DNA is a native .xll add-in. If the native code loader runs, but then Excel does something like block the CLR from loading (that would be possible) then Excel-DNA will show a message.

-Govert
Mar 13, 2014 at 2:11 PM
Hi Govert

Thank you for pointing out the installed vs disabled scenario with VBA.

I will also look into that.

Thanks again
Kevin
Coordinator
Mar 13, 2014 at 2:20 PM
Hi Kevin,

I've checked disabling from Method 5 in here: http://support.microsoft.com/kb/281954

Adding the VBAOff key under HKEY_LOCLA_MACHINE didn't make a difference (the VBA IDE still opened). Adding it under HKEY_CURRENT_USER worked (no VBA available anymore). In this situation, no add-ins seem to load - I tried a simple native add-in from the Excel SDK and it also does not load.

I believe the Microsoft documentation you quote is not correct for recent versions of Excel.

-Govert
Mar 13, 2014 at 2:54 PM
Hi Govert

I didn't think of trying that way. I will also try the other suggested method of disabling via group policy.

Thank you again for your speedy help.

I will post you on the response from Microsoft support as well.

Thanks
Kevin