xldynamic Returns

Normal 0 false false false MicrosoftInternetExplorer4 I have a website http://www.xldynamic.com/ which tries to provide some useful Excel information, different to the many other Exel websites. This site includes what is probably the most useful SUMPRODUCT discussion on the web. Around about August 2008, my host provider, 123-reg.co.uk, started to tear down my site on or around the 20th of every month, due to exceeding my bandwidth limit (I admit, until then I had not been aware I had a limit). I was confused as to why this should happen, my site was popular, but was never one of the … Continue reading xldynamic Returns

Shreds And Patches

“Man is physically as well as metaphysically a thing of shreds and patches, borrowed unequally from good and bad ancestors, and a misfit from the start” (Ralph Waldo Emerson)   A number of us British Excel’ers (I nearly said English, Roger would never forgive me) have been holding Excel conferences for a few years. It had been a paid event, but there was no such event in 2008 as the 2007 event was not well enough attended. In 2009, we decided to try something different, and make the event free! Naturally, interest was high, and at the conference (held at … Continue reading Shreds And Patches

Hand-holding for Dummies

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

Time has come today

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

Names Should Be Seen

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

As Constant As The Wind

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

Clear As Mud

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

What’s My Type?

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?

Nostalgia IS What It Used To Be

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

Ribbons – Gain Without The Pain

Normal 0 false false false MicrosoftInternetExplorer4 If you are in the habit of building or customising Excel 2007 Ribbons, there is an indispensable new tool that has just hit the market. Andy Pope has released his RibbonX Visual Designer, which can be found here.   I have blogged previously about how I find cutting XML tedious, and the inadequate tools available, so this is a more than useful addition to make ribbon design and coding simpler. It provides a GUI to facilitate the Ribbon build, simple point and click rather than the tedious line coding. The big advantage of this … Continue reading Ribbons – Gain Without The Pain