Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

December 2009 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Software Pricing

    Last week I was getting some blue screen errors. A hardware diagnostics test reported that my hard drive had a couple of bad sectors. I bought a new hard drive and set about installing all of my programs. Wow, I have a lot of applications installed. One of the application is X1; a [...] Read More...
    Filed under:

  • Using controls on worksheets

    Hi all, I decided I need a bit more content on my site for the average to intermediate Excel users: Using controls on worksheets Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your [...] Read More...

  • Hello World Button on a Ribbon

    Many of my spreadsheets use Excel 2003 Commandbars for application control. Having loaded these spreadsheets into Excel 2007, I see my commandbars presented on the Add-In tab of the Ribbon. I’m a late starter with Excel 2007 ribbons, but I guess it’s time to move. I’ve found that the various guides on the Internet tend to throw you [...] Read More...

  • Date Formatting

    In New Zealand, we format our dates dd/mm/yy. However, the United States format as mm/dd/yy. When I see a date by itself, like 12/01/09, I ask “is this the 12th of January or the 1st of December”? When I started working with Oracle RDBMS, a habit I picked up was to format dates dd-mon-yyyy. This avoided the dd/mm [...] Read More...

  • Methods in Excel Podcast #3

    I recently did an interview with Ross at MIE. Check it out. I’m able to subscribe to MIE podcasts via iTunes now. So if that’s your preferred method, have at it. Read More...
    Filed under:

  • New content on my site about 2010

    Hi all I create a 2010 section on my Tip index page with the first pages about 2010. http://www.rondebruin.nl/tips.htm Load different RibbonX when opening file in Excel 2007 or 2010 Download to find the names of the Excel 2010 ControlIDs (819 kb) Backstage view in Excel 2010 I not say they are good but this are my first examples that I [...] Read More...
    Filed under:

  • Pasting Excel Ranges in Outlook

    When I copy and paste this Excel range into an Outlook email, it doesn’t look so good. I thought I would use JoinRange to put some tabs in between the text. I used a function like this =PERSONAL.XLS!joinrange(A2:D2,CHAR(9)) A tab is ASCII code 9. It will show up as squares in Excel but will turn into real tabs [...] Read More...
    Filed under:

  • Multiple Substitute VBA

    In regard to Multiple Substitute Formula, here’s one way to do it in VBA. Sub RemoveStates()         Dim rInput As Range     Dim rStates As Range     Dim vaInput As Variant     Dim vaStates As Variant     Dim i As Long, j As Long     Dim sTemp As String         Set rInput [...] Read More...
    Filed under:

  • Multiple Substitute Formula

    MaryAnn asks an interesting question. In column A there is text that may or may not contain the name of a US state. In column B there is a list of US states. In column C, we want the column A text without the state names. I think this would be a pretty [...] Read More...
    Filed under: ,

  • Preventing auto_open and Workbook_Open events from running

    As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running. With Excel 97 to 2003, this was simple: hold the shift key when you click “Enable macros”. With Excel 2007 and [...] Read More...
    Filed under: , , , ,

  • Formula Tokenizer

    A few years ago, I wrote a Formula Formatter add-in to present a long formula for easier reading. It does it through a process called Tokenizing, which is basically a process of putting the components of a formula into labelled boxes. That add-in was compatible with Excel 2003 formulas, but Excel 2007 brought us extra formulas [...] Read More...

  • Short DOS Path

    Remember DOS? Back in the day, we had to limit our file names to eight characters plus a three digit extension (8.3) and we liked it. Today I needed to add Google Chrome to my startup batch file. Normally I can convert long Windows paths to short DOS paths in my head, [...] Read More...
    Filed under:

  • Benford’s Law

    J-Walk posted about Benford’s law and I thought I’d test it out. I found an Excel file with a lot of numbers, namely a price list. In a new workbook, I put the following code: Sub MakeNumbers()         Dim wb As Workbook     Dim ws As Worksheet     Dim rCell As Range   [...] Read More...
    Filed under:

  • ExcelUser Sale

    ExcelUser.com is having a Half Off Sale through this Friday. From now through midnight Pacific Standard Time (GMT -8), December 4, I’m offering a 50% discount on four of my most-useful dashboard products, and six free bonuses… For those of you not running an ad blocker, you probably noticed that ExcelUser.com runs an ad at [...] Read More...
    Filed under:

  • How to Get Office for Free

    How do you get Microsoft Office for free for Macs and PCs? is written by fellow MVP Jim Gordon. He’s a Mac guy, but I still like him. Strategy #5 Ask for Microsoft Office for your birthday or a holiday, such as Christmas. Hey, you never know! Good stuff. Read More...
    Filed under:
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.