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!
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.
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.
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) )
This weekend I had the honor of reviewing Chandoo’s Excel School.
- 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
This weeks’ Excel Optical Illusion is called, Enigma.
This week’s Excel Optical Illusion is called Breathing Square.
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
is a reference to all of column A.
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
|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
Application.ScreenUpdating = True ---> 1,423.56 seconds
DoEvents --->; 65.33 seconds
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.