Excel procedure to calculate SE for Excel pivot table

I would like to share an Excel procedure that can be used in conjunction with Excel pivot table to calculate SE for ACS PUMAs data using replicate weights. This procedure works on a pivot table on the active worksheet. This procedure is written for person weight but it can be easily re-written for household weight.

(if you copy and paste procedure below to your personal macro workbook (personal.xlsb – will be created if you “record” a new macro (you may need to customize ribbon to show “developer” items) the procedure will be available anytime you open Excel)

If you have question/comment, please let me know.
Regards,
Toshihiko
Salem, OR

Sub get_se()
'Calculates SE for ACS PUMAs data using replication weights
'Procedure works on the pivot table on the active worksheet
'Created March 5, 2014 T.Murata
Dim pwgtp00 'main weight 2 dimentional array
Dim pwgtpxx '1 to 80th weight 2 dimentional array
Dim pwse() 'place to do calculation and hold final se
Dim i, j, k, x, y
'clear previous se automatically (next 4 lines to)
On Error Resume Next
ActiveSheet.Range("_get_se_").Clear
ActiveWorkbook.Names("_get_se_").Delete
On Error GoTo 0
With ActiveSheet.PivotTables(1)
'resetting current data field
For i = .DataFields.Count To 1 Step -1
.PivotFields(.DataFields(i).Name).Orientation = xlHidden
Next

'get main weight value
.PivotFields("PWGTP").Orientation = xlDataField
pwgtp00 = .DataBodyRange
x = .DataBodyRange.Row
y = .DataBodyRange.Column + .DataBodyRange.Columns.Count + 1
ReDim pwse(1 To UBound(pwgtp00, 1), 1 To UBound(pwgtp00, 2))

'clear main weight
.PivotFields("Sum of PWGTP").Orientation = xlHidden
i = 1

'cycle through all replicate weights - get sum((Xr -X)^2)
For i = 1 To 80
.PivotFields("pwgtp" & i).Orientation = xlDataField
pwgtpxx = .DataBodyRange
For j = 1 To UBound(pwgtp00, 1)
For k = 1 To UBound(pwgtp00, 2)
pwse(j, k) = pwse(j, k) + (pwgtpxx(j, k) - pwgtp00(j, k)) ^ 2
Next k
Next j
.PivotFields("Sum of PWGTP" & i).Orientation = xlHidden
Next i

'get final se for each cell - take sqrt of sum of difference squared * 4/80
For j = 1 To UBound(pwgtp00, 1)
For k = 1 To UBound(pwgtp00, 2)
pwse(j, k) = Sqr((4 / 80) * pwse(j, k))
Next k
Next j
'go back to main weight
.PivotFields("PWGTP").Orientation = xlDataField
End With
'paste the results next to the pivot table
ActiveSheet.Cells(x, y).Resize(UBound(pwgtp00, 1), UBound(pwgtp00, 2)).Select
Selection = pwse
'add name to se range so it can be cleared next run
ActiveWorkbook.Names.Add Name:="_get_se_", RefersToR1C1:="=" & ActiveSheet.Name & "!" & Selection.Address(ReferenceStyle:=xlR1C1)
End Sub