时间戳

首页 > 解决方案 > 将单个数据点添加到系列并在 VBA 中指定其属性

excel - 将单个数据点添加到系列并在 VBA 中指定其属性

问题描述

背景

我从 python in 接收 StdOut 并解析信息，所以我有一组有 N many (x,y) 坐标属于 M many 系列。我希望能够单独绘制每个点并控制它们各自的属性，即颜色、形状、标记大小等。

但是，到目前为止，我看到的所有示例都涉及必须一次绘制所有值，然后循环遍历该系列。我想要的功能看起来像下面的伪代码。

.SeriesCollection(seriesNumber).AddPoint(PtNum).xvalues = x
.SeriesCollection(seriesNumber).AddPoint(PtNum).values = y
.SeriesCollection(seriesNumber).Point(PtNum).markersize = some integer

更多详细信息

我从 python 接收的数据遵循这种格式，存储为变体。

SeriesName, Point# in the series, X or O, SeriesNumber, xcoord,ycoord, instance number

O代表需要一个新系列，X代表一个已经存在的系列

instance number是一个点被重复的次数

Point#是系列中某个坐标的点索引

示例数据行

series1,1,O,1,0.25,64,1
series1,2,X,1,0.25,64,2
series2,1,O,2,0.3,90,1

当前代码

    Dim pyData() As Variant

    pyData = Connect_2py.recv_Data(xArgs, yArgs, seriesArgs) 'exec python script and returns data
    For i = 0 To UBound(pyData) - 1

        py_lineData = Split(pyData(i), ",")
        createSeries = StrComp(py_lineData(2), "O", vbBinaryCompare) = 0
        seriesName = py_lineData(0)
        seriesNumber = CInt(py_lineData(3))

        If createSeries Then
            .SeriesCollection.NewSeries
            .SeriesCollection(seriesNumber).Name = seriesName
            Debug.Print ("Hooray new series has been made")
        End If

       'ENTER CODE HERE TO ADD EACH DATAPOINT TO A SPECIFIED SERIES
    Next

标签： excelvbaplot

解决方案

好的，所以它似乎是可行的。我很惊讶扩展一系列点数组并没有丢失现有的标记属性。

Sub Tester()
    Const N = 10
    Dim i As Long, x(), y(), sz(), cht As Chart, ser As Series

    ReDim x(1 To N)
    ReDim y(1 To N)
    ReDim sz(1 To N)

    'create some initial data
    For i = 1 To 10
        x(i) = i
        y(i) = i ^ 1.5
        sz(i) = 2 + (Rnd() * 10)
    Next i

    'create a series, populate with the initial data
    '   and set some marker sizes
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set ser = cht.SeriesCollection.NewSeries()
    With ser
        .XValues = x
        .Values = y
        For i = 1 To 10
            .Points(i).MarkerSize = sz(i)
        Next i
    End With

    'extend that series one point at a time
    For i = 1 To 5
        ExtendSeries ser, i * 10, i * 10, i * 5
    Next i

End Sub

'Extend the data in a series by adding a new X/Y pair
'Also shows how to configure (eg) the added point size 
Sub ExtendSeries(ser As Series, xVal, yVal, sz)
    Dim x, y, newlen As Long
    With ser
        x = .XValues
        y = .Values
        newlen = UBound(x) + 1

        ReDim Preserve x(newlen)
        x(newlen) = xVal

        ReDim Preserve y(UBound(x) + 1)
        y(newlen) = yVal

        .XValues = x
        .Values = y
        '.Points(newlen).MarkerSize = sz        'edit: for some reason this doesn't work...
        .Points(.Points.Count).MarkerSize = sz  '...but this does
    End With
End Sub

推荐阅读