A couple annoying VBA add-in things
 

Hi,

I've working on this big (by my standards) VBA add-in and I find
several little things annoying:

1. If the user attempts to close Excel, but the opts out of it,
Auto_Close still seems to get executed. This deletes the attached
menu, for example.

2. I gave my add-in a very nice description, such as "My Wonderful
Addin", but in the "Add-ins available" window it appears as
"Mywonderfuladdin".

3. I keep working on the addin while it is a regular workbook and then
save it as an addin. (I need (?) to do this because it has a couple of
spreadsheets which are not visible if it is an add-in.) Is there a way
avoid doing this? It forces me to do a lot of
opening/saving/loading/unloading, etc.

Thanks!

Bura

A couple annoying VBA add-in things
 

Hi Bura,

1. If the user attempts to close Excel, but the opts out of it,
Auto_Close still seems to get executed. This deletes the attached
menu, for example.

There is a way around this:

Normal module:

Option Explicit

Public bUnInstalling As Boolean

Sub auto_close()
'Of course this may go in the Thisworkbook_BeforeClose event too
'Your Code to tidy up goes here

'This sets an ontime macro that fires when Excel is NOT closed
'Set bUnInstalling to True in code that YOU use to close the Addin
'so the addin doesn't init again
Application.OnTime Now, "'Reinstate """ & bUnInstalling & """'"
End Sub

Sub Reinstate(bUnInstalling As Boolean)
If bUnInstalling Then 'avoid reinstate after Add-in uninstall
ThisWorkbook.Close False
Exit Sub
End If
'Your code to make sure menu's are placed and other initialisation is
done
End Sub

2. I gave my add-in a very nice description, such as "My Wonderful
Addin", but in the "Add-ins available" window it appears as
"Mywonderfuladdin".

Use the Title in the File, Properties dialog. This should show up
unchanged in the addins list (At least in XP).

3. I keep working on the addin while it is a regular workbook and then
save it as an addin. (I need (?) to do this because it has a couple of
spreadsheets which are not visible if it is an add-in.) Is there a way
avoid doing this? It forces me to do a lot of
opening/saving/loading/unloading, etc.

I always do it this way because some Excel versions don't like saving
from the VBE. But you could change the IsAddin property of the
thisworkbook object to False to be able to see the worksheets. Then set
back to True to hide them again.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

A couple annoying VBA add-in things
 

In addition to Jan Karel's comments.

2. You cam also set the Comments in the File Properties as a small
description, which will display in the add-in manager in Excel when the
add-in is selected in the list.

3. I think this is the best way to do it. A|bstract the source from the
executable, and backup the source.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan Karel Pieterse" wrote in message
...
Hi Bura,

1. If the user attempts to close Excel, but the opts out of it,
Auto_Close still seems to get executed. This deletes the attached
menu, for example.

There is a way around this:

Normal module:

Option Explicit

Public bUnInstalling As Boolean

Sub auto_close()
'Of course this may go in the Thisworkbook_BeforeClose event too
'Your Code to tidy up goes here

'This sets an ontime macro that fires when Excel is NOT closed
'Set bUnInstalling to True in code that YOU use to close the Addin
'so the addin doesn't init again
Application.OnTime Now, "'Reinstate """ & bUnInstalling & """'"
End Sub

Sub Reinstate(bUnInstalling As Boolean)
If bUnInstalling Then 'avoid reinstate after Add-in uninstall
ThisWorkbook.Close False
Exit Sub
End If
'Your code to make sure menu's are placed and other initialisation is
done
End Sub

2. I gave my add-in a very nice description, such as "My Wonderful
Addin", but in the "Add-ins available" window it appears as
"Mywonderfuladdin".

Use the Title in the File, Properties dialog. This should show up
unchanged in the addins list (At least in XP).

3. I keep working on the addin while it is a regular workbook and then
save it as an addin. (I need (?) to do this because it has a couple of
spreadsheets which are not visible if it is an add-in.) Is there a way
avoid doing this? It forces me to do a lot of
opening/saving/loading/unloading, etc.

I always do it this way because some Excel versions don't like saving
from the VBE. But you could change the IsAddin property of the
thisworkbook object to False to be able to see the worksheets. Then set
back to True to hide them again.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com