Copying A Range As A Graphic
Today I was working in Photoshop Elements, and pasted a graphic image that I copied from another source. At least I thought I did. As it turns out, I forgot to copy the graphic image, and Photoshop Elements pasted an Excel range that happened to be on the clipboard.
I was surprised because it never occurred to me that Excel stores a graphic format of a copied range. I immediately tried it with IrfanView (my favorite graphics viewer) and it also worked with that program.
Probably every other Excel user knows this already, but I wrote it up as a tip just in case: Saving A Range As A Graphic File. I also wrote a simple VBA macro that automates the process.
Enhanced Data Form Date Problem Fixed
If you use the J-Walk Enhanced Data Form -- and you don't use the U.S. date formats -- here's good news. I finally figured out how to get it to work properly with all date systems.
The problem has plagued me for years. As it turns out, I was looking for the solution in the wrong place. The problem wasn't writing the dates to the worksheet; it was reading them into the form. Doh!
In any case, I've updated both the Excel 2007 version and the Excel 97-2003 version of this add-in, and you can download it here.
Sorting Oddity Posted
I posted a new oddity: Sorting Oddity/Bug. It describes a weird sorting bug that occurs if you use sheet-qualified references -- but only if the sheet qualification is the same sheet as the formula.
It was contributed by Excel MVP Bob Umlas, king of Excel weirdness. Bob is the author of an interesting book called This Isn't Excel, It's Magic.
Slide Show Demo Posted
I just posted a VBA procedure that displays a full-screen slide show of all embedded charts on a worksheet: A Quick And Dirty Slideshow Macro.
There's also a demo file that you can download.
It's no match for PowerPoint, but it might do in a pinch.
Maximizing Excel On Your Virtual Screen
I just posted a tip that contains VBA code to Maximize Excel Across All Monitors.
Since I only have one system with multiple monitors, my testing was extremely limited. If you have a multi-monitor system, please try it and let me know if it works for you. I'm especially interested in how it works if the monitors aren't the same resolution.
Calendar With Holidays Posted
A perfect example of Excel obsession...
First, I create a perfectly fine workbook that displays a yearly calendar for any year. Then, I must improve it by adding highlighted holidays. I just posted a new download: Yearly Calendar - With Holidays.
I created a range that calculates the holidays. Then, I used a conditional formatting formula to check the holidays and highlight the calendar days for these U.S. holidays: New Year's Day, Martin Luther King Jr. Day, Presidents' Day, Easter, Memorial Day, Independence Day, Labor Day, Veterans Day, Columbus Day, Thanksgiving Day, and Christmas Day.
There must be a cure for this obsession, eh?
By the way, I also fixed the error in the original download. Sources tell me that April occurs only once per year.
J-Walk Chart Tools
Today I posted an old add-in called J-Walk Chart Tools.
Perhaps its most useful feature is the ability to select a range a cells to be used for data labels in a chart series -- an often-requested feature that has never materialized in Excel.
This add-in is not compatible with Excel 2007. However, most of the features are available in PUP v7.
Displaying An Annual Calendar
This morning I saw a post at the Microsoft Excel blog: How to Create a Perpetual Yearly Calendar in Excel. You can download the workbook, but you need to be running Excel 2007.
I created a similar workbook, that's actually much better. Download it here: Yearly Calendar Workbook.
The advantages:
- It works with all versions of Excel.
- It's much simpler, and doesn't use any named formulas.
- It displays the text in the language specified in the Windows Regional Control Panel
- You can easily change the appearance by modifying any or all of four named styles: MonthHeader, DayNames, DateCells, and Background.
- It's laid out better: Each row of months represents a quarter.
- The day dates are generated using array formulas, so the user cannot accidentally delete a formula.
- It doesn't go all the way up to the year 9999. Clicking the inside of the slider increments by 10 years rather than 500 years.
- It doesn't use a background image that make the calendar difficult to read.
- You don't have to validate your copy of Excel before you can download it.
Tip: If you use this workbook with Excel 2007, you should modify the four named styles so they use Office 2007 theme colors and fonts. Then you can change the look instantly by applying a different theme.
IsDate Tip Posted
I posted a new tip about one of VBA's least reliable functions: Understanding the IsDate Function.
I've been having date-related problems with my Enhanced Data Form. And I think finally figured it out. I was relying on IsDate, when I shouldn't have been.
Easter Formula
New tip posted: Calculating Easter.
It also has a chart (created from a pivot table) that shows the date on which Easter occurs for a 300-year period.
MP3 File Lister Posted
I just posted a new file in the Downloads section: MP3 File Lister.
It's a useful app that generates a list of MP3 files, and includes the following information: Path, Filename, Artist, Album, Title, Track, Genre, Duration, and Size.
VBA programmers might find it useful because it contains recursive code to retrieve files in a directory and all of its subdirectories.
I use a customized version of this code to generate a list of my music files, and create an attractive pivot table report.
Formula To Convert Unix Timestamps
New tip posted: Converting Unix Timestamps.
I remember the first time I imported a MySQL table into an Excel worksheet. I spent hours trying to figure out those pesky timestamps.
Color Scales Conditional Formatting
One of the new conditional formatting options introduced in Excel 2007 is called Color Scales. It shades cells based on their value. I just posted a file in the Download section that uses this feature in a way that probably wasn't anticipated by the designers: Animated Color Scales.
A 62x62 range of cells contains formulas that are driven by three other cells. A few simple VBA macros select random colors, rescales the display, and even animates it. It's kind of fun, and you'll be amazed at some of the colorful patterns that come up.
Here are some thumbnails of what you can expect:
The Bible Is Here
A new download: The Bible, In Excel.
Even if you're not religious, you might appreciate this workbook. It has the complete text of the King James Bible, with lots of cool features.
Excel 2007 Upgraders FAQ
I posted five new tips today. Well, they're not really new. In fact, they're not even tips. They are lists of common questions people might have when the upgrade to Excel 2007.
I originally posted these at Daily Dose of Excel. I updated them slightly, and augmented them with information posted by commenters.
They are:
Spreadsheet Page Blog
Welcome to the Spreadsheet Page Blog. This is where you find the latest news on my books, add-ins, and other Excel-related topics. Comments are welcome.