Excel VBA

I recently used MS Excel 2010’s Visual Basic for Applications to normalize and automate the reporting of test cases by severity. The purpose of this view is to answer the question, “Of the failed tests reported, how many of the defects are high severity vs. how many are low?” So instead of just seeing just a pass/fail percentage like this:

You could publish the customer impact of the associated defects like this:

HP ALM 11 natively reports test case results and defect results, but not both in one view. If you’d like to see failed tests with their associated, you need a custom SQL query and you’ll end up with very generic tabular data like this:

Sorting and formatting the data was painful.  The following scripts provide automation for most of the steps involved using Excel’s built in VBA macro system.

All we care about is the highest severity level defect linked to each test case.  The others (like the second row above) can be discarded.  (If a test/feature fails on a Show Stopper, we don’t care if there are also typos on it, at least not yet.)  This is the VBA to de-dupe by sev:

'
' Normalize duplicate failed tests
'

' Skip the header row, process non blank rows
' Need to constrain this by an end-of-data function, not an # of rows integer
For i = 2 To 200
    ' Don't process blanks
    If Cells(i, 1).Value <> "" Then
        ' If there is >1 defect logged against the same test
        If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
            ' If the current row has lesser or equal severity defect than the following row, delete the current row
            If Cells(i, 6).Value >= Cells(i + 1, 6).Value Then
                Cells(i, 1).EntireRow.Delete
            ' Otherwise delete the following row
            Else
            Cells(i + 1, 1).EntireRow.Delete
            End If
        ' Reset i to account for the row deleted
        i = i - 1
        End If
    End If
Next i

' Sort by sev
    Columns("F:F").Select
    ActiveWorkbook.Worksheets("Failed Tests with Severities").sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Failed Tests with Severities").sort.SortFields. _
        Add Key:=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Failed Tests with Severities").sort
        .SetRange Range("A2:J96")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

This summarizes the results:

'
' SummaryTable Macro
'
    Range("L2") = "Show Stopper"
    Range("L3") = "High"
    Range("L4") = "Medium"
    Range("L5") = "Low"
    Range("L6") = "Passed"
    Range("L7") = "Blocked"
    Range("M2") = WorksheetFunction.CountIf(Columns(6), "1-Show Stopper")
    Range("M3") = WorksheetFunction.CountIf(Columns(6), "3-High")
    Range("M4") = WorksheetFunction.CountIf(Columns(6), "4-Medium")
    Range("M5") = WorksheetFunction.CountIf(Columns(6), "5-Low")
    Range("M6") = 1
    Range("M7") = 1

And this graphs them:

‘ Graph Macro

Range(“L2:M7”).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPieExploded
ActiveChart.SetSourceData Source:=Range( _
“‘Failed Tests with Severities’!$L$2:$M$7”)
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).HasLeaderLines = False
Selection.Position = xlLabelPositionOutsideEnd

ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.SeriesCollection(1).Explosion = 0
ActiveChart.SeriesCollection(1).Explosion = 1
ActiveChart.SeriesCollection(1).Points(1).Select
Selection.Explosion = 15
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 117, 117)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(4).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 117, 117)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 167, 167)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 167, 167)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Solid
End With
ActiveChart.Legend.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 12
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 12



			
			
			
		

A $1 Planter

How do you build a planter for one buck? First, forget to throw away that old fence you tore down earlier in the year. Second, think rustic. A bent nail or split piece of wood just makes it more stylish. Then invest about thirty cents in nails and about the same in a plastic painting liner.

And wallah!

I need about four of these, so the price is right. Put a little TLC into the project and you are sure to create a planter of your own that looks like it cost at least three or four bucks.

WordPress UnGallery

UnGallery dynamically publishes directories of images and embeds them into your WordPress blog.  The name describes the goal of the plugin to stay out of your way and allow you to manage your images with no administration in WordPress.

This approach allows your powerful administration tools like FTP, SCP, automation scripts, and image editors to directly manage your image library.  Changes are automatically reflected in WordPress.  There are no web forms to select files for upload.  There is no importing or modifications to the database.  The gallery files are accessed remotely by WordPress and remain accessible and manageable, outside the WordPress environment.

If you’ve ever had to reorganize where your photos are stored, remove a few from a large set, or edit the red-eye of a dozen pictures after uploading, you know how inconvenient it is to return to the website management UI to update everything.  With UnGallery, all these add/remove/edit changes you make to your library are automatically rolled into WordPress.

 

 

Features:

  • Unlimited depth, breadth, and number of photos. The gallery here has approximately 25,000.
  • Photo library is managed outside of WordPress.  Simply update via FTP, SCP, etc.  UnGallery displays changes automatically.
  • Multiple gallery views:  Top level marquee (optional), thumbnails, browsing previous and next pictures.
  • Gallery hierarchy breadcrumbs with links to parent, present, and sub-galleries
  • Optional and default banner captions
  • Unbrowsable, hidden galleries
  • Caching for faster page reloads
  • Support for MP4 movies embedded within WordPress
  • Automatic image rotation of jpegs with exif orientation, if available

Notes:

  • The layout is configured for the default WordPress installation.  I use the excellent Atahualpa theme and increase the page width to a modern browser standard.  The sizes of all thumbnails, images, and columns are configurable.
  • Feel free to use it, extend it, or contact me with questions.

Download:

  • Grab the current version at WordPress.org.
  • The installation steps are mostly standard, though there are couple simple, extra steps, noted in the instructions.

Screencast:

UnGalleryScreencast

A Hybrid Fence

We wanted to see down into the greenbelt valley behind our home. So I took down the decrepit fence at the rear of our propery. Great! We can see the deer, rock squirrels, falcons, etc. But it’s also somewhat of a security hole, since the home is now more accessible from that side and with less visibility than from the front.

So, how about putting up a property line fence that does not block the view? The standard solutions for this either chain link which looks cheap or wrought iron which for 100+ feet came in at about $5,000. This is about $5,000 more than I was looking to spend considering our back yard is classified as zero maintenance, rarely entered and not much to look at.

I decided on treated 2×2s attached to the existing 4×4s already mounted in concrete for the current fence. These will act as crossbeams and when cut in half at a 45 degree angle, the pickets. The architecture is structurally sound but not as completely sturdy as if I’d used 2×4s for the crossbeams. A strong person could tear at the fence and break sections apart, but then again, I can pull off pieces of the old fence and the neighbor’s fence with one hand. And the other neighbor has no fence. So this option, complete with sharp, upward facing spires will provide an obstacle that is not scalable and would force a probably loud demolition to break through.

Maybe I’ll stain it, maybe reinforce the crossbeams with 2×4s, maybe not. But the yard is fenced again and now we can see down into the valley!

Configuration Automation

This project scripts the remote setup of Ubuntu 8.x servers as a Ruby on Rails application servers. It installs all necessary libraries, packages, and optionally, deploys Ruby on Rails applications. I’ve published a few of these automation scripts on the FiveRuns Blog. The most recent version of configuration-automation includes a menu of Rails application choices to install after the Linux bootstrap completes and was published in February on the FiveRuns blog. I’ve archived those posts here with additional comments.

The current post: Automatic Rails Applications

The previous post: Automatic Production Rails

The original post: Automatic Rails at Slicehost