The website AccountingWeb recently ran an article titled "Understanding Spreadsheet Risks". The article notes that the "US Sarbanes-Oxley Act and related regulations increased the level of scrutiny over the way spreadsheets are used and controlled in financial reporting and other critical business functions."

The company that I work for has to comply with Sarbanes-Oxley. Below is a brief summary of what must be done to comply.

Workbook Inventory

A central database will be maintained for any spreadsheet that serves as a source for a journal entry. An individual record within that database is kept for each spreadsheet. That record must contain:

• The name of the workbook file.
• A control number that is assigned to that workbook.
• The author of the workbook.
• The purpose of the workbook.
• Who depends on the workbook.
• The site location where the workbook resides.
• The directory location of the workbook.
• A log of all passwords tied to the workbook.

Workbook Security

• Workbooks and worksheets should be password protected.
• Cells containing static values must be locked.
• As people change positions, access rights should be updated.

Change Management

• Changes to workbooks must be approved, documented, and managed to include previous and revised formulas, assumptions, and reasons for making the change.
• A log of all changes must be recorded in separate worksheet.
• Changes require review and approval by at least two managers.
• Changes should be communicated to all of those that may be impacted.

Archiving

• An archived version of the original file must be kept before saving any changes.
• Archived versions must be kept for a period of time directed in a separate retention policy.

All of this courtesy of Enron, WorldCom, Tyco, and others. Thoughts?

A standard in the Healthcare industry is a dual-series clustered bar chart that compares a series of key statistics on a month and year-to-date basis. In Excel, the data source for this chart would be set up similar to below.

Unfortunately it can be a frustrating experience if you use the Chart Wizard to set this chart up for the first time. As can be seen in the image below the category axis labels, bars, and legend data all appear to be backwards.

The chart can be cleaned up in just a few easy steps. The frustrating part is that if you're not very familiar with Excel, figuring out these steps can take a lot of time. The steps are:

• Category Axis - Format Axis - Patterns - Tick Mark Labels = Low
• Category Axis - Format Axis - Scale = Categories in Reverse Order
• Value Axis - Format Axis - Patterns - Tick Mark Labels = None

A simple macro will accomplish the same thing:

Sub SetUpChart()

    Application.ScreenUpdating = False
    
    With ActiveChart.Axes(xlCategory)
        .ReversePlotOrder = True
        .TickLabelPosition = xlLow
        .MajorTickMark = xlNone
    End With

    With ActiveChart.Axes(xlValue)
        .MinimumScale = -0.2
        .MaximumScale = 0.2
        .MajorTickMark = xlNone
        .TickLabelPosition = xlNone
        .Border.LineStyle = xlNone
    End With

    ActiveChart.Axes(xlValue).HasMajorGridlines = False
    ActiveChart.Axes(xlValue).HasMinorGridlines = False
    
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(2).ApplyDataLabels

    ActiveChart.PlotArea.Border.LineStyle = xlNone
    ActiveChart.PlotArea.Interior.ColorIndex = xlNone
    
    ActiveChart.Legend.Position = xlBottom

    ActiveChart.Deselect

End Sub

The final chart looks like the one below. The Y-axis labels now flow from the top down, the monthly bars appear on top of the YTD bars, and the month appears before YTD in the legend. At this point only the formatting needs further work.

It's been almost four months since my last post. The reasons why . . .

• Work has been extremely busy. It seems that in an economic downturn my job seems to get busier rather than slow down. As the business goes south there's an increased need for the financial analysis that supports the decisions that management makes.
• Preparing for a professional exam. Studying and finally taking the test takes up a lot of time.
• A lack of fresh ideas. That's probably the real reason.

That being said, the VBA Express site posted a simple macro that allows the user to view each sheet starting at the top-left cell. A variation of that macro is shown below. In my opinion, it's more professional to distribute a workbook to others with the active cell for each sheet at "A1". This macro allows you to quickly and efficiently set up your workbook to do so before sending it out.

Sub GoToCellA1()

    Dim Sheet As Worksheet
    Dim CurrentSheet As String
    
    CurrentSheet = ActiveSheet.Name

    Application.ScreenUpdating = False
    
    For Each Sheet In Worksheets

        Application.Goto Sheet.Range("A1"), scroll:=True

    Next

Sheets(CurrentSheet).Activate

End Sub

In response to a newsgroup question, I recently referenced a VBA procedure at Ozgrid.com that shows how to change relative and absolute row - column references. I thought I might document some slightly simplified versions for my own use:

To convert all formulas within a stated range to absolute references:

Sub All_Absolute()
    Dim Rng As Range
    Set Rng = Range("A1:A5")
    Rng.SpecialCells(xlCellTypeFormulas) = Application.ConvertFormula(Rng.Formula, _
    FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
End Sub

To convert all formulas within a stated range to relative references:

Sub All_Relative()
    Dim Rng As Range
    Set Rng = Range("A1:A5")
    Rng.SpecialCells(xlCellTypeFormulas) = Application.ConvertFormula(Rng.Formula, _
    FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
End Sub

To convert all formulas within a stated range to relative row / absolute column references:

Sub Relative_Row_Absolute_Column()
    Dim Rng As Range
    Set Rng = Range("A1:A5")
    Rng.SpecialCells(xlCellTypeFormulas) = Application.ConvertFormula(Rng.Formula, _
    FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
End Sub

To convert all formulas within a stated range to relative column / absolute row references:

Sub Relative_Column_Absolute_Row()
    Dim Rng As Range
    Set Rng = Range("A1:A5")
    Rng.SpecialCells(xlCellTypeFormulas) = Application.ConvertFormula(Rng.Formula, _
    FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
End Sub

In Displaying a Chart in a Userform, John Walkenbach describes how to use VBA to save an embedded chart as a GIF file and then load that file into a userform. The userform contains controls that allow you to scroll forward or backward through all of the charts that are available.

I recently built a model where I needed to to take this example another step further by building in functionality to allow the user to:

• See a list of the names of all charts available to be displayed.
• Allow the user to pick what chart he or she wanted to see based on that list.

To do so, the use of a listbox in a userform seemed like the ideal setup to use.

As an example, start with a spreadsheet containing four embedded charts:

The charts are located on a sheet named "Charts". All of the charts are aligned to the worksheet grid and sized exactly the same. To align and size the charts, I used the following macro:

Sub AlignCharts()
    Application.ScreenUpdating = False
    Dim ChtObj As ChartObject
    For Each ChtObj In Sheets("Charts").ChartObjects
        ChtObj.Top = ChtObj.TopLeftCell.Top
        ChtObj.Left = ChtObj.TopLeftCell.Left
        ChtObj.Height = 126
        ChtObj.Width = 192
    Next ChtObj
End Sub

A name is applied to each chart. Naming the chart is the key to allowing you to control the order in which the chart appears in the listbox. You can use any name that you want. In this example the first chart is named "Cht01", the second "Cht02", the third "Cht03", and the fourth "Cht04". To name each chart, I used the following macro:

Sub NameChart()
    ActiveChart.Parent.Name = "ColumnCht"
End Sub

Note that to run the "NameChart" macro, you must activate the chart (click on it) first and then run the macro.

At this point it's time to build the userform. As in the Spreadsheet Page example, the userform contains an image control and buttons that allow the user to scroll forward or backward through the charts. I've added the additional listbox control:

When you click on the "Chart Userform" button which is embedded in the "Charts" sheet, the userform is shown via the following code. Note that the button embedded in the "Charts" sheet is named "CB1", the userform is named "userform1", and the listbox is named "listbox1". The code is placed in the "Charts" sheet and the charts are ordered high-to-low in any order that you want the user to see.

Private Sub CB1_Click()
'   Clear the RowSource property
    UserForm1.ListBox1.RowSource = ""

'   Add items to ListBox
    UserForm1.ListBox1.AddItem "Column Chart"
    UserForm1.ListBox1.AddItem "Line Chart"
    UserForm1.ListBox1.AddItem "Area Chart"
    UserForm1.ListBox1.AddItem "Bar Chart"

    UserForm1.Show vbModeless
End Sub

After the userform is shown via the code above, it is then initialized to show the first chart in the image control via the code below. A textbox (named "TB1") located above the image control contains the chart title.

Public Rng As Range
Public Cht As String

Private Sub UserForm_Initialize()
    TB1 = "Column Chart"
    Cht = "Cht01"
    Call UpdateChart
End Sub

Private Sub UpdateChart()

    Set CurrentChart = Sheets("Charts").ChartObjects(Cht).Chart

    '   Saves the chart as GIF file
    Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

    '   Shows the chart in the image control
    Image1.Picture = LoadPicture(Fname)

End Sub

Finally, a listbox click event allows the charts to appear in the image control when the listbox is clicked.

Private Sub Listbox1_Click()
    If ListBox1.ListIndex = 0 Then
        TB1 = "Column Chart"
        Cht = "Cht01"
    ElseIf ListBox1.ListIndex = 1 Then
        TB1 = "Line Chart"
        Cht = "Cht02"
    ElseIf ListBox1.ListIndex = 2 Then
        TB1 = "Area Chart"
        Cht = "Cht03"
    ElseIf ListBox1.ListIndex = 3 Then
        TB1 = "Bar Chart"
        Cht = "Cht04"
    End If
    Call UpdateChart
End Sub

You can download the example file via the link below:

Note: the technique of loading a chart into a userform is credited to John Walkenbach. His example of Displaying a Chart in a Userform can be found at The Spreadsheet Page.

Gary Klass is a member of the Department of Politics and Government at Illinois State University. He is the author of the book titled "Just Plain Data Analysis: Finding, Presenting, and Interpreting Social Science Data". Of special note, in the companion website for the book Gary provides a summary of the charting differences between Excel 2003 and Excel 2007.

Because we still use Excel 2003 at work I've still not made the change to Excel 2007. Gary notes in his list of differences between Excel 2003 and 2007 that the macro recorder in Excel 2007 does not work on chart objects. As a result it is necessary to record macros with 2003 to use them in 2007. When I read that I immediately referenced John Walkenbach's book titled Excel 2007 Power Programming with VBA for a confirmation. John notes on page 574 that "Microsoft downgraded macro recording for charts to the point where it's virtually useless". I didn't realize that until now, and the unavailability of macro recording for charts in Excel 2007 will probably sway me against upgrading for a very long time.