Daily Dose of Excel

Hi everyone!

As you may have seen on some blogs, Microsoft now enables you to include any Excel file -or parts thereof- in your web pages and blog posts.

For example (yes you can make changes to the cells, they are not retained):

I have written an article that explains how this is done.

Even more: I have also created a demo where you can enter information in a web form (a textbox), which in turn updates information in the embedded Excel web app file.

Enjoy the read: Embedding Excel files on your website

Regards,

Jan Karel Pieterse
www.jkp-ads.com

If you’re in or around London in late January, don’t miss the UK Excel User Conference. It’s Wednesday, January 25th at Skills Matter

Here’s the agenda.

Check out this gem from the agenda:

15:30 – 16:30
VBA to C : Pratfalls and Perils
- Stories based on a c++ newby’s efforts to convert 10K lines of VBA UDFs to
C++ XLLs.

If that doesn’t sound interesting to you, you’re just not trying.

Pay the fee here and somebody live blog the event. I can’t make it because I’ll be in Cancun that week but I’ll still be thinking about VBA.

When you carol along with The Twelve Days of Christmas do you think of spreadsheets? I do, and that certainly says all you need to know about my musical ability. The folks at PNC Wealth Management do also. Annually they issue their Christmas Price Index, with the 2011 version here. Spreading the data out, The Twelve Days of Christmas looks like this:

PNC provides the Bundled Cost. We have to work backwards to find the Unit Cost. In other words, eight total maids-a-milking cost $58. The data sources are at the Wiki link. The poor dairy lasses are working minimum wage.

For DDoE, PNC’s table is a good way to review the concept of named ranges. Define Christmas as =Sheet1!$B$2:$M$14, _12th_Day as =Sheet1!$M$2:$M$14, Bundled_Cost as =Sheet1!$P$2:$P$14, Maids as =Sheet1!$B$6:$P$6, Quantity as =Sheet1!$N$2:$N$14, and Unit_Cost as =Sheet1!$O$2:$O$14.

The total number of gifts can be =SUM(Christmas) or =SUM(Quantity), being 376 (more on this later). The cost of the 12th Day of Christmas can be =SUM(Bundled_Cost) or = SUMPRODUCT(_12th_Day,Unit_Cost), being $24,263.18. The total expenditure of your True Love is =SUMPRODUCT(Quantity,Unit_Cost), or $101,119.84. A very generous person, your True Love.

We can use the Intersection Operator, a space, as =Maids Quantity, to find that we need 40 work hours from them. The value of named ranges is in the legibility it brings to your spreadsheet. You can expand this until you cover all days and all gifts. The work is in the defining of the names, the benefit is in the presentation.

PNC says the total gifts number 364. Surely a partridge and a pear tree count as two gifts in your household, no? Happy Holidays!

…mrt
©¿©¬

Charles Williams had an interesting post¹ the other day about the Text, Value, and Value2 properties of the Range object. In it, he wrote:

So .Value2 really should be the default, and is definitely the one to use 99% of the time.

I disagree. I never use Value2.

The Value2 property returns a Double data type (64-bit floating point). The Value property returns the same thing except when the cell is formatted as Date or Currency. When it’s currency, Value returns a Currency data type (64-bit scaled integer), and when it’s a date, Value returns a Date date type (another 64-bit floating point). Who cares (besides Charles and me) and what does it mean?

Let’s imagine that we’re writing our own spreadsheet program to get a better understanding of what Excel is doing. I don’t actually know what Excel does under the hood, so this is all conjecture. In our spreadsheet application that we’re writing from scratch, we’ll store all numbers in memory as IEEE double precision floating point number, or Doubles. There is a case when the user may want more calculation accuracy than floating point allows and is willing to sacrifice a little precision (I’m looking at you fellow accountants). To offer that feature, we’ll allow the user to identify certain numbers as scaled integers with 15 digits to the left of the decimal and four digits to the right. Let’s call that data type Currency. We’re still going to store the value as a Double, but we’ll do the conversion prior to any calculations to ensure the accuracy.

As spreadsheet writers, we have a decision to make. I said we’d allow the user to identify certain numbers as Currency, but how exactly do they identify it. We could add a property called DataType to the Range class. OK, but how does the user set that property? Most Range properties are set in the Format Cells dialog box. We have a few tabs on that dialog already, so let’s add another one called Data Type. The user can set the data type and we’ll do the floating point to scaled integer conversion when the DataType property is set to Currency. That’s when the usability people come in.

The usability people begin by hurling epithets at us regarding our lack of sex lives and penchant for role playing games. They say that normal people (i.e. non-programmers) don’t have any idea what a “data type” is and if we make them learn, our sales will go down by 14.1% (a totally made up number because marketing people can’t do math). Surely, they go on, there is a better way to identify Currency values. Then they get out the corporate programming guidelines and remind us that no dialog box can have more than six tabs. We already have six tabs on the Format Cells dialog, so we can’t add another (Tools – Options hasn’t been invented yet).

Time to compromise. One of our bright, young interns suggests that we make Currency a format. As programmers, we are incensed. We already have an Accounting format and a Currency format will be confusing. And besides, Currency is a data type, not a format. Due to our lack of persuasiveness or the caffeine withdrawals, we agree to add it is a format. Because of the Currency debacle we go from two levels to three levels for data display. The bottom level is the raw Double that’s stored in memory, just like before. The top level is the text representation of the number with all the commas and periods and such, just like before. We need to add a pre-processor level. At this level, we’ll check to see if the Range object has it’s NumberFormat property set to Currency and, if so, we’ll convert from floating point to scaled integer. Once converted, we’ll send to the formatting layer to add the text goodies.

The deed is done and we all feel a little dirty. We code the changes and send them over to the VBA folks. The guy building the object model already has a Value property and a Text property. Now that we’ve added another layer, he doesn’t know which value to return for the Value property. There’s really not much debate – it has to be the value that comes from the pre-processor. If people “format” a value as Currency, they’re going to expect a scaled integer from the Value property even if they don’t know what the hell a scaled integer is. Object model guy then asks if he should expose the raw value. Of course. We’re all about exposing ourselves. We call in the dullest intern on staff to come up with the name and, true to form, he produces Value2. Positively inspired.

And scene.

You may disagree with some of the decision made in the above dramatic reenactment, but you have to realize they’re all connected. I personally disagree with calling Currency a format. But whether we call it format or a data type, doesn’t change the fact that we have to convert it. You can disagree with the decision to store all numbers as Doubles, and just store Currency as a scaled integer in memory. That doesn’t really change how the user identifies Currency values and you can’t simply store everything as currency because then everything would be slow.

Back to my assertion that Value is the appropriate default (or, put another way, that Value points to the right layer). I contend that if you “format” something as Currency or Date, that you do so knowingly and for a specific purpose. One of the knocks on Value is that you lose precision for Currency because Currency only goes four digits out to the right. I say you don’t lose precision. The precision that’s there is a myth. It’s only there because we had to convert the number to a float. When we chose Currency, we consciously forfeited all rights to precision beyond the 10,000th place (or should have). The fact that we can convert that number to a Double and see what looks like precision, doesn’t make it there.

For the Date type, I have a different argument. It’s not that there’s any problem with the data manipulation when converting from Double to Date, it’s just that it takes longer. If I’m reading in a date and spitting it back out, I agree that Value2 is probably the better choice. However, if that’s all I’m really doing I might want to address what value I’m adding. In most cases, I’m manipulating the date in such a way that I care that it’s a date. What I don’t want to do is read in a double using Value2 (avoiding the conversion overhead), then have to convert that number to a date myself to manipulate it, then convert it back to a double. In almost all cases, I want a Date or Currency typed number when I “format” it that way.

I suspect that most people who prefer Value2, in fact, disagree with the decision to confuse formatting and data types in the user interface. And if so, then we agree on that. Let me see if I can reword part of Charles’ statement so that we both agree with it: When performance matters, Value2 should be used 100% of the time there are no dates or currency, and Value should be used 100% of the time there are. I wonder if that will fly.

Footnotes
¹In fact, all of Charles’ posts are interesting, so if you’re not subscribed to his RSS feed² yet, get on it.
²If you’re still not reading blogs via RSS, be sure to say hello to my mom at the next Rotary meeting.

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