To add images of embedded charts to this weblog, I use the VBA procedure below. To run the procedure, first make sure the specifications are correct. You can make changes to the chart height, width, file name, and file path. When all of the inputs are correct, click on (activate) the embedded chart and run the macro.

Sub ExportChart()

    Dim Cht As ChartObject
    Dim Path As String
    Dim FileName As String
    
    Set Cht = ActiveChart.Parent

    Cht.Height = 210
    Cht.Width = 336
    FileName = "2008032301.gif"
    Path = "C:\Program Files\"
    
    ActiveChart.Export Path & FileName

End Sub

Please note that the height and width are not really needed. I've added this code only because I like to make sure all of the charts are exactly the same size.

Many companies produce reports at a top-level, department, division, and/or cost center level. When Excel is used to build these reports, the normal practice seems to be to create a new sheet for each department, division, and/or cost center. Each sheet contains the data and calculations for that area. A single top-level reporting sheet is then created to which each department, division, and/or cost center sheet links to. When it's all over and done the workbook can become very large, prone to error, and difficult to audit.

An easier and more accurate approach is to use the combination of lookup formulas and a VBA loop to build the report. In this example, only four sheets in the Excel file are illustrated (it could be only one if designed that way). The first sheet, called "dBase", contains the raw data for each department, division, and/or cost center. The second sheet, called "Calcs", contains a single set of calculations needed to build the final report. The third sheet, called "Load", contains a summary of the data from the Calcs sheet needed for the final report. The final sheet, called "Report", is the final report.

The example begins with a screenshot of the sheet called "dBase" (for database). The "dBase" sheet contains ten departments. The Commerical / Contract and Government volumes for each department is provided for the years 2007 and 2008. The final report requires that you show the components of payer mix variance for each department.

The next screenshot shows the sheet called "Calcs". The blue cell (C2) contains the name of the department. The yellow cells contain lookup formulas that reference the "dbase" sheet.

For example, the lookup formula in cell E8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,2,FALSE)

The lookup formula in cell F8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,4,FALSE)

The next screenshot shows the sheet called "Load". The purpose of this sheet is to reference all of the values that need to appear in the final report into one row of data.

The data that appears in the "Load" sheet is referenced to the green cells in the "Calcs" sheet.

Finally, a VBA procedure is run to create the report. A loop is created that, when combined with the lookup formulas, does the following:

• The VBA procedure loads the name of the department into cell C2 of the "calcs" sheet.
• The lookup formulas in the calcs sheet reference the data from the "dBase" sheet for that particular department.
• The payer mix calculations are performed.
• The payer mix data is referenced from the "calcs" sheet into row of data in the "Load" sheet.
• The data from the "Load" sheet is copied as values into the final report.

In this example, the procedure described above is performed ten times - once for each department as referenced in the range B8:B17 of the "Report" sheet. The VBA code that performs the work is below:

Sub BuildReport()
    Application.ScreenUpdating = False
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    Set Rng2 = Sheets("Calc").Range("C2")
    Set Rng3 = Sheets("Load").Range("B3:G3")
    Set Rng4 = Sheets("Rpt").Range("C8:H8")
    For Each Rng1 In Sheets("Rpt").Range("B8:B17")
        Rng2.Value = Rng1.Value
        Rng3.Copy
        Rng4.PasteSpecial xlPasteValues
        Set Rng4 = Rng4.Offset(1, 0)
    Next Rng1
    Sheets("Rpt").Range("A1").Select
End Sub

After the macro has been run, the final report looks like this:

The advantages of using lookup formulas and VBA loops to build the report are these:

• Only one set of calculations (the "Calcs" sheet) needs to be audited for accuracy. This is especially advantageous as the number of departments, division, etc. needed on the final report grows.
• The audit risk concerning the workbook is reduced due to less sheets and data ranges needing to be proofed.
• The physical size of the workbook is reduced which make it easier to maintain and transfer to others via email, web, etc.
• The VBA procedure runs fast and the report is produced almost instantaneously.

Potential disadvantages of using lookup formulas and VBA loops to build the report are these:

• If you need to pass the workbook on to someone that has no experience with VBA, the issue of who can maintain the workbook may come up.
• If your manager is clueless when it comes to lookups or VBA, he or she may prefer the "many sheets and calculations" approach so they can understand it (runs hand-in-hand with the first bullet).

The need to fill a blank range with zeros often occurs when dealing with data that is downloaded from a mainframe. Recently I've seen several examples that utilize a loop structure to fill a blank range with zeros. Rather than using loops, below is an example that utilizes the SpecialCells method to fill a range.

Sub FillBlanksWithZeros()
    Dim Rng As Range
    On Error GoTo ErrHandler
    Set Rng = Range("A1:B10")
    Rng.SpecialCells(xlCellTypeBlanks) = 0
    Exit Sub

ErrHandler:
    MsgBox "Error # " & Err.Number & " - " & Err.Description
End Sub

The SpecialCells method is much faster than using a loop structure.

A recent post to the Microsoft Excel Charting Newsgroup asked "how can I return a non-integer value from a scrollbar? A VBA approach would be to divide the scrollbar output by 10, 100, 1000, etc. Assuming the scrollbar is named "SB1", the example below returns values between 0.1 and -0.1:

Private Sub SB1_Change()
  Range("A1").Value = SB1.Value / 1000
  ScrollBar1.Max = 100
  ScrollBar1.Min = -100
End Sub

Private Sub SB1_Scroll()
  Range("A1").Value = SB1.Value / 1000
  ScrollBar1.Max = 100
  ScrollBar1.Min = -100
End Sub

This simple procedure loads the values in the range A1:A5 into an array. The Debug.Print statement prints the contents of the array in the Immedicate Window of Visual Basic Editor.

Sub LoadArrayFromRange()

    Dim arrRange()
    Dim Rng As Range

    x = 0

    For Each Rng In Range("A1:A5")
        ReDim Preserve arrRange(x)
        arrRange(x) = Rng.Value
        x = x + 1
    Next Rng

    For Each Item In arrRange
        Debug.Print Item
    Next

End Sub

I'm not sure where I found this so unfortunately I can't credit the original author.

Last weekend I spent several hours searching Excel's Help and the Internet trying to come up with a procedure to reverse the items in a one-dimensional array. I was using an array as the source for a listbox load and the loop below to load the items into the listbox:

For Each Item In Array1
     frmTrace.ListBox1.AddItem Item
Next Item

Unfortunately this routine loads the data from low to high instead of high to low. After spinning my wheels, I posted my problem to the Microsoft Programming Newsgroup and it was solved within hours. I received three responses and all three worked. Below is the simplest correction:

For Each Item In Array1
     frmTrace.ListBox1.AddItem Item, 0
Next Item

Thanks to those in the Programming Newsgroup who helped me with this.