Excel 2010 VBA 绘图宏

Excel 2010 VBA Plotting graphs macro

提问by James

I need to plot a line graph of a set of data. I know the column containing the time series (C26 to the bottom) and I'll know the column of the data from the button calling it and the row the data starts in (26). The labels will be in row 26. The button will be in row 24. The data contains no blank spaces.

我需要绘制一组数据的折线图。我知道包含时间序列的列（C26 到底部），我会知道来自调用它的按钮的数据列以及数据在 (26) 中开始的行。标签将在第 26 行。按钮将在第 24 行。数据不包含空格。

The graph needs to have the data label in. Here's how far I've got, any suggestions, please let me know! At the moment it only plots 1 against the time axis.

图表需要有数据标签。这是我得到的程度，任何建议，请告诉我！目前它只在时间轴上绘制 1。

Sub GraphTest()
Dim xaxis As Range
Dim yaxis As Range
Dim fullRange As Range
Dim topcell As Range
Set xaxis = Range("$B", Range("$B").End(xlDown))
Set yaxis = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Set yaxis = Range(Cells(yaxis.Row, yaxis.Column).Offset(2, 0), Cells(yaxis.Row, yaxis.Column).Offset(2, 0).End(xlDown))
Set topcell = ActiveSheet.Buttons(Application.Caller).TopLeftCell

Set fullRange = Union(xaxis, yaxis)
fullRange.Select
topcell.Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=fullRange
End Sub()

采纳答案by James

Never got this to work too well, but here's the solutions I've had to go with,

从来没有让它工作得很好，但这是我不得不采用的解决方案，

Sub Graph()
Dim xaxis As Range
Dim yaxis As Range
Dim temp As Range

Dim total As Range

Set yaxis = ActiveSheet.Buttons(Application.Caller).TopLeftCell
yaxis.Select

Set yaxis = Range(Cells(yaxis.Row, yaxis.Column), Cells(yaxis.Row, yaxis.Column))
yaxis.Select
Set temp = yaxis

yaxis.Offset(2, 0).Select

ActiveCell.Activate
ActiveCell.Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
Set yaxis = Range(yaxis.Offset(2, 0), ActiveCell.Offset(-1, 0))
yaxis.Select

Range("$b").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Activate
Loop
Set xaxis = Range("$b", ActiveCell.Offset(-1, 0))
xaxis.Select
Set total = Union(xaxis, yaxis)
total.Select
temp.Offset(2, 0).Activate
temp.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=total

回答by Jean-Fran?ois Corbett

Things get much easier if you create and control your series individually, rather than shoving your entire dataset in at once with SetSourceData. Example:

如果您单独创建和控制系列，而不是使用SetSourceData. 例子：

Dim xaxis As Range
Dim yaxis As Range
Set xaxis = Range("$B", Range("$B").End(xlDown))
Set yaxis = Range("$C", Range("$C").End(xlDown))

Dim c As Chart
Set c = ActiveWorkbook.Charts.Add
Set c = c.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With c
    .ChartType = xlLine
    ' set other chart properties
End With

Dim s As Series
Set s = c.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis
    ' set other series properties
End With

---

---

EDIT

编辑

So-called "Line charts" don't always behave as you would expect. This is a case in point. When time is on the x axis and it's formatted as dd/mm/yyyy hh:mm, line charts force all points that share the same date (day) into the same bin, regardless of the time of day. Why did they make it like this? I have no clue. Line charts do all kinds of other crazy stuff.

所谓的“折线图”并不总是如您所愿。这是一个很好的例子。当时间在 x 轴上并且它的格式为 dd/mm/yyyy hh:mm 时，折线图强制所有共享相同日期（天）的点进入同一个 bin，而不管一天中的什么时间。他们为什么要这样做？我没有线索。折线图可以做各种其他疯狂的事情。

I'm pretty sure that what you want is a scatter plot. In fact, what you want is almost alwaysa scatter plot, and almost never a line chart.

我很确定您想要的是散点图。实际上，您想要的几乎总是散点图，而几乎不是折线图。

Here's an example. I made my own temperature data, but kept your datetimes.

这是一个例子。我制作了自己的温度数据，但保留了您的日期时间。

Produced using this code:

使用此代码生成：

Dim xaxis As Range
Dim yaxis As Range
Set xaxis = Range("$B", Range("$B").End(xlDown))
Set yaxis = Range("$C", Range("$C").End(xlDown))

Dim c As Chart
Set c = ActiveWorkbook.Charts.Add
Set c = c.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With c
    .ChartType = xlXYScatterLines 'A scatter plot, not a line chart! 
    ' set other chart properties
End With

Dim s As Series
Set s = c.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis
    ' set other series properties
End With

With c.Axes(xlCategory)
    .MajorUnit = 0.125
End With

With c
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature"
    .HasLegend = False
End With