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?

Posted on: June 17, 2009 | Filed Under: Around the Web | Comments: (0)

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.

Posted on: June 11, 2009 | Filed Under: Charts | Comments: (0)

• 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

Posted on: May 30, 2009 | Filed Under: VBA | Comments: (2)

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

Posted on: January 26, 2009 | Filed Under: VBA | Comments: (0)

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.

Posted on: January 20, 2009 | Filed Under: Charts | Comments: (0)

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.

Posted on: January 14, 2009 | Filed Under: Around the Web | Comments: (1)

Posted on: | Filed Under: Around the Web | Comments: (0)

Now use the Chart Wizard to create a bar chart. The Wizard produces the following:

Most people read from top to bottom. If you look at the chart the Category items appear to be in reverse order (i.e. D, C, B, A). However, Excel recognizes the reverse order as the correct order.

It's an easy fix. Click on the Category axis to bring up the Format Axis Dialog box. On the Scale tab, select the Categories in reverse order option and the Value (Y) axis crosses at maximum category option.

This little macro works too (assuming an embedded chart . . click on or activate the chart first and then run it):

Sub SetUpBarChart()
    Dim Cht As Chart
    Set Cht = ActiveChart
    With Cht.Axes(xlCategory)
        .ReversePlotOrder = True
        .Crosses = xlMaximum
    End With
End Sub

After doing so, the chart looks like this:

I wonder why the programmers did not / could not design the bar chart to show categories in a top-to-bottom order first?

Posted on: January 09, 2009 | Filed Under: Charts | Comments: (3)

Sub CopyChartsAsShapes()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Rng As Range

    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart

    For Each Rng In Sheets("Dash").Range("F4:F11")

        Cht.CopyPicture
        With Rng.Parent
            .Paste
            With .Shapes(.Shapes.Count)
                .LockAspectRatio = msoFalse
                .Left = Rng.Left
                .Top = Rng.Top
                .Width = Rng.Width
                .Height = Rng.Height
            End With
        End With

    Next Rng

    Cht.Deselect
    
    Application.ScreenUpdating = True

End Sub

In the example, the procedure copies a chart that resides on the sheet called "MC" to each cell within the range F4:F11 on the sheet called "Dash". The poster had named the chart "Cht1", which can easily be done by activating, or clicking on the embedded chart and then running the macro below:

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

Posted on: January 07, 2009 | Filed Under: Charts | Comments: (0)

To build the chart, assume the following set of data:

Some additional data and calculations need to be added to prepare the chart source. In the screenshot below, the range A9:C13 will serve as the source of the bar chart. The original range A2:D6 will serve as the X axis points of the XY chart and the Y points are shown in the range F2:F6.

Start by creating the floating bar chart. To do so, refer to a process posted at the Peltier Technical Services site that illustrates how to make a series invisible so that a second series floats in place (titled: Floating Column Charts) as a guide.

In the example, note that the "Dummy1" series is exactly the same as the "Min" values. The "Dummy2" series is calculated by subtracting the "Min" value from the "Max"value. Select the range A9:C13 and create a stacked bar chart. The chart should look like the one below:

Double-click the "Dummy1" series. In the Format Data Series dialog box - Patterns tab, set the Border to None and the Area to None. The chart should now look like this:

Now that the floating bar chart is created, it's time to incorporate an XY Scatter chart which will show the minimum, maximum, and average values. Select the chart and in the Source Data - Series dialog box, add a third series called "Minimum" based on the range F3:F6. After doing so, the Source Data - Series dialog box should look like this:

Note the new third series has appeared as a set of yellow bars. These bars need to be converted to XY Scatter points. Click on the new third series (the yellow bars) and, in the Standard toolbar, select Chart -> Chart Type -> XY (Scatter) -> type = Scatter. After doing so, the chart looks like this:

In the Standard Toolbar, select Chart -> Source Data -> "Minimum" Series. Currently there are no X values selected for the XY points (see the screenshot below):

Select the Minimum values as the X Value range (see the screenshot below):

And the chart should look like this:

With the incorporation of the XY Scatter points, two new secondary axes have been added to the top and right sides of the chart. To properly align the new XY points with the bars, the maximum values for these new axes need to reflect the same maximum values of the primary category and value axes. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Scale tab - set the maximum to the same value as the maximum of the Value axis (in this example the maximum is 60).

At this point the minimum values are now aligned with the minimum values of the bars. The same process of adding XY scatter points has to be completed to set up the average and maximum values. After adding the average and maximum values as XY points, the chart looks like this:

All that's left is to do now is some clean-up. Double-click on the Secondary Value (X) Axis (the axis values at the top of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. Likewise, double-click on the Secondary Value (Y) Axis (the axis values on the right side of the chart) and in the Format Axis dialog box -> Patterns tab set the Tick Mark Labels and Major Tick Mark Type options to None. The chart now looks like this:

To eliminate the legend references to the "Dummy1" and "Dummy2" series, click first on the legend and second on the "Dummy1" legend entry. Handles should appear around the "Dummy1" legend entry if it has been selected correctly. An example of how the chart looks with the "Dummy1" legend entry selected is below:

To get rid of the "Dummy1" legend entry, hit the delete key. Follow the same process for the "Dummy2" reference.

Select the plot area. In the Standard Toolbar select Chart -> Chart Options -> Gridlines and deselect all of the gridline options. Select the Minimum, Average, and Maximum series and in the Format Data Series dialog box -> Data Labels tab select the X value.

Continue to format until finished.

Posted on: November 30, 2008 | Filed Under: Charts | Comments: (1)