循环浏览 Excel 表格并创建图形

Question

我在一个工作表中有多个表，我需要遍历表(列表对象(并生成相应的折线图。我尝试为每个循环使用，但它不起作用： 如何使用"for each"循环生成图形？如何将每个列表对象作为我的图表的范围引用？

Sub chart_create()
Dim tbl As listobject
'Loop through each sheet and table in the workbook
For Each tbl In ActiveSheet.ListObjects         
Call graph
End Sub
Next tbl
End Sub
'macro to generate charts 
Sub graph()
Dim rng As Range
Dim cht As ChartObject
'how do i change this to reference corresponding list object 
Set rng = Selection   
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
End Sub

Answer 1

将 ListObject 的范围作为参数传递到第二个子例程中：

Sub chart_create()
Dim tbl As listobject
'Loop through each sheet and table in the workbook
For Each tbl In ActiveSheet.ListObjects         
Call graph tbl.Range
Next tbl
End Sub
'macro to generate charts
Sub graph(rng as range)     
Dim cht As ChartObject
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
End Sub

Answer 2

首先，看起来你有一个额外的End Sub。Next tbl必须先于End Sub，否则将永远无法实现。

其次，您需要将对表的引用传递到图形函数中。

Sub chart_create()
Dim tbl As listobject
'Loop through each sheet and table in the workbook
For Each tbl In ActiveSheet.ListObjects         
Call graph(tbl)
Next tbl
End Sub

然后。。。

Sub graph(tbl As ListObject)
'Make your graph here, referencing the tbl you passed in
End Sub

编辑：最后，为了清楚起见，您的评论说您正在"循环浏览工作簿中的每个工作表和表格"，但您实际上只是在循环浏览活动工作表上的列表对象。 如果要遍历每个工作表，则需要在现有循环之外有一个额外的循环，例如：

For Each ws In Worksheets
'For Each tbl In ws.ListObjects....
Next ws