CODEWITHSUNDEEP
excelvba
Sparta
Sparta

Reputation: 35

Excel VBA macro for generating graphs from multiple tables - nested macro

I have a macro to generate graph automatically based on my selection. This works fine if I select one table. I have to select one table at a time to generate the graph. Is there a way I can set up a nested macro to select the worksheet and generate the individual chart for each table?

Any inputs will be appreciated. Thank you :)

macro to generate one chart

' Macro3 Macro ' Keyboard Shortcut: Ctrl+a

Sub graph()

Dim rng As Range
Dim cht As ChartObject

Set rng = Selection
rng(1, 1).Clear
Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=450, _
    Top:=ActiveCell.Top, _
    Height:=250)

'Give chart some data
cht.Chart.SetSourceData Source:=rng

'Determine the chart type
cht.Chart.ChartType = xlLine

'Ensure chart has a title
cht.Chart.HasTitle = True

'Change chart's title
cht.Chart.ChartTitle.Text = "My Graph"


'Add Legend to the Bottom
cht.Chart.SetElement (msoElementLegendBottom)

End Sub

Table 1: period BA BC BD BE 20174 4.1 4.1 4.1 4.1000 20181 4.0 4.5 5.0 5.0544 20182 4.0 5.3 6.5 6.2598 20183 3.9 5.8 7.6 7.4139 20184 3.8 6.3 8.5 8.5021 20191 3.8 6.6 9.3 9.1129 20192 3.8 6.9 9.7 9.5360 20193 3.8 7.0 10.0 10.0037 20194 3.7 7.0 9.9 9.8787 20201 3.8 6.9 9.7 9.6216 20202 3.9 6.8 9.5 9.3975 20203 4.0 6.6 9.2 9.2134 20204 4.0 6.5 8.9 9.0484 20211 4.1 6.3 8.6 8.8423

Table 2: period CA CC CD CE 20174 -5.38462 -5.3846 -5.3846 -5.3846 20181 -6.97674 4.6512 16.2791 17.5446 20182 -2.43902 29.2683 58.5366 52.6777 20183 -2.50000 28.8889 52.0000 46.6822 20184 -5.00000 18.8679 30.7692 35.8204 20191 -2.56410 13.7931 22.3684 22.9164 20192 .00000 9.5238 14.1176 12.1615 20193 .00000 6.0606 7.5269 9.7749 20194 -2.63158 1.4493 2.0619 3.5928 20201 .00000 -1.4286 -3.0000 -3.8200 20202 5.40541 -2.8571 -4.0404 -4.8709 20203 5.26316 -4.3478 -5.1546 -4.2420 20204 2.56410 -4.4118 -6.3158 -3.7151 20211 2.50000 -4.5455 -6.5217 -4.0282

Upvotes: 0

Views: 391

Answers (1)

user465960
user465960

Reputation: 1

  1. Format each cell range you want to use as the source data for the chart, I would recommend formatting as Excel tables via CTRL+T on the keyboard.

  2. Rename each table. Use Table Design Ribbon > Name where you can change the table names away from defaults of Table1, Table2, ...

  3. In VBA, reference or loop through each of the tables in the workbook, they are ListObjects according to the excel VBA reference.

    Dim ListObj As Excel.ListObject
Dim ws As worksheet

For Each ListObj In ws.ListObjects
    ' make a chart
Next ListObj

Other methods can work based around how you format/set up/refer to the range with your data.

You could make your own named ranges to refer to the tables, Table1:"A1:Z4" etc. then refer to those in the code or make a list of their addresses ("A1:Z4") ("AA1:AZ4") to include in the VBA, etc. These may work but offer less flexibility compared to formatting data as excel tables.

Upvotes: 0

Related Questions