excel - Assigned arrays of doubles to XLScatter chart series' .XValues & .Values properties; however .XValues get converted to strings somehow. Why?
问题描述
I have functional VBA code that creates a XYScatter chart of data provided to it in the form of an "X" array and a "Y" array (as opposed to data that exists inside a range on a worksheet). Both arrays are declared as doubles (i.e. Locals window lists the arrays as type "Double(# to #)" and each array element as type "Double"). They are assigned respectively to the .XValues and .Values properties of a series in the chart, and everything appears to be displayed as expected.
A separate sub attempts to adjust the .MinimumScale & .MaximumScale properties of the chart's axes scales based on the extent of the data it finds in .XValues and .Values, and it does so successfully for the Y-axis (the .Values). However, it fails for the X-axis. Somehow, although I've provided the same data types to those two properties, doubles passed to .XValues are getting converted into strings while those within .Values remain doubles. To be precise, they're listed respectively in the Locals window as arrays of "Variant/String" and "Variant/Doubles".
Thus, when I try to retrieve the min and max X values in order to set the X-axis scale Application.Max(.XValues)
all I ever get back is 0, presumably because Excel's Max function doesn't recognize the strings as numbers.
It's worth noting that if I attempt the same axis scaling on a chart with XY data from a range in a worksheet I don't encounter this issue, and when I use the Locals window to peer into such a chart it lists both the .XValues and .Values properties as arrays of Variant/Doubles.
Below I've pulled together the relevant pieces of my originally separate subs to troubleshoot and/or illustrate the issue. Please keep in mind that while it would appear the simple solution is to use the max of xArray to set the X-axis scale, my original code has creation of charts occurring in one sub and rescaling of charts occurring in another; xArray is only accessible by the first sub, and I'd like to keep it that way.
Sub test()
Dim MaxX As Double, MaxY As Double, MinX As Double, MinY As Double
Dim xArray(1 To 5) As Double, yArray(1 To 5) As Double
Dim CHRT As Chart, SER As Series
xArray(1) = 0: xArray(2) = 0.000001: xArray(3) = 9.99999
xArray(4) = 10: xArray(5) = 20
yArray(1) = 0: yArray(2) = 0.000001: yArray(3) = 9.99999
yArray(4) = 10: yArray(5) = 20
Set CHRT = ActiveSheet.Shapes.AddChart2(-1, xlXYScatter).Chart
With CHRT
.SeriesCollection.NewSeries
Set SER = .SeriesCollection(1)
With SER
.XValues = xArray
.Values = yArray
End With
End With
With Application
MaxX = .Max(SER.XValues)
MinX = .Min(SER.XValues)
MaxY = .Max(SER.Values)
MinY = .Min(SER.Values)
End With
Stop '...to check Locals window; we expect MaxX = MaxY
End Sub
解决方案
I've been unable to determine why I can't get the results I expect so I've implemented a work-around which, in the context of my original troubleshooting code would require declaring two new variables...
Dim tempX() As Double, ii As Integer
and then replacing...
With Application
MaxX = .Max(SER.XValues)
MinX = .Min(SER.XValues)
MaxY = .Max(SER.Values)
MinY = .Min(SER.Values)
End With
...with...
ReDim tempX(LBound(SER.XValues) To UBound(SER.XValues))
For ii = LBound(SER.XValues) To UBound(SER.XValues)
tempX(ii) = CDbl(SER.XValues(ii))
Next ii
With Application
MaxX = .Max(tempX)
MinX = .Min(tempX)
MaxY = .Max(SER.Values)
MinY = .Min(SER.Values)
End With
Now MaxX is being correctly reported as 20 rather than 0.
推荐阅读
- javascript - 如何串行运行基于承诺的功能
- point-cloud-library - 如何使用 pcl 将 3D 点云离散化为 xy 平面上的 2D 网格,而不是使用体素网格?
- php - 当我在 laravel 5.1 API 中使用邮递员运行 API 时,发布方法不起作用
- python - Python列表取决于长度
- java - 如何从分布式环境访问位于 S3 存储桶中的 DBFS 文件?
- r - 在 R 中使用 Data.table 中的 Data.table
- javascript - 当资源太大时,如何绑定事件处理程序以请求关闭套接字的承诺?
- nunit-3.0 - NUnit 3:如何全局设置 CurrentDirectory 以测试程序集的目录
- php - 如何将下拉选择从字符串转换为布尔值
- javascript - 通过 GmailApp.sendEmail 在电子邮件的 html 正文中使用电子表格值