11521

Microsoft sneaked a couple of ne It is generally accepted that it is a good programming practice to not use monolithic code, but to break functional units of code into separate subs or functions and call these from a control procedure. Functions are very useful for passing values back to the calling procedure, but functions return a single value so what if you want to get more than one value returned to you calling procedure? I have often seen this topic raised in the Excel forums, a statement that a function cannot return multiple values, or a question on how … Continue reading If One Is Not Enough, …

Excel Is Superman, DAX Is The Dark Knight Excel Formulas Are easy, So Why Is DAX So Hard? Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,   “I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your … Continue reading DAX, The Dark Knight

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?

Keep It Simple? Don’t Be Daft! I was recently working on a project where we were using SSRS to create XML reports which are then imported into Excel to facilitate further analysis. Unfortunately, I could not see any option in PowerPivot to connect to an XML source. There is the option for text and RSS, but no XML that I can see. I know I could create an RDL and connect to that, but XML is far more than just SSRS. I raised this with some colleagues, and one of them pointed me to this Microsoft article, Data Feed XML … Continue reading Keep It Simple? Don’t be Daft!

As I am sure that you are all aware, PowerPivot is the best thing to hit Excel since PED. Although I am not a big data cruncher, I have been playing with PowerPivot; to keep abreast of an important technology; it plays into one of my primary interests of Business Intelligence; and partly to seek out further opportunities. Although I have been scouring the blogs and dedicated websites, I do like to have a book as a reference point, and so I was very pleased to get a review copy of PowerPivot for the Data Analyst: Microsoft Excel 2010, by … Continue reading Self Service Is Cheap?

Recently, I was working on one of my apps, one that is database centric. Whilst making some changes, I came across this piece of code that inserts a new record into the database With RS .AddNew .Fields(FIELD_AUDIT_DATE) = sh.Cells(Rownum, COL_FU_AUDIT_DATE).Value .Fields(FIELD_CONSULTANT_ID) = GenerateID(FIELD_CONSULTANT_ID, Designer) .Fields(FIELD_SALES_TYPE_ID) = GenerateID(FIELD_SALES_TYPE_ID, sh.Cells(Rownum, COL_FU_SALES_TYPE).Value) .Fields(FIELD_CUSTOMER) = sh.Cells(Rownum, COL_FU_CUSTOMER).Value .Fields(FIELD_QUOTE_NUMBER) = QuoteNumber .Fields(FIELD_PRODUCT_ID) = GenerateID(FIELD_PRODUCT_ID, sh.Cells(Rownum, COL_FU_PRODUCT).Value) .Fields(FIELD_QUOTE_DATE) = sh.Cells(Rownum, COL_FU_QUOTE_DATE).Value .Fields(FIELD_QUOTE_AMOUNT) = sh.Cells(Rownum, COL_FU_QUOTE_AMOUNT).Value .Fields(FIELD_ESTIMATED_PROFIT) = sh.Cells(Rownum, COL_FU_PROFIT).Value .Fields(FIELD_REVISED_QUOTE) = sh.Cells(Rownum, COL_FU_REVISED_QUOTE).Value .Fields(FIELD_ACTUAL_PROFIT) = sh.Cells(Rownum, COL_FU_ACTUAL_PROFIT).Value .Fields(FIELD_STATUS_ID) = GenerateID(FIELD_STATUS_ID, sh.Cells(Rownum, COL_FU_QUOTE_STATUS).Value) .Fields(FIELD_DECLINED_ID) = GenerateID(FIELD_DECLINED_ID, sh.Cells(Rownum, COL_FU_DECLINED).Value) .Fields(FIELD_QUOTE_NOTES) = sh.Cells(Rownum, COL_FU_NOTES).Value .Fields(FIELD_UPDATED_BY) = ThisApp.LogonUser .Fields(FIELD_UPDATED_ON) = … Continue reading VBA Has No Class

Excel’s ability to run pivot tables against an OLAP cube in Analysis Services is a truly wondrous beast. The ease of use; volumes of data; the filtering; all of these provide a rich environment for users to easily inspect their data and get real information out. Add to this that Microsoft seem to believe that pivot tables are a winner and keep improving it, access to cubes and slicers being some of the latest significant changes, I can see no reason for all serious Excel users not to get to grips with this technology. In addition to all of this … Continue reading Cracking The Code

I have been playing with PowerPivot recently, and one of the sites I have visited is Rob Collie’s PowerPivotPro blog, http://powerpivotpro.com/. One particular post that caught my eye was a guest post by Colin Banfield, on building complex DAX formulas in NotePad++. As Colin noted, the PowerPivot formula editor is severely limited, a better option is required when working with more complex DAX formulae. As Colin suggested, NotePad++ can provide this better option; with code folding, keyword highlighting, and Intellisense. In his post, Colin showed how to extend NotePad++ to provide this functionality for DAX formulas. Being primarily an Excel … Continue reading Formulas Made Easy

Microsoft sneaked a couple of new functions into Excel 2010. These functions are WORKDAY.INTL and NETWORKDAYS.INTL. They appear to be addressing the age-old question of handling different working day patterns around the globe. Whilst we have Saturday and Sunday off, some countries may have Friday and Saturday. And of course, when planning work, some poor souls only get a single day of the week off. These new functions provide a new optional argument, weekend, to specify which days are to be treated as weekend days. This should remove those complex formula solutions to handle this particular problem. Unfortunately, MS as … Continue reading The US Isn’t The Whole World?

One of the better additions to Excel 2007 are, in my view, the functionality added to tables http://www.jkp-ads.com/articles/Excel2007tables.asp. I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark. Formatting The Table For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case. Figure 1 shows an example of a typical table in Excel 2007. Figure 1 Let’s assume that I want to show those rows where … Continue reading Who Scratched My Table?