Daily Dose of Excel

Back in my newsgroup days, about once a month someone would ask how to have a formula that accumulated entries from another cell. For example, put a formula in C3 that refers to A3. Every time a value is entered in A3, have the value in C3 change by that amount. Impossible right?

The standard answer is that you can’t and that you should use a Worksheet_Change event to monitor A3. When it changes, change the value in C3. Then I read Charles Williams’ comments about the Text property

When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference).

I had no idea that was true. Now I can write a UDF like this:

In C3, I put =updown(A3). Whenever I change A3, C3 changes by that amount. I’m sure there are all kinds of problems with this – dates and errors to name two – but I thought it was interesting nonetheless. Apologies if the entire Excel universe knows this already, but it’s new to me.

Excel’s MATCH() function will return the relative location of the first occurrence that satisfies the match criterion (the lookup_value) within the specified array (the lookup_array).  Most often, this is what you want.  For instance, if you have duplicate entries, returning the first match is no different than returning the last match.  However, if you have unique entries with the duplicated criterion, how do you return all the unique values?

For example, you have a list of the 122 major professional sports teams (32 NFL, and 30 MLB, NBA, and NHL) sorted alphabetically in Column D.  In Column E you have code that returns the length of the team name, i.e. =LEN(D1).  Your data might look like this:

For no good reason we want to create a descending sort on name length. You can do it from the Sort selection, but we also want to keep the alpha-sort. In F1 you put =LARGE($E$1:$E$122,ROW()) and fill down. The ROW() function will cause the return of the first largest, then the second largest etc. Then you want to know how far down Column E those descending numbers fall, so that you can Index the name list that amount. In G1 put the code =MATCH(F1,$E$1:$E$122,0) and fill down. You’ll see from the data that the longest name is 29 letters, and it’s in ROW(52).

You can begin to see the problem: There are three teams with a 22-letter name, three with a 21-letter name, and at least 3 (in fact 13) with a 20-letter name. If in H1 we put =INDEX(D:D,G1) and filldown, we get:

This is exactly what we wanted to avoid. So, using Col(H) now as a helper column, in H1 put =G1. In H2 put

• =IF(G2<>G1,G2,H1+MATCH(F2,INDIRECT(“E”&(H1+1)&”:E122″),0))

and fill down. If the value did not repeat, just copy it over; and if it didn’t, we’re making a “sliding range” that starts one row after the start of a repeated pair, ends at the end of the data, and “slides” down Column E. The sliding range is made by the INDIRECT() function. It builds the range one row down (H1+1) from the first of a repeating match. We add H1 to that result, and we have our index down Column D. This may be better seen in H3, adjacent to G3, where the first repeat starts.

• =IF(G3<>G2,G3,H2+MATCH(F3,INDIRECT(“E”&(H2+1)&”:E122″),0))

The IF() is false. We are looking for the relative location of the lookup_value (22) in a new lookup_range (E31:E122). It’s 33 rows in. We add the original 30 to that, and the index is now 63. A similar thing happens in H4. H5 is G5 copied over. In Column I, I1 =INDEX(D:D,H1), filled down. The resulting table:

The names are now sorted by length, by alphabet. INDIRECT() is volatile, so there is a penalty for this. For extra credit, and no peeking, what’s the shortest name?

…mrt
©¿©¬

As you may have figured out, I’ve had some trouble with the site for a while. We lost the database server and had corrupted backups. About 98% of the posts were recovered and I’m not yet sure which are still missing. I know that at least some of the links to downloads are broken and it may be all of them. If you had a username and password, those will be changed. I’ll keep you posted on new login credentials.

Sorry for the inconvenience. I should be back up and posting soon.

Recently, I wanted to create a list of all the folders in my MS Outlook PST file together with the size of each folder. Outlook provides that information through the user interface. Unfortunately, it shows the result in a modal dialog with no way to save the information elsewhere. So, I decided to check if I could find some ready-to-use (or nearly ready-to-use) code that did the needful. A search of the web led to several ideas and suggestions but no code to do the needful. So, I decided to put together a VBA module that would save the information in an Excel worksheet.

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/1201%20Outlook%20folder%20info.shtml

Tushar Mehta

$150 per hour. $120 per hour if the project is big enough that you won’t have to look for work for a while. $100 per hour if you’re really hungry.

I’m sick of reading blog posts about how much to charge. They don’t tell me anything I don’t already know. And they never tell you how much to charge. You’re welcome.

Someone reading this post is charging half that much. You are doubly welcome. You won’t make any more money but you’ll make the same doing half the work. Use that other time to improve your skills.

Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads to a “loss of state,” which includes the loss of all global variables.

In http://www.dailydoseofexcel.com/archives/2011/08/14/save-a-global-variable-in-an-excel-workbook/ I describe a way, first proposed by Rory Archibald, to save the handle to an object in an Excel cell (or named constant). That is an easy to implement and relatively self-contained approach.

At the same time, I wanted a solution that worked not only with Excel but also other applications such as PowerPoint and Word. I also wanted a solution that worked with variables other than objects declared outside of the VBA project. Consequently, I opted to use a separate add-in that did nothing more than save global variables in a VBA collection. While this requires coordination between two add-ins, the benefits include the ability to extend the solution to platforms other than Excel, the ability to save the state of any global, and also a solution that does not require a Windows API. This note documents such an approach.

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/1018%20Protect%20a%20global%20variable%20in%20another%20VBProject.shtml

Tushar Mehta

I’ve been following Dick’s VBHelpers Build series (1, 2, 3) and his last post reminded me that, from time to time, I need to sort a collection of items in-memory.
I don’t have to sort all that often, so my approach has changed over time. I’ve kind of settled on the following.

Let’s say I have a People collection that contains Person items.
In my Person class I’ve written a method (Function) called CompareTo. It works a lot like VBA’s StrComp, returning -1, 0 or +1 depending on whether Item 1 is less than or greater than Item 2.
I’d use it against two person items:

In my People collection class, I’ve created a method called Sort that returns itself in sorted order.
It’s an Insertion Sort that I converted from Wikipedia’s article into VBA. Notice how it uses the CompareTo method for deciding on item placement.

Now I get to use the above in my main code routines:

The code above is available for download. It’s an extension of the code I posted a year ago on the same topic (links 1, 2). It also includes the Enum enhancements suggested by Andy Pope way back then.

You can download SortClass.zip

In this iteration, I

• Create a generator for a FillFromRange method of the parent class
• Get rid of those unisightly blank lines when converting Public variables to Property statements
• Change FindBy to ChildBy (thanks Steve J)

If I have a range of records that I want to put in a class, which happens a lot, I wanted to create a quick way to generate the code to fill the class. It’s not particularly tricky code to write, but I get sick of typing all the Offset statements. Most of the code is string concatenation and isn’t very interesting. The meat of the procedure is identifying which properties to create lines for.

In this code fragment, I loop through all the code lines looking for lines that are LIKE this constant:

. I started out looking for Property Let statements, but ran into trouble. When I create a class that I know will come from a Range, I generally type in the Public variables in the order they appear in the Range. I can control that order, but I have less control over the order of the Property Let statements and frankly I don’t want to control it. I also can’t be sure there wasn’t a Property Let written later that isn’t a simple getter/setter.

So I look for the private variables, then look for the corresponding Property Let statement. If I find it, I grab the ProcOfLine, which gives me the string I would call using the class in a procedure; msModel becomes just Model. I ignore any property that ends in ID and the Parent property because I set those in the Add method. However, it should be noted that while I want this code to compile right out of the gate, I don’t have any problems with editing it when necessary. If I want to assign the ID property explicitly, I can still do it with an edit and most of the rest of the code is still generated for me.

The sCode variable is extended to include the new line, which is in a With block accounting for it starting with the period. The convention is that the private variables in the child class are in the same order as the data appears in the range. Again, if that’s not right it’s still easier to edit than to write from scratch.

If I have a CCar class with Make, Model, and Year properties, it generates a method like this:

I added a couple of functions to find 1) the first Property statement and 2) the first blank line after the first Property statement. If I add properties later, it would put the newly created Property statements one line away (or at the bottom). I didn’t like it, so I keep them all together and get rid of the blank lines.

Finally, Steve commented that I shouldn’t be using verbs in Property Get procedures, and he’s absolutely right. I changed the FindBy statement to ChildBy. So instead of FindByMakeAndModel As CCar, it generates CarByMakeAndModel As CCar.

Next up on my todo list for this addin:

• Refactor the code so I can do all this stuff at once as well as one at a time
• Automatically create a class from an Access table
• Generate a ChildrenBy method so I can return a filtered parent class. clsCars.CarsByMake(“Toyota”) would return a CCars instance with only Toyotas in it.
• Generate inter-class relationships. If I have CInvoices and CInvoiceLines, I want to generate the relationship between them automagically.

You can download VBHelpersBuild3.zip

Over the years, I have followed the “best practice” of always using a property get and let/set combination rather than just declaring a public variable. But, over the last few months I’ve started questioning this dictum.

Now, before people start jumping up and down, I am aware of the many very, very good reasons why one should use properties rather than public variables. This comment is *limited* to the case where the property provides unrestricted access to the underlying value.

For those who want a clarification of what I am writing about, in a class module, one could have either

or

As far as a consumer of this class goes, the code would be identical irrespective of which of the above methods the developer of the class used.

One could argue that at some point, the developer may want to enforce a check on R (e.g., enforce that R > 0). Or one might want to provide a property that is read-only or write-once-read-many or one of many other scenarios where a property Get / Let would be required. But, until that happens, what’s the difference whether the class developer uses an unrestricted property or a public variable?

How was that video? Pretty super-awesome, huh?

You can download VBHelpers.zip

It’s rough, to say the least, but if you want to mess around with it, have at it. Here’s some more information on what was happening in that video:

0:00 First I insert a class module. Inserting modules is one of those activities that bugs me. I do it enough that the extra few steps get on my nerves. Normally, I Alt+I+M/C/U. Then I F4 to open the properties box and change the name. The utility that I use now does a few things. It prompts for a module name. Based on the first letter of the module name, it creates the appropriate module.

M = Standard Module and adds a private constant to the module called msMODULE with the module’s name. This is for the error handling stuff that I copied out of Professional Excel Development

C = Class Module and adds the line Public ModNameID As Long The CreateParent code relies on the presence of this property.

U = Userform and adds nothing.

0:15 I add some more public variables to the class

0:34 The Convert Public to Property finds all the public variables in the class and converts them to private variables and Property Get and Let/Set statements. This is hardcoded to my personal preferences. Namely, all module level variables start with “m” and another prefix indicating the data type. If you don’t like those preferences, you won’t like this add-in.

0:40 Create Parent Class make a class module whose name is the plural of whatever class module is active. If you’re in CPerson, it create CPeople. CCar spawns CCars. And so on. It creates a text file in your My Documents folder and puts all the ATTRIBUTE goodness so you can use For Each and you can avoid using Item. It uses a collection to store the child instances of the class. People seem to prefer to dictionaries, but I think the extra reference dependency makes it not worth it. It generates an Add method, a property that returns an Item (named after the child class) and a Count property. Then it imports that text file into your project. Already have a class with that name? You’ll probably get an error and the error handling is pretty weak right now.

0:53 Create FindBy There are a couple of things that I find myself typing over and over. One of those is a FindBy property in the parent class. This utility sets it up for you. It prompts you for a space delimited list of colon delimited strings (got that?). In the video, I want a property that returns a CEmployee instance given the EmployeeName property. I type EmployeeName:String to create the code. I could also have created a FindBy property for two or more properties. If I had typed EmployeeName:String HireDate:Date, it would have generated a FindBy that looks like this:

And that’s it for class creation. Parent, child, and FindBy in about one minute. The rest of the video is writing a procedure to show that it compiles and works.

The other code that I’m constantly writing but don’t want to is FillFromRange code. If I have a list of employees in a spreadsheet, I want to generate the code that creates all the CEmployee instances and adds them to the parent class. Coming soon I hope. Enjoy and let me know how it goes.