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

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

Categories

HOME
solr
rabbitmq
selenium-chromedriver
monitoring
bloomberg
castle-windsor
etcd
linuxmint
quandl
apache-spark-2.0
fluent-assertions
relation
angularjs-ng-route
esapi
sensenet
zillow
bootstrap-select
flip-flop
xlwings
summary
cookie-session
webcam
geckofx
neo4jclient
cloudfoundry-uaa
nsdate
psftp
lektor
thinktecture-ident-model
responsive-filemanager
card-flip
oracle-ebs
c#-6.0
protactor
gold-parser
cakephp-2.x
dwt
wistia
subtitle
fluentbootstrap
jsdoc3
wso2dss
unicode-string
jquery-events
asana-connect
munin
express-stormpath
stdio
deferred-loading
parrot
pxe
reducers
unreal-blueprint
user-mode-linux
kdiff3
f#-interactive
pdf-extraction
javascript-globalize
android-context
eofexception
authentication-flows
jaxb2-maven-plugin
pig-udf
lightroom
review
mysql-num-rows
ember-testing
httplib
tokumx
mux
kiwi
restriction
firstdata
mathgl
apiaxle
styledtext
mapstraction
inter-process-communicat
apprequests
cgimagesource
iwebkit
dotfuscator
jax-rpc
hyperterminal
jlayer
clang-complete
viewstack
xemacs
testdriven.net
cohesion
log5j
silverlight-embedded

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