November 2010 - Daily Dose of Excel

Invalid Pattern String

I just came across an “Invalid Pattern String” error while use the Like keyword to fill a listbox. I thought Like was more robust than that. Here’s what msdn has to say about it. I changed my code to this: Private Sub LoadSheets(Optional sFilter As String) Dim sh As Worksheet [...] Read More...

Published Tue, Nov 30 2010 6:03 PM by Daily Dose of Excel

Filed under: ActiveX Controls

Decimal Degrees to Degrees-Mins-Secs

GPS tells you where you are in Decimal Degrees (DD), with positive degrees being north latitude or east longitude, and negative degrees being south latitude or west longitude. Most charts (I was a nautical type before I was an Excel type. Think maps if that’s not you) tell you position in degrees-arc minutes-arc [...] Read More...

Published Mon, Nov 29 2010 11:50 AM by Daily Dose of Excel

Filed under: General

The Join Function

The Join function concatenates a bunch of strings in an array into one long string. I rarely use this function, but maybe I should start, as JP suggests. I wondered if it was really that much faster. Declare Function timeGetTime Lib "winmm.dll" () As Long Sub TestJoin() Dim i As Long [...] Read More...

Published Fri, Nov 26 2010 6:30 AM by Daily Dose of Excel

Filed under: Arrays

Insert a screenshot with Office 2010

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://tushar-mehta.com/publish_train/xl_vba_cases/0110_Office_2010_insert_screenshot.shtml Read More...

Published Wed, Nov 24 2010 10:15 PM by Daily Dose of Excel

Filed under: Excel 14

Ranking Countries

Big Think posted a map showing what the world would look like if the countries swapped places so their population and land mass rankings matched. Pretty interesting. Strangely enough, the US itself would not have to swap its population with another country. With 310 million inhabitants, it is the third most populous nation in the [...] Read More...

Published Wed, Nov 24 2010 6:30 AM by Daily Dose of Excel

Filed under: Counting and Summing Functions, VBA, Charting

My Last Post on Filtering Pivottable Source Data

Based on the comments from these three posts, there appears to be exactly zero people interested in this topic, so I’ll make it my last post on the matter. In short, I wrote some code that subverts the default action of double clicking on a Pivot Cell (creating a new sheet with the underlying data) and [...] Read More...

Published Tue, Nov 23 2010 2:31 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

A JoinAll function

Based on a discussion on the mrexcel.com website of Harlan Grove’s AConCat function, I wrote a join (or concatenate) function that works with a scalar data type (a number, string, or boolean) as well as complex data types like a range, an array, a collection, or a dictionary. It also accepts an arbitrary number [...] Read More...

Published Sun, Nov 21 2010 2:25 AM by Daily Dose of Excel

Filed under: User Defined Functions, VBA Code Library, VBA Functions and Keywords

Unicode and VBA’s ChrW() and AscW() functions

Spreadsheets have their CHAR() function, and VBA has its Chr() function. Both return the text character for the specified numerical input, 0 to 255. And spreadsheets have their CODE() function, and VBA has its Asc() function. Both of those return the ASCII code for the leading character of a text string. All [...] Read More...

Published Fri, Nov 19 2010 8:40 AM by Daily Dose of Excel

Filed under: General, VBA, VBA Functions and Keywords

CSV Templates

The Add method of the Workbooks object exhibits some behavior that’s new to me. Normally when you supply the Template argument, you get a copy of the template with a “1″ appended to the name. For instance, Workbooks.Add "Sales.xls" opens a workbook named Sales1. However, this code Workbooks.Add "Sales.csv" Opens the CSV as read-only. That’s not how I expected [...] Read More...

Published Tue, Nov 16 2010 11:37 AM by Daily Dose of Excel

Filed under: VBA, Workbook Object

Show Us Your…

…Spreadsheets! Wiley is having a contest. Submit a photo of yourself with your favorite John Walkenbach (Mr. Spreadsheet) or Michael Alexander (DataPig) book OR a photo of you creating stellar spreadsheets, dynamic dashboards, or something equally excellent in Excel to enter to win cash and other cool prizes. Here are the official rules. Photos will be posted [...] Read More...

Published Mon, Nov 15 2010 10:01 AM by Daily Dose of Excel

Filed under: Books

Loading XL97 Help

In Personal.xls Dick declared a winner: fzz’s loading of the XL97 help files in lieu of later, poorer implementations of Excel Help, and then assigning it to the F1 key. Nobody said how to do it. Here’s my approach. It uses the Shell command to load winhlp32.exe, and passes to it [...] Read More...

Published Fri, Nov 12 2010 1:31 PM by Daily Dose of Excel

Filed under: General

FORECAST Doesn’t Like Dates

I’ve been working with the FORECAST worksheet function a little lately and it’s been a struggle. First a quick look at the function FORECAST(x,known_y's,known_x's) The known x’s and known y’s form a trendline based on linear regression, and the function returns the y coordinate on that line for the provided x. The worksheet function seems to handle [...] Read More...

Published Thu, Nov 11 2010 6:30 AM by Daily Dose of Excel

Filed under: VBA, Formulas, Math and Stats Functions

Filter Pivot Table Source Data Calling Procedures

Filter Pivot Table Source Data Filter Pivot Table Source Data II I call my filtering code from two separate places. First, if a cell in a pivot table is double clicked, the code is called. My code is in an add-in, not in the workbook that contains the pivot table, so I have to set [...] Read More...

Published Wed, Nov 10 2010 12:02 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Filter Pivot Table Source Data II

In this post I show the main procedure that filters a pivot table’s source range. In this post, I show how to determine which filters are applied to a particular cell in a pivot table. First, I count the number of filters. Public Function FilterCount(Target As Range) As Long Dim [...] Read More...

Published Tue, Nov 09 2010 2:47 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Filter Pivot Table Source Data

I started by showing you my awesome code to parse the SourceData of a pivot table. Code that has been rendered obsolete by John’s comment. I was going to continue with the supporting utilities used by the main procedure, but it may be hard to put things into context, so I’ll show the [...] Read More...

Published Mon, Nov 08 2010 1:44 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

November 2010 - Daily Dose of Excel

Syndication

Recent Posts

Tags

News

Archives

Invalid Pattern String

Decimal Degrees to Degrees-Mins-Secs

The Join Function

Insert a screenshot with Office 2010

Ranking Countries

My Last Post on Filtering Pivottable Source Data

A JoinAll function

Unicode and VBA’s ChrW() and AscW() functions

CSV Templates

Show Us Your…

Loading XL97 Help

FORECAST Doesn’t Like Dates

Filter Pivot Table Source Data Calling Procedures

Filter Pivot Table Source Data II

Filter Pivot Table Source Data