Recently I was in a business meeting, the chap from the “other side” suggested that i should stop making posts in the wee small hours! Since I started my new job time has been a bit harder to come by than it was before so I end up thinking about Excel later in the night!!!! Here’s my question to other bloggers; how and when do you write your posts, whats your motivation and and how do you make the time?

And for no reason other than I thought of it, he’s a link to the main man of midnight stuff, from one of my fave cartoons ever! Even his grammar is right! (for me anyway!!!)

NB: See comments for errors and corrections to this code!

Today I needed to get the Standard Deviation for a number of counts of scores. Like this:

I dont know of a way to do this in the worksheet, and a quick google didnt turn anyhting up eirther, so it was off to VBA, this is what I came up with:

This works with data in rows (one row, many columns), you would have to swap the counter i to the row index to get it to work with a column of data.

Any better ways? Thoughts on this code?

Cheers

Every now and then this blog seems to go a bit nuts and refuses to allow me to load pictures, or even edit post normally. That's whats been happening over the last few days, I could not load images and when I made changes they where not saved! I rocked up to work this morning and thought I give it another go and bang, everything's working OK? Hosting issues I guess.

A couple of new blog seem to have popped up in the last few weeks:

Code for Excel and Outlook by JP

and

XLNS by Jelle-Jeroen

I would also like to point out JP's code for getting route distances out of Yahoo/Map quest, which might be of interest to some of the folks who commented on the Geo-coding post I made here.

The standard approach to professional application design in Excel is to split out the data, the logic, and the presentation (Bullen et al 2005). In practice this is often harder than it at first seems, but that’s not the point of this post, the point is that if you adopt this structure you have to set about designing you worksheets appropriately.
There are a few guides to worksheet design. Some talk about how a worksheet should flow, top to bottom and left to right(Codematic, Eusprig), some focus more of how and why they should be formatted (SpreadsheetStyle ).

In my experience, more often than not, there needs to be some compromise in any none trivial application. This is especially the case in the “reporting/presentation” layer.
Recently Simon posted about the first thing we look for in a spreadsheet we inherit. He was interested in know the key indicators that the spreadsheet has been developed poorly. This in turn sparked a discussion about how much formatting is ok?

I’ve haven’t done much development work for quite a while, until last week when I developed a model for an in-house project Below is one of the sheets, with some notes on it.

Basically I have tried to make it easy to use. I have removed anything that is not used by the model and tried to guide the user to the bit that are.

Thoughts: Do you consider this too much formatting, too little formatting. What would you do differently

EditGrid is one of the better on-line spreadsheets. I've not used it much but took a quick look today. I was impressed with what I saw and interestingly they have cleverly made working with Excel and EditGrid easy with there addin:
Take a look here
This, I would think is a key factor, dont try to replace Excel (yet), but work with it!

I've been morning a bit lately so I thought I would take the time to make a post about some thing good!
I have been using Google notes for about a month or so now and I find it really works well.
Basically it allows you to copy bits of web pages (and stores a link back to the source), then you can joint down notes along side what you have clipped. The idea has been around for a long time, there used to be another app that did the same thing, (snobole or something).

The notes screen is a little pop up type affair which sits in the bottom (or top) of the window, you can select text from a web page and add it with a click.
When you want to you can export/transfer/copy it to Google docs, and play around with it.

If you do a lot of research on the web, give it a try.

Now, if it could auto-magically add citations out of Pdf's,....

hummm, insulting, fun, totally missing the point, a bit of all three?
VBA Person

Recently I've set up a Virtual PC, that is to say I've installed Microsoft Virtual PC 2007. I'm running it on Window Vista Home Premium, which is not actually a supported OS but it seems to work OK all the same:

I set up my “Virtual Machines” like this:
One base XP install with sp2
Then VS6 + Office XP(I didn't have a copy of 2000 kicking around)
VS2005 + Office 2003
VS2008 + Office 2007

That's it for the moment. Does this make sense? How do you have yours set up

The main thing I have noticed while using these VM's is the use of memory/performance. I have a Centrino Duo ( T5500/1.6), Geforce Go 7600 and 2 gigs of Ram, Ok I'm running the VPC in Vista, but the maximum I can run any VM with is about 1.3 gigs, some times they run a bit slow, and I only really run one at a time.

I guess the performance hit is worth the isolation?

This morning I woke up (always a good start to the day), and had one thing on my mind. Whats the second best spreadsheet?*

As far as I can see there are only really (really) 3 spreadsheets that could be considered .

OOo is working a way on the VBA API, when that gets there or there abouts things might start to get really interesting, or are people using VBA in OOo already. Have you been using other SS, how are you getting on with them?

*Yes, worrying isn't it.

Last week I was in a meeting for my Cricket Club. One of the things we have problems with is collecting and recording membership fees. Currently I have a database and coaches etc have SS which they update, send to me and I update the database (.mdb). We also need to keep a load of other info as well, like medical details, emergency contact number etc, etc.

I thought a possible solution might be to use Blist . I thought I might be able to set up a DB and a simple front end. No such luck. But thats not even the 1/2 of it. There are about 600 or so lines in the file and the performance was utter rubbish, when you scroll down the page, you get a please wait message!

Also notice the UI, ribbon in a web app anyone? (I wonder if this is ok with MS, might Blist be a direct competitor to office?!?) I'm not trying to run BList into the ground, I like what there are trying to do, I've been a fan of the Excess (Excel+Access in one app) idea for a long time, I think it makes sense. I just think that web apps are still very limited for anything "real".

Oh and it's not just Blist. After that I thought I'd give Zoho a go. Same problems.

The solution? I just set up an account with Tripod and will write some Php + mySQL to get the job done.

Office of the web? Not for a while.

Here at MIE, we strive to bring you cutting edge news and information :
spreadsheet wars
Worth a watch if you have a 1/2 hour with nothing better to do!

Hair cuts have come along way!

What's you gut answer right now; without reading the rest of this post?

In my (seemingly) never-ending quest along the path of Uncapacitated Facility Location problems, I found myself thinking if I would be better of write some core functions in C (unmanaged) and calling them in my VB.net project? I thought that it probably would be, but decided to check first. Here’s the link, you can skip to the conclusion at the end for the low-down:
http://www.grimes.demon.co.uk/dotnet/man_unman.htm

While looking form some P-median code samples, I stumpled upon this, it's amzing what people get up to, anyone for a spot of tennis?!

For most of January I have been working with an open source GIS mapping component called SharpMap. It's a cool tool, and very powerful, but being open source it - to some extent, -suffers from a lack of clear examples, particularly for programmers new to this type of programing (geospatial) .

Today I wanted to calculate the distance between to points on the earths surface. This is quite a difficult thing to do mathematically, but luckily for me in 1975 this fellow came up with a good answer. Even better is that this fine fellow wrote it up in a C# Class - so that saved me a few hours today ;-).

Anyway i thought it might be useful to show how I have put this into practice using VB.net. Enjoy!

Go to Mike Gavaghans website and down load his C# code. unzip it and copy the file called "Gavaghan.Geodesy.dll" from the "Dist" folder to your base folder for your solution. Then add a reference to it in your IDE. The following function takes 4 arguments and returns a distance between them in meters.

Then you can do all sorts of useful stuff!

I've not really had a popper play around with this yet, despite begin in the beat program for quite a while. Now they have released the first full version. The good news is the non-commercial version is completely free! And to be fair the commercial version is only 50 quid!

Why not take a little look?

UPDATE!
hueymanchew, quite rightly points out that .text is read only, so the you can't write to that property of a cell! A case of not seeing the wood for the tree I think!

I'm writing a light wight class to use in VB.Net projects which will late bind to Excel and dump a load of data into a worksheet or two. I want to late bind to avoid having to worry about which Interop Assemblies to use. Now with liberal use of "Object" decelerations it all work ok - sort of.
One strange thing is the fact that range(x).Text throws a wobbely, but that range(x).value is ok, probably something to do with whats the default? I've not seen this when automating with an early bound object and a "proper" PIA.

Code:

Error:

Anyone else seen this - know what's going on?

Right that should get a few people here!

XL Dennis has made public the first version of his code librarian - ".NET Co Library".
I've done a bit of testing for this addin and can personally vouch for how useful it is - keep all you VBA code in one place - copy the .mdb and take it with you, bung the .mdb on a network drive and you can share your code with the world (well the folks with access to the network any road)

Here's what Dennis has to say:

.NET Co Library is a managed COM add-in for Microsoft Excel. The acronym Co in its name refers to both Connection and Code.

With the tool you can:

Store created VBA code, code snippets / procedures / modules, and SQL Queries in a well organized way enabling you to reuse the code in all kind of Excel VBA solutions.

Create connection strings to a various number of databases with two wizards, the .NET Wizard and the Data Link Wizard. Store the created connection strings in a structural way enabling you to reuse the connection strings in all kind of Excel VBA solutions.

.NET Co Library has been designed so it can be shared by a group of VBA developers over a network.

Thats right, it's the future, it's .Net!!!

The addin, makes it easy to save code snippets, (even whole modules) and search back through them in other projects - it's a much better way to work with code than just copying it out of other workbooks...

But it's not just VBA code, it's SQL, and connection strings too...

What more could a Excel programmer ask for? Go on give it a try! And did i mention it was free...

more here:

Last night I downloaded Turbo Delphi, tonight I installed it, well, I tried to install it!

Looks like good old Delphi uses some parts of the .Net Framework and SKD - and why not really, but a little bit surprising?
Clearly I don't have 1.1 - so I'll have to install this in a few weeks(when time allows), at which point I'll tell you want I think of it - I bet you can't wait!

The world of managed com addins is a mess. VSTO has not been received well and the story of unmanaged code (Excel) and managed code is of deployment and stability nightmares.

There are a quite a few things right from the off that cause problems for Office devs wanting to use .Net technology:
- .Net is inherently slow, and slower still when com interop is needed
- mscorelib (hell, ;-))
- Security
- Deployment (esp. VSTO < v3)
- Framework availability

VSTO and managed com addin can over come the first 3 issues by using so called “com shims”, which are proxies sitting between the managed code and the com application (com shims themselves are unmanaged code). Although these solutions actually compound some of the other problems (i.e. speed), they generally work well and have been successful.

Deployment for managed com addins is not such an issue, as it is up to the dev to ensure the addin installs correctly. However the first two versions of VSTO tried to simplify this process, unfortunately Microsoft never “managed” to get it right. VSTO 3 seems to have cracked this particular nut, although we should wait for reports from the wild until we take this as gospel.

There is little anyone can do about framework penetration, but having to install a .Net framework to run a addin if frankly a laughable proposition and there is quite literally no way to avoid it, it’s fundamental to the concept of .Net. The problem then becomes what if users are unable to install frameworks. The thinking is that this problem will vanish with time, as windows service packs and new installs load the framework “organically”.

However, the last point may be moot because there is potentially a black cloud hang over managed code and Office!

Stephane Rodriguez of xlsgen points out that the framework version Excel (office) loads first, will be the framework versions that all subserquent managed addins will have to use. The com shim does not effect this out come as it’s controlled at the Application level, i.e. the process belones to Excel, not to your addin. This is comfirmed by Microsoft here, where the conserquencies are spelt out quite plainly!

If other managed code, such as an add-in, requires the later version of the .NET Framework, it will not run.

In the research I have briefly done of this issue there does not seem to be a solution available as of today. This seems like quite a serious shortcoming, and to me at least, hugely surprising because one would imagine that MS are aware of the implications.
It does beggar the question as to why this sort of thing should ever be an issue for devs, Microsoft trumpet long and hard about how easy MS products are to use, and often that is true, but with .Net and Com this could not be further from the truth.

This the is yet another blow for .Net and Office, it has implications for all the .Net technology, VS, VSTO, and VSTA (should VSTA ever make it in to a actually product?!), what are MS doing to us?!!?

I’m a big fan of .Net, its quick and easy, and very powerful, but is it the right tool for current office products? It’s Unlikely that MS will port Office to a .Net platform (currently no MS product of note are built using .Net – odd that!)

So where does that leave us? VB6 has long departed (although is still exceedingly popular) VBA is no longer developed, MS variations of C, and their claims to nativity is up for debate and it’s hardly a tool you want to use to build true RAD solutions. So, anyone for Delphi?

Heres a little example of some VBA functions I put together to get geocodes into excel using the google maps API.
It's not the best geocoder in the world, but it does ok in the test I've done so far, although it does just come up with blanks for some locations. To use it you will need a Google maps API key which you need to store in a text file on the same path as the workbook, details are on the first sheet of the workbook.

As for the code itself I had to automate IE rather than use an API or the HTTP or XMLHTTP libraries, as my corporate firewall would not allow access using these. The function only asks for 1 string, so you have to add spaces yourself, this might be done better, but it suited my needs (that is to say, I didn't bother writing in optional arguments etc.)

It's worth while looking at the code as I have done a few things that might not be considered "best practice", mainly not closing the IE application when the function has run - this is to speed up the function - there is a public function that you can use to close it from a worksheet if you wish (see code as it's not explained in the "documentation")

Theres probably some other stuff that I should mention, but I did this a week or so ago and have not been able to post it as my internet connection was down, so I have forgotten that!

Feel free to post any questions and I'll feel free not to answer them!!!!

Enjoy
Ross

Download M.I.E Google Maps Geocoding Example (zip)