Where’s the innovation?
Although I get a fair number of visitors to my web site - www.oaltd.co.uk - many of whom seem to be interested in its contents (I’m approaching the millionth download), I receive almost no comments about it. So I use Google to locate pages linking to my site to read what they have to say about it. I saw one yesterday that described my site as ‘aged’. Though not exactly a great endorsement, that single word does (I guess) sum up my site - and most of the Excel sites I come across. All the truly innovate breaking-new-ground pages and examples seem to be dated from the last century (but they’re still totally relevant). All the new pages (even including this blog) seem to just be explaining the same old techniques in new ways and/or to the new users that will always come along. I read the support newsgroups every day and scan the various online forums occasionally, but again I always see the same questions and the same answers.
So, who is breaking new ground in Excel now? Who can point to any techniques that have appeared on the scene in recent years and been generally adopted by the Excel community? Where is the innovation?
Regards
Stephen Bullen
Hi Stephen,
I guess this lack of new things to talk about all point to the same cause: Microsoft has not added any really new (useful) features since Excel 97 (or even Excel 5).
Nor fixed many bugs in the current century.
Although they *did* degrade the help system in the latest release. So there ought to be a raft of new questions since the help is now less useful than before.
Hi Stephen
I totally agree with Jan Karel. How could one expect innovation if the application/the basis itself has not changed that much over the last 6-7 years. I still haven’t seen that many usages of XML together with Excel 2003. I’d call Excel 2003 a very mature product (with all its faults…)
This is a great question, I think. I don’t blame MS’s lack of new features so much as some other factors. Really, any new features that MS may add will either 1) make Excel easier to use/develop or 2)muddy up what Excel should be used for. The first won’t necessarily foster innovation, though it may spark some innovation in how things are done rather that what things are done. The second may foster innovation, but probably for the wrong reasons (more Excel based text editors or accounting systems).
That leaves new features like VSTO and XML. They may be great features, but had I not edited your book, I still probably would not have ever used either. The incremental benefit of these (to me at this point in time) is outweighed by the costs of implementing them. That may change however.
That leaves it up to us to innovate. We are still constrained by the fact that Excel is just a spreadsheet. I could write a program to create web pages in Excel or to read RSS feeds in Excel, but it’s the wrong tool for the job. There’s so many other good tools to do those things, it would merely be an exercise to do them in Excel.
Where should/can the innovation come from?
Elevating users - The old saying that 95% of Excel users use 5% of the available features is likely still true. The innovation could come in how you get more users into the fold. Such as changes in delivery vehicles for communicating advanced featers (like blogs
). The more people that use advanced features, the more true innovation like FormFun will be sought out. In turn, that should spark more innovative development.
Best practices - As we understand more about how the program works, what we consider a best practice today might be supplanted tomorrow. Getting new and existing users to design/code optimally rather than just do what works, could be an avenue for innovation.
New blood - The more intermediate and above users we get to read Professional Excel Development, the more innovation will come. Different people, different problems now with better tools and information.
None of these are very ground-breaking, but as Frank said, we’re dealing with a mature product. I think the innovations will necessarily be small.
So all we need is this blog (to elevate users) and our new book (for the best practices and new blood)? I don’t buy it. Science is a mature technology too, yet innovations are continually being made.
When writing the book, both Rob and I learnt some new things from each other that we’d never enountered before, so people are definitely coming up with innovative ways to use Excel’s existing features - not just working out how to use the stuff Microsoft adds each time.
Great post & feedback. It seems that the real power of Excel over the last 6 or so years has been with VBA. Just about everything that we want to accomplish can be done in VBA, but obviously it takes time and knowledge - both of which are far beyond the average user. However, on that point, the average use still thinks of Excel as a table for storing data… you know the type. The one that I have a meeting with today at 3:00 so that I can show them how to use a couple of standard functions in Excel to produce the data analysis that they want.
But as we all know, Excel is a powerful data analysis and manipulation tool… but it is also a great user interface. Lately I have been doing a lot of work using Excel as a UI for a SQL Server 2000 database. While Excel supports embedded SQL queries using MS Query, it is difficult to use if you want to have parameters in your queries. So for example, I have developed an Excel spreadsheet that acts as an Work Effort estimating template for proposed projects. The Excel template needs to display critical information for projects from a remote SQL Server db, and then accept user input to write back to the database. I am using a combination the ADO objects in VBA to build and excecute SQL statements on a remote server, then manipulate and display the results in a spreadsheet, and then write the user’s input back to the SQL Server db. It works great, especially because I can make user of Excel’s built in UI features such as data validation, conditional formatting, filtering and sorting.
While in terms of new features for Excel it would be nice to have the datasource bound to the spreadsheet so that there would be dynamic write back of the data from the spreadsheet to the SQL db, I can accomplish the same results using VBA and ADO. So I guess while there are some features that would still be nice to incorporate, they are things that the average use would not be able to implement themselves, and for the advanced user they probably already can implement using VBA and other refernce libraries and Windows API calls.
I am all for new and improved features in Excel, so bring them on, but in the mean time I’ll plan to continue to try to resolve business needs using the advanced features that it has.
And Dick thanks for the Daily Dose… it’s a good read every day.
Cheers,
Eric Miller
Portfolio Project Manager
CheckFree
Hi Stephen
the difference between Excel and Science is that with Excel you have a very restricted basis in which you can develop new ideas.
No question that there’re still new things to develop in Excel (looking forward reading your book) but after over 7 years developing solutions in a nearly unchanged Excel environment I’d guess >80/90% of Excel’s capabilities have been deployed.
Or to be more precise: This is ture for the common used features which apply to the majority of users.
So IMHO all new innovations in Excel (which still happens) are probably only useful to a very small number of Excel power users.
And as Dick stated: I think Excel is quite o.k. for common spreadsheet tasks
I’m one of those intermediate users that you have been discussing. I thought I’d throw my two cents into the fray as well.
I’ve learned to use Excel through the idea that if I think is should be easier, there probably is a way to make it so. I’ve used Dick’s blog, Exceltip.com and John Walkenbach’s books for the majority of the projects that I have needed to develop. My most impressive project to date is a workbook that compares my corporations FedEx rates to our UPS rates. I sorted through their websites to find the pieces of information necessary and boiled it down to a user entering in four items — Origin Zip, Destination Zip, Weight and Maximum number of days to deliver the package — to calculate what the cost will be for each provider.
Educating Excel users is what is going to make Excel better. Most of the people I encounter don’t seem to understand that it should work for you, not you work for it. I’m reputation for being “great” at Excel, but as I read my above listed sources, I know that I have only touched the tip of the ice berg.
Even though the information isn’t “new” it is “new to me.” To 95 percent of the users, it is “new.” Forget about the qualifying “to me.” Having the resource is vital. Knowing that it is monitored by advanced users is priceless.
Staice
Now then this is interesting!!!!!
Stephen, brillo in stirring up this debate!, but don’t these 2 things seem to be in opposition:
“…who can point to any techniques that have appeared on the scene in recent years and been generally adopted by the Excel community? Where is the innovation?”
and,
“so people are definitely coming up with innovative ways to use Excel’s existing features - not just working out how to use the stuff Microsoft adds each time”
Maybe I’m missing something, at the risk of being rude, how do you define innovation in this context; is it the same as Dick and Frak, JPK?
I think some of the VBA games look impressive, Colo recently made a RSS feed addin which looked good, but analogous to Dick’s view, although these are impressive and good “proves of concepts” (- I assume the RSS uses XML?), maybe it’s only when we all start to use xml feeds and the like to update tables that we will consider it to be a new method. Invovation can, after all only come about when there is a need? Nobody, of sound mind, is going to develop a new method for which there is already a reliable and efficient establish protocol, are they?
95%, using 5% or not, nearly 100% of users use excel for one thing and that’s to shift numbers around, and in my opinion there’s only so many ways you can do that!
As a side bar:
“Science is a mature technology too, yet innovations are continually being made”.
You’re not comparing like with like here! Excel is like an abacus, a calculator maybe, namely a tool, science is a discipline – to boot, how can you assert it’s mature!
Justmyopinionon
It’s great that the best exponents of excel in the world can communicate with average users like this, not many other things I can think of where that occurs.
Good comments all! I was hoping for a few comments like “Haven’t you seen http://www.fred.com. He’s got some really great new ideas for userforms.”
Instead, everyone seems to agree that all the innovation has been done and there’s nothing left to discover. Is that really the case? Are all the Excel web sites in terminal decline because there’s nothing new to add to them? Is there any point in writing new Excel books ;)?
I fully agree that the problem of intermediate users not knowing about key techniques is a real one - that’s why I wrote the book. But do people actually read the web sites that contain the material and use the techniques they describe? (and Ross, that’s the difference between my two statements. I sometimes see innovation happening, but I rarely see ‘the community’ adopting the new ideas).
For example, I hope most of you have downloaded the new version Jan Karel’s NameManager utility from http://www.jkp-ads.com. One of the great new features is that the dialog can be resized. That’s a standard userform, with just 4 extra lines of code and a plug-in class module to handle the resizing. The code to do it has been on my web site for 5 years and mentioned in my books, but the NameManager is the first time I’ve seen it used.
So Stacie, how do we educate the users to adopt these techniques, such that they can then leap-frog them and break their own new ground? What more can we do?
Yeah, I see what you mean.
I think that some of the VB gaming sites (mostly, in Japanese I think?) do some really cool stuff, the pacman game is Amazing!…But I’m never gonna do anything like that, I might look at the code and think oh that’s a good way of doing something, I could use that to do this that and the other, but invariably I wont. Manly I’ll just look for an answer and not try and created one, no need to reinvent the wheel, and laziness.
I am currently working with VBA5/xl 97, and I nkow that Andy Pope (site’s) has some cool code to make (these) userforms modeless, but it’s not stable, so I’m unlikely to use it in anything I develop, same with coloured tabs.
I don’t think that every thing that can be done has been done, but I do think that there very little that needs to be done, that hasn’t already, which comes back to the maturity point really.
All very intersting stuff
Hi Stephen,
I have to agree with Ross. I too question the relevance of innovations when these lead to VBA games or RSS. These are simply irrelevant to my work in Excel.
I am an skilled old-fashioned Excel user. I calculate and handle data inside Excel or prepare it for input into external models. I capture output and create graphs. I work for decision makers / policy makers. I could probably still perform all my work in Excel 5. It is years ago that I even considered upgrading (currently Excel 2000).
I think we have to accept the lack of improvement of Excel is limiting the development of true innovation among its users and blame Microsoft. I would wish for more functions, I want more columns and I surely wish I could handle my graphs with VBA more efficient.
You call for innovation actually surprises me. A wonderful discussion came from it but I have never lacked innovation from Excel’s VMPs. As a matter of fact, I more and more often stumble unto tricks that are truly beautiful, but too clumbersome to implement. As a user, limited in time, I do accept the limitations of Excel far more easier that a MVP would do.
Gerrit
The kind of innovation I’m thinking of is definitely within the Excel mainstream and totally applicable to everyday (developer maybe) usage. For example:
David Hager’s ‘count distinct’ array function of, )).
=SUM(1/COUNTIF(
Rob Bovey’s use of conditional formatting to create automatically expanding, self-validating data entry forms (no code required).
Using a combobox’s DropDown event to show a frame, giving us totally customizable dropdowns (not just a simple list) on userforms.
None of those require an extreme knowledge of Excel, just a unique way of looking at what Excel gives us. Anyone with an intermediate knowledge of Excel can look at it afresh and come up with the new way of combining A, B and C that results in a compelling new technique. Don’t leave it to the MVPs!
If I ever meant to differentiate MVPs from Excel users it is only in respect.
I have to agree with Gerrit.
1) More columns:
The current restriction really inhibits creativity.
Rows have increased almost version for version.
2) More attention from MS to graphs/charts. It’s almost as if they have stood still.
It’s not helped by the incredibly clumsy object models
Specifics
a) Turn off the stupid Illegal Entry warning when using log scales on charts (for negatives/zeros).
b) Let’s be able to have activex controls on chart-sheets
c) Some newer chart types: true 3D scatter plots for instance.
d) animated charts
Mpemba
Hi Mpemba
re: “Rows have increased almost version for version.”
The last increase was with Excel 97 (from 16K to 65K). So nothing really has happened in this area in the last versions.
Though I agree at least having anoth column for creating a calendar would be great.
Frank
Stephen—
I’m willing to bet that my answer will be appropriate for several fields and lines of work.
If someone is interested, they will dig in to find out more. If it seems too hard, they’ll keep on doing it the same way, with the same results — becoming frustrated that Excel is too complicated.
I am consistently doing vlookups for the same few people over and over as their focus is on the results, while mine is on the process.
I mention the word “Macro” and people shudder in fear (maybe a slight exaggeration.) I record most of my macros and then go back and piece in what I forgot in the first place.
But rather than playing around with such a feature, reading about macros or simply educating themselves where necessary, users don’t feel that they have the time needed to do the front work and make it simplier in the long run.
It all comes down to what interests the user — the end result or the process.
Stacie
Stacie, Stephen,
I agree with Stacie that it is primarely an effort-gain relationship that has a too steep a curve for most users. Why did I started coding in VBA and continue doing it? Because I like the puzzle of it: To imagine and create, and too improve it. The process as Stacie puts it.
But in the end I think all of my coding that required true effort has been a loss of time that I never regained by applying the code! I believe this to be the rule. There are exceptions, there are some beauties and of course there is the gain of learning VBA.
Stephen. Some thoughts I’d like too share:
I started buying professional Excel books far too late. The WWW and Excel’s Help File brought me really far, but basic VBA coding techniques that are required for a well structured project I only learned from books on the desk. I think they can only be learned from books on the desk. (Please write this on the cover of your next one)
Then: I have a couple of things in VBA I’d like to finish and publish, but they work for me now and I probably will always leave it there, moving on to other priorities. Bad luck for the world, although personally I think not much is lost.
Third: What I find an interesting area for development is the case of other programs incorporating VBA. It is beyond the scope of this blog. I think of CAD-programs like AutoCAD and BricsCAD that have incorporated Micsosoft’s VBA under license and allow me to use a wide range of VBA code original developed in Excel to be used on
“map-layers” instead of “worksheets”.
Gerrit
I work in a normal office, doing inventory tracking but I’m also in charge of imports/exports. Our database frontend, while being pure crap from a programmer’s point of view, can barf query outputs into Excel format. When we switched to that Windows application (we were on AS400 before), I never really used Excel before. Being in charge of the inventory (let’s just say we have over 25,000 different products), I saw the potential in it.
The software spews the data directly into Excel, without any formatting. I’ve lost countless days removing thousands of useless lines/columns by hand. Then, one day, having heard of macros and VBA, I launched the VBA Editor. Gasp! In two days, I had coded a small UserForm which you get my input and format the reports the way I wanted - 10 seconds instead of 2 hours. I knew I was on something big.
Granted, I’m a hobbyist programmer, having dived in Python, C and PHP before so I wasn’t lost with VBA, which is quite easy. Everything I needed to know about VBA I learned it from the help file. That is, until I found this website, a few weeks ago. I’ve now written about a dozen small program and one of those is being used by the 10+ people working at Customer Service. In fact, I made the exact same thing Stacie did - how much will it cost to send X to Y. It’s so easy to use - and scalable, for future development - that I didn’t even have to explain to the CS people how to use it. I may have “wasted” a few hours doing this but it’s going to make me save more hours *not* answering the phone to give a price for a specific shipment.
Anyway, enough personal experience. The thing is, we’re only two people knowing about the existence of VBA on more than 80 people working there. Every time I say “Oh, I wrote something to do that automatically”, the execs look at me suspiciously and don’t take me seriously. That it, until I show them the feat. So yes, it’s all about education. Those who can code, should code. Those who can’t should trust those who can. Because of Excel and its nifty tricks, I am now enjoying my job because I don’t have to waste an entire day to do something a 10-lines piece of code can do in a second.
The irony is that I’m a Linux user at home, I normally loathe MS products. But I must admit they did a great job with Excel. Not just because it works but because it was so easy (for me at least) to dive in it. I still learn new sticks everyday by reading this website but also simply by searching through the help file.
Finally, I’m just saying that yes, while you may be reinventing the wheel again, I am not gonna buy an Excel book - mainly because it’s not part of my workload. I am not supposed to play with Excel. I did, I got good results and my bosses trusted me. But I’m far from being an expert (just starting to understand the power of PivotTables) and references like this website helped me a lot to get where I am. Simply.
Manu -
>>I am now enjoying my job because I don’t have to waste an entire day to do something a 10-lines piece of code can do in a second.<<
This is exactly the benefit I sell to my clients. Sure, I do nice charts and stuff, but I can put a set of buttons on the menu that will turn days into minutes, hours into seconds. My clients want to do their regular work, not become Excel jockeys.
And you have to say this about Microsoft. Despite the problems with the help system, they’ve engineered their Office suite to interact rather smoothly. You need to be aware of the mines in the minefield, but you can get them to talk together nicely. A great deal of the time I save people is turning Excel information into reports and presentations.
- Jon
Manu—
Do you by chance work in Oshkosh WI??? Cos your company sounds alot like mine.
Stacie
Somehow the rest of my post was cut off.
Manu said:
>>I am now enjoying my job because I don’t have to waste an entire day to do something a 10-lines piece of code can do in a second.
I replied:
This time savings is what I sell to my clients. I can write a few lines of code, attach that to some buttons, and they can carry out some analyses, make some graphics, and dump it into a report or presentation in no time.
Say what you will about Microsoft, but their Office products for the most part play well together, once you learn their individual quirks.
- Jon
Stacie,
No, sorry, I’m in Montreal, Canada.
Manu—
Just goes to show that people are the same everywhere.
Stacie
< < All the truly innovate breaking-new-ground pages and examples seem to be dated from the last century
(but they’re still totally relevant). >>
If innovation doesn’t catch on, it is still called innovation, isn’t it? I think most true innovation, be it in arts, science or technology, came way ahead of its time.
Before I discovered this website I knew Excel was a clever application but I had no idea it could be subject of heated debates among professional and advanced users. It convinces me that Excel still has future.
Hi Stephen,
even thought I’m an irregular visitor, but every time I come here, I find and learn new things. Your site is far from being “aging”. I lnow I’m not the next bill gate$ of EXCEL, yet I consider myself a power user. But I not only find your site useful, I also “distribute” it to people I know are interested in learning EXCEL.
Well this was my 10 c.
Thanks for all the help and “keep up the good work”!
M@x
Thanks Max, and thanks to everyone else for their comments. It was a great discussion, but ultimately confirmed my fears that little is ‘happening’ in the Excel world lately.
Regards
Stephen Bullen
Maybe Excel is in a stage where the novelties introduced are only picked up by a small cummunity. That brings us back to the first comments in this discussion, about the absence of new versions and about the advanced “maturity” of the application base.
I agree.
- We repeatadly stumble across the same problems again and again.
- Excel do not add new functionality or improve any well known problems.
- There is no spread sheet program competition.
So we are stuck in a cul-de-sac.
I hope Microsoft would improve Excel with the help of the MVP’s. But so far…?
I agree that “95% of Excel users use 5% of the available features”. But 99% of what is available in Excel is actually used.
Statistical Business Analysis and Graphical presentations is getting more important every day. I can see that Excel don’t need to change those features out of profit reason but it would improve Business Efficiency worldwide.
“Although I get a fair number of visitors to my web site … I receive almost no comments about it”
But do you respond to the comments you receive
? I recently sent some comments about a fundamental problem with your famous FormFun but got no response. In case you are listening now …
I’ve noticed that when I use it, I can never release its reference to my UserForm. To demo the problem:
1 Add CFormChanger.cls to the project.
2 Add a new UserForm.
3 Add the following simple implementation code to the UserForm’s code module:
Option Explicit
Private oFC As CFormChanger
Private Sub UserForm_Activate()
Set oFC = New CFormChanger
With oFC
.ShowCaption = True
.ShowSysMenu = True
.ShowCloseBtn = True
Set .Form = Me
End With
End Sub
Private Sub UserForm_Deactivate()
Set oFC = Nothing
End Sub
4 In the VBE, close all code modules/views apart from the graphical view of our UserForm.
5 From the menu, choose: Run, Run Sub/UserForm.
6 The form shows.
7 Close the form by pressing the close button on the system menu.
Expected result: the graphical view of my UserForm should once more show in the VBE.
Actual result: no view shows in the VBE (I must choose: Run, Reset to get it to show).
I can get CFormChanger to work as expected if I comment out this line:
Set moForm = oForm
The code retains the UserForm’s hWnd, which is good enough for me. It seems that when retaining a reference to the UserForm, it doesn’t later get released, even if I change CFormChanger’s Form property to allow it to be explicitly set to Nothing.
Jamie.
–
Jamie: Yes, I generally respond, but nobody’s perfect - particularly when under pressure to get a book completed :-). I just checked my email and see one from you at the start of August, saying that you’d found a problem and fixed it (by setting the class object to Nothing in Userform_Terminate).
I routinely run with all the VBE windows closed, so I’ve never noticed the symptom. Also, whenever I use it, I declare it as a local variable in Userform_Activate, not at module level. The formfun example only has it at module level because of the interactive nature of the demo.
Sorry for not responding in August!
Regards
Stephen
“saying that you’d found a problem and fixed it (by setting the class object to Nothing in Userform_Terminate)”
Oops! Seems I found the fix then forgot it. Thanks for the reply.
Jamie.
–
I oopsed too soon. Even when I declare as a local variable in Userform_Activate as recommended, the form doesn’t get released if I use the ShowTaskBarIcon property. Here’s my test code:
Private Sub UserForm_Activate()
Dim oFormChanger As CFormChanger
Set oFormChanger = New CFormChanger
With oFormChanger
Set .Form = Me
.ShowSysMenu = True
.ShowCaption = True
.ShowCloseBtn = True
.ShowTaskBarIcon = True
End With
End Sub
As before, I must press the Reset button to get the design instance of the form to show in the VBE. Any further ideas?
Thanks,
Jamie.
–
If I put that code in the Activate event, the form doesn’t display properly, but if I put it in the Initialize event, it works fine for me (kinda - Windows XP didn’t show the form in the task bar for some reason).
Regards
Stephen Bullen
“If I put that code in the Activate event, the form doesn’t display properly”
We seem to be getting different results. Using Excel97+Win2K, I get the expected results i.e. form shows fine (i.e. with icon in taskbar) and the form unloads in the VBE. Using Excel2K+Win2K or ExcelXP+WinXP, the form shows fine but doesn’t subsequently unload until I press Reset.
“I put it in the Initialize event, it works fine for me”
For me, moving the code into the _Initialize event for all versions/platforms tested causes a run-time error 400, ‘Form already displayed; can’t show modally’ immediately following the _Initialize event. Admittedly, at this point the form does unload but I then can’t get focus properly back to the main Excel window so I have to kill the app. For Excel97+Win2K the form doesn’t show at all and I still lose Excel. If I didn’t know who I was addressing I’d question whether you are sure you really did get this to work from the _Initialize event
“Windows XP didn’t show the form in the task bar for some reason”
Ah, I only have the problem with the form not unloading when the icon *does* show in the taskbar. Like you, I thought I’d got it to work but on closer inspection I realised it wasn’t showing in the taskbar. I restarted Excel, it reverted to showing in the taskbar but I also got the non-unloading form problem again. In short, if you haven’t seen the icon in the taskbar, you haven’t seen the problem
Let me try a different question: do you think I should be worried about the non-unloading form? I’m assuming it is symptomatic of a problem e.g. a memory leak (of which I’m paranoid due to a recent bad experience). Your FormChanger code is by far my favourite Excel utility and I’d like my confidence in it to be restored if possible.
Thanks again,
Jamie.
–
This is a very interesting discussion. Thank you.
One innovation I would like to see is the ability to compile projects without spending a thousand dollars.
I would be something you do when you digitaly sign your project, and it goes into production mode.
More columns would be nice, too.
I would like to point out that inovation is relative. I have been using VBA for the last several years, when the company I work for finally switched to Excel. I must admit that I was one of the Lotus 123 afficionados who grumbled about having to redesign hundreds of spreadsheets. And, yes, the Lotus 123 graphs required much less work to produce good looking results. I am now considered an expert Excel user (I wish!). To get to my point - Recently, I discovered Excel’s WebQueries (Get external data). After some work, I was able to automate a number of jobs, and tasks that used to take a couple of hours now get done in a few seconds. This blog helped solve some of the difficulties I encountered. Thank you very much!
Is this innovation? Perhaps not by the standards of MVPs who may somehow have a solid grasp of all of excel’s capabilities. For me & my company? It definitely is innovation. Automation of office and other products is an area that needs a lot of work.
Numerous key concepts are NOT taught in the classes we were all sent to, most notably lists (Thank you to John Walkenbach and his Excel VBA for dummies). All too often, I find myself looking at spreadsheets I developed and thinking what an idiot I was to do a task that particular way… There is always that danger if you try to keep learning.
Most users never explore the available toolbars to find out what else is hidden there ( I try, in the spare time between 11 PM and 1 AM, when I should be fast asleep).
So, what else is hiding in excel, that I could use to advantage?
Michael -
>>After some work, I was able to automate a number of jobs, and tasks that used to take a couple of hours now get done in a few seconds….
>>Is this innovation?
When I started my Excel development business, I assumed I’d be doing lots of data manipulation and charting, all that stuff on my web site. And I do a fair amount of it, but what’s a bigger part of my work, and what the clients appreciate more, is the automation. I use Excel as the front end, but often the product is a Word report or a PowerPoint presentation. Using code to converting Excel numbers and charts into tables and figures in another application saves many hours or days.
I don’t know how innovative it is on a broad scale, but for the clients, anything that frees them from the drudgery of building a report so they can analyze what it means is a huge innovation for them.
Try to learn something new every day. Not just Excel, either. Today I learned about the waste disposal industry from a potential client. It’s not directly related to my other work, but perhaps it helps broaden my perspective.
- Jon
Jamie,
There are, indeed, lots of permutations that could be affecting how the form’s display works - including whether showing it modally or modeless (why would you want to show a modal form in the taskbar?). All the class does is flip style bits for the form, to change how Windows draws the window. As such, it’s hard to conceive how that could result in a memory leak (though I guess it can’t be totally ruled out). All I can say to restore your faith in it is that I’ve never had a problem with it, and yours is the first comment I’ve received about a potential problem in it.
I’ll keep at it, and see if I can identify the problem.
Regards
Stephen Bullen
“whether showing it modally or modeless”
That sounds a likely candidate e.g. Excel97 doesn’t show the problem and doesn’t natively support modeless userforms either.
“why would you want to show a modal form in the taskbar?”
When I’m hiding the main Excel app while my modal form is showing.
Jamie.
–
“why would you want to show a modal form in the taskbar?”
When I’m hiding the main Excel app while my modal form is showing.
Good point - thanks for the use case!
Stephen
Jamie,
Oops - I was just reminded that I was told about a problem with the ‘Show in Task Bar’ button back in October, but promptly forgot it!
I might have to fall back on the get-out clause of “This is not supported by Microsoft. Use at your own peril!”. More accurately, userforms were (obviously) not designed to have task bar icons, so if you want a reliable way to use them, consider creating the form in VB (wrapped in an ActiveX DLL) and showing it from Excel.
Sorry,
Stephen Bullen
Its now common to see VBA be used for all sorts of applications, a kind of “101 uses for Excel you hadn’t thought of” even if the Application wasn’t really suited for a data manipulation tool. So I consider breakthrough techniques to be non-VBA related
Excel is a relatively mature application with a huge user base so I think its natural that new technqiues are like hens teeth. Most of the clever work with a high demand need has been tackled in areas such as
Array Formulas
Data Validation
String Parsing
Lookups
Jon Peltier’s Graphs
On a different note, I’ve found that the layout, design and transparency of Excel models has improved significantly over the past 5 years. The company I work for has a very detailed document detailing how financial models should be constructed sand formatted so that attention is paid to the data not the algorithims. Improved presentation is a breakthough too.
I’d like to take this opportunity to beg anyone who may be listening for the return of Excel Expert E-letter, it is the best collection of techniques of seen. Kudos to you too Steve, your website is second to none for its addins and example downloads
Cheers
Dave
“I might have to fall back on the get-out clause”
Stephen, No worries. With no evidence that anything detrimental is happening, I’m happy enough. Many thanks.
Jamie.
–