|
Excel as an application user interface?
Our main software product is currently delivered through a web browser interface. We want a richer user interface than a browser can currently deliver, and have investigated a number of alternatives.
Our application is a data entry and analysis application, for our customers to provide transaction and data exchange with their own customers. Over 95% of the end-users of our software run a variety of MS-Windows. The remainder use Macintosh.
We've developed some internal concept prototypes for new user interfaces in RealBASIC and Flash-MX, with RealBASIC seeming like a better alternative.
In discussing our development plans with some of our end users, I noticed that EVERY user of our software had a version of Microsoft Office installed on their workstation. Further, EVERY end-user is an all-day user of Microsoft Excel, and regularly exchanges data with our application through our import/export facilities.
That started me thinking ... would it be possible to build our entire application within Excel? I know we can develop the technical components, that isn't difficult, what I'm not sure about is the ability of Excel to deliver a rich application interface outside the simple grid-of-cells model.
Does anyone have experience of developing data processing applications using Excel as the user interface, or can anyone point me to suitable references that might help my thoughts on this matter?
HeWhoMustBeConfused
Friday, October 17, 2003
I think Excel's great. You have a couple of options, I think:
* Excel supports VBA which has basic facilities for user forms to add to the "rich" GUI capabilities. You could try to look for Excel applications like @Risk or CrystalBall to see how good an interface you could create from within Excel.
* Alternately, you could embed Excel as an OLE object inside a normal Win32/.Net application, and have the entire facilities of the OS at your disposal.
Ankur
Friday, October 17, 2003
VBA is very powerful for integration across the desktop. If you application can take advantage of it, and you are correct that your customers have office (or you are now willing to make it a requirement) go for it.
However, do yourself a favor and not limit it to excel. You did not say what your application was, but if it uses data, make the interface common so it can be summoned from excel, access, even word. A little forethought on this can make your product appear (and be) more powerful than a typical front end.
MSHack
Friday, October 17, 2003
I agree 100% with Ankur. There's also a third option -- develop a managed COM add-in for Excel using VB.Net or C#. (I'm developing a fairly powerful Microsoft Word add-in right now using VB.Net.) There are lots of articles about this on MSDN. A starting point:
http://msdn.microsoft.com/vstudio/office/default.aspx
Microsoft is about to release a new developer suite called Visual Studio Tools for Office that will add some additional capabilities to VB.Net and C#:
http://msdn.microsoft.com/vstudio/office/officetools.aspx
This suite allows you to write code that "runs behind" a particular Office document. E.g., you could write an Excel spreadsheet template that automatically connects to a server, downloads some XML data, and generates a sales report.
I attended a user group meeting where the VSTO team demoed the software, and the team was actively promiting Microsoft Word and Excel as an "environment" for hosting managed applications -- exactly what HeWhoMustBeConfused is thinking of.
You can do all this with regular VBA, of course, but VB.Net or C# can give you some extra capabilities. The only downside is that this would limit you to Win32 for now, as the .Net Framework hasn't been released yet for other OS's.
Robert Jacobson
Friday, October 17, 2003
I have an idea excel is more limited on the mac than it is on windows, if you truly want to develop a xplat solution Id look carefully at the capabilities of excel on the mac, that will be your bottleneck in terms of functionality.
Another vote for REALbasic btw, Im doing an increasing amount of xplat development in that, the flexibility it provides is rather useful.
FullNameRequired
Saturday, October 18, 2003
one issue you will have to deal with is what version of excel the users have. VBA will work on most recent (post 97?) versions, the cool new things like VSTO will only work on Office 2003, there are some in-betweens.
if you have a web service interface to your app, you can build different ui portions. there is a web service toolkit for excel. there are however two versions, one for office XP and one for Office 2003, I have not worked with either.
mb
Saturday, October 18, 2003
VBA first appeared in Excel 5.0 (1994).
Joel Spolsky
Saturday, October 18, 2003
Thanks for your comments, guys, but I may not have been clear enough in my original message.
Our application is for corporate/commercial electronic banking. That's like your personal internet banking on steroids. Our users are corporate treasurers, accountants, people like that.
I'm 100% comfortable with VBA. In fact, our internal code generator is an Access/VBA application which generates C code for at least 1/3 of our software.
I bought the VBA/Excel books by Korol, and Kopfler, which have given me a good enough understanding of how I can manipulate Excel.
What I'm not comfortable/familiar with is the USE of Excel. That is, how to design a user interface which maximises its spreadsheet AND other capabilities. What would probably suit me best are some pointers to other applications based on Excel. I've seen a couple of data mining/analytical tools, but I'd also like to see some data entry or transaction processing components built within this framework.
*grin* ... perhaps I should have used some of my lazy time during the past few years to take a course in accounting ... then Excel would be more familiar than my right hand :)
HeWhoMustBeConfused
Saturday, October 18, 2003
I think you would be making a mistake:
Bloat - You would only need a tiny subset of Excel's facilities. Problem is you can't turn everything else off. In support you will find yourselves constantly firefighting against people using the application in a different way and getting unexpected results.
Security - This leads on from the bloat. Your application will have more backdoors than the Matrix.
Perhaps you could create a good query tool with Excel, but it certainly couldn't handle any real transactions.
Ged Byrne
Saturday, October 18, 2003
Regarding combining Excels form and spreadsheet capabilites, I don't think the two mix very well.
The route to take has always been to develop application in Access, which can then embed spreadsheets and pivot tables, so Excel was never really taken down the application building route.
Unfortunately, there is no Access for the Mac.
Another thing to consider is perception. If the application is delivered as an Excel spreadsheet it will have a lower perceived value and an executable.
Ged Byrne
Saturday, October 18, 2003
Ged, our clients are banks, who buy our software, run it on their servers, and electronically deliver banking services to their clients. It is the bank who pays.
In the case I am considering, Excel couldsimply be a delivery mechanism. I'd like to provide more, however, and that is why I'm shopping for ideas on how to maximise the use of the product.
I'm not concerned about bloat, because I'd LIKE customers to have all the facilities they like to manipulate our data once it is available within their chosen platform. As I stated earlier, I've yet to see an end user of our software who was not (also) an Excel user.
And security is no more of an issue than with any other interface. We would provide a component (DLL, maybe) which implemented the channel to the bank using our existing message infrastructure. This has been running our browser based system since 1996, and we've never had a security breach.
HeWhoMustBeConfused
Saturday, October 18, 2003
I once did a VBA project which had to work on Excel 97 & later and Excel for Mac.
Excel 97 has an old version of VBA so if you use functions like Replace, Split you'll need to write your own versions.
The Mac will be your biggest issue: even using the File Open dialog box can be a problem (KB183750). And of course you won't be able to use Windows specific stuff like ActiveX,...
If you can eliminate your Mac users (in the nicest possible way) it might be feasible.
J D
Saturday, October 18, 2003
VBA appeared in 1994, but the object model changed through the years. The advice to map what versions the users have on their disk is important:
If you write for an older version, it will (almost always) work on the newer version, but not vice versa. The MSDN docs don't usually mention which feature was added in which version, so ... if your "reasonable common denominator" is Office 97, be sure to use Office97 to develop your app.
Ori Berger
Saturday, October 18, 2003
I develop Excel applications for a living, from simple templates to a stock reporting system that included a screenscraper (!) that extracted data from an old mainframe.
The main mistake I initally made was knowing VB and thinking that I knew VBA. Well, I did, but I needed to know Excel. My first apps were horrid, you really need to know what Excel can do. Huge object model!
Where possible, avoid VBA, mainly due to lots of people running Excel 97, paranoid firewalls deleting your files, since macro = virus, and people needing to click 'Enable macros' all the time. When you get stuck, add VBA.
Most of it is easy to figure out, like controls, charts, sorting etc.
Learn filtering, pivot tables (like crosstabs in SQL), the databse functions, consolidation (combine data from multiple sheets), the lookup functions (the infamous VLookUp), conditional formatting, goal seek, sharing, scenarios, grouping and protection, just to name a few.
Excel will drive you nuts. It's Char(), not Chr$(). Assigning with .Value occasionally removes formatting. Use .Value2. What's the difference? Dunno! Stuff in weird places, comments are really shapes, and so on.
Still, it's worth it. Get it running and it will keep running.
AJS
Saturday, October 18, 2003
In my opinion storing data in Excel is marginally safer than storing it on a load of post its, with a note pinned to the wall saying please don't turn the fan on, but that's about it.
Hide cells to the far left of your spreadsheet and then sort the vfisible cells to the right, and hey pronto, you've trashed your data because the hidden cells don't resort! (If they are in the middle they do just to give you tha necessary feeling of false security).
And boy, are you going to have fun with different people opening the same Excel spreadsheets.
I would junk the Macs, and use Access as a front end, storing the data in MSDE, MySQL or MSSQL according to needs. Then write as much code or macros as you want for the data to go into Excel to be ANALYSED but neve, ever let it be ENTERED iOR STORED in Excel.
Stephen Jones
Saturday, October 18, 2003
Excel is good at being a general purpose numerical modeling tool. Good enough for most financial and some scientific applications. As an appication interface, it just plain stinks. No amount of VBA can fix it.
You can make a good living following Excel application developers around and *re-writing* the application in any good general purpose language. Customers love this. And the calculation stuff is usually pretty simple too. Rare that you'll say "no, I can't make that better for a reasonable price"
When all you have is an Excel, everything looks like a spreadsheet.
fool for python
Monday, October 20, 2003
I'm not talking about Bloat as in the number of megabytes, I'm talking about the possible number of variatations that your front end will have to deal with.
An application that has an interface built in Excel will be a maintenance nightmare. Your support team will suffer.
I know, I've been there.
Ged Byrne
Monday, October 20, 2003
Excel is not for everyone or everything, but it is surprising what you can accomplish if you know what you're doing.
Few people really know Excel.
That said, the usual setup is to have the data (and data entry) in Access, with Excel performing analysis.
One advantage of Excel is no installation.
The downside is VBA, with the brilliant solution of annoying the user with the Enable/Disable macro dialog.
It's a shame VBA can't be split into 2 pieces, harmless stuff and 'nasty' stuff, like file handing & CopyMacro.
My current job involves wondering why someone decided to use Crystal Reports to query an Access database... hmmm... I smell resume padding...
If the 'Interface Hall of Fame' still existed, I'd send the screens for this one in.
AJS
Monday, October 20, 2003
---" but it is surprising what you can accomplish if you know what you're doing."---
Yep, for example you can use it to print off attendance sheets, and use nice near zero height rows because you haven't thought of looking closely at borders and shading. If only you could have hooked it up to a webcam to get the expression on my face as I tried to use it as a data source for a mail merge!
Or you can link the workbooks together so that you can write code to deal with 3/4 of a million records from ten different sales offices for your company.
--"One advantage of Excel is no installation."---
Err, same for Access, and you don't keep getting these irritating autosave reminders.
---"Few people really know Excel."---
Thank God for small mercies!
Stephen Jones
Thursday, October 23, 2003
Ah Stephen, why so bitter?
I used to have the same attitude (Excel is for lusers, real men do statistics in machine code, if at all), then I decided to read the manual!
I use Excel for 'disposable' data, you analyse it, scratch your head a while, print prety graphs, then throw it away. Daily logs, monthly sales, etc. Store the data in Access.
For bigger chunks of data, say about 10k records, use SPSS. At that point though, you're probably using a better DB than Access.
As to the topic, the answer is 'No, however...'
Consider this - one of the most common VB 'help-me's' is - 'This grid control sucks, is there a better one?'
I've never had a mail merge problem.
Could be worse, the topic could have been 'Powerpoint as an application user interface?'
AJS
Friday, October 24, 2003
You seem only to have read my last posting.
I recommended Excel for reports, and disposable data. Exactly the same as you.
As to the mail merge, try doing one when the guy has used blank cells to make a double underline, which was what I was talking about.
Stephen Jones
Saturday, October 25, 2003
 Recent Topics
Fog Creek Home
|
