excel


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!

Related Links

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

Categories

HOME
sails.js
sendmail
mono
fiware
attributes
mips32
linuxmint
pymc
spring-transactions
openmeetings
double
shogun
jlabel
pixel
j
sirikit
build-automation
selenium-rc
pki
cookie-session
mikrotik
mpeg
eof
navigation-drawer
android-appbarlayout
pi4j
servicestack.redis
ggiraph
cassandra-3.0
asn.1
powerdesigner
smartassembly
grayscale
jenkinsfile
background-position
dwt
gitversion
inputstreamreader
proof-of-correctness
mypy
generated
orders
nosuchelementexception
optional
gyp
date-formatting
unite.vim
code-collaborator
video-recording
display-bugs
devpartner
agile-project-management
test-and-target
amazon-mobile-analytics
into-outfile
graphic-effects
android-6.0.1
johnny-five
traffic-simulation
jedit
msbuildcommunitytasks
delphi-5
elmah.mvc
apache-commons
authentication-flows
smslib
childviewcontroller
google-client
geographical-information
android-view
rgs
image-manipulation
navigationbar
class-relationship
desktop-shortcut
xcode-instruments
code-metrics
syntactic-sugar
angularjs-ng-show
pixate
restriction
datacontext
resharper-7.1
pivotitem
inference
inter-process-communicat
c++builder-6
mat-file
tandem
panning
outlook-2003
windmill
pull-to-refresh
contentflow
memory-profiling
mono-service
snk
whiteboard

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App