VBA & AddInExpress AddIn

Add-in Express™ Support Service
That's what is more important than anything else

VBA & AddInExpress AddIn
VBA Password request on closing Excel File with VBA Makro 
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

I got your code, thank you. I was very busy as I had a virus on our server(not my computer) and it infected the installation files, so I had to erase the last 2 update versions. It was all a bit of a battle, but seems ok now.

With regard to this topic, I will look at it over the weekend and come back next week.

thank you & have a good weekend.

Michael
Posted 31 May, 2019 08:33:50 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

Thank you very much. The issue is still there. If you open the file I send you in the email yesterday, Press END on the form, go into Developer- Visual Basic, double click VBAProject(Passwordtest.xlsm) but cancel the password prompt. Then close VB Frame and close the file with “Don’t Save”

If I open the file, using your code, my logfile looks like this

19.06.01 , 09:06:00.23 , AdxExcelAppEvents1_WorkbookOpen 1
19.06.01 , 09:06:00.25 , AdxExcelAppEvents1_WorkbookOpen 2
19.06.01 , 09:06:00.25 , AdxExcelAppEvents1_WorkbookOpen 3
19.06.01 , 09:06:00.25 , AdxExcelAppEvents1_WorkbookOpen 4
19.06.01 , 09:06:00.26 , AdxExcelAppEvents1_WorkbookOpen 5
19.06.01 , 09:06:00.28 , AdxExcelAppEvents1_WorkbookOpen 6
19.06.01 , 09:06:00.31 , AdxExcelAppEvents1_WorkbookOpen 7

I do not get any errors

I added a Err.Raise(999) and my log looks as follows:

After opening Passwordtest.xlsm

After pressing END on the Form the following is added to the log

19.06.01 , 09:30:01.28 , Error in DLL _ , alera , Program _ , WorkbookOpen , Error number _ , 999 , Eine Stop-Anweisung wurde gefunden.

So my error trapping works. Wonder why you get Error 1004 and I do not get it.

As far as I read in the net, the issue might be specific to one Windows and Office Version. I am using:

Windows 7 Professional / Service Pack1 64 bit
Microsoft Office 2010 EXCEL 14.0.7232.5000 (32 bit)


Thank you for your help.

kind regards

Michael
Posted 03 Jun, 2019 02:20:56 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

please see my email from now.


Thank you and kind regards

Michael
Posted 04 Jun, 2019 05:44:10 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

I also copy my email from just now into the forum, it might be interesting to some others also.

The VBA macros are not digitally signed, of course my VB.net Excel AddIn has a code signing certificate.

I now did the following:

I completely removed the Ribbon Design in AddInModule [Designer]
I completely removed the AdxExcelAppEvents1 [Designer]

When I the register the AddIn the Password prompt is still there.

As you know, my project has two AddIn’s an Excel AddIn and a Com AddIn

With the Com AddIn removed and the Excel AddIn installed, the Password prompt is still there
With the Excel AddIn removed and the Com AddIn installed, the Password prompt in NOT there

So it must be something in the Excel AddIn which provokes the Password prompt on closing?

Any idea where we should look next?

Thank you & have a nice evening

Michael
Posted 04 Jun, 2019 10:55:27 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

Thank you very much for your post. I did replace XLLModule1.vb but it did not help.

Over the weekend, I did more research and found this interesting articles:

Web Link 1

Web Link 2

I did set LoadBehavior = 2 but realised that this cannot work after reading documentation on LoadBehavior

Still, it looks like my ExcelAddIn has still some resources not released properly.

Do you know if there is a way, how I can check and list all resources which are not released or any other way to ensure all resources are released when the AddIn is closed and before the Excel Instance is terminated?

Thank you for your help an kind regards

Michael
Posted 10 Jun, 2019 04:27:34 Top
Andrei Smolin


Add-in Express team


Posts: 16588
Joined: 2006-05-11
Hello Michael,

Actually, they suggest that you turned off other COM add-ins and Excel add-ins. To turn a COM add-in off, you set LoadBehavior=2 in {HKCU or HKLM}\Software\Microsoft\Office\Addins\{a Prog ID}. To turn off Excel add-ins, use the Excel add-ins dialog window. To prevent loading XLA add-ins, remove them from these Excel default locations:
- Application Templates
- User Templates

Find these default locations specified on this page: File | Options | Trust Center | Trust Center Settings | Trusted Locations.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Jun, 2019 05:10:02 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Hi Andrei,

thank you very much for your prompt response.

if I disable all Com AddIns (with Loadbehavior = 2) and disable all Excel AddIn's in the Excel Option Dialog, then the prompt still comes up as long as my AddIn is installed. No other Com or Excel AddIns show on the Active Application AddIn List. When I remove my excel AddIn then teh promt does no come up. When I then open the file with the VBA and then open the Excel File Option AddIn Dialog and check the alera Com AddIn, then the password prompt comes up 7 times, which is the number of protected sheets in the VBA file.

I believe that my AddIn tries to get access to the VBA code on installing the ComAddIn or disposing/closing of the Excel instance.

Any idea, where I could search next.

Thank you & kind regards

Michael
Posted 10 Jun, 2019 07:26:51 Top
Andrei Smolin


Add-in Express team


Posts: 16588
Joined: 2006-05-11
Michael,

Is there a way for me to get this issue reproducible and debug the code?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Jun, 2019 08:55:55 Top
Michael Kaden




Posts: 241
Joined: 2017-11-15
Dear Andrei,

thank you for your message.

I am not sure how to reproduce the issue on you machine, as even on my machine, the behaviour is not consistent. From all the research I have done, it looks like the problem is a MS Office (Excel) bug which is related to a specific Windows and Excel Version. The bug is then triggered by a specific Code in a third party AddIn.

I use

Windows 7 Professional / Service Pack1 64 bit
Microsoft Office 2010 EXCEL 14.0.7232.5000 (32 bit)

so as a first step you would have to use a machine with the same Versions installed. Is that possible?

Further for you to reproduce the issue, I believe, that you have to use my complete project, possibly set up with Visual Studio 15.8.5 and AddIn Express 8.9.4453

Then you would also have to utilise the exact VBA file I am using.

If this is possible for you, please specify. I will have to find out which exact VB.net project are necessary to be send to you for the complete project. I will do that and send the files to you, as soon as you confirm that you can model a setup with the software Versions as above.

Alternatively I can uprate the AddIn Express to the latest versions. As said before, following the principle, never change a winning horse, I did not upgrade so far. Do you think the upgrade will solve the problem?

Thank you and kind regards

Michael
Posted 12 Jun, 2019 00:10:05 Top
Andrei Smolin


Add-in Express team


Posts: 16588
Joined: 2006-05-11
Hello Michael,

I have VS 2017 15.9.13 and Excel 2010 14.0.7153 32bit on a Windows 7 SP1 64 bit machine. I can install any Add-in Express build. So, I assume, I'm prepared to this journey.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 14 Jun, 2019 09:50:28 Top