Win7 VirtualStore Weirdness with Excel 2002 and earlier

[Greg Lovern]

I thought I'd share a weird experience I had today in case it saves
anyone else some time.

I updated an add-in, updated the installer for it -- which registers
it as an add-in for all installed versions of Excel -- and tested it.
Fine in Windows 2000 in all installed versions of Excel. Boot to
Windows XP, fine there too.

Boot to Windows 7. Fine in Excel 2010, 2007, and 2003. But Excel 2002
and 2000 were still loading the old version of the add-in.

I uninstalled the add-in, made sure it was gone, and reinstalled it.
Same problem in Excel 2002 and earlier; still works fine in Excel 2003
and later.

I opened the VB editor in Excel 2003 and in Excel 2002, and looked at
the code. I can see the differences in the code for the two versions
of the add-in. Also, I had changed the add-in's VBA password, and I
have to use the old password in Excel 2002 and the new password in
Excel 2003.

I assumed that 2002 must be getting the file from some other location.
so I used thisworkbook.path in the Immediate pane, expecting some
other path for 2002. But it's the same path. Wondering if there was a
space in one of the paths, I also check len(thisworkbook.path), but
it's the same in both versions. So I checked thisworkbook.name and
len(thisworkbook.name), and both of those are the same. I alt-tabbed
quickly back and forth between Excel 2002 and 2003 many times, and I
can see the different code and different toolbar look flashing back
and forth, but the text in the Immediate pane is exactly the same --
VBA reports exactly the same filename, filename length, path, and path
length for both versions of the add-in.

So I saved a new change to the add-in in 2002, and made a note of the
system time. Then I closed both and did a search on the drive for the
add-in filename, and sorted by date. I found two with that date/time
stamp:

C:\Users\<mylogin>\AppData\Roaming\Microsoft\Office\Recent\
C:\Users\<mylogin>\AppData\Local\VirtualStore\Program Files\<my add-in
folder>\

I doubted that the Recent one was the problem, but I wanted to rule it
out, so I deleted it and restarted Excel 2002. Same problem.

Then I tried to delete the one under VirtualStore. It said it couldn't
delete it because it was open in Excel. I had left Excel 2002 open
after testing the one in Recent.

So I closed Excel 2002, deleted the one under VirtualStore, and
restarted Excel 2002. Problem fixed; it was now seeing the new version
of the add-in.


BTW, this was all on 32-bit Windows 7. I haven't tested this on 64-bit
Windows 7 yet.



Obviously this could be a problem for my customers upgrading to a
newer version of my add-in while using an older version of Excel (I do
have a tiny percentage of such customers; some are still on Excel 97).
Fortunately, most of those customers are also still on older versions
of Windows (one asked about running the add-in on Windows 2000 just
days ago), so it wouldn't be an issue for them.

But if any of my customers run into this issue, because they're on
Windows Vista (turns out it does the same thing; I just hadn't used it
enough to see this issue) or later but on Excel 2002 or earlier, and
want to upgrade to a newer version of my add-in, I'm not sure what I
should do for them other then give them instructions to manually
delete the one under VirtualStore. I'm not sure that it would be a
good idea for my add-in's installer to try to delete it there.

Any thoughts?


Greg

 

[GS]

Addin installers usually are configured to write the necessary Registry
keys to add/remove/update addins. If you're trying to do this some
other way then I'm not surprised you're experiencing problems.

I have XL8 to XL12 installed and don't have the problem you report
here. It shouldn't matter where you store the addin (1 place only) as
long as your installer updates the appropriate Registry keys. *Note*
that Registry keys must be edited while the addin's host app is not
running.

 

[Greg Lovern]

Addin installers usually are configured to write the necessary Registry
keys to add/remove/update addins.

What addin installers do you mean? I don't know of a setup program
product that knows, out of the box, how to register Excel add-ins for
all versions of Excel.

I assume you don't mean a utility like this...

http://nonotech.blogspot.com/2007/06/excel-add-in-installer.html

....which apparently does the same thing in the registry that my setup
program does anyway.

If you're trying to do this some
other way then I'm not surprised you're experiencing problems.

What I do in my setup program is look in the registry for installed
versions of Excel with which my add-in is compatible, and for each of
them, add the registry entry that registers it as an add-in. For
example:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

I have XL8 to XL12 installed and don't have the problem you report
here. It shouldn't matter where you store the addin (1 place only) as
long as your installer updates the appropriate Registry keys.

My installer installs the add-in in a single place, replacing any
older version of the add-in there. No problem with that part; the
older version does get correctly replaced there.

*Note*
that Registry keys must be edited while the addin's host app is not
running.

Actually that works fine with my installer, not that I would recommend
doing it that way of course. To verify, just now I uninstalled the add-
in, and then opened Excel 2010, 2007, 2003, 2002, 2000, and 97, and
verified that none of them tried to load the add-in. Then, with all of
them running, I installed the add-in . When the add-in's setup program
was done I closed and reopened all of the Excel versions, and all of
them now were loading the add-in, with the add-in working normally.


Greg

 

[GS]

Greg Lovern explained on 5/25/2011 :

What addin installers do you mean? I don't know of a setup program
product that knows, out of the box, how to register Excel add-ins for
all versions of Excel.

Any software 'Setup' utility will do what I'm talking about. You have
to provide the script yourself.

I assume you don't mean a utility like this...

http://nonotech.blogspot.com/2007/06/excel-add-in-installer.html

...which apparently does the same thing in the registry that my setup
program does anyway.

I can't speak to that installer because I don't use it. Is its source
code available?

I use Wise and write my own scripts. What do you use?

What I do in my setup program is look in the registry for installed
versions of Excel with which my add-in is compatible, and for each of
them, add the registry entry that registers it as an add-in. For
example:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
\OPEN1\<my full path to add-in file>

This covers only half the job. You're installer script should also
delete any entry that may exist in the Add-in Manager key in the event
the addin is not listed in the Options key.

I have XL8 to XL12 installed and don't have the problem you report
here. It shouldn't matter where you store the addin (1 place only) as
long as your installer updates the appropriate Registry keys.

My installer installs the add-in in a single place, replacing any
older version of the add-in there. No problem with that part; the
older version does get correctly replaced there.

*Note*
that Registry keys must be edited while the addin's host app is not
running.

Actually that works fine with my installer, not that I would recommend
doing it that way of course. To verify, just now I uninstalled the add-
in, and then opened Excel 2010, 2007, 2003, 2002, 2000, and 97, and
verified that none of them tried to load the add-in. Then, with all of
them running, I installed the add-in . When the add-in's setup program
was done I closed and reopened all of the Excel versions, and all of
them now were loading the add-in, with the add-in working normally.

That's normal behavior. However, if a user is running Excel while you
update the addin it won't be available to them until they close/re-open
Excel. I concede to changing "must be edited" to "should be edited".<g>

 

[Greg Lovern]

Any software 'Setup' utility will do what I'm talking about. You have
to provide the script yourself.

It sounds like we're talking about the same thing. I used an off-the-
shelf installer and added script to register the add-in with the
installed versions of Excel.

I use Wise and write my own scripts. What do you use?

I thought you said in this group that Rob Bovey wrote the script you
use:

"Honestly, I don't know if just writing to the Options keys works in
any version because I've always done it the way I was told to by Rob
Bovey. (Incidently, he provided the install script and is what I use
whenever I install addins via the Addins Manager)"


I'm still using VB6 Package and Deploy. Need to move on when time
allows.

Off the shelf, it has nothing in it for registering Excel add-ins, so
I wrote all that myself.

This covers only half the job. You're installer script should also
delete any entry that may exist in the Add-in Manager key in the event
the addin is not listed in the Options key.

How would that factor into the problem?

Excel writes to that key when the user unchecks an add-in from the Add-
ins dialog. I don't understand how that key could have caused the
original problem unless Excel somehow wrote the VirtualStore path to
the Add-in Manager key. But VBA reported it (per thisworkbook.path) to
be in the installed path, not the VirtualStore path.

I tested just now to see whether Excel 2002 VBA would report the
installed path when the registry entry actually pointed to the
VirtualStore path. I unchecked the add-in from the add-in dialog, so
that Excel removed the add-in from the Options key and added it to the
Add-in Manager key, then went into the registry and changed the path
in the Add-in Manager key from the installed path to the VirtualStore
path, then went back into Excel and checked the add-in in the Add-Ins
dialog. Then I went into the VB editor and checked thisworkbook.path.
It reported the VirtualStore path -- the path I had just put in the
registry -- not the installed path. So, it looks like the fact that
VBA reported the installed path during my original tests shows that
the Add-in Manager key had nothing to do with the VirtualStore path
problem.

Clearly Excel along with its registry entries thought the add-in was
in the installed path, while Windows 7 was redirecting to the
VirtualStore path.


Greg

 

[GS]

Greg Lovern brought next idea :

It sounds like we're talking about the same thing. I used an off-the-
shelf installer and added script to register the add-in with the
installed versions of Excel.



I thought you said in this group that Rob Bovey wrote the script you
use:

"Honestly, I don't know if just writing to the Options keys works in
any version because I've always done it the way I was told to by Rob
Bovey. (Incidently, he provided the install script and is what I use
whenever I install addins via the Addins Manager)"

Yes, Rob did provide me with the original 'Registry key' script. (He
didn't say he wrote it, only that it was what he uses) I used that
script for installing addins in the user's default instance of Excel
(as via the Addins dialog). My addins use their own automated instance
of Excel and so I had to rewrite my installer script accordingly. I do,
however, still use the 'Registry key' script Rob provided me with for
utility addins for installing into a client default instance of Excel.

In any event, the script has to be written since installers don't (as
you state) provide such scripts 'out-of-the-box'. Also, I use Wise for
installing stand-alone Windows apps as well.

Note that with the release of Vista/Win7, all scripts were re-written
to establish access permissions for my projects (XLAs or VB6 EXEs) to
be able to write to their respective folders without being hampered by
UAC. As a result, the only part of the scripts provided by Rob that
remain is the Registry key processing. The addins that use their own
automated instance of Excel don't require the Registry settings because
automated instances don't include installed addins, and I don't want
them 'installed' as addins in the user's default instance. Automated
instances just require the XLA be opened as any workbook would be.

I'm still using VB6 Package and Deploy. Need to move on when time
allows.

Yeah, for sure1 There's a lot of interest in Inno Setup and I think
there's free version of it available.

Off the shelf, it has nothing in it for registering Excel add-ins, so
I wrote all that myself.

Yep, that's why I went with Wise; it's what Rob was using and he
offered me his script without me asking for it. I didn't have VB6 at
the time but I did have the Developer version of MSO2000, which shipped
with the VB P&D wizard. Obviously, I couldn't use his Wise script with
P&D so I bought the Wise package.

How would that factor into the problem?

See below...

Excel writes to that key when the user unchecks an add-in from the Add-
ins dialog. I don't understand how that key could have caused the
original problem unless Excel somehow wrote the VirtualStore path to
the Add-in Manager key. But VBA reported it (per thisworkbook.path) to
be in the installed path, not the VirtualStore path.

I tested just now to see whether Excel 2002 VBA would report the
installed path when the registry entry actually pointed to the
VirtualStore path. I unchecked the add-in from the add-in dialog, so
that Excel removed the add-in from the Options key and added it to the
Add-in Manager key, then went into the registry and changed the path
in the Add-in Manager key from the installed path to the VirtualStore
path, then went back into Excel and checked the add-in in the Add-Ins
dialog. Then I went into the VB editor and checked thisworkbook.path.
It reported the VirtualStore path -- the path I had just put in the
registry -- not the installed path. So, it looks like the fact that
VBA reported the installed path during my original tests shows that
the Add-in Manager key had nothing to do with the VirtualStore path
problem.

Clearly Excel along with its registry entries thought the add-in was
in the installed path, while Windows 7 was redirecting to the
VirtualStore path.

Win7 didn't redirect anything. You just stated that you changed the
setting from installed path to VirtualStore and when you checked in VBE
it reported what you had changed it to. Why would it report something
other that that. The entry from the Add-in Manager key that you changed
to VirtualStore was moved to the Options key when you went back and
checked it in the Addins dialog. Seems to be working correctly to me,
unless I'm misunderstanding what you're saying here.

I'm going to try to explain what you just discovered in terms that also
may help you understand why I mentioned that Excel should not be
running when you run your installer.

There's 2 scenario possibilities at install time:
1. Addin is installed and so is listed in Options key
2. Addin is not installed and so is listed in Add-in Manager key

It can't be listed in both places at the same time, as I expect you're
already aware of. If you write to Options during install while the
addin is listed in Add-in Manager, you must remove it from Add-in
Manager. Since your script doesn't know where it's listed, it's safe to
assume that you should enter it in the next available Options\OPEN key,
but make sure you also remove it from Add-in Manager in case it happens
to be listed there if not 'installed' at the time your installer runs.

Now here's the kicker; there's a separate set of keys for each version
of Excel that's installed on the target machine. That means each
version must be in its own folder under Program Files\Microsoft
Office\. That precludes that your installer must loop through to find
every installed version, then update both Registry keys for each
version. Doing this causes all installed verions to use the same
addin.xla file.

 

[Greg Lovern]

Win7 didn't redirect anything. You just stated that you changed the
setting from installed path to VirtualStore and when you checked in VBE
it reported what you had changed it to. Why would it report something
other that that. The entry from the Add-in Manager key that you changed
to VirtualStore was moved to the Options key when you went back and
checked it in the Addins dialog. Seems to be working correctly to me,
unless I'm misunderstanding what you're saying here.

That was exactly my point and the reason for that test. It sounds like
you didn't understand my original post.

My test showed that the path reported by thisworkbook.path is the same
path that Excel gets from the registry. Therefore, my test showed that
the original problem would not have been caused by the VirtualStore
path being in the registry while thisworkbook.path reported the
install path, because if the VirtualStore path had been in the
registry at the time of the problem, then thisworkbook.path would have
reported the VirtualStore path.

But when the original problem occured:
-- thisworkbook.path in Excel 2002 reported the install path, BUT:
-- saving a change to the add-in in Excel 2002 did NOT update the
time stamp on the file in the install path, AND:
-- saving a change to the add-in in Excel 2002 DID update the time
stamp on the file in the VirtualStore path, AND:
-- the file in the VirtualStore path could not be deleted while Excel
2002 was running because Excel 2002 had it open, AND:
-- deleting the file in the VirtualStore path fixed the probem.

Therefore, at the time the problem occured:
-- The registry entry for Excel 2002 was the install path, NOT the
VirtualStore path.
-- As far as Excel 2002 was concerned, it had opened the file from
the install path, NOT the VirtualStore path. However:
-- The file was ACTUALLY opened from the VirtualStore path, NOT the
install path. Therefore:

Windows 7 SP1 was doing some kind of redirect. Excel 2002 thought it
had the file open from the install path, but in fact it had it open
from the VirtualStore path.

I'm going to try to explain what you just discovered in terms that also
may help you understand why I mentioned that Excel should not be
running when you run your installer.

There's 2 scenario possibilities at install time:
  1.  Addin is installed and so is listed in Options key
  2.  Addin is not installed and so is listed in Add-in Manager key

It can't be listed in both places at the same time, as I expect you're
already aware of. If you write to Options during install while the
addin is listed in Add-in Manager, you must remove it from Add-in
Manager. Since your script doesn't know where it's listed, it's safe to
assume that you should enter it in the next available Options\OPEN key,
but make sure you also remove it from Add-in Manager in case it happens
to be listed there if not 'installed' at the time your installer runs.

In my tests, it works fine to have it listed in both places at the
same time. If I close Excel and write it to both places, then start
Excel, the add-in is enabled and works normally. If the user removes
it from the Add-In dialog, then closes and restarts Excel, it's still
there in both places. If the user then checks it back on in the Add-In
dialog, then closes Excel, Excel removes it from the Add-In Manager
key, so then it's back to being in one place. Excel never puts it in
both places at the same time, but it doesn't seem to mind having it in
both places at the same time.

Yet you say that can't work. Do you have repro steps that show how
that doesn't work for you?

In my tests, the only problem that happens if it isn't removed from
the Add-In Manager key is that if it isn't removed from the Add-In
manager key when the add-in is *uninstalled*, and the user then goes
into the Add-In dialog and checks it (because it's still listed there
though unchecked), Excel gives them a friendly message that it can't
find the add-in and offers to remove it from the list. This doesn't
surprise the user since they probably remember uninstalling the add-
in. The user chooses OK and Excel removes it.

Do you have repro steps for any other, more serious problem that is
caused by not removing the add-in from the Add-In Manager key?

Now here's the kicker; there's a separate set of keys for each version
of Excel that's installed on the target machine.

Anyone can see there's a separate set of registry keys for each
installed version of Excel by looking at the registry; it's as plain
as day. It was one of the first things I noticed about 6 years ago
when I set about writing the installer code to register my add-in with
all installed versions of Excel.

That means each
version must be in its own folder under Program Files\Microsoft
Office\.

The reason each version needs its own folder under Program Files
\Microsoft Office\ is because if they didn't, there would be a lot of
filename conflicts, such as all versions wanting Excel.exe to be in
the same folder. If someone were to try to install multiple versions
of Office to the same folder under Program Files\Microsoft Office\,
probably the last one they installed would "win", with the other
versions represented only by a few stray files and registry entries
that were unique to that version.

That precludes that your installer must loop through to find
every installed version, then update both Registry keys for each
version. Doing this causes all installed verions to use the same
addin.xla file.

Yes, my installer code loops through all versions listed in the
registry under HKCU\Software\Microsoft\Office\ with which the add-in
is compatible. It also looks for an infinite number of future versions
-- I didn't have to update the loop for Excel 2007 and 2010, though I
won't be surprised one day when Microsoft changes how add-ins are
registered in some future version of Excel, requiring a change to my
install code.


Greg

 

[Peter T]

In any event, the script has to be written since installers don't (as you
state) provide such scripts 'out-of-the-box'. Also, I use Wise for
installing stand-alone Windows apps as well.

Note that with the release of Vista/Win7, all scripts were re-written to
establish access permissions for my projects (XLAs or VB6 EXEs) to be able
to write to their respective folders without being hampered by UAC.

I'd be very interested in that, Inno if pos

There's 2 scenario possibilities at install time:
1. Addin is installed and so is listed in Options key
2. Addin is not installed and so is listed in Add-in Manager key

To be very pedantic there's a third possibility. Addins located in one of
the default addins folders are automatically added to the Addins collection,
but if not installed will not be referred to in the Add-in Manager key.

Regards,
Peter T

 

[Peter T]

Greg, I don't follow where things aren't working for you but I can only echo
everything Gary has been trying to explain. This script by KeepItCool for
VB6 does just about everything but not the new permissions stuff.

https://groups.google.com/group/mic...mming/msg/e3c6020456329d67?hl=en&dmode=source

Regards,
Peter T

 

[GS]

Windows 7 SP1 was doing some kind of redirect. Excel 2002 thought it

had the file open from the install path, but in fact it had it open
from the VirtualStore path.

I only have Win7 x64 and so I can't speak to x32, but I don't have any
problems with my installer in x64. I did use it on Win7 x32 but it was
for an addin using an automated instance, which doesn't require writing
to the Registry keys for addins.

In my tests, it works fine to have it listed in both places at the
same time. If I close Excel and write it to both places, then start
Excel, the add-in is enabled and works normally. If the user removes
it from the Add-In dialog, then closes and restarts Excel, it's still
there in both places. If the user then checks it back on in the Add-In
dialog, then closes Excel, Excel removes it from the Add-In Manager
key, so then it's back to being in one place. Excel never puts it in
both places at the same time, but it doesn't seem to mind having it in
both places at the same time.

Yet you say that can't work. Do you have repro steps that show how
that doesn't work for you?

I didn't say it "can't work". I said it can't be listed in both places
at the same time. Although, I do see the inference that statement
implies. Since you understand that Excel never puts it in both places
at the same time, what I meant is that our installer should respect
that and behave according. Since our installer needs to be configured
to test where it's list to determine what to do, it's easier to just
edit the Options key so it's 'installed' AND remove it from Add-in
Manager in case it's listed there at install time.

Do you have repro steps for any other, more serious problem that is
caused by not removing the add-in from the Add-In Manager key?

There is no problem that I can think of. I just claim it's 'good
practice' to *not deliberately* cause it to be listed in both places at
the same time. I don't think it's a good idea to leave things in a
state that requires the user to toggle the addin in the Addins dialog
just to compensate for being lazy about including an extra line of
script in my installer<IMO>.

Other than that, it looks like you've got installer concerns handled
fairly well.

The reason I went to using automated instances is because the installer
scrips where getting too long for including each version I needed to
write Registry keys for. Automated instances use whatever version is
the current default version running on the target machine. I use a
FrontLoader.EXE (sample code also provided by Rob Bovey) to start the
Excel instance only if all startup criteria is met. I find this works
better for task-specific solutions because I can lock down the instance
to the point where users may not be aware they're using Excel. (I
change the Title Bar caption & icon to use those of the app, and only
provide menus needed for the task)

 

[GS]

Peter T formulated on Thursday :

I'd be very interested in that, Inno if pos

I don't have an Inno nor a Wise script for this. I use a VB6.Exe that I
install temporarily to the install folder, run it, then delete the
file.

You can read about and download sample VB code (Part1/Part2) at:

http://www.jsware.net/jsware/vbcode.php5#perms

Note that this requires your installer be 'Run As Administrator'!

To be very pedantic there's a third possibility. Addins located in one of the
default addins folders are automatically added to the Addins collection, but
if not installed will not be referred to in the Add-in Manager key.

Yes, I'm aware of that too and so is why I did not mention it in
context to installers writing the Registry.

 

[Greg Lovern]

Greg, I don't follow where things aren't working for you but I can only echo
everything Gary has been trying to explain.

Garry does not appear to understand the problem behavior as explained
in my first post.

This script by KeepItCool for
VB6 does just about everything but not the new permissions stuff.

https://groups.google.com/group/microsoft.public.excel.programming/ms...

Thanks, but that only checks for the following versions:

For Each vVers In Array("11.0", "10.0", "9.0", "8.0")

You could add more versions to that of course, but mine quickly finds
ALL installed versions, including any number of future versions, at
least until Microsoft changes how add-ins are registered.

It does that by enumerating all registry keys right below HKCU\Software
\Microsoft\Office\, and making an array out of all of those keys that
can be resolved as a number. So the array then consists of all
installed version numbers, at least until Microsoft changes how they
do it. No version numbers are hard-coded.* I wrote the code in 2005,
and did not have to update it for Excel 2007 and 2010; it
automatically found them. That means that product I shipped as early
as 2005 is still fully compatible with Excel 2010, automatically
registering as an add-in in Excel 2010.

Also, mine is integrated into setup, not a separate command-line tool.
But come to think of it, moving it out into a separate command-line
tool might be better.


*Except later on in the code when I'm writing add-in registration
keys, where I special-case Excel 97 because it doesn't have the
Options key; instead it has a "Microsoft Excel" key. The same is
probably true of earlier versions but 97 is the earliest my add-in is
compatible with, so my installer code skips any versions earlier than
97 anyway. The code you pointed to handles that like this, which also
assumes there are no versions earlier than 97:

sKey = HKMS & "\" & vVers & "\Excel" & IIf(vVers = "8.0",
"\Microsoft Excel", "\Options")


Greg

 

[Greg Lovern]

I only have Win7 x64 and so I can't speak to x32, but I don't have any
problems with my installer in x64.

I don't appear to have any problems with my installer either. It
writes the correct registry keys, and Excel thinks it opened the add-
in from the path in the registry key written by my installer.

The problem is that in Excel 2002 and earlier the add-in is ACTUALLY
opened from a different path than the path that Excel appears to think
it opened it from, per thisworkbook.path and the registry key. In
other words, the registry key and thisworkbook.path point to the
installed path, when in fact the add-in is actually opened from the
VirtualStore path.

I don't think it's a good idea to leave things in a
state that requires the user to toggle the addin in the Addins dialog

There is no need for the user to do that. Excel doesn't mind having it
in both places.

The reason I went to using automated instances is because the installer
scrips where getting too long for including each version I needed to
write Registry keys for.

Assuming you looped through the installed versions, which takes a
fraction of a second to enumerate on even an old computer with several
Excel versions installed, why would adding a version make your code
significantly longer?

Automated instances use whatever version is
the current default version running on the target machine. I use a
FrontLoader.EXE (sample code also provided by Rob Bovey) to start the
Excel instance only if all startup criteria is met. I find this works
better for task-specific solutions because I can lock down the instance
to the point where users may not be aware they're using Excel. (I
change the Title Bar caption & icon to use those of the app, and only
provide menus needed for the task)

My add-ins add features to Excel, so that wouldn't work for me. The
features added by my add-ins are always available from beginning to
end of all Excel sessions.


Greg

 

[GS]

Greg Lovern expressed precisely :

*Except later on in the code when I'm writing add-in registration
keys, where I special-case Excel 97 because it doesn't have the
Options key; instead it has a "Microsoft Excel" key. The same is
probably true of earlier versions but 97 is the earliest my add-in is
compatible with, so my installer code skips any versions earlier than
97 anyway. The code you pointed to handles that like this, which also
assumes there are no versions earlier than 97:

sKey = HKMS & "\" & vVers & "\Excel" & IIf(vVers = "8.0",
"\Microsoft Excel", "\Options")

Version 9 is the oldest any of my users had active and so I've never
needed to have script for V8. The Registry script Rob gave me also
starts with V9 and so I needed nothing further. Your contribution of
this info is, however, good to know. Thanks for including it!

As for version compatibility, I usually go back 2 versions from current
release. This means 2003 would become the earliest version I develop
under as of the release of v2010, but some of my users still run v2002
and so I continue to support it. (Not that there's a lot of difference
v10 to v11)

 

[GS]

It happens that Greg Lovern formulated :

I don't appear to have any problems with my installer either. It
writes the correct registry keys, and Excel thinks it opened the add-
in from the path in the registry key written by my installer.

The problem is that in Excel 2002 and earlier the add-in is ACTUALLY
opened from a different path than the path that Excel appears to think
it opened it from, per thisworkbook.path and the registry key. In
other words, the registry key and thisworkbook.path point to the
installed path, when in fact the add-in is actually opened from the
VirtualStore path.

I see what you're saying. As stated, I haven't installed an addin in
Win7 x32 and so I can't speak to this. I have installed addins to Win7
x64 running MSO x32 without issue and so I know the problem you're
having doesn't happen in this scenario. Since you didn't say what
OS/MSO combination you're using I can't test for duplicating the
problem.

There is no need for the user to do that. Excel doesn't mind having it
in both places.

Whether Excel minds or not wasn't my point!

Assuming you looped through the installed versions, which takes a
fraction of a second to enumerate on even an old computer with several
Excel versions installed, why would adding a version make your code
significantly longer?

The script Rob provided handles each version in a separate code block.
It's configured to loop the Registry keys and load Boolean vars for
each version found, then edits the keys based on that later on. I'm
sure the script can be refined to run more efficiently but since I've
moved away from writing feature add-ons for default Excel I didn't have
any interest in reworking Rob's portion of my scripts for those rare
occasions where, like you, an addin may be purely for the purpose of
adding features to Excel.

My add-ins add features to Excel, so that wouldn't work for me. The
features added by my add-ins are always available from beginning to
end of all Excel sessions.

Yeah, I understand that. My addins used to all be run that way but over
time clients wanted more task-specific solutions that didn't include
all the distractions of the normal Excel UI. The most I do now for
standard instance of XL is for my own use and all code is stored in
PERSONAL.XLS.

 

[Greg Lovern]

As for version compatibility, I usually go back 2 versions from current
release. This means 2003 would become the earliest version I develop
under as of the release of v2010, but some of my users still run v2002
and so I continue to support it. (Not that there's a lot of difference
v10 to v11)

Given how quick and easy it is to enumerate all installed versions and
register in all of them, you might be limiting your user base
unnecessarily. Just a few months ago I had a conversation with a
customer who was using one of my add-ins in Excel 97.

That isn't very common in the USA and other wealthy parts of the
world, but it's more common in less wealthy areas. Many of my
customers who have asked me about running my add-ins in earlier
versions of Excel have been in Eastern Europe (my add-ins are
internationalized for input and output, though not for
documentation).

Greg

 

[Greg Lovern]

The script Rob provided handles each version in a separate code block.

Wow. It would be interesting to hear why he did it that way. Hard to
believe that a guy who writes books about Excel like "Professional
Excel Development" would do it that way. You'd have to copy and paste
a new code block for each new version of Excel, and if you ever wanted
to change the code, you'd have to make the same change to each code
block.

The only experience I've had with Rob Bovey's code is when I wanted to
position a userform at a cell. He has a section in the 2nd edition of
"Professional Excel Development", which says on the back that it is
"fully updated for Excel 2007", on how to do that. When I tried it, I
found that it didn't work in Excel 2007 or 2010, but worked fine in
Excel 2003 and earlier. I used Spy++ to investigate and found that the
special Excel window he was using to get the target position did not
exist on Excel 2007 or 2010, and no equivalent window existed either.
Then I searched the web on what I had found, and found that some user
had pointed that out to him (that's where I learned it was his code
rather than either of the other coauthors), on the publisher's forum
if I recall correctly, and he agreed there that it did not work on
2007.


Greg

 

[GS]

Greg Lovern wrote :

Given how quick and easy it is to enumerate all installed versions and
register in all of them, you might be limiting your user base
unnecessarily. Just a few months ago I had a conversation with a
customer who was using one of my add-ins in Excel 97.

That isn't very common in the USA and other wealthy parts of the
world, but it's more common in less wealthy areas. Many of my
customers who have asked me about running my add-ins in earlier
versions of Excel have been in Eastern Europe (my add-ins are
internationalized for input and output, though not for
documentation).

Greg

Greg,
That's a valid point. Please know I'm not looking to expand my user
base, but rather looking to downsize it. (I'm 60 yrs old & have Lou
Gehrig's, and so I don't look much further than hoping I wake up
tmo!<g>) So running with what my current clients use is all I'm willing
to do.

I have not deliberately 'internationalized' my projects because my
clients are all in Canada/USA. I expect that any competant local
programmer could convert my project to their locale easily enough and
so I have no desire to cater to that right now.

I commend you on your ambitions, and wish you all the best in your
endeavors (however far you want them to reach)! Also, your
contributions to the NGs has always been appreciated by me and so I bid
you to keep up the good work...

 

[GS]

Greg Lovern pretended :

Wow. It would be interesting to hear why he did it that way. Hard to
believe that a guy who writes books about Excel like "Professional
Excel Development" would do it that way. You'd have to copy and paste
a new code block for each new version of Excel, and if you ever wanted
to change the code, you'd have to make the same change to each code
block.

As I stated earlier, Rob did not say he wrote the Wise script; only
that it was what he uses. Yes, you are correct in your assessment of
how the script works. I won't speak my opinion on that beyond what I
already stated about it needing some refinement. Otherwise, any code
samples I've received from Rob worked as expected. Also, any code I've
received from Rob is pretty well documented and follows along the same
quality as does his source code for the CodeCleaner project.

In any cases where I had problems it was usually my misunderstanding of
what the text was saying. I don't have the 2nd edition of PED but I do
have the Excel 2007 VBA edition of their Programmer-to-Programmer
series. These get me bye for now but I know I'll be looking for more
after I install v2010, once I decide which machines to install it on
and find out whether x64 and x32 can coexist on the same machine.
Meanwhile, Rob is always just an email away and is usually very
receptive to answering Qs about the content of his publications.
(That's why the authors provide their contact info) I, for one, am very
grateful for the relationship he has nurtured between us. He's a busy
fella, but will always answer my mail when he can break away from his
work.

 

[Greg Lovern]

after I install v2010, once I decide which machines to install it on
and find out whether x64 and x32 can coexist on the same machine.

The v2010 installer won't install both bitnesses in the same
installation of Windows. I keep the two bitnesses in separate
partitions, each with its own Windows installation.

BTW if you don't already have it, EasyBCD makes multibooting with
Vista and Win7 very easy. It even lets me have Windows XP and 2000
both on my main boot menu instead of the Vista/Win7 boot menu having
one entry that goes to the legacy NT/2000/XP boot.ini boot menu.

It would be interesting to hear of a hack (Danger, Will Robinson!) to
get both Office 2010 bitnesses to install and work correctly in a
single installation of Windows, but last time I searched I didn't find
one.


Greg