Create a dynamic excel chart VBA

Question

I would like to create a chart that looks like this

On my x axis i would like to have my dates so for example 2016-01-01 On my y axis there should be a a number on how much this event occurred on that date for example 4

So far so good how my data looks like that I am trying to create my chart above:

FailureLogStart
2017-01-09 18:20
2017-01-14 14:23
2017-01-14 15:14
2017-01-15 11:05
2017-01-16 09:36
2017-01-16 10:02
2017-01-16 10:23

This is the only column I can use to creat my chart so the values for the Y: axis should be the dates without hte time and the x axis are the number of occurrences per date as an example on the 14 there would be tow.

What I have done so far:

Sub Test()
    Range("Table_Query_from_WatchDog_DB_1[[#All],[FailureLogStart]]").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("'Controll & Data'!$C$18:$C$25")
    ActiveSheet.Shapes("Chart 6").IncrementLeft 518.4782677165
    ActiveSheet.Shapes("Chart 6").IncrementTop -308.4782677165
End Sub

What I have right now is the opposite that I would like to have the dates are on the Y:Axix and the occurrences are on the X:Axis

(Btw how can I select only a the date without the times)

@Wujaszkun not at all do you think that would make my life easier ? — Niko.K
– Niko.K, Commented Jan 23, 2017 at 10:48
The way i would do it is to set a Table where the source data will be stored, then connect a pivot table to it with (so the source range will change dynamically based on the record number in table) and finally add a pivot chart. It's a lot easier to set it up and maintain than doing it in VBA. — PSotor
– PSotor, Commented Jan 23, 2017 at 10:54

PSotor · Accepted Answer · 2017-01-23 12:11:01Z

1

As i already stated in the command to the first post i would use Pivot Table/Chart to show this data:

The short date is counted with the formula : =ROUNDDOWN([@FailureLogStart],0)

Pivot table configuration:

Row Labels -> Short Date

Values -> Count of Short Date

Source -> Table1 (put here the name of your table)

EDIT: Step by step Pivot Table preparation:

Prepare your input -> Format your data as Table

Mark your Table -> (Ribbon) Insert -> PivotTable:

a. Set Table/Range Field -> Table1 (or your table name)

b. At the bottom tick "Existing Worksheet" and put a cell address where you want to place the pivot.

c. Once done that a blank pivot will be inserted into your sheet. Now in the right panel drag and drop the "Short Date" to "Row labels" and "Values" fields.
Click on the pivot -> (Ribbon) Pivot Table Options -> Pivot Chart

edited Jan 23, 2017 at 12:11

answered Jan 23, 2017 at 11:04

PSotor

3461 gold badge4 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Niko.K Over a year ago

btw add this pice of code for the auto update of the pivot tabel : Private Sub Worksheet_Activate() Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable End Sub

PSotor Over a year ago

The chart is connected to the Pivot Table, so it will respond to pivot filters only. If you need to see the records behind the summary in pivot table just double click on the number, e.g in 2017-01-16 3 click on the 3 and you'll get a new sheet with the source data.

Collectives™ on Stack Overflow

Create a dynamic excel chart VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related