FLT

Funny Little Thing.

I’ve been blundering around around for years flicking back and forth to the VBAIDE immediate window to run simple lines of VBA. Its always niggled a little, but never really registered what a PITA it was.

That was until I found Ross’s bit of code to rip the immediate window out of VBA and put it in the Excel desktop instead. What a great idea, I wish I’d thought of it. I didn’t, but it did inspire this little form based tool. Its as tatty as, just a mega rough and ready thing that I probably wont bother to develop further. No idea if its any use, of if I’ve missed something obvious, and there is a more simple way to do this. If so, be sure to let us know via comments.

You are welcome to have it and do what you want (apart from sue me of course). Open it with macros disabled to see what it is doing (not much). If you don’t know much VBA this may have limited value, if you do know VBA this may have limited value. There is no real error checking so it just falls apart at the first sign of trouble. Some people might say all my code is like that!

I wont bother with a screen shot because its short and wide, and therefore will wreck all the wordpress formatting (must get a wider theme).

Its pretty much just a text box and a label on a form with a button you type any one liner you would put in the immediate window and it executes it and puts the answer in the label. Stuff like ‘?activecell.numberformat’, or ‘?activeworkbook.path’. You can also do stuff like ‘activecell.formula = xlapp.activecell.value’ to remove formulas, or even selection… for ranges. (the xlapp is a reference to the Excel application and represents an opportunity for improvement!).

I did a couple of For Eaches so if you type in ‘FEworksheet.unhide’ (with FE in uppper case) it will unhide all worksheets. There is also an ‘FEworksheet.zoom’ in which case you put the zoom value (10-400 I think?) in the little text box, it then zooms all sheets in the active wb to that level.

It relies on the Microsoft script control (msscript.ocx) which is on the form somewhere. No idea where that control came from, or what it came with, but if you get a compile error check your refs. It fires some activex component security warning on first activation, which I assume is the control itself. You can use javascript with this, which is pretty neat, I didn’t bother.

Hmm looks like many people might not have that control (http://support.microsoft.com/kb/184739).

The next developments would have been putting it in a toolbar, and maybe using a combo box and keeping previous commands, maybe a better way to handle parameters, but I don’t have the time.

If you decide to develop it further and would like to share those improvements then let us know. If you know a better way then definitely let us know that.

Heres the zip before I forget, when I say tatty I’m not being modest.

Let us know what you think

 cheers

Simon

1 million rows

[got a few posts in quick succession this week, so please don’t be put off, and keep commenting on the old ones] 

This has come up a few times here and there so I thought I would come clean.

I have always said this is a bad idea. I would much rather have seen better db integration (maybe like the structured sheets I was on about). I know it can help in quick and dirty jobs, but actually most of those would be quicker and less dirty in Access/ db of choice. If you are dealing with >64k/1 million records and are unable to use basic database features, that would represent a significant operational risk in my view.

Around the time the 1M was announced one of the Excel team asked what my number 1 feature for Excel 12 would be. His eyes nearly popped out when I said reduce rows to 10,000 and improve db integration. More rows was the number 1 requested feature (by an order of magnitude I think). (This may explain why the ribbon is such a dumbed down interface!)

To my mind the whole spreadsheet paradigm falls down at large row numbers and you have to move to a more stuctured approach. If you have the same formula in every cell in a column, why not define it once as per a calculated field in Access, of a query in any db?

That said, I havent heard of any million row disaster stories yet, have you?

What do you think? 10k? 64k? 1M?, a screens worth? a user definable value?

Gnumeric has supported any maximum of rows and columns just by changing a couple of constants and recompiling for years (maybe for ever?)

As Excel gets more and more features for the lower ability user, I think it gets harder and harder to develop on, and less appropriate as a development platform. What do you think?

cheers

Simon

Spreadsheet types

This was a post I meant to do a week or 2 ago, but limited access to upload the graph slowed me down.

Simply put, what proportion of a given system is implemented in a spreadsheet grid and what proportion in some other technology?

Heres my views, please add yours as comments, especially if they differ. I have combined data and code as the other axis, but it might be better to split them out (not from an ease of graphing POV though!). I’d be very interested to read your ideas on distribution too, they dont have to add up to 100 (mine don’t!).
No big reason to split it into 5, or for even 20% steps, I don’t think it matters much, the key point is this continuum from pure cell formula spreadsheet to pure code/data source based systems. This post tries to make some sense of some of the different types along the way. Of course it is very open to interpretation.

Type A
Description: Almost completely cell based, maybe some simple, non critical macros, perhaps a print macro, or some data import code, or maybe a link to an external data source.
Distribution: I’d say 80-90% of all commercial spreadsheets are in this category, maybe even 99%?
Issues: To me this is the ideal use of a spreadsheet, fully leverage the power of the grid, and add a little automation sugar to help things along.
This sort of spreadsheet should be easy to move between different spreadsheet applications (such as Excel and OpenOffice Calc or Gnumeric)

Type B
Description: Mainly cell based, but with a fair dose of code or data access in places. The code is likely to be fairly important, and may include user defined functions, and/or complex coded manipulations. Could be a fairly smart front end to an enterprise data source.
Distribution: Maybe 5-10%?
Issues: Perhaps beginning to bump up against some intrinsic spreadsheet issues like lack of security or fragility. Much of the code could be workarounds for these issues. Requires dual skill sets to maintain. Seems likely that both the code and the cell based functionality will be fairly complex. These often use such a rich set of host features they would be challenging to move to another spreadsheet program.

Type C
Description:Functionality fairly evenly split between spreadsheet and code. Many dictator apps that take over the Excel environment probably fall into this category. Could be a server based system that outputs a combined data and formula spreadsheet as a smart report. Heavy use of xll UDFs could put a system in this category too.
Distribution: Up to 5%?
Issues:If its server based in may not be within the influence of the users. If its Excel/VBA based then the reasons for using a spreadsheet as the basis could be questioned. As functionality moves from the spreadsheet to the code migration becomes more feasible. Perhaps to another spreadsheet application, but more likely to an executable with some sort of grid or spreadsheet control.

Type D
Description:Mainly code based with significant use of the grid. .net and VSTO type solutions probably often fit in this bracket. Could be making extensive use of a small part, for example the calculation engine. Dictator add-ins could fall into this category. Probably more of an external standalone program, that uses automation to manipulate the spreadsheet application.
Distribution: <5%?
Issues: As the spreadsheet is a minority feature it is likely only a small subset of its features are used. Some of those may have a negative impact, such as lack of security. Might be possible to migrate to a grid control or another spreadsheet program, assuming the required functionality is there.

Type E
Description: Almost completely code based with minor or no spreadsheet functionality. An example might be a spreadsheet that is used as a dumb grid based report for an enterprise data source.
Distribution:5% – 10% (I think there may be lots of basic reports from an external datasource)
Issues:Same as type D, and also deployment? As virtually all the logic sits in code, actually a powerful grid control may be more appropriate. Often will not need advanced spreadsheet skills, mainstream coding would be more useful. The spreadsheet component of this type of system is likely to be easily moved to other technologies, some of which could open up new markets.  Such a system may well have very low client install pre-requisites, making deployment relatively straightforward (potentially).

I would say most of my stuff falls into type A or B. Although some of the Essbase report writing is probably more of a C/D overall.

The reference checker that I wrote is probably an example of an E. it could easily be an executable with a simple grid control or even some messaging component. But an xls is a better deployment story.

Is this classification useful? what do you see as the distribution? what features of each type have I missed? would a code/data split be useful? which categories would you put your stuff in?

cheers

Simon

Office user group

A load of us went to Microsoft Reading for the Office user group event on Fri (27 Apr).

It was an excellent event, lots of interesting presentations, and the opportunity to meet fellow devs and discuss the real issues we face.

Its great to hear the latest and greatest for Microsoft, but its also good to get a dose of reality. VSTO certainly does look good for a certain set of applications, but there are plenty of us with very limited opportunity to deliver solutions to customers based on it. Personally I feel VBA devs are in limbo, have been for several years and will continue to be for the foreseeable future. I’d classify that as an opportunity for someone.

The session were:

The power of Office, which went a good way towards explaining what Office is these days (hint: its not just Word and Excel (and Access) anymore!)

Excel and BI, showed some of Excel 2007’s BI features in conjunction with Analysis services. Some neat stuff in there, maybe I should port some of those AS worksheet functions back to ’97-2003 versions.

Excel and the Ribbon, Nick Hodge nearly persuaded me the ribbon wasn’t totally rubbish. Nearly. I am starting to think that the ribbon may end up bearable in a few versions, unless its replaced of course. There was plenty of useful advice for anyone working with the ribbon.

Visio 2007, Very impressed at what can be done by connecting Visio to a database, I’ve only really used it for software development (DDL, UML etc). There look to be some great data visualisation features. One point I found extremely interesting was the fact that Visio isgetting an MCP exam. Excels was retired in 1997, Access was retired in 2001(ish?), the point being that there has been no Office focused exams for years, and now Visio is getting one. Does that mean other Office products will get them back, or are we stuck with MOUS?

Groove, now I finally know what this is, I think. Its a cross between Lotus teamrooms, and the briefcase (if anyone remembers/ever used that). Efficient data replication that allows people to work offline, with simple replication/synchronisation as soon as they go back on line. The presenter also suggested it might be useful for disaster recovery. It will probably be 5 years or more before my clients get it, but I might be able to make use of it myself, in this way.

Office integration, took a variety of raw data, through a range of processes and products to produce a bunch of reports. A great example of using the best features from each product to get the job done, quick and easy.

Underused products, a look at one note and info path. These look great in the demos, but I can see why they are ‘underused’. For me even having seen them in action, they are less than compelling. Although OneNote does character recognition on pictures which was pretty neat. If I ever get a tablet pc, I may well go for this. I’m sure some people could make great use of them, but they are probably a bit far from the very limited area I operate in.

There was also a questions and answers session where we had a livelydebate about the future of VBA/VSTO/VSTA. Of course no-one had any hard facts, but we still had a good debate.

Overall the event was excellent, good content, good atmosphere, good organisation and we were well looked after by Microsoft. If you get chance to go to another I would recommend it.

The highlight for me was meeting some of the people I have been chatting with on-line and putting faces to the names. And we had a good chance to talk through a few things after the event which was very good too.

I’ve forgotten most of the quotes I was trying to remember sadly. Except:

One presenter mentioned that he liked the ribbon because the buttons were easy to hit. Now if he were a Yorkshire lad I’d have taken that as ‘them gurt buttons are just for the cack handed’ (big buttons for the clumsy), but he wasn’t so I’m not sure. Anyway, someone else said, well fat people are easy to hit because they are big and slow moving, thats not really an advantage though is it?

A few of us are thinking to get together in a couple of months to discuss some developer stuff, share war stories etc. I’m thinking Oxford on a Friday maybe, end of June/ early July? I’ll post again in a few weeks with some suggestions for an agenda and location. If you fancy it leave a comment.

Cheers

Simon 

VBA WTC???

[WTC = What the chuff!] (chuff)

Marcus spotted this over at JOS:

VBA for Mac goes away (may be one or two down by now)

I think we’ve discussed this before, but I thought there may still be a bit of mileage in it.

Official reason VBA is going away on the Mac, is few people use it, its hard to maintain (for MS), Apple has AppleScript instead.

Unofficial reasons/conspiracy theories revolve around commercial pressure to slow adoption of Macs (they are rapidly increasing market share (still sub 5% though I think)).

Hopefully we all know there is limited love for VBA at Microsoft, where the (current) preference is for .net everywhere. Also MS recently released PowerShell which looks to be built on/a version of Windows scripting host, so they are still keen on scripting.

VBA is almost just a scripting client to an Excel server. You can do pretty much the same stuff with VBScript (or Javascript) with .vbs or .js files. I can imagine some people thinking thats good enough, no need for VBA. They miss some critical facts though. Scripts are late bound, VBA is early bound, scripts are separate files/components, VBA is integrated into the .xls. VBA is a proper language with data types etc, Got any others?

Now I’m not saying VBA on Windows is going away any time soon, I have no idea what the current plans are. But VBA has already stagnated for 10 years, the only VBAIDE update in 2007 was the mouse wheel scroll. Where are our tabbed code pages (like VS2003)?, tasklist (VS2003)? where are our snippets(VS2005)?, and there are plenty of other useful coding features that are almost certainly never going to make it to the VBAIDE. And no third party is going to target it, 1. because its nearing retirement, and 2. because the editor doesn’t expose much object model.

I believe if MS want to drive uptake of the next version of office they should revamp the VBAIDE. I thought VSTA would be in 2007, which it isn’t, so its easy to begin to wonder if MS are losing faith in Office product based Office development. Do they really think we’ll all move to Visual Studio? Or that VS devs are interested in targeting Office?

I do know of a few other devs who are rather concerned with what MS seem to be doing in this area. Are you bovvered?

Do you get the sense they are pushing us out of Excel/VBA development? Or at least trying to make other dev tools more attractive, by not developing the VBAIDE.

As a side note Novell are currently working on native VBA support in OpenOffice Calc, which I think could prove to be an ironic success.

see some of you tomorrow

cheers

Simon

Eusprig 2007

I recently heard my (our!) paper got accepted, so thanks a million everyone for your input.

If you are interested in spreadsheeting, risk or quality and you can be in London in July then I heartily recommend the conference. More info and booking here:

www.eusprig.org

Here is a link to the post with the paper if you want to refresh yourselves, any additional input very welcome, as comments here or email or post.

https://smurfonspreadsheets.wordpress.com/2007/03/07/spreadsheet-hell/

I have a ton of work to do in this area, I’ve got some more web space and got my web development team (Mrs smurf) primed for a new web site. Don’t hold your breath though, its chaos here at the mo.

Cheers

Simon

Office v rest of world

Following on from the office v browser discussion we were having.

I was chatting this through with one of my ‘big system’ buddies a while ago, here is what we came up with:

Excel/VBA is often used for housekeeping, day to day type work. It is used for full systems too, but often it is short term tactical solutions. Often they are a single developer or a small team. It is also used to finish what the big systems promise but never seem to fully deliver. A rough cost might be 10 – 50k.

Big system implementations such as Essbase, Magnitude, or Business Objects are often pretty large projects with perhaps 10/20 or more people involved. There are also significant licence costs involved, and often new hardware. A rough cost would be 100k plus.

These big projects are often capitalised, where through the gift of modern accounting, the more you spend the greater the value of asset you create. The net result of this is that the best rates of pay would seem to be in and around big system implementations that are going to the balance sheet, rather than day to day fire fighting that is going straight to the P&L. The former projects seem quite price insensitive.

Has anyone else seen this? would you agree?

I’m sure we have all seen counter examples, or massive projects based around office apps, but in general I think this could be valid. I’ve worked on quite a few big system implementations and in general there does seem more budget available than when doing some basic reporting app. Maybe the life expectancy of the app has some impact? (on some sort of unspoken cost per year basis?)

I’m wondering if others have seen this, whether developing skills in one of these ‘big systems’ might pay off more than say .net programming skills. I guess effectively the question becomes what sort of project do you want to work on, and what technologies does that type of project tend to deliver?  And then of course whether or not its feasible to develop skills in that area.

I had a sniff around the Oracle site looking for developer versions of Essbase, but it didn’t jump out at me. This is where Microsoft put all the other vendors to shame, Microsoft products are just so accessible. They are like the Coca Cola of software (Coke are famous for their superb product distribution).

So has anyone else seen this ‘big system’ effect on rates? Or any other thoughts on technologies to focus on?

Cheers

Simon

More BI moves

Business Objects are buying Cartesis more detail here

The news sites don’t seem to have picked up on it much as yet.

No idea on the impact, maybe good news for people with Magnitude skills?

Anyone got any thoughts?

Anyone used Magnitude much?

cheers

Simon

Office v browser – results in

The survey results are in:

http://www.theregister.co.uk/2007/04/20/desktop_office_suites/

I’m rather surprised that office type apps had such a low showing. Its great to get a different perspective, some of the comments are interesting too. I sometimes forget how diverse peoples use of computers is, stuff like this give me a gentle reminder.

cheers

Simon

Office v browser

Interesting article here:

http://www.theregister.co.uk/2007/04/18/office_versus_browser_poll/

I’ll be interested to see the results when published.

I think it may be a bit too broad brush, with lumping word processing, spreadsheet and presentation together, but we’ll see.

As a comment I put ‘Excel is the centre of my world by an order of magnitude’.

I’m guessing most of us here are similar?

cheers

Simon