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!
VBA Excel: How to loop through Column B for cells containing alphanumeric or just numeric content?
Run-time error '50290': Application-defined or object-defined error Excel
VBA: saving to specific path based on cell values
how to extract only specific strings in VBA
Excel VBA: Communicating via named pipe
macro inserts the word TRUE in cell
Output difference of two columns excel with exclusion criteria
Excel VBA code reads wrong innerHTML code
Get the length of decimal places
Excel Formula to copy cell value to a column based on condition
Excel Webservice Function Yahoo Finance
Using a dynamic cell value in excel VBA
Excel VBA gives type mismatch on dividing decimal numbers after split
Get data from a website table to the excel sheet
VBA Set Dropdown value
VBA Access to Excel export, Error 1004 and 70 when re-running the script