Author Archive
Interrupting Calculations
Normally, Excel calculates your workbooks so fast you don’t notice. Sometimes, however, your workbook is so big that it takes Excel some time to calculate it. I’m guessing readers of this blog run into that situation more than the average Joe. While Excel is calculating, the status bar displays its progress. If you continue to work during the calculation, Excel will pause its calculation so you can work at a normal speed. Nice feature, I think.
Excel in mid-calculation
Make a keystroke and it goes back to ready, but shows that more calc’ing is needed
I guess Microsoft is assuming that if you doing other things, you’re not really looking at the results. If you’re not looking at the results, it can wait until you’re done to finish calculating. For humans, I think that’s a fine assumption. For VBA code, not so much. Your code may be continuing on happily without knowing that the workbook isn’t fully calculated.
The Excel object model provides the Application.CalculationInterruptKey property to control how/if calculation can be interrupted. xlAnyKey is the default and it pauses whenever the user starts working. xlEscKey only pauses when the escape key is pressed. It’s used when you want to allow the user to interrupt calculation, but you want to make sure they do it explicitly and not by accident. xlNoKey prevents interruption. It seems that any time you calculate in code, you should set this property to xlNoKey, then set it back. Are there times it wouldn’t matter?
Thanks to Bill Manville for “discovering” the CalculationInterruptKey property.
Quick PivotTables
Sometime in the mid-1990s, a man named Lyle Lanley walked into Microsoft with an idea. Probably the greatest.... Oh, it's more of an Apple idea. "Just tell us the idea and we'll implement it", said Bill Gates.
I'm on a quest to rid my life of wizards. The wizards that Microsoft seems to use for every single command. It all started with fixing the find dialog as I grew tired of clicking the Options button. Then I saw an opportunity to speed up Text to Columns which is unnecessarily wizardish. Today's victim is the Pivot Table.
Have you seen the wizard for Pivot tables? They couldn't put that on one screen? Well I say, No more! I just want to make a pivot table:
- from an Excel range
- that I've already selected
- and I want it on a new sheet
Almost always. Unless I need something out of the ordinary, I'm using this code:
Sub CreatePivotTable()
Dim rData As Range
Dim shNew As Worksheet
Dim pcNew As PivotCache
Dim rCell As Range
Dim lFieldCnt As Long
Dim ptNew As PivotTable
Const sFIELD As String = "Field"
'Make sure a range is selected
If TypeName(Selection) = "Range" Then
Set rData = Selection.CurrentRegion
Set shNew = rData.Parent.Parent.Sheets.Add
'put column headers in blank cells
lFieldCnt = 1
For Each rCell In rData.Rows(1).Cells
If IsEmpty(rCell.Value) Then
rCell.Value = sFIELD & lFieldCnt
lFieldCnt = lFieldCnt + 1
End If
Next rCell
Set pcNew = shNew.Parent.PivotCaches.Add(xlDatabase, rData)
Set ptNew = shNew.PivotTables.Add(pcNew, shNew.Cells(1))
ptNew.AddFields rData.Rows(1).Cells(1).Text, rData.Rows(1).Cells(2).Text
ptNew.AddDataField ptNew.PivotFields(rData.Rows(1).Cells(3).Text)
End If
End Sub
Dim rData As Range
Dim shNew As Worksheet
Dim pcNew As PivotCache
Dim rCell As Range
Dim lFieldCnt As Long
Dim ptNew As PivotTable
Const sFIELD As String = "Field"
'Make sure a range is selected
If TypeName(Selection) = "Range" Then
Set rData = Selection.CurrentRegion
Set shNew = rData.Parent.Parent.Sheets.Add
'put column headers in blank cells
lFieldCnt = 1
For Each rCell In rData.Rows(1).Cells
If IsEmpty(rCell.Value) Then
rCell.Value = sFIELD & lFieldCnt
lFieldCnt = lFieldCnt + 1
End If
Next rCell
Set pcNew = shNew.Parent.PivotCaches.Add(xlDatabase, rData)
Set ptNew = shNew.PivotTables.Add(pcNew, shNew.Cells(1))
ptNew.AddFields rData.Rows(1).Cells(1).Text, rData.Rows(1).Cells(2).Text
ptNew.AddDataField ptNew.PivotFields(rData.Rows(1).Cells(3).Text)
End If
End Sub
Those last two lines add row, column, and data fields from the first three columns of the data. I don't like that, as it's prone to error. What I like less is what I get when I exclude those lines.
Compare four blank blue boxes to what you get via the user interface
I don't like the four blank squares and I don't like using the first three columns. I have to find something better.
XL
If I were Roman, that's how old I would be today.
Today I will be spending my birthday processing inventory transactions and answering the phone - great fun. Tomorrow, however, we're having a little shindig in my honor with family, friends, and beer.
Look Up
The last control on the right click menu is Look Up. That opens the Research task pane.
I'd be interested to know if anyone actually uses this. I accidentally click on it several times a week, but I don't actually use it. Anyone?
Entering Dates Into Cells Part II
I'm sure you all remember Entering Dates Into Cells from five years ago. It was a simpler time. Brittney Spears was telling us about her prerogative, the Olympics returned to Athens, and my date entering needs reflected the simplicity of the times. Now I need more.
Here are some things I discovered about myself:
- I almost never need to enter the time into a cell
- I don't need to continue editing the cell after I press Cntl+; to enter the date
- I often have to enter yesterday or tomorrow
Time to bloat the PMW again. I'm going to take over the Control+; (control plus semicolon) shortcut and the Control+Shift+; shortcut. The former will put the date into the activecell unless a date already exists there, in which case it will increment the date by one day. The latter will do the same except that it will decrement the date by one day.
You know what's great about a video demonstrating keyboard shortcuts? Anyway, here's what selecting C4, pressing Cntl+; three times, and pressing Cntl+Shift+; three times looks like:
In my Auto_Open and Auto_Close macros, respectively, I have these OnKey assignments
Application.OnKey "^;", "IncrementDate"
Application.OnKey "^+;", "DecrementDate"
Application.OnKey "^+;", "DecrementDate"
Application.OnKey "^;"
Application.OnKey "^+;"
Application.OnKey "^+;"
I should have made the following all one macro, but I'm a little lazy like that.
Sub IncrementDate()
If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value + 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End If
End Sub
Sub DecrementDate()
If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End If
End Sub
If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value + 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End If
End Sub
Sub DecrementDate()
If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End If
End Sub
Reading XML Files in VBA
In Creating State Maps... I copied some data out of an XML file and graphed it into a state map. I wanted to try to read the XML file directly in VBA because I've never done it before. First, I set a reference to Microsoft XML v6. Six was the newest version I had, but I don't know what the differences are.
Next I started inspecting the objects via the Object Browser (F2). Definitely DOMDocument was the big one I would need and the Load method took a file name as an argument, so that looked promising. Here's what I ended up with:
Sub ReadStateXML()
Dim xmlDom As MSXML2.DOMDocument
Dim xmlPlaceMark As MSXML2.IXMLDOMNode
Dim xmlPolygon As MSXML2.IXMLDOMNode
Dim xmlCoord As MSXML2.IXMLDOMNode
Dim sName As String
Dim vaSpace As Variant, vaComma As Variant
Dim i As Long, j As Long
Set xmlDom = New MSXML2.DOMDocument
xmlDom.Load "C:\Downloads\overlay_1198.kml"
For i = 0 To xmlDom.childNodes(1).childNodes(0).childNodes.Length - 1
If xmlDom.childNodes(1).childNodes(0).childNodes.Item(i).nodeName = "Placemark" Then
Set xmlPlaceMark = xmlDom.childNodes(1).childNodes(0).childNodes.Item(i)
Set xmlPolygon = xmlPlaceMark.childNodes(2).childNodes(0)
Set xmlCoord = xmlPolygon.childNodes(0).childNodes(0).childNodes(0)
sName = xmlPlaceMark.childNodes(1).childNodes(5).nodeTypedValue
'Stop
With Sheet4.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = sName
vaSpace = Split(xmlCoord.childNodes(0).Text, " ")
For j = LBound(vaSpace) To UBound(vaSpace)
vaComma = Split(vaSpace(j), ",")
.Offset(0, 1).Value = vaComma(0)
.Offset(0, 2).Value = vaComma(1)
Next j
End With
End If
Next i
End Sub
Dim xmlDom As MSXML2.DOMDocument
Dim xmlPlaceMark As MSXML2.IXMLDOMNode
Dim xmlPolygon As MSXML2.IXMLDOMNode
Dim xmlCoord As MSXML2.IXMLDOMNode
Dim sName As String
Dim vaSpace As Variant, vaComma As Variant
Dim i As Long, j As Long
Set xmlDom = New MSXML2.DOMDocument
xmlDom.Load "C:\Downloads\overlay_1198.kml"
For i = 0 To xmlDom.childNodes(1).childNodes(0).childNodes.Length - 1
If xmlDom.childNodes(1).childNodes(0).childNodes.Item(i).nodeName = "Placemark" Then
Set xmlPlaceMark = xmlDom.childNodes(1).childNodes(0).childNodes.Item(i)
Set xmlPolygon = xmlPlaceMark.childNodes(2).childNodes(0)
Set xmlCoord = xmlPolygon.childNodes(0).childNodes(0).childNodes(0)
sName = xmlPlaceMark.childNodes(1).childNodes(5).nodeTypedValue
'Stop
With Sheet4.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = sName
vaSpace = Split(xmlCoord.childNodes(0).Text, " ")
For j = LBound(vaSpace) To UBound(vaSpace)
vaComma = Split(vaSpace(j), ",")
.Offset(0, 1).Value = vaComma(0)
.Offset(0, 2).Value = vaComma(1)
Next j
End With
End If
Next i
End Sub
It almost gets the job done, but not quite. I use a lot of XMLNode variables so I can keep track of where I am, but the code is hardly self documenting. The childNodes property only takes an integer as an argument, so I can't call out the nodes name. This is only slightly better than reading it in as a text file. To be fair, I've only started messing with this object library, so maybe I can define a schema that makes things easier. I just don't know.
Based on this initial chart, it looks like a got the coordinates parsed correctly.
There are few dots over at x=0, y=50. Alaska has a few islands that cross the date line and have x coordinates around 179. Rather than try to convert them, I just converted the x's to zero. Ultimately I deleted those data points. Also, I should have made each polygon its own series. I don't know the limit on chart series, but I'll be I'd have reached it. Having only one series doesn't allow me to connect the dots.
I also added a trend line (in pink). It appears my country is in decline.
Creating State Maps with XY Charts
According to eggheadcafe, Excel doesn't have built-in maps anymore. I'm not a charting guy, as you know, so I didn't know that. I also didn't know that they ever did. But I wanted a map of the US with sales by region. That seems possible, but all I got this time was a map of Nebraska. Kind of a proof of concept before I'm ready to tackle the whole US. Here's how I got it:
First I went to Finder to get a KML file. A KML file appears to be a special purpose XML file. You know XML? That data format that's perfect for everything in every situation? When I got to Finder, I noted a CSV download. Perfect, I thought. Alas, the CSV does not provide the same information as the KML file, so I had to go the long way. I'm not sure what good that CSV file is. That is, I don't really know what you could make out of that data.
I downloaded the KML file and started inspecting it. There is a coordinates tag that plots the outline of every state. Some states, like Nebraska, have one coordinates tag because we are a closed polygon of a state. States that touch water, however, have more than one coordinates tag because there's always an island. Alaska has ten million coordinates tags (not really, but it's a lot).
If I had Office 2003 Professional, I could probably get that XML file right into a spreadsheet. But I don't. I have Small Business Edition - no XML facilities in here. Although I intended (and intend) to make a map of the US, I thought I'd start simply and just do one state - my state. I copied the coordinates tag with all of those coordinates and pasted into a text file. Then I used this macro to list them in a worksheet.
Sub GetCoordinates()
Dim sFname As String
Dim lFnum As Long
Dim sLine As String
Dim vaCoords As Variant
Dim vaIndiv As Variant
Dim i As Long
lFnum = 1
sFname = Environ$("userprofile") & "\My Documents\NE_Coordinates.txt"
Open sFname For Input As lFnum
Do While Not EOF(lFnum)
Line Input #lFnum, sLine
vaCoords = Split(sLine, " ")
For i = LBound(vaCoords) To UBound(vaCoords)
vaIndiv = Split(vaCoords(i), ",")
Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = vaIndiv
Next i
Loop
Close lFnum
End Sub
Dim sFname As String
Dim lFnum As Long
Dim sLine As String
Dim vaCoords As Variant
Dim vaIndiv As Variant
Dim i As Long
lFnum = 1
sFname = Environ$("userprofile") & "\My Documents\NE_Coordinates.txt"
Open sFname For Input As lFnum
Do While Not EOF(lFnum)
Line Input #lFnum, sLine
vaCoords = Split(sLine, " ")
For i = LBound(vaCoords) To UBound(vaCoords)
vaIndiv = Split(vaCoords(i), ",")
Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = vaIndiv
Next i
Loop
Close lFnum
End Sub
I actually tried pasting the coordinates from the XML (opened in Firefox) directly into Excel and splitting them out with Text to Columns and some transposing, but I think I ran into a character limit - not everything pasted. So I went with the text file/VBA method.
With the data in Excel, I created an XY Scatter Chart. I removed the axis, gridlines, makers, and connected the data points with lines.
Not too bad. I'm not sure how to ensure the axes are on the same scale. This looks a little horizontally squashed. Here's what it looks like before the clean up.
I need to read the XML file directly into VBA. There are some XML libraries (under VBE - Tools - Reference) that look promising, but I haven't tried them yet. Then I should be able to draw the whole country without a lot of effort.
