0845 643 64 63


Excel crashes when enabling macros

I’ve had a problem with Excel 2013 crashing a number of times when opening/editing spreadsheets with macros. When I click ‘Enable Content’ to enable macros, the Excel window fades out, goes unresponsive and then crashes with a glorious “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” message. Unfortunately Windows never seems to be able to find the elusive solution that we need.


I’ve found that the spreadsheets tend to stay working whilst you’re looking at them (if you don’t click ‘Enable Content’), however it crashes when you try and do anything useful such as copy/paste, etc. Sometimes Excel just crashes when opening the file, even without enabling macros. This is seemingly caused by a corrupt compile of the macros. Yes I know it’s VBA code which is interpreted and not compiled, but I’m over-simplifying here.

There a a couple of solutions to this:

1) Before enabling the macros try adding a new blank sheet, and then save the spreadsheet, reload, and sometimes it corrects itself. But not always.

2) Recompile the macro

  • Open the Excel spreadsheet (but don’t enable macros yet)
  • In the ‘Developer’ ribbon, click ‘Visual Basic’


  • On the ‘Tools’ menu, click ‘Options’
  • Then on the ‘General’ tab, untick ‘Compile On Demand’, then click OK


  • Then scroll down in the ‘Project’ window, right click on ‘Modules’, then Insert a new module



  • Then just close the Visual Basic window, save the spreadsheet and close Excel.
  • Hey presto, when you then load your spreadsheet you’ll be able to enable macros, and Excel won’t crash.

Thanks to Paulina from Microsoft for helping me find this solution – it’s saved my bacon a few times now!


SQLBits 8 in Photos

Well what a few days. Everyone I spoke to agreed that SQLBits 8 has been the best SQLBits conference yet. The organisers did a fantastic job, the whole event seemed to run incredibly smoothly and was very well received. I’m sure there’s a certain element of the swan effect, with frantic paddling behind the scenes, but they pulled it off very well indeed. A massive thanks for all of your effort.

For me, this was the first SQLBits conference that I’ve presented at, and I have to say it made it even better for me. I’ve got so much out of SQLBits over the years it’s really nice to be able to give something back and contribute to the event. I thoroughly enjoyed presenting my sessions, and will definitely get a session or two lined up for the next conference.

I took a few photos over the 3 days, and thought I’d share them to give you a feel of the event if you couldn’t make it.

The venue: Brighton

And we couldn’t have asked for better weather. Just fabulous.

The hotel, The Brighton Grand.

The general consensus seems to have been that the best SQLBits conferences so far have been Celtic Manor and Brighton. To me, this is because both were held in hotels, which brings the social and work elements of the conference together.

The first day: SQLBits Insight

This is the first time the ‘Insight’ day has been held, and is aimed at more of a non-technical audience; managers, decision makers, architects etc.

I thought it was a great success, and was far better attended than I expected, with every seat full and standing room only. Great to see.

The panel discussion.

Some really interesting topics covered, with a very experienced panel. Mark Souza (SQL Server General Manager) and Guy Lucchi (CTO for the NHS project at CSC) were especially interesting. If anyone tries telling you that SQL Server doesn’t scale, tell them to get in touch with Guy – the sheer scale of the NHS project defies belief, and is all SQL Server…

The Woz!

One of the highlights for me was Steve Wozniak, co-founder of Apple. He gave a fascinating talk about his history with electronics, and how it has led him to be involved with the Fusion IO team. If you’ve not heard of Fusion IO, go and look them up, they have some awesome kit for insanely fast storage systems. Very impressive.

Not just fast storage space…

Love this photo, tweeted by Aaron Bertrand. Is this the world’s most expensive iPhone charger?!

The Games.

On Thursday night, the Fusion IO guys put on the best SQLBits party yet. Not only can they give you really impressive IO stats, they can also give you a really impressive hangover… One of the highlights was the bucking bronco.

This is Nigel from Barclays Loans getting thrown off with an element of style! He won an X-Box for his troubles though!

Nigel has just been in touch to clarify a point… He did of course give his XBox to Kath (from innocent drinks) straight away as an act of gallantry. As he wonderfully put it: “All I got out of it is nasty bruising, a faulty thumb, and a good story”

And who says chivalry is dead?! Most admirable Nigel, you put the rest of us to shame as there’s no way I’m giving up my iPod!

Woz signed iPod!

I couldn’t resist having a go myself, and won an iPod signed by Steve Wozniak! Result.

The mandatory late night greasy spoon. Well, you need something to soak up the free bar!

One of my favourite parts of the conference is the people. It’s great to meet up with old colleagues, friends and clients, as well as putting faces to the twitterati and of course meeting some great new people.
The SQLCAT team.

Great to have Thomas Kejser and co from the SQL Customer Advisory Team. These guys know their stuff!

I have to confess, I did manage to sneak off for a few minutes and enjoy an ice cream on the beach… It would have been rude not to, I’m sure you’ll agree…
Friday night fun and games began with a game of giant Connect 4. I’m proud to say I managed a 4 game unbeaten run before having to quit on a high and head off to the speakers and sponsors meal.
The speakers and sponsors meal on Friday night
My conference room!

I gave two talks on the Saturday, one ‘lightning’ talk and one full hour session, both in this room. The lightning talks were a great success, lots of speakers both experienced and new, presenting a brief 5-15 minute session on something interesting. I was pleasantly surprised by the great turnout to these sessions, I hope they do more at the next SQLBits.

It’s not all about learning SQL stuff…

Jamie Thompson did a great job of recording some great impromptu videos which he’s uploaded to YouTube. One of his more amusing and random videos is of Allan Mitchell explaining the finer details of the espresso macchiato.

He also recorded a couple of me summarising my two sessions: here and here.

In summary, great venue, great conference, great sessions, great people.

See you all at SQLBits 9!


Seagate Momentus XT Hybrid Drive

I thought I’d take a break from writing posts about Business Intelligence and SQL Server, and instead share with you my elation at finding a laptop hard disk that quite simply makes the world a better place, the Seagate Momentus XT hybrid drive.

When I purchased my curent laptop (Dell XPS M1530 if you’re interested, with 4Gb RAM) I was presented with a choice between a fast 7200rpm 200Gb drive or a slower 5400rpm 320Gb drive. Due to the size of the databases I tend to work with I had to opt for the larger of the two, a Western Digital Caviar, taking the hit on performance.

I’ve been tempted for a while to upgrade the disk to a 7200rpm but have been secretly holding out (in vain) for solid state disks to increase in size and performance whilst decrease in price. £600 for a 256Gb SSD still renders them too expensive and too small to be an effective option for my needs. 512Gb drives are expected soon, but with a price tag of over £1000. No thanks.

Enter Seagate, with their Momentus XT hybrid drive which is now available in the UK. The 500Gb version (also available in 250Gb and 320Gb) is a standard laptop sized 2.5″ drive which combines 4Gb of super fast SLC NAND solid state storage alongside a 500Gb traditional 7200rpm drive. It also has 32Mb of drive-level cache. The drive monitors disk usage and automatically uses the SSD for the most commonly used files, without any help or drivers on the operating system. Thus you get the size/cost benefit of a standard drive but the performance boost of an SSD for your most accessed files. And all this for less than £100… How could I resist?!

After a weekend of reinstalling Windows 7 Ultimate (x64), Office 2010, SQL 2008 R2 and the usual plethora of other software, the results are quite simply staggering. My previous setup would let me login to Windows after 60 seconds, but I had to wait a total of 7.5 minutes until Outlook was open and usable. In the new setup I can login to Windows after 35 seconds, and Outlook is open and usable in under 1.5 minutes. 6 minutes saved per day just on bootup. That’s a whole 24 hours per year.

I have to place a caveat here, that there are a number of software differences between the two systems so it’s not by any means a scientific test. My old system was XP Pro x86 and the new one is Windows 7 Ultimate x64, I’ve changed SQL Server 2008 to 2008R2, and all the drivers/software are 64 bit instead of 32 bit. This will certainly make a difference on its own so the performance is not entirely down to the drive, however I have to assume that it takes the majority of the credit. Every detailed review that I’ve seen reports average performance as pretty much mid-way between a 7200rpm and a SSD disk.

The only downside is that I’ve now got to spend a few more weekends upgrading the other company laptops!

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out