Write values to a header row

I saw a newsgroup post where someone was writing to a header row (row 1) by walking through each cell, setting an object reference to it, then putting in a cell value. Oh boy. So I wrote a routine that writes values to a header row by using a Variant type and just dumping it to the worksheet in one go. It's much more efficient to minimize the amount of times that VBA needs to interact with the worksheet, and the code is shorter.

The following function takes a Variant array of values and a Worksheet object, and writes the values to row 1 of the worksheet, starting in A1. The reason we pass a Worksheet object is so that we can loop through the Worksheets collection and write headers to each sheet, as we'll see shortly.

Sub PopulateHeaderRow(headerValues As Variant, sht As Excel.Worksheet)

Dim rngCount As Long
Dim rngHeader As Excel.Range

  ' count number of values to determine number of header cells being used
  rngCount = UBound(headerValues) + 1

  ' set range reference to exact number of header cells
  Set rngHeader = sht.Range(sht.Cells(1, 1), sht.Cells(1, rngCount))

  ' write array values to header
  rngHeader.Value = headerValues

End Sub

Update 9/31/2011: Thanks to site visitor Meg for pointing out an error with the previous version of the above procedure.

Write header values to active sheet

Sub TestHeaderRow1()
  On Error GoTo ErrorHandler

  Dim headerValues As Variant
  Dim sht As Excel.Worksheet

  headerValues = Array("First Name", "Last Name", "Street Address", "Apartment Number", "City", "State", _
                       "Zip Code", "Telephone Number", "Fax Number", "E-mail Address", "Notes")

  Set sht = ActiveSheet

  Call PopulateHeaderRow(headerValues, sht)

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Write values to every worksheet

Here's how we can write an array of values to every sheet in the workbook. We could do a number of different things with this code. For example, we could use a Select Case statement and populate the headerValues variant differently depending on the sheet being edited, and so on.

Sub TestHeaderRow2()
  On Error GoTo ErrorHandler

  Dim headerValues As Variant
  Dim sht As Excel.Worksheet

  headerValues = Array("First Name", "Last Name", "Street Address", "Apartment Number", "City", "State", _
                       "Zip Code", "Telephone Number", "Fax Number", "E-mail Address", "Notes")

  For Each sht In ActiveWorkbook.Worksheets
    Call PopulateHeaderRow(headerValues, sht)
  Next sht

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub