Introducing My Left Brain

I have two sides to my brain. One is the right-side that you know and love – the one that’s responsible for my creative output (aka all these words and stuff). The other is the left-side – the one that I use for my day-job as a Geek (not my official job title).

Gird your loins and take a gander at what my left-brain produced yesterday:

Sub GraphGen()

‘ Macro for generating graphs automatically.
    
    ‘determine last row of data
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row
   
    ‘loop through rows
    Dim i As Integer
    For i = 0 To LastRow – 11 Step 10
   
        ‘set variables
        Dim RangeCover As Range
        Set RangeCover = ActiveSheet.Range(“J2:Q11”).Offset(i, 0)
        Dim RangeXValues As Range
        Set RangeXValues = ActiveSheet.Range(“D2:D8”).Offset(i, 0)
        Dim RangeValues As Range
        Set RangeValues = ActiveSheet.Range(“E2:E8”).Offset(i, 0)
        Dim RangeSeriesName As Range
        Set RangeSeriesName = ActiveSheet.Range(“H10”).Offset(i, 0)
        Dim RangeChartName As Range
        Set RangeChartName = ActiveSheet.Range(“H9”).Offset(i, 0)
        Dim RangeXAxis As String
        RangeXAxis = “Week Number”
        Dim RangeYAxis As String
        RangeYAxis = “Value”
        Dim RangeArea As Range
        Set RangeArea = ActiveSheet.Range(“H11”).Offset(i, 0)
        Dim RangeAreaColour As String
        If RangeArea.Value = “Customer” Then
            RangeAreaColour = RGB(205, 172, 230)
        ElseIf RangeArea.Value = “Cost” Then
            RangeAreaColour = RGB(255, 204, 0)
        ElseIf RangeArea.Value = “Network Quality” Then
            RangeAreaColour = RGB(83, 169, 255)
        Else
            RangeAreaColour = RGB(255, 0, 0)
        End If
       
       
        ‘create charts
        ActiveSheet.Shapes.AddChart2(332, xlLineMarkers, RangeCover.Left, RangeCover.Top, RangeCover.Width, RangeCover.Height).Select
        ActiveChart.SetSourceData Source:=Range(“A1”)
        ActiveChart.FullSeriesCollection(1).XValues = RangeXValues
        ActiveChart.FullSeriesCollection(1).Values = RangeValues
        ActiveChart.FullSeriesCollection(1).Name = RangeSeriesName
       
        ‘set colour of data line
        ActiveChart.FullSeriesCollection(1).Select
        With Selection.Format.Line ‘line
            .ForeColor.RGB = RangeAreaColour
        End With
       
        With Selection.Format.Fill ‘data point
            .ForeColor.RGB = RangeAreaColour
        End With
       
        ‘add Trendline
        ActiveChart.FullSeriesCollection(1).Trendlines.Add
        ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
        With Selection.Format.Line
            .DashStyle = msoLineSolid
            .Weight = 0.75
        End With
       
        ‘turn on data labels and positions them above data
        ActiveChart.FullSeriesCollection(1).ApplyDataLabels
        ActiveChart.FullSeriesCollection(1).DataLabels.Position = xlLabelPositionAbove
       
        ‘rename chart
        Dim ChartName As String
        ChartName = RangeChartName.Value
        ActiveChart.Parent.Name = ChartName
       
        ‘add axis labels
        ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
        Selection.Caption = RangeXAxis
        ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
        ActiveChart.Axes(xlValue).AxisTitle.Select
        Selection.Caption = RangeYAxis
       
        ‘format axes and data labels – no decimal
        ActiveChart.Axes(xlCategory).Select ‘horizontal axis
        Selection.TickLabels.NumberFormat = “#,##0”
‘        ActiveChart.Axes(xlValue).Select ‘vertical axis
‘        Selection.TickLabels.NumberFormat = “#,##0”
‘        ActiveChart.FullSeriesCollection(1).DataLabels.Select ‘data labels
‘        Selection.NumberFormat = “#,##0”

    Next i
   
    MsgBox “All done.”

End Sub

I love that I can do stuff like this and do stuff like that too. It kinda means that I’m balanced. I can do the intuitive, creative stuff and I can also do the intellectual, logical stuff.

So there you go – that’s what I do for money.

Have a nice day.

Advertisements

36 thoughts on “Introducing My Left Brain

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s