Browse by Tags

Ease of reuse of modular code

The primary reason I write modular code is that it is self-documenting, easy to understand, and easy to maintain. A secondary reason is the ease of reuse. One of the comments to my post Two new range functions: Union and Subtract (www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/) was a request for code to copy a range from one worksheet to [...] Read More...

Published Fri, Aug 08 2011 12:19 PM by Daily Dose of Excel

Filed under: VBA, VBA Basics, Modules

ByVal or ByRef Parameters

Procedure arguments, either for a subroutine or a function, are declared either as ByVal or ByRef. The use of ByVal means that the called procedure does not want to change the value of the argument passed to it whereas the use of ByRef means that the called procedure may change the value. This note looks [...] Read More...

Published Tue, Jun 06 2011 2:27 AM by Daily Dose of Excel

Filed under: VBA Advanced, VBA, VBA Basics

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 02 2011 6:21 PM by Daily Dose of Excel

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

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 02 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 02 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 02 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 02 2011 3:24 PM by Daily Dose of Excel

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

Abbreviating Company Names

Last week I was creating file folder labels with my new Dymo LaserWriter 450. The information on the folder label is serial number, part number, company name, and purchase order. Normally, this works great. However, I ran into one on Friday with a 30 character part name and a 29 character company [...] Read More...

Published Mon, Aug 08 2010 11:12 AM by Daily Dose of Excel

Filed under: VBA Basics

Bushy Trees

A favorite peeve of mine is code with “bushy trees.” I first saw this phrase in Kernighan and Plauger’s Elements of Programming Style. Recently, I saw some code that checked if a RefEdit control referred to a single cell that contained a non negative integer. I cleaned up the formatting some since the original [...] Read More...

Published Sat, Aug 08 2010 1:37 AM by Daily Dose of Excel

Filed under: VBA Basics, CodeCritic

A quick tip - Working with arrays with unknown bounds

Two recent instances required transferring data from one array to another. One was a solution to speed up the use of COUNTIF for each element in a large range testing against another large range. In the process I had to build several building blocks including a replacement for the native Excel TRANSPOSE function, [...] Read More...

Published Tue, Oct 10 2009 12:49 AM by Daily Dose of Excel

Filed under: VBA, VBA Basics, Arrays

Summing the Digits of a Number

Summing the digits of a number is a chore I’ve been doing alot lately. Originally I’d parse the number out over the columns. And since SUM() ignores text, I’d turn the characters into digits by applying an arithmatic identity operation, like this: =- -MID($A10,COLUMN(),1) That’s double minus signs before the MID() function. The reasons [...] Read More...

Published Sat, Oct 10 2009 5:01 PM by Daily Dose of Excel

Filed under: General, VBA Basics

Russian Peasant Multiplication

The Daily WTF posted a challenge to code the Russian Peasant Multiplication. Here's mine: Function RussianPeasant(lFirst As Long, lSecond As Long) As Double Dim lDiv As Long Dim lMult As Long Dim lMod As Long lDiv = lFirst lMult = lSecond Do Until [...] Read More...

Published Wed, Aug 08 2009 5:11 PM by Daily Dose of Excel

Filed under: VBA Basics

Graphics I Grok

As a big-time college basketball junkie (Villanova ‘96 grad school–Kerry Kittles et al), I thought the NY Times had two excellent NCAA basketball tourney graphics. No pie charts! I needed a tech assist from Dick to learn how to do this, so I’m not as timely as I should have been, and that [...] Read More...

Published Tue, Mar 03 2009 9:10 PM by Daily Dose of Excel