XYScatter Plot Incorrect XAxis when Drawn in Visual Basic?
I'm trying to create a scatter plot using visual basic, the y axis being numerical values and the x axis being dates. The intention is for the plot to contain multiple series. Here is the relevant code: ActiveWorkbook.Charts.Add ActiveChart.ChartArea.Select With ActiveChart .ChartType = xlXYScatter .HasTitle = True .ChartTitle.Text = "Time Trend of Data" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates" .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "m/d/yy;#" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total Time" .Legend.Position = xlLegendPositionBottom End With ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count) ActiveSheet.Name = "Time Trend " + CStr(currTT) ' This is just to make sure the new sheet does not have the same name After I generate some data, I attempt to plot it with a loop. The arrays I use are chartLabels - which is the name of each series, chartData - a 3d array with several data points for each series and xval and yval - arrays built from the chartData array which are plotted against each other: For j = 0 To UBound(chartLabels) If IsEmpty(chartLabels(j)) Then Exit For Erase xval Erase yval ReDim Preserve xval(0 To 0) ReDim Preserve yval(0 To 0) xval(0) = chartData(1, j, 0) yval(0) = chartData(2, j, 0) For i = 0 To UBound(chartData, 3) - 1 If Not IsEmpty(chartData(2, j, i + 1)) Then ReDim Preserve xval(0 To i + 1) ReDim Preserve yval(0 To i + 1) xval(i + 1) = chartData(1, j, i + 1) yval(i + 1) = chartData(2, j, i + 1) End If Next MsgBox (Join(xval, " || ")) ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(j + 1).XValues = xval ActiveChart.SeriesCollection(j + 1).Values = yval ActiveChart.SeriesCollection(j + 1).Name = main.chartLabels(j) Next The MsgBox Statement is included to view the array that I am passing as the XValues to my Scatter Plot. The Output of the Array from the first series looks like: All of these values are in date format. The number of series created varies. Also, the number of data points in each series can very depending on options that the user picks. However, the output that is generated on the scatter plot looks like: Everything on the graph is correct except the xaxis is scaled to the position of each data point in its respective series, NOT the actual date (i.e. 1/0/00 is actually 0 in date format and 3/10/00 is 70 because there are about 70 data points). I've tried using xlCategoryScale and xlAutomaticScale as my CategoryType. I've tried using CDate() on each of my xvals as well as CStr(). I've tried outputting different arrays for the XValues. Nothing works. I have my suspicions that the problem is related to the fact that I'm trying to graph multiple series of data. But, if anybody could tell me the actual issue and/or a way around this issue, I would appreciate it very much. Thank you in advance!
I'm not sure why I didn't think of this... Two hours after I posted this I discovered that though using CStr() and CDate() on my XValues was useless, CDbl() actually worked. I'm going to refrain from just deleting my question because I don't think this is very intuitive. I'm not sure why this worked because I'm still formatting the xaxis as .CategoryType = xlTimeScale. I added this bit of code to the code above (this is the code I had tested before with CStr() and CDate(), but I did not include it in the question): For k = 0 To UBound(xval) xval(k) = CDbl(xval(k)) Next This was added right before the MsgBox in the code from the question. This is the new output from the MsgBox for the XValues of the first series in the set: Note: These values are all in the 42000's because each whole number is equal to a day and this data is from 2015. 2015 - 1900 = 115 years 115 years * 365.25 days/year = 42003.75 'In reality 2015 = 42005 (because of what years were leap years, etc.) Finally, here is the actual output given the "Numerical" dates as xvalues in the scatter plot: I guess the lesson here is that the xaxis likes numbers and using anything else could turn out to be a royal pain. If anyone comes across this post, I hope it was helpful!
Excel Index & Match two columns while pulling data for a third
Excel VBA: Is it possible to read a large zip archive without copying the files?
Vba Excel - if value = value filtering and copy on corect sheet - speed up
Using a relative reference (offset) in a COUNTIF function in Excel VBA
Excel VBA - Copy from a sheet (scan for name) then insert it in another sheet
How to reference cell selections for hyperlinks?
Excel to voice cells' value whenever it is selected with VBA
ImportXML slow while importing data in excel table
Using VBA to Add Rows based on a cell value onto another worksheet
Excel PivotTable for counting words for female and male
Filter a PivotChart with Dates
Combobox drop down showing up in other sheets
Excel Marco set range.address
Excel VBA to update only one cell at one time
Excel VBA - Refer to a worksheet as a variable in an IF statement
Excel Formula to Lookup multiple columns