Daily Dose of Excel

Importing text files in an Excel sheet

2010-03-11T09:27:54Z

Hi there! Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time. Opening the file in Excel [...] Read More......(read more)

Using the MATCH Function for a Linear Search and a Binary Search

2010-03-10T20:43:01Z

Given the problem I have with images and blog posts, I’m trying something new. I uploaded the content of the post to my website and am using an iframe to post it here. Yes, the UI experience is somewhat different than scrolling the entire browser window but hopefully it is something most people [...] Read More......(read more)

Keno Odds

2010-03-09T21:37:44Z

We were trying to figure out the odds on keno the other day. There’s something about statistics that doesn’t create appropriately strong synapses in my brain. I got an A in stats in college, so I know I can retain the information long enough to be tested on it. But put me [...] Read More......(read more)

Getting Array Data from a Filtered List in VBA

2010-03-08T21:50:31Z

Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this: vMyArray = Sheet1.UsedRange.Value I thought it would be keen to fill an array from a filtered list, so I coded Sub ArrFilteredList() Dim vArr As Variant vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value [...] Read More......(read more)

Plain and Pretty

2010-03-05T11:30:44Z

Mike Alexander tells me that I have to sell, so sell I shall. First, the pretty: Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together again to bring you our acclaimed Excel Dashboard and Visualization Bootcamp! This 3-day boot camp is designed for Excel users who need to more effectively synthesize [...] Read More......(read more)

Office Automation

2010-03-04T13:00:58Z

Someone asked if anyone automates Office applications any more. That struck me as kind of funny. If I had to guess, I would guess that there are more lines of VBA code (poorly written or not) that automate Office applications than all the COBOL, VB6, PHP, and any other language you can think [...] Read More......(read more)

PowerPivot - Part 3 of 4: Conditional Shape Colors

2010-03-03T02:13:11Z

Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Part 2: http://www.dailydoseofexcel.com/archives/2010/02/27/powerpivot-part-2-of-4-prepping-the-census-data/ As mentioned in the Part 1, I had developed this technique to conditionally color shapes some time ago. Based on customer feedback and my own experience I made changes to simplify the various connections required to make it all work. Also, documenting the technique was easier to do with a webpage [...] Read...(read more)

Web Query Lessons

2010-03-02T14:57:31Z

I’ve recently learned some things about Web Queries that I’d like to share with you. But first a little background. You create a Web Query in Excel 2007 by clicking From Web on the Data tab. This launches a browser within Excel through which you navigate to a web page and select a [...] Read More......(read more)

IsHex Function

2010-03-01T22:18:14Z

I have a need to verify some user input is hexadecimal (0-9 and A-F). Public Function IsHex(sInput As String) As Boolean Dim lResult As Long On Error Resume Next lResult = CLng("&H" & sInput) IsHex = sInput = "0" Or lResult > [...] Read More......(read more)

PowerPivot - Part 2 of 4: Prepping the census data

2010-02-27T21:24:35Z

Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Since Excel 2007 (and later) can handle a million rows of data, I figured it made sense to explore PowerPivot (PP) with a data set larger than that. In addition, I have had a long time curiosity about the US census data. So, this seemed an appropriate time to combine the [...] Read More......(read more)

PowerPivot - Part 1 of 4

2010-02-27T03:33:07Z

After the MVP Summit I was motivated to further explore a Microsoft add-in for Excel 2010 called PowerPivot. I don’t know all its capabilities but at the very least it can analyze millions of rows of data. If you are using Excel 2010 you can download the add-in from http://www.microsoft.com/downloads/details.aspx?FamilyID=48a5b47b-8c9c-450f-ab6e-178600a733ca&displaylang=en Do keep one thing [...] Read More......(read more)

Listing Formulas

2010-02-26T13:00:39Z

This code will list all the formulas in the selection in the Immediate Window. Sub ListFormulas() Dim rCell As Range If TypeName(Selection) = "Range" Then For Each rCell In Selection.Cells Debug.Print String(4, " ") & rCell.Address(0, 0), rCell.Formula Next [...] Read More......(read more)

Drawing in Excel

2010-02-25T16:02:16Z

Brad sent me a link to this cool YouTube video http://www.youtube.com/watch?v=4YG_WWZYqUs Libro1? Is that Italian Excel? Then there’s this: http://www.youtube.com/watch?v=8oR1_PA3yTw And you thought 3D charts were useless. Read More......(read more)

Sheet Tab Colors

2010-02-24T02:16:02Z

CR rants: One thing about Excel 2007 that really bugs me are the tab colors. Most of the choices look so washed out, rather than a nice solid color. One does not have a choice when Excel makes the tab text white or black. Sometimes, Excel chooses a white font for the text [...] Read More......(read more)

New RDBMail Mail add-in for Excel/Outlook 2007-2010

2010-02-21T12:33:38Z

Hi all FYI I upload a new add-in named RDBMail for Excel/Outlook 2007-2010 this week http://www.rondebruin.nl/mail/add-in.htm The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options and a option to insert a worksheet template to your workbook so you can send more then one mail with one click because the info for each mail [...] Read More......(read more)