在 for 循环 VBA 中创建和定位图形
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11415063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating and positioning graphs in a for loop VBA
提问by bealor
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".
我有一个 Excel 表格，我想从中提取数据并将数据表示为名为“图形”的新选项卡上的图形。
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.locationcommands but cannot seem to find the right one.
我一直在尝试activechart.location命令，但似乎找不到正确的命令。
Here is my code at the moment. This generates the charts and outputs them to the current excel sheet.
这是我目前的代码。这将生成图表并将它们输出到当前的 Excel 工作表。
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.
我对 vba 完全陌生，即使是这一小块代码也需要付出很多努力！我的问题是，我可以在这里放什么，以便在创建每个图表以定位它时为每个图表提供一个位置。最好是我可以递增的新变量。
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.
编辑：忘了提，我不能用宏来做到这一点，因为我需要多次使用页面，使用宏意味着使用图形名称来识别它们，因此在之后跟踪图形变得不可能第一代图表随着图表名称不断走高。
回答by Siddharth Rout
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 nTopand nLeftwhich will define the position of the newly created graphs.
我创建了两个变量nTop，nLeft它们将定义新创建的图形的位置。
HTH
HTH
