在for循环VBA中创建和定位图形 [英] Creating and positioning graphs in a for loop VBA

问题描述

我有一个excel表，我想从数据中提取数据，并将其表示为一个名为图的新选项卡。

我能够生成图表非常容易，但是当它们被创建时，它们都被堆叠在一起，因为我无法找到一个体面和简单的方式来给他们设置位置，我可以在每个循环完成后增加。

我一直在尝试 activechart.location 命令，但似乎找不到正确的一个。

这是我现在的代码。这将生成图表并将其输出到当前的Excel表。

  Dim i As Integer 
i = 30 
开始=开始+ 2（全局变量）
完成=完成+ 2（全局变量）
对于i = 30到56 
范围（ci：bbi）。选择
 ActiveSheet.Shapes.AddChart.Select 
 ActiveChart.HasTitle = True 
 ActiveChart.ChartTitle.Text = Cells（i，2）.Value 
 ActiveChart.SourceData Source：= Range（Cells（i ，开始），Cells（i，finish））
 ActiveChart.ChartType = xlColumnStacked 
下一个
  

$ b $我完全是vba的新手，甚至这个小块代码正在付出很大的努力！
我的问题是，我可以在这里放什么，为每个图表给出一个位置，我创建它来定位它。

提前感谢

编辑：忘了提一下，我不能用宏做这个，因为我需要多次使用这个页面，使用一个宏就意味着图形名称被用来标识它们，所以在第一个图表名称的生成继续越来越高。

解决方案

尝试这个

  Sub Sample（）
 Dim i As Long，nTop As Long，nLeft As Long 
 Dim strChrt As String 
 
i = 30：nLeft = 20：nTop = 20 
 
开始=开始+ 2：完成=完成+ 2 
 
对于i = 30到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 
 
'~~>增加图表的下一个Top位置
 nTop = nTop + ActiveSheet.Shapes（strChrt）.Height + 20 
下一个
 End Sub 
  

我创建了两个变量 nTop 和 nLeft 它将定义新创建的图形的位置。

HTH

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.

解决方案

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.

HTH

这篇关于在for循环VBA中创建和定位图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！