Introduction I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy. The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the … Continue reading Who Says The Ribbon Is Hard?
Being a developer can be addictive fun. Sometimes you just get into the swing and before you know it is midnight and the house is eerily quiet. But it can also be hugely frustrating. Yesterday I had just such one of those days. I spent a whole day on a task that should have taken an half an hour maximum. I have inherited the maintenance of a small application that that creates Word documents from a set of inputs in an Excel form and spreadsheets. Now being a good coder, the original developer created Word templates with a series … Continue reading Thicker Than A Length of 4be2
Excel 2003 Addins In Excel 2007 Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version. One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to … Continue reading Deploy Me Simple
I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder. XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a … Continue reading The XML Is On The Kid
In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO. As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the … Continue reading The Kid Grows Up
Dynamic Analysis Excel 2007 provides the capability to have a pivot table connected to an OLAP cube. Furthermore, detailed analysis can be built by using various CUBE functions. One of the great things about Excel 2007 pivots and CUBE functions is that you can use a value from the pivot as a member value within a CUBE function. For instance, a CUBEVALUE might be getting the net gas sales for a particular company and period with CUBEVALUE(CubeName, “[Transaction Company].[Company Drilldown].&”, “[Activity Date].[Date Drilldown].[Month].&&&”), “[Measures].[Gas_Amt_Net]”) If you also have a pivot that is filtering the company drilldown, the … Continue reading Cycling Through The Fog
VBA is a very simple language and programming environment, very easy to get something up and running very quickly, but it is showing its age these days, and with Microsoft making no improvements to the language it will not get better. Which all means that we should utilise all of the functionality already available to us. I was using a particular technique the other day, and I recollected a discussion on one of the popular Excel forums that I had participated in, one in which I was surprised at the lack of knowledge of this technique. The situation I found … Continue reading Conditionally Yours
I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I … Continue reading What’s My Type?
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
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?