# June 2010 Archives

| 1 Comment | 0 TrackBacks
It seems like every day lately I've been receiving more and more requests for automatic email delivery of my blog posts. I've finally got around to adding that!

I'm rolling all blog entries into my new Excel Hero Newsletter along with some extra tips for subscribers. People tell me every day now, that they come here because of the fresh spin on Excel and the creative ideas I share. If this is you, I promise I'll make the newsletter worth your time.

Here is the form to use to subscribe, but you'll also find it in the sidebar of every blog entry:

## Excel Optical Illusions Week #19

| 1 Comment | 0 TrackBacks
This week I have two Excel optical illusions to share. Both were submitted by Excel Hero readers and both are tributes to M. C. Escher, the brilliant Dutch graphic artist.

The first is called Escher Dice and is the third submission from Hui that I've published.

This is not a chart, but rather some very clever cell formatting.

The second illusion was submitted by Gerald Higgins, and is called Ascending and Descending.

This is definitely a chart and comes with the ability to alter the perspective.

Both of these impossible reality illusions really twist my eyes. Excellent work, lads!

Here are the files:

## Which Function To Use - Part 1

Excel sports a large number of worksheet functions that can be used to slice and dice data. One of the qualities that makes Excel so fascinating is the endless variety that these functions can be combined into formulas producing powerful, surprising, even elegant solutions. There is much to learn here and the user is easily bewildered, wondering which function or combination to work with to produce the desired results.

Just knowing where to start in a given situation requires experience. There's a science to it, but truly inspired solutions are as much art as science. Think of this series of articles as a treasure map, as I will attempt to lead you to the most productive paths.

Before we delve in, I need to backup. We must understand what a range is and how it is specified. A range is a group of one or more cells. The range is NOT the values in those cells. This distinction is important. Some functions (and hence formulas) return values. Some return ranges. Still others can return either in varying circumstances. The value is just one property of a cell. Other properties include color, type of border, font, etc. Typically just the value property is directly accessed from a formula, but many of these other properties can be manipulated via formulas when using Conditional Formatting. All range properties can be accessed when using VBA.

Ranges can be specified by either R1C1 notation or A1 notation. R1C1 is no longer common and requires an option setting. This article will discuss A1 notation exclusively.

Excel recognizes three reference operators. These three operators combine input ranges and produce an output range.

The first is the Range Operator, which produces a minimum rectangle around the inputs. The Range Operator is the COLON:

`A1:B4`

You've seen this in action a million times, I'm sure, but you may have never realized what it means. The colon specifies the range defined by the minimum rectangle circumscribing the input ranges. The input ranges can be specified by any method that describes a range, including defined names, functions, and formulas:

`A1:K2:N16:W15A1:lstLastInvoiceINDIRECT("B1"):OFFSET(Z1,,,100,100)`

In each case the Range Operator returns the range that circumscribes the input ranges with the tightest rectangle. At first glance, the first example seems redundant, and in fact, it can be simplified:

A1:W16

but by substituting formulas for the input ranges, novel ways of controlling other formulas begin to present themselves.

There are two other reference operators and you may not even know about them. These are the Union and Intersect operators. The Union Operator is the COMMA. The Intersect Operator is the SPACE character (space bar).

While the Range Operator always returns a rectangular range, these two other operators can return ranges of any shape, contiguous or not, as long as all of the input ranges are on the same worksheet:

`(A1,B2,C3,D4,E5,F6)(A3:E3,C1:C5)(A3:E3 C1:C5)(A1,A10,J10,J1)(CurRow CurCol)`

The first of these five examples returns a range in the shape of a diagonal. The second returns a range in the shape of a plus sign. The third returns a range composed solely of C3. The fourth returns a range of four disconnected cells in the shape of the vertices of a square. The last returns the range where the named formulas intersect. This last example is probably the most overlooked lookup method in Excel and can be used to conveniently return information from a list or a table of data. Again, the caveat is that all of the input ranges must be on one worksheet.

Another note is in order for the Union and Intersect Range Operators. In math, a union of two or more sets will only include an element once, but the Excel Union Range Operator does not work this way (pity):

`=SUM( (A2:B2,B2:C2) )`

The above formula uses the Union Range Operator. It should be the equivalent of:

`=SUM(A2:C2)`

But it is not. Whatever is in B2, gets summed twice in this example. Also, notice the parentheses around the compound range. This is good practice since functions that require multiple input parameters use commas to separate those inputs. The parentheses ensure that Excel is not confused by ambiguous input.

So the Union Range Operator joins input ranges but is not a true Set Union Operator. On the other hand, the Intersect Range Operator is a true Set Intersect Operator, returning only elements (cells) in common.

There's at least one more twist to range referencing that deserves mention. Ranges can also be 3-D:

`Sheet1:Sheet12!A14:Z14`

Notice that the Range Operator is used twice in this 3-D range example. This returns a range composed of A14:Z14 for twelve worksheets. This could be useful for adding monthly sheets, for example. The other two reference operators do not work on 3-D ranges. Here is an article from Microsoft explaining 3-D ranges in greater detail and specifying which functions work with them. There's actually more functions than listed there, but that is a topic for another day.

Now that we know what a range is, we need to realize that the roster of functions that we can use to slice and dice our data, use these ranges as inputs. This is most commonly done with rectangular ranges, but the others can produce interesting results.

There is a lot to go over here and so I am going to break it up into a series of articles. To begin with, I thought I would share a video presentation I made a couple of years ago on the VLOOKUP function, long before I began this blog. I had the idea for the blog at the time, but really did not know the direction I would take with it. Here is the VLOOKUP video:

It's a long video; 27 minutes on a simple function! Probably too long. But I'd like to hear your feedback. I've always wanted to host a masters class in Excel, and after seeing the popularity of Chandoo's foundational Excel School, I started thinking of this once again. The production is not as polished as I would like, but hey it was a couple of years ago when I was just learning how to make videos.

The idea behind the masters series is to explore each function in detail and then see how they can be combined into elegant formulas. Of course it would have to have advanced charting, excelhero style. So what do you think? Is this something I should invest my time in?

## Excel School - Last Day to Sign-Up!

This weekend I had the honor of reviewing Chandoo's Excel School.

I can tell you categorically that he has done a fine job. The online school is first rate, as are the downloadable workbooks and videos. It is obvious from the moment you first log on that Chandoo has put a lot of effort into designing the curriculum and developing the lessons, with the business user in mind.

The motto of Chandoo's blog is Become Awesome in Excel. Excel School will definitely help get you there. In fact, if you are interested in being an Excel Hero, I would go so far as to say that the information in Chandoo's Excel School is a prerequisite.

Every lesson can be viewed online or downloaded, and the class includes over 1,200 minutes of video instruction hosted by Chandoo himself who not only is the creative force behind the famous Chandoo.org Excel blog, but he is also a Microsoft Excel MVP.

Working your way through this pertinent material will leave you with a solid, well-rounded Excel foundation. Just look at the lesson plan:

And in addition, Chandoo is adding three more lessons to the school:

1. Using Excel's Text formulas
2. Introduction to Excel 2010's new features
3. Integration of Excel & Access - a lesson by Mike Alexander from Data Pig Technologies

Chandoo's Excel School comes with over 40 crystal clear example workbooks and access to the Online Classroom which is an extremely valuable resource where class-members share ideas, ask questions, and discuss lessons.

There are already over 100 students enrolled, so the debate will be stimulating.

Classes begin as soon as you sign-up. Classes will end on July 28, 2010 and the school closes on August 20, 2010. However, all of the downloadable content will continue to work for you forever.

I receive inquiries all the time regarding where someone should go to gain a solid foundation in Excel. Obviously my blog and others are good resources, John Walkenbach's books are first rate. But for a hands-on, immersive experience, Chandoo's Excel School is unparalleled.

The only catch is that today is the last day to enroll. Chandoo has graciously extended the registration deadline until 12 Noon tomorrow (June 22, 2010) Pacific Standard Time (GMT-08:00). I wish that I had had the time to review the course before today. The Excel School is a tremendous value and an outstanding opportunity that will pay you productivity dividends for the rest of your life!

## Excel Optical Illusions #18

This weeks' Excel Optical Illusion is called, Enigma.

When staring at the sun in the center, you should quickly see some strange and scintillating activity in the purple circles. If you stare long enough you may even see a supernova!

The Enigma optical illusion is subtle, the supernova, not so much. Ok. I added the animation with VBA. I was staring at the Enigma, and I could not resist.

The chart is really two Radar charts stacked on top of each other. The bottom one graphs the the black and white rays. The top one is responsible for the circles. Pretty basic stuff, but the effect is quite good in my opinion.

This one requires Excel 2007 or greater. Sorry, but the earlier versions weren't up to it. It's funny. The workbook is only 26 KB, while the animated GIF above is over 6 MB! There's some data compression for you. And, the animation in the workbook is quite a bit smoother than the GIF.

Here's the workbook.

--- UPDATE ---

I just looked at this blog entry with Internet Explorer 8. Wow. The animated GIF sucks. In Chrome and Firefox, it is not half bad. Just download the Excel 2007 file. The animation is way better than all of the browser rendered GIF files.

Here's a list of other animated charts on Excel Hero:

- Excel, A Presentation Platform (Number Spiral)
- Lilac Chaser (Optical Illusion)
- Stereokinetic (Optical Illusion)
- Illusory Contours (Optical Illusion)
- Breathing Square (Optical Illusion)
- Enigma (Optical Illusion)

## Excel Optical Illusions #17

This week's Excel Optical Illusion is called Breathing Square.

Notice the blue square, spinning in the background. To most observers it looks as if it is pulsating in size, breathing if you will. I can assure you that this is an illusion - the blue square remains a constant size at all times.

The attached workbook has a check box that toggles the masking yellow squares transparent, revealing the truth. There's also a check box governing the animation.

Most of my Excel Optical Illusions are inspired by Michael Bach's incredible site, and this one is no exception.

Regarding this illusion, the following excerpt is from Michael's site:

Usually, we are remarkably adept at detecting and identifying surface boundaries despite incomplete optical information. However, in "breathing illusions", rigidly rotating surfaces appear to pulsate or deform, even though a straightforward process of geometrical interpolation across space would reconstruct the veridical surface boundary. It is generally believed that such nonrigid outcome depends on a failure to apply a rigidity constraint across spatiotemporal discontinuities (more: Bruno 2001).

Sorry for being a day late on this illusion - minor emergency dealt with.

The workbook is an .xls and so should work in all versions.

Here it is:

Here's a list of other animated charts on Excel Hero:

- Excel, A Presentation Platform (Number Spiral)
- Lilac Chaser (Optical Illusion)
- Stereokinetic (Optical Illusion)
- Illusory Contours (Optical Illusion)
- Breathing Square (Optical Illusion)
- Enigma (Optical Illusion)

## Excel VBA Shortcut Range References

When working in VBA we often need to refer to a range of cells. What's the best way to do so?

Well that's a loaded question! It totally depends on your objective. As with so many things in Excel there are a zillion different ways to refer to a range from VBA. In many circumstances I prefer to use the shortcut brackets, [].

Now I will warn you upfront that the shortcut method is never the quickest, but for me the clarity and simplicity it provides usually wins out. And truth be told, the speed difference is modest. If I need to provide the quickest execution speed possible, I'll chose a different approach. But most of the time the tiny speed difference is irrelevant.

The most common method that I see used to set the value of a cell from VBA is this:

`Range("A1").Value = 123`

The shortcut method is this:

`[A1] = 123`

Just remember that the square brackets are a replacement for the Range/Parentheses/Quotation Marks construct. I guess phrasing it that way highlights the brevity of the shortcut.

The method returns a real reference to the range, so just about anything that can be done with a normal range reference can also be done with the shortcut reference. It can be used on either side of the equal sign. It can be used to feed other functions. And it has all of the methods and properties of a normal range.

To be fair I cheated a little in the above comparison. But to demonstrate that shortcut references have the properties of a normal range, we could have typed it like this:

`[A1].Value = 123`

What is inside the square brackets can be a normal A1 style range reference, but it can also be a defined name. For example, if the workbook contained a Named Range called MyRange, then:

`MsgBox [MyRange].Address`

would display a message box with the address that defines the range.

`[a:a]`
is a reference to all of column A.

`[7:7]`
is the range defined as all of row 7.

`[r14, d20:g30, MyRange, a1].ClearContents`
deletes the values from four distinct ranges all at once.

`[h:h 5:5] = "hello world"`
references the intersection of two ranges to set the value of H5 to a familiar text.

`Set vArr = [CurRow CurCol].CurrentRegion`
sets the variant array, vArr, equal to all of the cell values in the currently used rectangular range surrounding the intersection of the named ranges, CurRow and CurCol. If thosed named ranges were built from dynamic named formulas then such a construct would be potent indeed. vArr will always be a 2-D variant array, with the rows from the range as the first index, and the columns as the second index.

Now when the shortcut is used on the left side of an equal sign, method, or property assignment, it is important that it reference a range. For example:

`[some_named_range] = lngVariable`

If the named formula some_named_range references a range on a worksheet, even if a formula is used to produce it, then this will work. On the other hand, if some_named_range references a constant value (from a formula or from a named constant) then the above notation will raise a Run-time error.

I agree with this behavior in the case of a value returned by a named formula, but a named constant should be able to have its value set in this way, in my opinion. But obviously other range properties would not be accessible in the case of a named constant (such as .Address, or .Font, etc.) and this is probably why you cannot change a named constant this way.

The most common usage of the INDEX worksheet function returns a value, not a range like OFFSET does. So if some_named_range was built using INDEX the common way in the name manager, then it is a value and would fail in the above assignment. If it were instead built on OFFSET, then it would work. INDEX can be used to return a range, but that is a story for an upcoming post.

If some_named_range referred to the range of 1,000 cells then:

`[some_named_range] = 5`
would place a 5 in all 1,000 cells.

In my Animated Business Chart, the AnimateChart sub contained these two lines:

`Names.Add "old", [chart_values]`
`Names.Add "switch_to_record", [record_index].Value`
which deserve some explanation.

Names.Add is how to create (or alter an existing named range/formula) from VBA. The first parameter to the method is the name to be applied the named range/formula. The second parameter is the value or values or formula the defined name should refer to. If the name exists when this code runs, it will be replaced by whatever is in the second parameter. There is no way to edit an existing name directly, nor is there a need to delete the existing range first.

Now why does the first line use for the second parameter a range reference, while the second line uses the Value property of a range reference?

Here's why. [chart_values] is a named formula that returns a set of values and not a range, so it is impossible to use the .Value property on it. As an aside, I usually wouldn't anyway as the .Value property is the default property of any range reference, and it offers no advantage in speed or clarity. In fact, as you can see in the table below, .Value is usually SLOWER.

Ah, but then why do I use the .Value property for the [record_index] named range in the second line? The answer is a little subtle, but is critical to the successful animation of the chart. The second line is defining a named constant, and it should have a hard number like 3 as its defined value. On the other hand, [record_index] is a named range pointing at a cell on the worksheet. If we dropped the .Value, we would be setting [switch_to_record] to the same cell reference, and NOT to a hard constant value. Using the .Value property ensures that it is set to the hard number and not the range reference.

So how modest is the speed penalty for using this shortcut? I timed a number of different ways of referencing a range, using the GetTickCount library which is accurate to the millisecond. For each test I closed Excel and freshly opened the testing workbook to ensure the memory was clear and that each method was operating under the same conditions. For the first round of tests, I set a range to a value 100,000 times in a For/Next loop, where "i" was the looping variable.

Here are the results:

```

Method
Seconds
Writes/Second

Set o = Sheet1.Range("a1"), o = i
21.672
4,614

Cells(1,1) = i
22.297
4,484

Set o = Sheet1.Range("a1"), o.Value = i
23.671
4,224

Range("a1") = i
23.812
4,199

Inside: With Sheet1:  .Range("a1") = i
23.961
4,181

Range("a1").Value = i
24.594
4,066

Range("a_named_range") = i
24.703
4,048

Sheets(1).Range("a1") = i
25.843
3,869

Sheet1.Range("a1") = i
25.938
3,848

Worksheets("Sheet1").Range("a1") = i
26.141
3,825

26.906
3,716

Worksheets(1).Range("a1") = i
28.468
3,512

Sheets("Sheet1").Range("a1") = i
29.031
3,444

[a1] = i
31.234
3,201

Inside: With Sheet1:  .[a1] = i
33.375
2,996

[a1].Value = i
34.375
2,909

[a_named_range] = i
37.703
2,652

```

The clear winner is setting up an object variable to represent the range (it should be noted that "o" was dimensioned as a Range type variable), followed closely by the Cells keyword. If absolute speed is critical to your circumstance then one of these is the way to go.

But remember that these times were the cumulative total required to write a value to the range 100,000 times. So even the slowest method in the test was writing to the range at a speed of 2,652 writes per second!

An interesting note is that reading the ranges is six to seven time faster for each of the range methods, but their relative performance is roughly the same.

I want to also point out that sometimes we need to update the worksheet in a loop. For example to get the animation to show on a chart, we need to give the charting engine a chance to work. If we just run the loop without addressing this issue, we will never see the animation, just the completed state. There are two methods commonly used to do this. The first is to use:

`Application.ScreenUpdating = True`
inside the loop.

The second is to use:

`DoEvents`
inside the loop.

So I tested these on the fastest method of range referencing (the object variable method) in the same loop of 100,000 iterations. Here are the results:

`Application.ScreenUpdating = True ---> 1,423.56 seconds`

`DoEvents ---> 65.33 seconds`

No that is not a mistake on the first one. The ScreenUpdating method took nearly 24 minutes! So to update a worksheet in a loop, always use DoEvents.

One final point that you should be aware of is that the shortcut range reference only works from within Excel. If you are using some other language to automate Excel, the shortcut range reference will not work.

The shortcut range reference from VBA is a shortcut in the number of characters used to refer to a range, it is not a shortcut in code speed of execution. But for me, in many situations, the brevity and clarity are more important. I work hard to craft the examples I post to Excel Hero, making the VBA clear and concise. I do this to maximize their readability and teaching potential. I feel that my liberal use of the shortcut range reference supports that goal.

## Excel Optical Illusions #16

This week's Excel Optical Illusion is a spin on the Swirling Almonds.

As long as you move your eyes, the almonds just keep swirling and swirling.

No VBA. Just a simple XY (Scatter) type chart.

Here it is.

## Excel Business Application Development Services

The Excel Hero blog is now four months old, and this is my 49th blog entry. What an adventure it has been. I have never blogged before and to go through the process of nurturing the fledgling blog like a seedling and watching it flourish is very rewarding. The process, and indeed the blog itself, takes on a life of its own. In the past seven days Excel Hero has had five different days where the number of unique visitors was over 1,000 and one day where it was almost 2,000.

In my lifetime this has been one of the most interesting things I've ever done. I would like to thank each and everyone of you for bothering to read, for downloading my samples, and for your input. My goal from the beginning has been to build a community of creative solutions. That community cannot exist without you!

If this is starting to sound like a goodbye - fear not. I'm just getting started.

For many years I have made my living 100% from freelance Excel development. That means I'm a programmer with a focus on Excel. Clients from all over the world hire me to design and implement Excel based solutions to their business needs. I'm not cheap. Hey, I live in California with unbelievable costs of living. But I would venture to say that every single client I have believes that they receive excellent value for money. Well, there was this one guy who wanted to... but that's a different story.

My rate is \$150 per hour.

As a heart felt thank you I would like to offer my readers a \$50 per hour discount for any work you might want to have done anytime over the next 60 days! Just send me an email and mention Excel Hero, and I will gratefully extend this promotional discount.

This week I plan to have two new articles posted. The first discusses the pros and cons of using [ ] shortcut range references from VBA. The second is when to choose INDEX/MATCH vs VLOOKUP and some other methods of looking up information. And of course, I will have a new Excel based optical illusion on Friday.

I'm also looking for a few volunteers to test a new business productivity tool I'm bringing out. It allows you to create an Org Chart for your business with one click, pulling data from your spreadsheet. All a user needs to do is select a column of employees and a column of managers and click the Make Chart button. That's it. The add-in instantly creates a beautifully formatted Organizational Chart from connected shapes. Of course there are options available to spruce things up. The user can copy the chart to PowerPoint or Word. The chart is live, and it can be edited. It's a powerful time saver and it's already in beta testing. I have several people testing it right now and am looking for a couple more. If you are interested please send me an email. I plan to release the product for general distribution within the next two weeks.

• Atom feed
• Daniel Ferry