Creating and positioning graphs in a for loop VBA

Question

I have an excel sheet that I am wanting to extract data from and represent the data as graphs on a new tab called "graphs".

I am able to generate the graphs quite easily, but when they are created they are all stacked upon each other as I am unable to find a decent and simple way to give them set position that I can increment after each loop completion.

I have been trying activechart.location commands but cannot seem to find the right one.

Here is my code at the moment. This generates the charts and outputs them to the current excel sheet.

Dim i As Integer
i = 30
Start = Start + 2 (global variable)
finish = finish + 2 (global variable)
For i = 30 To 56
   Range("ci:bbi").Select
   ActiveSheet.Shapes.AddChart.Select
   ActiveChart.HasTitle = True
   ActiveChart.ChartTitle.Text = Cells(i, 2).Value
   ActiveChart.SourceData Source:=Range(Cells(i, Start), Cells(i, finish))
   ActiveChart.ChartType = xlColumnStacked
Next

I am completely new to vba and even this small block of code is taking a lot of effort! My question is, what can i put in here, to give a position for each chart as i create it to position it. Preferably a new variable I can increment.

Thanks in advance

EDIT: Forgot to mention, I am not able to do this with a macro as I need the page to be used multiple times, using a macro means that the graph names are used to identify them, so it becomes impossible to track the graphs after the first generation of graphs as the chart names continue to go higher and higher.

Nimantha · Accepted Answer · 2022-01-07 04:36:21Z

1

Try this

Sub Sample()
    Dim i As Long, nTop As Long, nLeft As Long
    Dim strChrt As String

    i = 30: nLeft = 20: nTop = 20
    
    Start = Start + 2: finish = finish + 2
    
    For i = 30 To 56
       ActiveSheet.Shapes.AddChart.Select
       ActiveChart.HasTitle = True
       ActiveChart.ChartTitle.Text = Cells(i, 2).Value
       ActiveChart.SetSourceData Source:=Range(Cells(i, Start), Cells(i, finish))
       ActiveChart.ChartType = xlColumnStacked
       
       strChrt = Trim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))

       ActiveSheet.Shapes(strChrt).Left = nLeft
       ActiveSheet.Shapes(strChrt).Top = nTop

       '~~> Increment the next `Top` placement for the chart
       nTop = nTop + ActiveSheet.Shapes(strChrt).Height + 20
    Next
End Sub

I have created two variables nTop and nLeft which will define the position of the newly created graphs.

edited Jan 7, 2022 at 4:36

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Jul 10, 2012 at 14:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

Collectives™ on Stack Overflow

Creating and positioning graphs in a for loop VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related