February 15, 2007, 3:04 pm by John Walkenbach
Today I realized that it was about 17 months ago when I saw the first demo of Excel 2007. Like just about everyone else in the room, I was very impressed with what I saw — even though it was little more than a rough draft at the time. Fact is, Excel 2007 demos very well.
Then, when I actually started using it, my initial enthusiasm waned — primarily because I couldn’t find the familiar commands. As I dug deeper, I discovered a few things that just didn’t work like they used to.
During the beta phase, I got very discouraged and really began to hate it. But, I was forced to use it because I was writing books about it. Over time, I actually began to like Excel 2007. By the time the final version was released, I got to the point where I dreaded having to use Excel 2003 rather than 2007. Now that I’m familiar with just about every nook and cranny of Excel 2007, I have a difficult time remembering where the Excel 2003 commands are.
The chart here roughly depicts my past 17 months with Excel 2007 in terms of “likeability.” I don’t know how typical this curve is. I suspect that it will be linear for some; they’ll start out hating it, and gradually learn to love it.
But I do think that anyone who uses this product for any length of time will not want to go back to a previous version. Sure, it has some problems. But its benefits outweigh the problems by a large margin as far as I’m concerned.
February 6, 2007, 11:18 am by John Walkenbach
I found some code that uses API functions to play MIDI music at a French site. I adapted the code so it’s easy to use. Copy the code below and paste it into a VBA module.
Option Explicit
Private Declare Function midiOutOpen Lib “winmm.dll” _
(lphMidiOut As Long, _
ByVal uDeviceID As Long, _
ByVal dwCallback As Long, _
ByVal dwInstance As Long, _
ByVal dwFlags As Long) As Long
Private Declare Function midiOutClose Lib “winmm.dll” _
(ByVal hMidiOut As Long) As Long
Private Declare Function midiOutShortMsg Lib “winmm.dll” _
(ByVal hMidiOut As Long, _
ByVal dwMsg As Long) As Long
Private Declare Sub Sleep Lib “Kernel32″ (ByVal dwMilliseconds As Long)
Dim hMidiOut As Long
Public lanote As Long
Sub PlayMIDI(voiceNum, noteNum, Duration)
Dim Note As Long
On Error Resume Next
midiOutClose hMidiOut
midiOutOpen hMidiOut, 0, 0, 0, 0
midiOutShortMsg hMidiOut, RGB(192, voiceNum – 1, 127)
lanote = 12 + CLng(noteNum)
Note = RGB(144, lanote, 127)
midiOutShortMsg hMidiOut, Note
Sleep (Duration)
midiOutClose hMidiOut
End Sub
The PlayMIDI Sub procedure accepts three arguments, and plays a single note. The argument are:
- voiceNum: A number from 1-128 that represents the instrument sound. Here’s a list of the MIDI voice numbers.
- noteNum: A number that indicates the note to play. For reference, C is 0, 12, 24, 36, etc. C# is 1, 13, 25, 37, etc.
- Duration: A number that indicates how long to play the note, in milliseconds (1,000 equals 1 second).
To play around with this, I set up a worksheet that has a 4-column list of notes. A lookup table provides the actual note letters for the values in column B. In the figure, I have it set up to generate random notes and durations. Then, a simple macro plays the song.
Then, a simple macro plays the song represented by the worksheet data.
Sub TestMidi()
Dim r As Long
ActiveSheet.Calculate
For r = 2 To Application.CountA(Range(“A:A”))
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
Next r
End Sub
By the way, I have no idea how this code works. Using the RGB function is a mystery to me. One final note. Avoid stopping the code by pressing Ctrl+Break. If you do that, you may get a stuck note that requires closing Excel.
January 23, 2007, 5:00 pm by John Walkenbach
Coincidentally, this post relates to two previous posts — which happen to be unrelated to each other:
Excel 2007 has a new Sort object. I discovered that this object does its thing even when it’s called by a UDF function used in a formula. In other words, yet another example of a UDF that changes the spreadsheet.
The figure below shows an example. The data in columns A and B is sorted immediately (by column B) whenever a change is made within the range A1:B50. So, if I change Anne’s score to 71, her row would move down between Paul and Oscar’s data.
Cell D2 contains a formula that uses the SortRange function:
Function SortRange(rngToSort As Range, Optional order)
‘ Excel 2007 only
‘ Order: 1=ascending, 2=descending
If IsMissing(order) Then order = 1
With rngToSort.Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=rngToSort.Cells(1, 2), order:=order
.SetRange rngToSort
.Header = xlYes
.Apply
SortRange = .Rng.Address(False, False) ‘Range actually sorted
End With
End Function
This function takes two arguments: The range to sort, and the sort order (an optional argument). The Sort object is a member of the Worksheet, so I use the Parent property to get that object, and enclose all of the object manipulations inside of a With-End With structure.
The function does this…
- Clears the SortFields (which is a collection)
- Adds a key. In this case, the sort key is the second column of the data, and it’s sorted in the order specified by the second argument.
- Gets the range to be sorted, using the SetRange method.
- Specifies that the range has a header row, via the the Header property.
- Does the sorting, via the Apply method
- Gets the address of the range that was actually sorted, and assigns it to the function (this is the text returned by the formula). The function is passing a 50-row range, but it only has 13 rows of data.
You can accomplish the same effect with a WorksheetChange event, but this technique allows the user to specify some options without modifying the code. The function, of course, could include more arguments that control how the sorting is done.
Practical applications, anyone?
January 19, 2007, 10:15 am by John Walkenbach
Assuming I work non-stop all weekend, I should have a beta version of my PUP v7 add-in ready to go next week. This version works only with Excel 2007.
If you’re interested in doing some informal beta testing, please let me know.
You’ll need a copy of Excel 2007, of course. If you’re able to devote a few hours trying out the utilities (and reporting the bugs to me), I’d appreciate your help. I’m especially interested in recruiting someone who can test it using a non-English version of Excel.
Here’s a tiny image of the PUP v7 ribbon (click to enlarge):
You can view a list of the PUP changes here: What’s New in PUP v7.
Creating that ribbon UI was the biggest challenge. It even includes a dynamicMenu control (for the PUP Bookmarks) which took me about 4-5 hours to figure out and debug. Doing the same thing with a CommandBar control would have taken about 30 minutes.
January 12, 2007, 5:50 pm by John Walkenbach
Do you know that you can write VBA worksheet functions that modify shapes on a worksheet? I didn’t know this until about an hour ago.
Paste the following UDF into a VBA module:
Function ModifyShape(ShapeNumber, ShapeType, Vis)
With ActiveSheet.Shapes(ShapeNumber)
.AutoShapeType = ShapeType
.Visible = Vis
End With
End Function
Then, add a shape to the worksheet and enter this formula into any cell:
=ModifyShape(1,55,TRUE)
The first argument is the shape’s index number. The second argument is a value that represents the shape’s type (values from 1-138 are supported). The last argument determines whether the shape is visible. The arguments, of course, could use cell references. Change the second argument and watch the shape change its shape. Change the third argument to FALSE and watch the shape disappear.
This sort of thing is a lot more useful in Excel 2007, because embedded charts are contained in shapes. Therefore, you can write formulas that manipulate the chart object properties such as size, position, and visibility. Even better, your UDF can even access the Chart object contained in the shape, and manipulate that. Here’s a simple example:
Function ChangeChartType(CName, CType)
‘ Excel 2007 only
ActiveSheet.Shapes(CName).Chart.ChartType = CType
End Function
This function assumes an embedded chart. It uses two arguments: the ChartObject’s name, and the chart type (e.g., 5 is xlPie, -4100 is xl3DColumn, etc.).
Even better — a function that allows you to specify a chart’s min and max scale values:
Function ChangeChartAxisScale(CName, lower, upper)
‘ Excel 2007 only
With ActiveSheet.Shapes(CName).Chart.Axes(xlValue)
.MinimumScale = lower
.MaximumScale = upper
End With
End Function
The ability to adjust a chart’s axes based on calculations has long been on the Excel wish list. I wonder if Microsoft even knows this is now possible?
December 8, 2006, 5:37 pm by John Walkenbach
Dick forgot to post a weekend free-for-all-thing last Friday, so I’m stepping in and taking over.
Let’s talk about music.
Who plays an instrument? Dick hasn’t talked about his piano playing in quite a while. How’s it coming, Dick?
If you’d visit my house, you’d find about 12 guitars, five banjos, a mandolin, two ukuleles, an electronic keyboard, a dulcimer, a fiddle, and a few harmonicas. I dabble a lot, but I’m a master of nothing. But I enjoy it, and that’s what it’s all about.
What type of music do you like to listen to?
Is there anyone who has no interest in music?
December 5, 2006, 5:59 pm by John Walkenbach
When I make a post here, I always wonder how many people actually benefit. Is it too advanced? Too simplistic? In other words, I have no idea what kind of Excel users actually read this blog.
So how about a poll? Please reply, even if you don’t actively participate here by posting comments.
December 4, 2006, 10:44 am by John Walkenbach
If you need to use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. Like this:
Sub LoopFillRange()
' Fill a range by looping through cells
Dim CellsDown As Long, CellsAcross As Long
Dim CurrRow As Long, CurrCol As Long
Dim StartTime As Double
Dim CurrVal As Long
' Change these values
CellsDown = 500
CellsAcross = 200
Cells.Clear
' Record starting time
StartTime = Timer
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal + 1
Next CurrCol
Next CurrRow
' Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub
>On my system, writing 100,000 values using a loop takes 9.73 seconds.
A faster way to accomplish the task is to put the value in an array, and then transfer the array to the worksheet. The procedure below writes 100,000 values in 0.16 second — about 60 times faster than the looping method.
Sub ArrayFillRange()
' Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim i As Long, j As Long
Dim StartTime As Double
Dim TempArray() As Double
Dim TheRange As Range
Dim CurrVal As Long
' Change these values
CellsDown = 500
CellsAcross = 200
Cells.Clear
' Record starting time
StartTime = Timer
' Redimension temporary array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
' Set worksheet range
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal + 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
' Display elapsed time
dApplication.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub
November 16, 2006, 8:12 am by John Walkenbach
If you create custom menus or toolbars, you’re probably familiar with the FaceId property — a numeric value that specifies an image. People have come up with lots of ways to display the FaceId images. Here’s one I haven’t seen before.
Start with an empty workbook, and paste the ShowFaceIDs procedure into a VBA module. Adjust the ID_START and ID_END constants to specify which images you’d like to see (it’s currently set up to show the first 500). Run the macro, and the 18 x 18 pixel images are displayed on the active worksheet. Click an image and you’ll see it’s FaceID value in the Name box.
Sub ShowFaceIDs()
Dim NewToolbar
As CommandBar
Dim TopPos
As Long, LeftPos
As Long
Dim i
As Long, NumPics
As Long
‘- – - – - Change These – - – - -
Const ID_START As Long = 1
Const ID_END As Long = 500
‘- – - – - – - – - – - – - – - – - – - -
‘ Delete existing TempFaceIds toolbar if it exists
On Error Resume Next
Application.CommandBars(“TempFaceIds”).Delete
On Error GoTo 0
‘ Clear the sheet
ActiveSheet.Pictures.Delete
Application.ScreenUpdating = False
‘ Add an empty toolbar
Set NewToolbar = Application.CommandBars.Add _
(Name:=“TempFaceIds”)
‘ Starting positions
TopPos = 5
LeftPos = 5
NumPics = 0
For i = ID_START To ID_END
On Error Resume Next
NewToolbar.Controls(1).Delete
With NewToolbar.Controls.Add(Type:=msoControlButton)
.FaceId = i
.CopyFace
End With
On Error GoTo 0
NumPics = NumPics + 1
ActiveSheet.Paste
With ActiveSheet.Shapes(NumPics)
.Top = TopPos
.Left = LeftPos
.Name = “FaceID “ & i
.PictureFormat.TransparentBackground = True
.PictureFormat.TransparencyColor = RGB(224, 223, 227)
End With
‘ Update top and left positions for the next one
LeftPos = LeftPos + 16
If NumPics Mod 40 = 0 Then
TopPos = TopPos + 16
LeftPos = 5
End If
Next i
ActiveWindow.RangeSelection.Select
Application.CommandBars(“TempFaceIds”).Delete
End Sub
Even if you don’t create CommandBars, you might have fun looking at all the little pictures.
November 10, 2006, 6:15 pm by John Walkenbach
This is just a test post, using Word 2007 Beta. I tried to get it to work with my own blog, but no luck. WordPress is one of the supported options, and it’s supposed to work with the MetaBlogger API. But either I can’t figure out what their cryptic dialogs want, or it just doesn’t work.
Let’s try a list:
- Bullet Point #1
- Another one
-
Final Bullet Point
Does indented text get tagged as blockquote?
How about a picture?
http://j-walkblog.com/images/absolutopenmic.jpg
Let’s try a table:
|
Column 1
|
Column 2
|
Column 3
|
|
Row 1, Column 1
|
Row 1, Column 2
|
Row 1, Column 3
|
|
Row 2, Column 1
|
Row 2, Column 2
|
Row 2, Column 3
|
Here’s a code listing using the [ vb ] tags.
<pre>Sub AddToShortCut()
‘ Adds a menu item to the Cell shortcut menu
Dim Bar
As CommandBar
Dim NewControl
As CommandBarButton
DeleteFromShortcut
Set Bar = CommandBars(
“Cell”)
Set NewControl = Bar.Controls.Add _
(
Type:=msoControlButton, ID:=1, _
temporary:=
True)
With NewControl
.Caption =
“Toggle &Word Wrap”
.OnAction =
“ToggleWordWrap”
.Picture = Application.CommandBars.GetImageMso(
“WrapText”, 16, 16)
.Style = msoButtonIconAndCaption
End With
End Sub
</pre>
<p>
(I updated this using Live Writer — which at least lets you edit the HTML code.)
And it handles images, too.
