Month: August 2009

Normal 0 false false false MicrosoftInternetExplorer4 In a previous post, XML Is Such A Pain, I showed the technique that I use to simplify the creation of XML code for custom ribbons. This is a handy technique, as it means that creating the XML is very simple, it is more or less a cut-and-paste job, and I spend very little time thereafter  with the XML, which I like as the tools for XML in Excel 2007 are not great. As much as I like this technique, it still requires a lot of VBA code. Whilst VBA is a great deal … Continue reading Autogen’ed Ribbon Code

Normal 0 false false false Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve.  I will not go into the details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at http://www.rondebruin.nl/ribbon.htm. The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this requires the following XML code <customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui” onLoad=”rxDemoRibbonOnLoad”> <ribbon startFromScratch=”false”> <tabs> <tab id=”tabDemo” getLabel=”Ribbon Demo” insertAfterMso=”TabHome”> <group id=”grpDemoGroup1″ getLabel=”Formatting Group”> <button id=”btnDemoButton1″ label=”Currency” image=”DollarSign” imageSize=”Large” screentip=”Button … Continue reading XML Is Such A Pain

The title was just to get your attention, it really should be entitled something along the lines of “What Made VBA The Success It Is Today?’, as this post is more about what VBA is and isn’t, albeit somewhat precipitated by the thought that VBA must be close to its end game. I recently saw a thread in a forum where one of the responses was along the lines that “VBA was not designed for programmers – i.e. MS dumbed down requirements …”. I found this to be a very facile argument and said so in my response. I won’t … Continue reading The End Of VBA

It is not my intention for EDDD to be another ‘Excel Tips & Tricks’ blog, there are plenty of good examples of these already, but I saw someone struggling in a demonstration recently so I am thinking that this technique is not as well know as I had presumed, nor as it should be. Therefore I shall share it with you all. Firstly, I hope we all agree that the Pivot table Field List in Excel 2007 is a big improvement over earlier versions. However, sometimes the cost of a nice facility is that comes at the cost of a … Continue reading Keeping It Clear

In a previous post, I mentioned that I had just got a new server, and what I intended to do with it. Progress has been slow. It had been my intention to load Windows 2008 on the machine, but I didn’t have an ISO image on disc and so I had to download it from MS. Unfortunately, that was far from simple. The download kept crashing saying that it could not read the file (after about 500Mb). I eventually managed a successful download last Saturday (it took hours). That was not the end of my problems, far from it. The … Continue reading Server Stage 1 Installed

Normal 0 false false false MicrosoftInternetExplorer4 In Excel 2007, if you start to enter a formula, such as =I, in the formula bar, it immediately provides you with intellisense of all functions starting with I. As with normal intellisense, the more you type, the more precise the list becomes. I am sure that we all agree that intellisense is a wonderful feature and its addition to formulas in Excel 2007 is a welcome step. I recently had a client who was building some UDFs, each of which would start with a particular id. They rightly thought that it would be … Continue reading Custom UDFs

So many times you see someone asking about whether they should split their VBA code into many modules, if so, how should they structure it? We probably know that there is a limit to the amount of code that a VBA code module can handle. The code module is essentially an in-file text file (if exported it can be viewed with a simple text eitor), and can accomodate a size of approx 64k bytes. As such, the answer to that previous question should always be an emphatic yes. But it also should be yes from a code maintainability persepctive, even … Continue reading Too many Modules Spoil the VBA Project?

Surely we all know how useful Pivot Tables are in Excel by now? They are great for knocking up a filterable, drillable list of the data, but they can be much more. For instance, it is realtively simple to create a Top 10 (or top n) list from your data in a pivot table. To my mind, this sort of table should form the basis of all dashboards, far better than those pointless guages, pie charts and stacked bar charts (but we digress!). Anyway, as I said, a top 10 list is simple to do. Here are the instructions for … Continue reading The Glory of Pivots

Big day today. Just taken delivery of a new server box, dual core with 8Gb and Raid-1. By the cringe, it was a heavy beastie. I will be installing Windows 2008 and SQL Server 2008 on it, as I intend to do some more heavy duty BI (still using Excel to be the presentation layer of course). In due course, I will add Sharepoint, and do some Excel Services (but let’s walk first). I am not really a hardware or OS guy, in the past I have always had team mates who did that sort of thing, so this will … Continue reading New Server

I had a simple requirement recently, I wanted to create a form where it was possible to navigate around using shortcut keys. Easy you would think, just assign accelerator keys to labels and/or commandbuttons … problem solved. Unfortunately, I also have a number of frames with multiple checkboxes in them, groups of checkboxes. And guess what, frames don’t support the accelerator property. My first thought was to overlay the frame with a label and blank out the frame caption. As labels have an accelerator property, this should work, using a shortcut key would hotkey to the label and then you … Continue reading Why Does Excel Make It So Hard?