Uncategorized

Microsoft sneaked a couple of ne PowerPivot activity announced on Twitter for the week ending 20th January 2013.   PowerPivotPro Modeling Viral Growth vs. Traditional Advertising in PowerPivot – bit.ly/10EOrS3 Javier Guillén Pervasive Business Intelligence Grouping by an Aggregation in DAX – bit.ly/SeC22c PowerPivotPro Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! – bit.ly/W6YrMa Javier Guillén Pervasive Business Intelligence Leveraging DAX query tables for staging Data Transformations – bit.ly/13Awule Some Random Thoughts Row Selection Using Slicers in PowerPivot – Part 1 – bit.ly/108bp2H Chris Webb’s BI Blog A Different Approach To Last-Ever Non-Empty in DAX – … Continue reading PowerPivot BlogRoll – 20th January 2013

We all know that coding is great fun, even code design is fun, but testing and debugging are most certainly not fun. As such, we have to do what we can to lighten that burden.  One of my underlying principles in coding is in keeping the code well structured, well laid out, and generally easy to follow, so as to make it easier to maintain, easier to debug, and just generally a better experience. Whilst spending some time on a forum today, I came across this code which had been found elsewhere. My question to you is, what is wrong … Continue reading Keep It Simple

My laptop blew this last weekend. Unfortunately, my backups were yet to be done, so I have lost a fair amount of work. That is incredibly frustrating, but this post is on another topic. I was an Excel 2000 user for many years. I did dabble with 2002/XP for a while, but I didn’t see any real advantage to it,  didn’t really enjoy it, so I went back to Excel 2000. When I did finally take the plunge, I upgraded to Excel 2003, and after a few querulous moments adapting to this flashy beast, where I first took notice of … Continue reading Regression Can Be Sweet

Normal 0 false false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 I was building a ribbon this week (yes, I can’t get enough of them), when I cam across what is a really stupid problem. My application is building a cascading dynamic menu , a list of all files in the selected directory, and all sub-directories in the parent directory are added as sub-dynamic menus (creating the cascade when they are clicked). No sweat I thought, just use FSO to get the files and the folders, and string the XML together dynamically … I’ve done it a hundred times, it will … Continue reading Coding Hurts, But the Ribbon Breaks Your Heart

Normal 0 false false false MicrosoftInternetExplorer4 I am an inveterate builder of addins for Excel. They are so flexible, so easy to build, but they have a potential deployment issue. Yes I know that for most of you installing an addin is trivial, but I create them for corporations as well, and they either a) cannot afford to go to every desktop and install addins manually, or b) cannot assume that all of their staff who might need the addin can install it themselves (even if they have the necessary permissions) . Of course, the solution is well known, build … Continue reading Hand-holding for Dummies

Normal 0 false false false MicrosoftInternetExplorer4 I saw a question in the newsgroups today where a guy was asking how he could get more than 10,000 hours in a cell, adding a hour number larger that 10,000 to another time number the formula did not give a correct answer.  Some of the answers suggested that Excel cannot hold a value of 10,000 or more hours. This is actually incorrect. It is true that it is only possible to enter a single value of 9,999:59:59 into a cell, but it can be tricked into holding more. For example, enter 5000:00 in … Continue reading Time has come today

Normal 0 false false false MicrosoftInternetExplorer4 Doing some work on an Excel spreadsheet this morning reminded me that there are some great products that have been developed that enormously enhance the ease of use, flexibility, and general usefulness  of Excel. Whilst this blog is not really about promoting Excel products, there is one product that I believe stands head and shoulders above any other out there. I am not a great fan of installing Excel addins, they usually have 200 functions of which I only want 1 or 2, but I have installed this addin and I don’t believe there … Continue reading Names Should Be Seen

I had an odd problem with VBA today. I used a table driven menu builder (don’t we all), and I define the column numbers of that table in an enumerated list.  This is that list Private Enum CB_COLUMNS    CB_LEVEL_COL = 1    CB_CAPTION_COL = 2    CB_POS_MACRO_COL = 3    CB_TAG_COL = 4    CB_PARAMETER_COL = 5    CB_SHORTCUT_COL = 6    CB_SPACES_COL = 7    CB_DIVIDER_COL = 8    CB_FACEID_COL = 9    CB_VISIBLE_COL = 10    CB_ENABLED_COL = 11    CB_TYPE_COL = 12    CB_DEBUG_COL = 13End Enum I was making some changes to the application today, and I suddenly got an error where the values in this list were … Continue reading As Constant As The Wind

Normal 0 false false false MicrosoftInternetExplorer4 A question on one of the forums recently asked about hiding sheets in a workbook to be posted as an example, but where that workbook contained sensitive information. As I replied, hiding the sheets doesn’t really hide the data so he would be just as exposed. Jimmy Pena has a a great application for generating random data, but sometimes it is just better to scramble the data that you already have. I suggested to the poster that they should really be scrambling the data, and further suggested that it should not be too difficult … Continue reading Clear As Mud

Excel 2007 has had much comment since its introduction, most of which has been centred around the ribbon, is it a piece of inspired insight by MS, or a blunder of enormous proportions? This post will not concern itself over that issue directly, but will take a look at how some have addressed the introduction of the ribbon, seeing product opportunity. I am referring of course to the advent of various applications that provide the old 2003 style menus within Excel.   Over the next few weeks I am going to look at a number of these applications, cover their … Continue reading Nostalgia IS What It Used To Be