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 
Subscribe
Michael Kaden




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

Yes the method is called and all code is executed.
The Handels clause is commented out as we had Problems with the "Friend WithEvents"

So we added

AddHandler AdxExcelAppEvents1.WorkbookOpen, AddressOf AdxExcelAppEvents1_WorkbookOpen


in Sub New in the AddinModule Class

To investigate further, I completely removed the AdxExcelAppEvents from the project, but the VBA password prompt does still come up when I follow the procedure:

Open Password test.xlsm
Click "End" on user form
Open Developer - Visual Basic
Double click VBA Project (Password test.xlsm)
Cancel VBA Project password prompt
Close VBA project
Close Password test.xlsm

The password prompt comes up

I have now done some internet research and many point into the direction, that the cause is non released objects.

MS on password prompt

if I interpreted the following article correct, there might be proxy objects created which I cannot release?

Excel OLE Embedding Errors

The fact that Windows releases objects through the Garbage Collector at a non predictable time, might be the cause that sometimes the bug does not happen?

I was under the impression that the VBA Workbook will only "use" Events such as WorkbookOpen etc. from the VB.net project and if I make sure that all objects are released in these event Methods, then no more objects are open? Is that not right?

Any idea what to try next?

Thank you & kind regards

Michael
Posted 23 May, 2019 01:19:57 Top
Andrei Smolin


Add-in Express team


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

I've never been able to reproduce the issue with my code. Can you send me your code so that I build and test it here? You can send it to the support email address; please make sure your email contains a link to this topic.

Releasing COM objects is required. Connect to the WorkbookOpen event of the ADXExcelAppEvents component at the design time. Do not use WithEvents or AddHandler to directly connect to the WorkbookOpen event of the Excel.Application object. The reason is: ADXExcelAppEvents correctly implements everything required for you to connect/disconnect to/from that event. We aren't sure that VB.NET implements this *always* correctly.

I strongly suggest that you check section Releasing COM objects at https://www.add-in-express.com/docs/net-office-tips.php#releasing.

Michael Kaden writes:
The fact that Windows releases objects through the Garbage Collector at a non predictable time, might be the cause that sometimes the bug does not happen?


Since it works in an unpredictable fashion, we must release COM objects in .NET explicitly.

Any given VBA macro releases COM objects correctly; the code the VBA macro generates ensures this.

To make VBA code leave COM objects non-released, you should write quite a complex construct; they give such an example at https://support.microsoft.com/de-de/help/280454/password-prompt-for-vba-project-appears-after-excel-quits.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 23 May, 2019 08:28:20 Top
Michael Kaden




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

thank you for your response. From your Post:

Hello Michael,

Thank you. I've reproduced the issue when using your add-in in Excel 2010 with my workbook. I can't reproduce the issue with my add-in (it prints the workbook name in the WorkbookOpen ......



I assumed that you could reproduce the issue?

I would like to send you the code of my AddIn, however in my project folder I have 535 Mb. So how can I just copy the files which are needed for you to install the project?

Thank you & kind regards

Michael
Posted 23 May, 2019 10:26:22 Top
Andrei Smolin


Add-in Express team


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

Michael Kaden writes:
I assumed that you could reproduce the issue?


I can't reproduce it using my code and I don't have yours.

I suggest that you send me the add-in module files: AddinModule.vb and AddinModule.Designer.vb (if applicable). I'll use them to create a test add-in that only uses the part of your code that relates to the issue. If it appears that the issue isn't reproducible for me and for you, I'll add the panes that you use (if any) and we repeat the cycle.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 23 May, 2019 10:34:55 Top
Michael Kaden




Posts: 212
Joined: 2017-11-15
Thank you Andrei,

have send the email just now.

kind regards

Michael
Posted 23 May, 2019 13:20:18 Top
Andrei Smolin


Add-in Express team


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

I sent you an email asking for some files...

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 28 May, 2019 03:29:15 Top
Michael Kaden




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

than you for your post. I cannot find your email. Kindly advise to which email address it was send and at what time.

Thank you very much & kind regards

Michael
Posted 28 May, 2019 09:30:53 Top
Andrei Smolin


Add-in Express team


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

I replied to your email that you sent to our support email address. Check Junk emails folder in your Outlook. I suggest that you right-click my email, expand Junk in the context menu and choose an option that applies best.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 29 May, 2019 05:16:07 Top
Michael Kaden




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

Ok found your mail. It was actually not in the Junk/Spam but hidden inside of some 100 forum@add-inexpress mails. I changed the mail rule now.

Thank you. I have send you the new ZIP file just now.

Thanks again and kind regards

Michael
Posted 30 May, 2019 02:38:34 Top
Andrei Smolin


Add-in Express team


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

Did you check the code I sent you by email?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 31 May, 2019 02:58:42 Top