Turn logged data into fancy plot

I'm logging data from an embedded platform over UART. This gets saved to a log file and I want to process the saved log with Excel.

I have no idea whether what I did was even remotely a good solution. My VBA experience so far consists mainly of code only used once. However, since I'll have to use this for every measurement series I conduct it's probably a good idea to clean it up. Not in the least because I can't stand ugly code, I'm about to expand on it and colleagues may want to use it in the near future as well.

Example input:

00032
00016
00016
00016
00032
00064
00080
00096
00112
00128
00160
00192

The size of the input can be thousands of values. All leading zeroes get dropped as intended. Values can be negative.

```
Option Explicit

Public Sub Stats()
'
' Stats Macro
' Give stats to column A and draw chart with trendline.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Columns("A:A").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 8109667
.TintAndShade = 0
End With

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$A")
ActiveChart.FullSeriesCollection(1).Tren

Avoid using Active*

You can use Worksheets collection to access a specified Worksheet object represents a worksheet.

Worksheets("YOUR SHEET NAME")

This can be useful to do explicit job, without changing the active sheet. The same way for ActiveCell, ActiveChart and ActiveChart, you can always access the target object by the collection.

Use With rather than Selection

Aside from error-raising macro problems, a recorded macro is less efficient, because it mimics all the mouseclicks and keystrokes (every cough and camera flash) that occurred while the recording was taking place. A recorded macro clicks on every object to select it, then performs an action on the selection:

Columns("A:A").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

If you have two or more property or method statements that work on the same object, wrap them in a With / End With block:

With Worksheets("Sheet1").Columns("A")
    ' read / write object's properties here
    ' e.g. add new color scale rule
    .FormatConditions.AddColorScale ColorScaleType:=3
End With

This block can increase the code efficiency and readability.

Check the existence of target object

The macro record is powerful, but it's more difficult to understand whether something exists already. For example, we may not want to apply 10 rules of color scale to the column A.

' drop color scale if exists
For Each fc In .FormatConditions
    If fc.Type = xlColorScale Then
        fc.Delete
    End If
Next

Here's just an example, there must be better logic for treating existing object.

As for your script, here's my suggest edits.

Public Sub Stats2()
'
' Stats Macro
' Give stats to column A and draw chart with trendline.
'
' Modified by Mincong HUANG
'

    ' declarations
    Dim wsName As String
    Dim chartName As String
    Dim srcRange As Range

    ' initialization
    wsName = "Sheet1"
    chartName = "Fancy Chart"
    Set srcRange = Worksheets(wsName).Columns("A")

    ' set format conditions at target column
    With srcRange

        Debug.Print .Address

        ' drop color scale if exists
        For Each fc In .FormatConditions
            If fc.Type = xlColorScale Then
                fc.Delete
            End If
        Next

        ' add new color scale
        With .FormatConditions
            ' add
            .AddColorScale ColorScaleType:=3
            With .Item(.Count)

                .SetFirstPriority

                ' advanced setting (optional)
                ' Optional because these values are the default setting
                ' but you can add other desired color

                .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .ColorScaleCriteria(1).FormatColor.Color = 7039480
                .ColorScaleCriteria(1).FormatColor.TintAndShade = 0

                .ColorScaleCriteria(2).Type = xlConditionValuePercentile
                .ColorScaleCriteria(2).FormatColor.Color = 8711167
                .ColorScaleCriteria(2).FormatColor.TintAndShade = 0
                .ColorScaleCriteria(2).Value = 50

                .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
                .ColorScaleCriteria(3).FormatColor.Color = 8109667
                .ColorScaleCriteria(3).FormatColor.TintAndShade = 0

            End With
        End With
    End With

    ' drop log chart if exists
    Debug.Print Worksheets(wsName).ChartObjects.Count & " charts"
    For Each c In Worksheets(wsName).ChartObjects
        If c.Name = chartName Then
            c.Delete
        End If
    Next

    ' add log chart
    With Worksheets(wsName).Shapes.AddChart2(227, xlLine)
        .Chart.Parent.Name = chartName
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = chartName
        .Chart.SetSourceData Source:=srcRange
        .Chart.FullSeriesCollection(1).Trendlines.Add _
            Type:=xlMovingAvg, _
            Period:=2, _
            Forward:=0, _
            Backward:=0, _
            DisplayEquation:=0, _
            DisplayRSquared:=0, _
            Name:="2 per. Mov. Avg. (Series1)"
        .Chart.ClearToMatchStyle
        .Chart.ChartStyle = 233
        With .Chart.FullSeriesCollection(1).Trendlines(1).Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.25
            .Transparency = 0
        End With
    End With

    ' release memory
    Set srcRange = Nothing

End Sub

By the way, I'm using Excel 2016. So I'm not sure the code compatibility. Please tell me if there's any error.

References

• Peltier Tech Blog - Quick Excel Chart VBA Examples

• Peltier Tech Blog - Naming an Excel Chart

• MSDN - Worksheets Object (Excel)

StackExchange Code Review Q#124553, answer score: 4