How to Enhance Excel as a Distributed Application?

[Johnno]

Hi,

I have distributed an Excel spreadsheet to a number of users, and
while it does what it should do, there are some broader issues that
need to be resolved. Let me explain:

The positives:
1) It has been easy enough to develop so far, within the limit of my
knowledge of VBA.
2) There is a single workbook containing both the VBA code and the
user's data.

The negatives:
1) There is a single workbook containing both the VBA code and the
user's data. As updates are needed, the user will have to delete the
workbook and replace it with the new one, meaning a loss of data.
2) There is code to hide Excel's standard features at start up and
insert a custom toolbar. This works well on Excel 2000 to 2003, but
the custom toolbar does not appear on Excel 2007.
3) Even with a splash screen, the user knows it is Excel. Something
that looks like a stand-alone application might have a higher
perceived value than "just a spreadsheet".
4) Depending on the version of Excel, there is a prompt regarding
macro security settings, which can confuse inexperienced users.
5) Some charts created in Excel 2000 (the earliest supported version)
don't display in Excel 2007.

I'm sure there's a way (or ways) to overcome these issues. What might
they be, and what would be the pros and cons of each?

 

[Tim Zych]

As far as #1, you can write code to replace all of the data, or
alternatively use a "code" workbook, which operates in the background
against a data-only user workbook. That can be an addin or hidden workbook.

#3: How far are you willing to dress up a cat to make it appear as a dog?
You can program and program until the cows come home, but sooner or later
Excel will be "known" and then you're left with all of this code that still
has to put Excel back into the state it existed in before your solution was
used.

 

[Jon Peltier]

#3: How far are you willing to dress up a cat to make it appear as a dog?
You can program and program until the cows come home, but sooner or later
Excel will be "known" and then you're left with all of this code that
still has to put Excel back into the state it existed in before your
solution was used.

My preference is not to hide Excel, because it is so hard to put back
properly (and so many home made utilities are so bad at it). I don't care if
the user knows that Excel is the underpinning of my utility, since most of
my utilities close gaps in Excel.

- Jon

 

[Johnno]

My preference is not to hide Excel, because it is so hard to put back
properly (and so many home made utilities are so bad at it). I don't care if
the user knows that Excel is the underpinning of my utility, since most of
my utilities close gaps in Excel.

- Jon

Thanks. If we leave Excel as the underpinning, is there a way to
overcome the issues to do with Excel 2007 (custom toolbar and charts
not displaying) and the macro security prompts?

 

[Jon Peltier]

Thanks. If we leave Excel as the underpinning, is there a way to
overcome the issues to do with Excel 2007 (custom toolbar and charts
not displaying) and the macro security prompts?

If Excel 2007 is the host application, you need to work with the ribbon
rather than with the old commandbars. It's a pain to get started with, but
its actually pretty easy once you've gotten started.

The charts not displaying issue seems to me (based on non-scientific
analysis, like using my memory) to be a problem with Excel 2003 and earlier
charts in Excel 2007. I have not personally observed this problem, but
whether I'm good or just lucky I don't know. If the project is completely
upgrading to 2007, just rebuild the charts. If you have mixed users, then I
have no advice.

The macro security prompts could be a problem in all Excel versions. There
are tricks to get around them. 'Professional Excel Development' by Bullen,
Bovey, and Green discusses this issue.

- Jon

 

[Johnno]

If Excel 2007 is the host application, you need to work with the ribbon
rather than with the old commandbars. It's a pain to get started with, but
its actually pretty easy once you've gotten started.

The charts not displaying issue seems to me (based on non-scientific
analysis, like using my memory) to be a problem with Excel 2003 and earlier
charts in Excel 2007. I have not personally observed this problem, but
whether I'm good or just lucky I don't know. If the project is completely
upgrading to 2007, just rebuild the charts. If you have mixed users, then I
have no advice.

The macro security prompts could be a problem in all Excel versions. There
are tricks to get around them. 'Professional Excel Development' by Bullen,
Bovey, and Green discusses this issue.

- Jon

Thanks Jon.
Alas, I have mixed users with Excel versions from 2000 to 2007.
The vertical bar charts created with Excel 2000 display in 2007, but
some horizontal stacked bar charts do not. It seems that these latter
charts, if created in 2007, will display in earlier versions.
I've managed to have a look at 'Professional Excel Development', which
suggests a front-lodaer VB6 EXE to start Excel without triggering the
macro security checks. It's probably beyond me , but I'll delve into
it a bit further.

Johnno

 

[Jon Peltier]

Thanks Jon.
Alas, I have mixed users with Excel versions from 2000 to 2007.
The vertical bar charts created with Excel 2000 display in 2007, but
some horizontal stacked bar charts do not. It seems that these latter
charts, if created in 2007, will display in earlier versions.
I've managed to have a look at 'Professional Excel Development', which
suggests a front-lodaer VB6 EXE to start Excel without triggering the
macro security checks. It's probably beyond me , but I'll delve into
it a bit further.

That front-loader was beyond me when I first tried it, but I've figured it
out and made a few little enhancements. It works in 2007 (at least in Win
XP, tho some users with Vista are having problems which may or may not be
related).

- Jon

 

[mryanaz]

Solving the multiple user problem

Sadly, Excel was not really developed as a multi-user application. The ability to have multiple worksheets may allow each user to muck around with their own sheet but can they be trusted to stay away from the sheets of others?

I have run into this situation on a number of occasions and found Distributed Spreadsheet solved the problem. I was able to assign a user to a worksheet and they could only view that worksheet. All formulas were on worksheets not assigned to users so no one but me could see the formulas. When the users sent me back their data, I could merge their numbers into my "master" and see the cumulative results.

I then made any modifications that were needed and sent the worksheets back out to everyone. No one's previously entered data was lost. Everyone seems very happy with the result.