Just some random VBA

Below is some vba code I wrote for a Excel 2007 spreadsheet. Essentially, it uses a proprietary add-in that will refresh data on calculate based on some date values. Then it moves that data to a temp sheet and does some massaging of the data. Then creates a pivot table that aggregates the data and a chart to show the data. I have written extensive comments to show what the code does.

'Declare intCount as a global to use in various sub procedures. Dim intCount As Integer 'The type command is one I find that people don't understand in all cases 'Secondly, even those that understand the concept didn't know you had 'the ability to create types in Excel VBA. 'Type allows you to create your own data type, an object for those object 'oriented programming folks. In this case, the type is used to represent 'a downtime event, including the code, description, and duration Type DowntimeData Fault As Integer Fault_Description As String Fault_Duration As Variant End Type 'This is my main sub routine, there is a button on the excel sheet 'that calls this subroutine when pressed. I like to use Main as my 'program control and call out to sub procedures. Sometimes you need 'to pass values back and forth between sub routines and the Main section 'gets cluttered with other items but I avoided that in this spreadsheet. 'Call allows you to call another subroutine. Sub Main() Call Refresh_Data Call Clear_Temp_Data Call Select_Useful_Data Call Copy_Data_To_Temp_Data Call Format_Data_In_Temp_Data Call Array_Populator Call Refresh_Pivot_Tables End Sub Sub Refresh_Data() 'Same as hitting F9 to re-calculate the values on the sheet Sheets("Data").Calculate End Sub Sub Clear_Temp_Data() 'Deletes all cells in Temp_Data sheet Sheets("Temp_Data").Cells.Delete End Sub Sub Select_Useful_Data() 'Using intCount to get a count of the data rows I have pulled 'dynamically and using it to figure out max potential data amounts 'Copying all the data cells needed using intCount to figure out the 'dynamic portion of data intCount = Sheets("Data").Range("A9").Value Sheets("Data").Range(Cells(9, 2), Cells(intCount + 8, 7)).Copy End Sub Sub Copy_Data_To_Temp_Data() 'Paste the values from Select_Useful_Data into a temp_data sheet Sheets("Temp_Data").Range("A1").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub Sub Format_Data_In_Temp_Data() 'Format Column A as a datetime with the format specified Sheets("Temp_Data").Columns("A:A").NumberFormat = "dd-mmm-yy hh:mm:ss" Sheets("Temp_Data").Columns("F:F")NumberFormat = "[h]:mm:ss;@" Sheets("Temp_Data").Columns("B:B").NumberFormat = "0" End Sub Sub Array_Populator() Dim arrdata() As DowntimeData Dim x As Integer ReDim arrdata(intCount) x = 1 For i = 1 To intCount If IsNumeric(Cells(i, 2).Text) = True Then If Cells(i, 2).Text <> 0 And Cells(i, 6).Text <> "" Then arrdata(x).Fault = Cells(i, 2).Text arrdata(x).Fault_Description = Cells(i, 3).Text arrdata(x).Fault_Duration = Cells(i, 6).Text x = x + 1 End If End If Next Call Dump_Array_To_Temp_Data(arrdata, x) End Sub Sub Dump_Array_To_Temp_Data(ByRef arrdata() As DowntimeData, ByVal x As Integer) Call Clear_Temp_Data Cells(1, 1).FormulaR1C1 = "Fault Code" Cells(1, 2).FormulaR1C1 = "Fault Duration" Cells(1, 3).FormulaR1C1 = "Fault Description" For i = 1 To x - 1 Cells(i + 1, 1).FormulaR1C1 = arrdata(i).Fault Cells(i + 1, 2).FormulaR1C1 = arrdata(i).Fault_Duration Cells(i + 1, 3).FormulaR1C1 = RTrim(arrdata(i).Fault & " - " & arrdata(i).Fault_Description) Next Columns("B:B").Select Selection.NumberFormat = "[h]:mm:ss;@" Columns("A:A").Select Selection.NumberFormat = "0" Cells.Select Cells.EntireColumn.AutoFit Call Create_Named_Range(x) End Sub Sub Create_Named_Range(ByVal x As Integer) Sheets("Temp_Data").Range("A1").Select Sheets("Temp_Data").Range(Cells(1, 1), Cells(x, 3)).Name = "Downtime_Data" End Sub Sub Refresh_Pivot_Tables() Sheets("Pivot_Table").Activate ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh Sheets("Chart_Sheet").Activate End Sub