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.

One of my responsibilities at work is to produce a monthly statistics package and top-level commentary. A single spreadsheet serves as a data repository and there are approximately 50 charts based on differing data that we use to visually present our monthly results. All of the charts are designed to be dynamic. By dynamic I mean that they automatically update as statistics are entered into the spreadsheet. The "upgrade" to dynamic charting vs. manual updates has probably saved at least one to two days of work as well as decreased the chance of error.

One of the easiest charts to automate is an actual vs. plan chart. The chart below is an example of a simple actual vs. plan dynamic chart:

The data source for the chart is below:

The monthly data is entered into the yellow shaded range C4:C15.

The chart is composed of three different chart types - a simple Line chart, an Area chart, and an XY Scatter chart. The actual data in the range F4:F15 is the base of the Line chart. The formula in cell F4 is below:

=IF(C4,C4,NA())

This formula is copied into each cell in the range F4:F15. The chart source data - series dialog box for the actual numbers looks like this (note that the example is on Sheet2 of the spreadsheet):

The plan numbers in the range G4:G15 simply refer to the numbers in the range D4:D15. The plan data uses the Area chart type. The chart source data - series dialog box for the plan numbers looks like this:

The next task is to automate the positioning of the data labels. In this case, we want the data labels to reflect the actual and plan numbers for the current month. The XY Scatter chart is used for data labeling positioning. Two XY Scatter points are used to track the actual and plan data points for the current month. The first XY data point reflects the actual month number and is based on the range J5:J6. Formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J5 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J6 (Y Axis point) is:

=OFFSET($C$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the actual XY Scatter point numbers looks like this:

Finally, the second XY data point reflects the plan month number and is based on the range J10:J11. As with the actual data labels, formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J10 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J11 (Y Axis point) is:

=OFFSET($D$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the plan XY Scatter point numbers looks like this:

In another variation of the chart above, the plan is shown going into the future only. The chart is made up of a Line chart with two series and an XY Scatter chart with two data label points. Below is an example:

The data source for the chart is below:

In this example, all formulas and chart sources are the same except for the formulas in range G4:G15. The formula in cell G4 is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

This formula is copied into each cell in the range G4:G15.

What's nice about using dynamic techniques such as these is that so many people don't. If you're lucky enough to work for one of these people, they'll still think that it takes more time than it really does to complete the work. As a result, set your email to send something to him or her every 1/2 hour and take the rest of the afternoon off. Unfortunately, once the secret's out they'll want everything now.

In my opinion, many Finance and Accounting textbooks present the mechanics of variance analysis as being way too complicated. The analysis is usually presented as a series of different formulas depending on which textbook you read. Although at a detailed level the formulas are the same, algebraically they're presented in different manners which can lead to confusion. Below are three examples from the textbooks:

Example 1:

Example 2:

Example 3:

Why can't it be as easy as this?

First calculate the actual vs. standard total sales (cells E9 and F9). Second, calculate the percentage change between the actuals and standards (cells G9 - G11). Finally, perform the analysis as presented in the yellow highlighted cells.

In the example below, total actual vs. standard sales increased $20,000. There are two components of variance -> price / unit (rate) and quantity. At the component level, the actual vs. standard price / unit (rate) is identified as going up 20%. The actual vs. standard quantity is identified as going up by 50%.

The advantages of using the last method are:

• Intuitively it makes more sense because the presentation is simple. You can easily see that if the price / unit increases 20%, the dollars component of variance needs to reflect that increase. Likewise, if the quantity component increases 50% the dollars component needs to reflect it.
• You don't need to remember formulas.
• The method can be easily applied to any number of variances. For example, in the Healthcare industry gross revenue variances may include five or more components of variance (work days, fees, volume, payer mix, acuity, etc.). If remembering the formulas for two or three components of variance is difficult, try memorizing the formulas for a five or more components.

In Combining Chart Types, Adding a Second Axis, Katherine Fifer of the Excel Team Blog describes in detail how to create the chart using Excel 2007.

It's been my experience that understanding the embedded chart object model can be difficult. Understanding how to declare variables based on that model can be even more difficult. Below is a guide that I refer to when attempting to isolate selected parts of the embedded chart object model. The guide uses a simple embedded line chart as an example. Each example can be put in a standard code module.

To name an embedded chart, first activate the chart (click on it once) and then run the following:

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

The example below declares the variable called "Cht" as a ChartObject. To select an embedded chart:

Sub SEC()
    Dim ChtObj As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Select
End Sub

The following three examples isolate the first series for an embedded line chart:

Example 1 - declares the variable called "Cht" as a ChartObject. To select the source values for series one in an embedded line chart:

Sub SeriesEx1()
    Dim ChtObt As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Chart.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 2 - declares the variable called "Cht" as a Chart. To select the source values for series one in an embedded line chart:

Sub SeriesEx2()
    Dim Cht As Chart
    Set Cht = ActiveSheet.ChartObjects("Cht1").Chart
    Cht.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 3 - declares the variable called "Srs" as a SeriesCollection. To select the source values for series one in an embedded line chart:

Sub SeriesEx3()
    Dim Srs As SeriesCollection
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection
    Srs(1).Values = Range("A1:A5")
End Sub

The three examples above show that the object model is built in a hierarchy, or levels, with ChartObject at the highest level i.e. ChartObject -> Chart -> SeriesCollection -> an so on.

The next two examples attempt to isolate the points for the first series for an embedded line chart. However, it's confusing because the first example refers to the series object while the second refers to the points object. When the macros are run, their results will appear in the immediate window of the visual basic editor.

Example 1 - declares the variable called "Srs" as a series object. To capture the value for the first point in series one in an embedded line chart (refers to the series object):

Sub PointsEx1()
    Dim Srs As Series
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1)
    Debug.Print Srs.Values(1)
End Sub

Example 2 - declares the variable called "Pts" as a points object. To count the number of points in series one for an embedded line chart (refers to the points object):

Sub PointsEx2()
    Dim Pts As Points
    Set Pts = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1).Points
    Debug.Print Pts.Count
End Sub

Although not the same, the modeling for many of the other embedded chart types (column, bar, etc.) works very similar to the above.

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.