ExcelDna.IntelliSense.xll does not work for auto-loaded .xlam add-ins

Question

ExcelDna.IntelliSense.xll does not work for auto-loaded .xlam add-ins

Nik-MO opened this issue 4 years ago · 20 comments

I am using Excel 2010, and defining VBA functions in a separate .xlam file. The xlam file also contains the IntelliSense sheet.

This all works great, thank you for this project! The function arguments dialog descriptions work for all types of spreadsheets.

I've noticed that the in-sheet Intellisense functionality only works with xlsm spreadsheets. The workaround I have found is if I go into the Addins menu, disable my xlam addin, then go back to the addin menu and re-enable it. For that session, intellisense works now. Next time I open the spreadsheet, back to square one. Of course I can't expect users to do this, or ONLY use xlsm type spreadsheets.

Is this expected behaviour? Is there a possible workaround? I am also running a VSTO addin, so I can add C# code on start-up if needed, or of course run some VBA.

p.s. I also got the Excel-DNA full add-in working, but it was becoming too complicated of a change for what I need (due to our quite large collection of libraries form which I want to run my code), and would hugely prefer to just use the standalone ExcelDna.IntelliSense.xll addin.

Thanks again!

Answer 1 · 2021-01-05T22:15:32.000Z

This sounds like a bug with how the open workbooks are tracked.
I'll have to investigate further.
Are you using ExcelDna.IntelliSense version 1.4.2?

Answer 2 · 2021-01-05T22:19:02.000Z

Looking at the title and your description, I understand it like this:

You have an .xlam file with functions defined in VBA
In the same .xlam file you have the 'IntelliSense' sheet.
You install the .xlam add-in and also install the ExcelDna.IntelliSense.xll add-in.
You open or create and save an .xlsm file.
When editing the functions in the .xlsm file, the in-sheet IntelliSense is displayed.
You then open or create and save an .xls file.
When editing the function in the .xls file, the in-sheet IntelliSense is not displayed.

That would be really strange behaviour. Is this what you are seeing, or have I misunderstood the scenario?

Answer 3 · 2021-01-05T22:24:58.000Z

This sounds like a bug with how the open workbooks are tracked.
I'll have to investigate further.
Are you using ExcelDna.IntelliSense version 1.4.2?

yes, it should be 1.4.2. I just downloaded the file from the Release page yesterday...unless you've uploaded a new version since then.

Answer 4 · 2021-01-05T22:29:34.000Z

Looking at the title and your description, I understand it like this:

You have an .xlam file with functions defined in VBA

In the same .xlam file you have the 'IntelliSense' sheet.

You install the .xlam add-in and also install the ExcelDna.IntelliSense.xll add-in.

You open or create and save an .xlsm file.

When editing the functions in the .xlsm file, the in-sheet IntelliSense is displayed.

You then open or create and save an .xls file.

When editing the function in the .xls file, the in-sheet IntelliSense is not displayed.

That would be really strange behaviour. Is this what you are seeing, or have I misunderstood the scenario?

This sounds exactly right! All the points you mentioned are correct.
XLSX:

XLSM

Answer 5 · 2021-01-05T23:01:19.000Z

Does it matter in which order you open the .xlsx and .xlsm files?
It seems more likely that the window tracking goes wrong, and so that the first or second window doesn't work right, rather than the file type. I don't think there is any code related to the file type.

Answer 6 · 2021-01-05T23:02:45.000Z

I believe I discovered the culprit! Still don't have a solution, but this will help:

When double clicked in Explorer, I have Excel set to open each file in a different instance of Excel by messing with the registry. For example,

Computer\HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\command in the Default key, change /e to /dde.

i.e. from "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /e "%1"
to: "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /dde

I have this set for for all types other than xlsm, lol, of course.

This is still an issue for me, as many of the users have this set up as well.

Also, since I run a VSTO addin, starting Excel from VS has the same issue, even if I re-set to open sheets in same instance. Not really much of an issue for me, but I would think if the above is resolved, it would resolve this as well.

When I reset to open in same instance, everything works as expected.

Answer 7 · 2021-01-05T23:09:14.000Z

Does it matter in which order you open the .xlsx and .xlsm files?
It seems more likely that the window tracking goes wrong, and so that the first or second window doesn't work right, rather than the file type. I don't think there is any code related to the file type.

You are right, it is not the file type, it is how the file is opened, see comment from 5 min ago. Not sure if anything can be done for this, any suggestions are very welcome. Thank you for your time, it is very appreciated.

Answer 8 · 2021-01-06T07:34:18.000Z

I think add-ins like the ExcelDna.IntelliSense are not loaded by Excel when Excel is started with the /e switch (Excel does a kind of fast-start in this case, because the /e normally means Excel is started via COM automation for 'embedding' purposes).

Here seems to be a supported way to ask for new instances, though I've not tried it myself: https://docs.microsoft.com/en-us/office/troubleshoot/excel/force-excel-to-open-new-instance

If your VSTO is loading in all the instances (it uses a different add-in mechanism, so might work differently) then you can load the ExcelDna.IntelliSense with a call to Application.RegisterXLL.

You can programmatically detect whether the add-in is loaded by checking the EXCELDNA_INTELLISENSE_ACTIVE_SERVER environment variable. From VBA: Environ("EXCELDNA_INTELLISENSE_ACTIVE_SERVER"), or from .NET: Environment.GetEnvironmentVariable("EXCELDNA_INTELLISENSE_ACTIVE_SERVER").

Answer 9 · 2021-01-06T07:51:02.000Z

Oh sorry - I see a Note on the link about opening separate instances which says:

This method works only when you use the Excel icon to open the application. If you use File Open within the Excel application or double-click a file in Windows Explorer, the files will still open in the same instance as designed.

So it might not be helpful to you.

Answer 10 · 2021-01-06T18:51:48.000Z

From what I can tell, the add-ins are in fact loaded whichever way a file is opened, they just don't seem to see some workbooks correctly. This can be observed in two different ways:

If you open excel from a shortcut, the default "Book1" sheet does not get intellisense. This happens regardless of registry as discussed above.
If you open with /e "%1" instead of /dde in the registry.

This seems to only be an issue when double clicking a file. If Excel is already open, and you drop an .xlsx file into it, then intellisense works.

I have a feeling this has to do with the fact that WorkbookOpen event does not actually fire in both cases 1 and 2, and some code in your add-in is never executed. If opening with /dde, then the event fires.

On app startup, I have tried using Environment.GetEnvironmentVariable("EXCELDNA_INTELLISENSE_ACTIVE_SERVER") and that returns a value: C:\SomePath\ExcelDna.IntelliSense.xll,29bba2149a21460a99f1d518cf8645e4,1.4.2.

I tried using Application.RegisterXLL() on app startup event, and that doesn't help either.

One thing to mention, when I was playing with the full Excel-DNA addin, intellisense always worked in all cases. Somewhat interesting as it is also an XLL. The reason I am not going this route is to avoid dealing with deployment issues.

Answer 11 · 2021-01-06T20:26:15.000Z

It appears my suspicions about the WorkbookOpen event were correct. I was able to work around the issue by adding the code below to my VSTO add-in in the startup event. I found a very dirty workaround by closing and re-opening the workbook, which fires off the WorkbookOpen event and the add-in now works. Note that you need to make the method async and use the await Task.Delay(1); line as Excel seems to need some CPU time to make this work.

Something interesting that I found while playing with this: When opening a file with /dde (i.e. the standard setting), then Application.Workbooks has zero count, while when opening with /e "%1", Application.Workbooks already contains the workbook, and I am guessing that is why the event never fires. Also, when launching Excel with a shortcut, book1 will already be in the workbooks, but it has no directory, so that is checked in the if statement. It still doesn't work when opened from a shortcut with a new workbook, but I don't really care about that too much.

To me it looks like when opening Excel with default settings, add-ins are loaded first, then the workbook is opened. On the other hand, if opening with the modified registry, or from a shortcut, Excel first loads the workbook, then loads the add-ins. This would explain the WorkbookOpen event issue - it fires, but no one is listening 😄.

private async void ThisAddIn__Startup(object sender, EventArgs e)
{
    if (Application.Workbooks.Count > 0 && Path.GetDirectoryName(Application.Workbooks[1].FullName) != string.Empty)
    {
	var book = Application.Workbooks[1];
	var name = book.FullName;
        book.Saved = true;
	book.Close();
	await Task.Delay(1);
	Application.Workbooks.Open(name);
    }
}

Not a fan of this solution, but this more or less accomplishes what I need. Do you want me to close this issue, or will you still be looking at it?

Answer 12 · 2021-01-06T20:59:15.000Z

@Nik-MO Thanks for digging a bit more and writing it down.

How would I reproduce a scenario where the add-in is installed, but the IntelliSense not working on a sheet?
I would certainly consider this a bug.
(Preferably without getting VSTO involved?)

Answer 13 · 2021-01-06T21:07:47.000Z

My pleasure, and thank you for you time so far!

To reproduce, I think it should be sufficient to just open Excel from a shortcut, such that you end up with the default Book1 workbook open. IntelliSense doesn't work for me in this situation.

You can also change the registry for one of the excel filetypes as I have done. Just follow the steps here.

As far as I can tell, add-ins are always loaded, but the order of opening workbook and loading add-ins changes.

Does this answer your question?

Answer 14 · 2021-01-06T21:35:08.000Z

If I just install the ExcelDna.IntelliSense add-in, and open Excel I get Book2 instead of Book1. This is a known quirk Excel-DNA quirk as it tried to get hold of the Application object. Then I open the test .xlam and IntelliSense works fine.

Do you see different behaviour?
I'm not testing on Excel 2010 though.

Answer 15 · 2021-01-06T21:55:35.000Z

Testing on on Excel 365 (have it on different machine). The registry here has not been messed with.

Note I do not use your full Excel-DNA add-in, only the standalone ExcelDna.IntelliSense.xll. Perhaps you need to disable that for testing?

Here are all the steps from start to end:

Place my xlam and ExcelDna.IntelliSense.xll files onto C:\Users\Nik\AppData\Roaming\Microsoft\AddIns
open Excel from taskbar, File->Options->Add-ins->Excel Add-ins, and enable the two add-ins. Close Excel.
Open Excel from the taskbar shortcut. Click on "Blank Workbook". I do get Book1, not 2.
IntelliSense doesn't work.
Close Excel. Find some excel file in Explorer, and double click on it. When it opens, IntelliSense now works.

Answer 16 · 2021-01-06T22:08:07.000Z

OK great - I can reproduce the problem now. I needed to have the .xlam installed too - previously I was opening an .xlsm afterwards, which does not exhibit the problem.

Answer 17 · 2021-03-22T15:10:40.000Z

I seem to be having the same issue, specifically with release 1.4.2. I have an xlam that makes use of intellisense and thought to upgrade to release 1.4.2. from release 1.3 64 bit with Excel 2016. With release1.4.2 there is NO intellisense of the user defined functions as defined in the special worksheet. I've never seen this behavior before. . Then I tried with releases 1.4.0 and 1.4.1, both of these releases do intellisense. I guess that release 1.4.1 is better than 1.3, so I should use 1.4.1. Loading manually didn't make any difference, I just can't get release 1.4.2 to do intellisense.

Answer 18 · 2021-03-22T18:02:10.000Z

@nVenticDW @Nik-MO I have tried to fix this issue with version 1.4.3.
Please try the prerelease from here: https://github.com/Excel-DNA/IntelliSense/releases/tag/v1.4.3-test

Answer 19 · 2021-08-03T08:54:18.000Z

@lindholmdavid Are you saying the current test version is working well for you? I don't know of problems other than what has been reported here.

Answer 20 · 2021-09-22T19:33:59.000Z

@nVenticDW @Nik-MO I have tried to fix this issue with version 1.4.3.
Please try the prerelease from here: https://github.com/Excel-DNA/IntelliSense/releases/tag/v1.4.3-test

@govert, Finally got around to testing it. Thank you for making the changes, the problem I had previously has been fixed!

However, there is a new weird bug that wasn't there in the previous version. This affects computers with more than one screen. The bug appears in both Excel 2010 and Excel 365. Tested on Windows 10. Only the user functions are affected, but built in Excel functions are fine.

Bug: When you have 2 screens, and make the RIGHT screen the main screen, then when the Excel window is in the left screen, the tooltip gets placed on the other screen rather than next to the cell.

To reproduce: Set your desktop to have two screens. Set the RIGHT screen as the main screen, and put excel into the left screen. When the LEFT screen is the main screen, seems to work fine.