There is usually a case where in we need to add or create charts but is a pain if there are some charts to be created.

OrderDate Region Rep Item Units UnitCost Total
1/6/2016 East Jones Pencil 95 1.99 189.05
1/23/2016 Central Kivell Binder 50 19.99 999.50
2/9/2016 Central Jardine Pencil 36 4.99 179.64
2/26/2016 Central Gill Pen 27 19.99 539.73
3/15/2016 West Sorvino Pencil 56 2.99 167.44
4/1/2016 East Jones Binder 60 4.99 299.40
4/18/2016 Central Andrews Pencil 75 1.99 149.25
5/5/2016 Central Jardine Pencil 90 4.99 449.10
5/22/2016 West Thompson Pencil 32 1.99 63.68
6/8/2016 East Jones Binder 60 8.99 539.40
6/25/2016 Central Morgan Pencil 90 4.99 449.10
7/12/2016 East Howard Binder 29 1.99 57.71
7/29/2016 East Parent Binder 81 19.99 1,619.19
8/15/2016 East Jones Pencil 35 4.99 174.65
9/1/2016 Central Smith Desk 2 125.00 250.00

Lets say we want to create chart for each of the Reps, Ie, One chart for each and having a status Item wise.

Please refer to my this post on how to build a summary.

Add Summary – VBA Excel

This is what we are looking for. But for all Reps and next time, they may increase / Decrease or make a sale of a new item?

ChrtReq.JPG

I would highly recommend downloading this file the run side by side to understand how this works. make-new-charts-vba

This is a modification of How to create summary so if you are uncomfortable, please see that link and understand that first.  Add Summary – VBA Excel

‘———————————————————-

Sub MySummary()

‘ let’s start by assigning objects the sheets to objects.

Dim SummarySheet As Worksheet
Dim SourceSheet As Worksheet

Set SourceSheet = ActiveWorkbook.Worksheets(“Data”)
‘ Lets add the Region

Dim Reps() As String
Dim Items() As String
‘ lets assign the unique reps and Items to array

ReDim Reps(0)
ReDim Items(0)

‘ Lets find the column names

Reps_Column = getColFromAddr(getAddress(SourceSheet, “Rep”))
Items_Column = getColFromAddr(getAddress(SourceSheet, “Item”))
Unit_Column = getColFromAddr(getAddress(SourceSheet, “Units”))
Total_Column = getColFromAddr(getAddress(SourceSheet, “Total”))
‘ Assign the first element to array

Reps(0) = SourceSheet.Cells(2, Reps_Column)
Items(0) = SourceSheet.Cells(2, Items_Column)

‘ Lets start a loop to assign unique from column to array

‘ i starts from 3 since the element at 2 was already assigned to array.

For i = 3 To SourceSheet.UsedRange.Rows.Count

found = 0

For j = 0 To UBound(Reps())

If SourceSheet.Cells(i, Reps_Column) = Reps(j) Then
found = 1
Exit For
End If

Next j

If found = 0 Then
mybound = UBound(Reps()) + 1
ReDim Preserve Reps(mybound)
Reps(mybound) = SourceSheet.Cells(i, Reps_Column)

End If

Next i

‘ For sake of simplicity lets use loop again, this can be merged above once you become comfortable

For i = 3 To SourceSheet.UsedRange.Rows.Count

found = 0

For j = 0 To UBound(Items())

If SourceSheet.Cells(i, Items_Column) = Items(j) Then
found = 1
Exit For
End If

Next j

If found = 0 Then
mybound = UBound(Items()) + 1
ReDim Preserve Items(mybound)
Items(mybound) = SourceSheet.Cells(i, Items_Column)

End If

Next i
Sheets.Add
ActiveSheet.Name = “Summary VBA”

Set SummarySheet = ActiveWorkbook.Worksheets(“Summary VBA”)

‘ Create Headers

SummarySheet.Cells(1, 1) = “Rep”
SummarySheet.Cells(1, 2) = “Items”
SummarySheet.Cells(1, 3) = “Units”
SummarySheet.Cells(1, 4) = “Total”

‘ lets add the Reps and Items to summary sheet

LastRow = 2

For i = 0 To UBound(Reps())

SummarySheet.Cells(LastRow, 1) = Reps(i)

For j = 0 To UBound(Items())

UnitSum = 0
TotalSum = 0

SummarySheet.Cells(LastRow, 2) = Items(j)

For k = 2 To SourceSheet.UsedRange.Rows.Count

If SourceSheet.Cells(k, Reps_Column) = Reps(i) Then

If SourceSheet.Cells(k, Items_Column) = Items(j) Then

UnitSum = UnitSum + SourceSheet.Cells(k, Unit_Column)
TotalSum = TotalSum + SourceSheet.Cells(k, Total_Column)

End If

End If

Next k
SummarySheet.Cells(LastRow, 3) = UnitSum
SummarySheet.Cells(LastRow, 4) = TotalSum
LastRow = LastRow + 1

Next j
Next i
End Sub

‘———————————————————-

Chart?????

Now comes the tricky part, We do not know the code on how to make a chart, Problem. No

The Macro Recorder comes to rescue.

Here is an excellent demo on how to use recorder

Macro Recorder

Select cells – B2 to D6 and press the record button

macro

Now Click insert and the 2D column chart that you e want to make.

Chartmake.png

Notice that a preview emerges when you hover over the types of chart.

This the code that we get

Sub Macro1()

‘ Macro1 Macro


ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range(“‘Summary VBA’!$B$2:$D$6”)
End Sub

Do you notice the last line ?  in Range we have  – Sheet name – and range that we selected.

Let’s add this code to a new macro with loops to run and make it on a separate sheet.

‘———————————————

Sub MakeCharts()
Dim ChartSheet As Worksheet
Dim SummarySheet As Worksheet

Sheets.Add

Set SummarySheet = ActiveWorkbook.Worksheets(“Summary VBA”)
Set ChartSheet = ActiveWorkbook.Worksheets(ActiveSheet.Name)

ChartSheet.Name = “Charts – VBA”

StartRow = 0

For i = 2 To SummarySheet.UsedRange.Rows.Count

If StartRow = 0 Then StartRow = i

If SummarySheet.Cells(i + 1, 1) <> “” Then

EndRow = i
MySelection = “‘” & SummarySheet.Name & “‘!” & “$B$” & StartRow & “:$D$” & EndRow

‘ The macro autocode here
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range(MySelection)

‘ See how be programmed MySelection above

StartRow = 0
EndRow = 0

End If

Next i

End Sub

‘———————————————

Look closely and all charts will be created one over the other. This is not what we want.

Here is handy code to regarrange them

numChartsInAColumn = 2
nCount = 0

For Each myChart In ActiveSheet.ChartObjects
myChart.Width = 450
myChart.Height = 200

myChart.Top = (nCount Mod numChartsInAColumn) * myChart.Height + 1
myChart.Left = Int(nCount / numChartsInAColumn) * myChart.Width + 1

nCount = nCount + 1
Next

AllCharts.JPG

Can you now add a title to chart?

Tip: use recorder to find the code.

Here is the source code and the full file. You can make separate macros or combine them to run in one go, your prerogative.

Make new Charts – VBA.xls

It’s my experience to make each part separately and after it works move to next and join in the end. Maybe in next posts, I ‘ll show that.

Next, I ‘ll post one of the complex applications that I developed and then step by step break down what went into developing it. It can be a good example to give ideas.

It was a prototype to develop data collection application.

In case you feel that any concept is hurried, pls go to my earlier post to find a common platform on which this is built.

Find a column by name – VBA Excel

Unique Array from a column – VBA / Excel

Add Summary – VBA Excel

Cheers

CuriousJatin

Advertisements