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!

# Monthly Archives: June 2010

# Excel Optical Illusions Week #19

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.

# 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.

A1:B4

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,B2,C3,D4,E5,F6)(A3:E3,C1:C5)(A3:E3 C1:C5)(A1,A10,J10,J1)(CurRow CurCol)

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

=SUM(A2:C2)

Sheet1:Sheet12!A14:Z14

# Excel School – Last Day to Sign-Up!

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

**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.

- Using Excel’s Text formulas
- Introduction to Excel 2010’s new features
- Integration of Excel & Access – a lesson by Mike Alexander from Data Pig Technologies

# Excel Optical Illusions #18

This weeks’ Excel Optical Illusion is called, *Enigma*.

# Excel Optical Illusions #17

This week’s Excel Optical Illusion is called *Breathing Square*.

*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).*

*.xls*and so should work in all versions.

# 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?

Range("A1").Value = 123

[A1] = 123

[A1].Value = 123

MsgBox [MyRange].Address

[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.

[some_named_range] = lngVariable

[some_named_range] = 5

Names.Add "old", [chart_values]

Names.Add "switch_to_record", [record_index].Value

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 |

Names.Add “a_named_constant”, i | 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 |

Application.ScreenUpdating = True

DoEvents

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

DoEvents --->; 65.33 seconds

# Excel Optical Illusions #16

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

# 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.