February 2011 - Daily Dose of Excel

Visualize Data with Shapes on a Map

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/13-03%20Shapes%20on%20a%20Map.shtml Tushar Mehta Read More...

Published Mon, Feb 28 2011 6:21 PM by Daily Dose of Excel

Filed under: VBA, VBA Basics, Charting, Data Visualization

Animal Songs

J-Walk compiled a list of songs with animals in the title. That data screams for analysis, doesn’t it? The vertebrates (phylum Chordata) kicked ass and mammals (class Mammalia) dominated. But Birds (class Aves) were not slouch either, although many of those songs only references “bird”. The most popular species is dog (Canis lupus). Oddly [...] Read More...

Published Mon, Feb 28 2011 6:30 AM by Daily Dose of Excel

Filed under: Pivot Tables

NACHA Files Bonus

Just when you thought the class module fun was over, it’s not. As long as I have all this class infrastructure, I can leverage that into making some reports. As you are no doubt aware, employees get pissed off when you make errors on their paychecks. Therefore, a review report prior to [...] Read More...

Published Fri, Feb 25 2011 6:30 AM by Daily Dose of Excel

Filed under: Classes

Reasonable Contract Terms

Several years ago, a potential client contacted me to work on a charting add-in. In the contract the client wanted me to sign was a clause that I would not create any chart that I created for this company and that I would never use any code that I used in creating its add-in. Given [...] Read More...

Published Thu, Feb 24 2011 6:53 PM by Daily Dose of Excel

Filed under: General, Miscellany

NACHA Files Part 3

In Part 1 we created some classes. In Part 2 we created some more classes, linked them, and filled them. Now we’re ready to actually produce some results. In a standard module, I write my code to generate the XML file. Public Sub GenerateACH() Set gshData = wshChecks [...] Read More...

Published Thu, Feb 24 2011 8:37 AM by Daily Dose of Excel

Filed under: Classes

NACHA Files Part 2

In Part 1, I created the CEmployee and CPayrollItem classes and their parents. For CPayrollItem, I used ItemName as the property for the first column because I didn’t want to name it the same as the class name, PayrollItem. Next, I need to create the CCheck and CCheckItem classes. For CCheckItem, I only need [...] Read More...

Published Wed, Feb 23 2011 6:30 AM by Daily Dose of Excel

Filed under: Classes

NACHA Files Part 1

There is a product that some banks use, like my bank, called Premier ACH. It’s hosted on the bank’s website. It allows you to enter the data for an ACH transactions (Automated Clearing House, think direct deposit) and creates a properly formatted NACHA file for you. You can submit a NACHA file to [...] Read More...

Published Tue, Feb 22 2011 6:30 AM by Daily Dose of Excel

Filed under: Classes

Recording a Sort Macro in 2003 vs 2007

Excel 2007 records the sort operation differently than Excel 2003. In 2007, the range is specified in the SetRange method while in 2003 the Selection object is used. Take this simple checkbook workbook. If you record a macro to sort on check number in 2003, you get something like this Sub Macro3() ' ' Macro3 Macro ' Macro [...] Read More...

Published Mon, Feb 21 2011 6:30 AM by Daily Dose of Excel

Filed under: Sorting

Name Manager Updated

Hi All, I’ve just updated Charles Williams and my Name Manager utility. New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name. I’m now off to [...] Read More...

Published Tue, Feb 15 2011 12:28 PM by Daily Dose of Excel

Filed under: Excel12, MVP, Downloads, Names, Excel 14

VBE Find - 2

Dick Kusleika shared VBE Find, a solution that intentionally had no choices and a straightforward UI. [In his post he also pointed out a few limitations with it.] Unfortunately for him, I liked it so much that I enhanced it to the point that he might now be uncomfortable with the bells and [...] Read More...

Published Tue, Feb 15 2011 11:57 AM by Daily Dose of Excel

Filed under: Visual Basic Editor, VBA Basics, UI Design

Adding Functionality 3 — Where is the cursor?

In addition to being able to sort the results of Dick Kusleika’s VBE Find I wanted the code to automagically fill in the search field. If there is a selection, the code should use that selection as long as it doesn’t span more than one line. If there is no selection, then the [...] Read More...

Published Mon, Feb 14 2011 12:59 AM by Daily Dose of Excel

Filed under: User Interface, VBA Basics, UI Design

Adding Functionality - 2b

In Adding Functionality - 2a I laid out the UI changes to support sorting of the results of Dick Kusleika’s VBE Find. In this post, I develop the actual sort routine. To recap, the task is to sort a 2D matrix on any number of columns. The sort columns are listed in a 1D array, with [...] Read More...

Published Sat, Feb 12 2011 8:26 PM by Daily Dose of Excel

Filed under: Visual Basic Editor, VBA Basics, UI Design

Adding Functionality - 2a

Dick Kusleika’s VBE Find is useful enough that it justifies the resources need to enhance it. In addition to adding Regular Expression searching (Adding functionality - I) a sort capability made sense since since the results include code from multiple code modules and procedures within them. The original code showed 4 elements for each match: [...] Read More...

Published Sat, Feb 12 2011 3:24 PM by Daily Dose of Excel

Filed under: General, VBA, Visual Basic Editor, VBA Basics, Userforms and Controls, UI Design

Adding functionality - I

Inspired by Dick Kusleika’s “Inflexible Find” I decided to add some flexibility to it. In a comment to his original post (http://www.dailydoseofexcel.com/archives/2011/02/10/vbe-find/#comment-58902), I indicated that the userform was now shown modeless. This meant that one could interactively search for a token and examine the associated code. Another improvement I wanted to make was a [...] Read More...

Published Fri, Feb 11 2011 2:45 PM by Daily Dose of Excel

Filed under: General

Copying HTML Tables over Merged Cells

I ran into some strangeness while copying tables from the web to Excel. To demonstrate, I’ll use Debra’s sample data table. I run this code Sub GetTable() Dim ieApp As Object Dim ieDoc As Object Dim Clip As DataObject Set ieApp = CreateObject("InternetExplorer.Application") [...] Read More...

Published Thu, Feb 10 2011 2:15 PM by Daily Dose of Excel

Filed under: Automation

February 2011 - Daily Dose of Excel

Syndication

Recent Posts

Tags

News

Archives

Visualize Data with Shapes on a Map

Animal Songs

NACHA Files Bonus

Reasonable Contract Terms

NACHA Files Part 3

NACHA Files Part 2

NACHA Files Part 1

Recording a Sort Macro in 2003 vs 2007

Name Manager Updated

VBE Find - 2

Adding Functionality 3 — Where is the cursor?

Adding Functionality - 2b

Adding Functionality - 2a

Adding functionality - I

Copying HTML Tables over Merged Cells