trying to programmatically change chart data range with vb.net

  • Thread starter Thread starter doofy
  • Start date Start date
D

doofy

I'm trying to adjust the data range for a chart, based on how many rows
of data have been imported in. The data range will be in the same
worksheet as the chart.

rowEnrolling and rowCnt are integers, but using integers to concat to a
string has been working so far.

Here's my line of code:

ws.ChartObjects(1).chart.seriescollection(1).values = ws.Range("f" &
rowEnrolling & ":f" & rowCnt - 1)

and I get a "data type mismatch", I'm assuming that the
seriescollection(1).values is not matching the ws.range

Any pointers on this?
 
Well, rows go up to at least 65536, which is twice the largest integer.
Perhaps if you use Longs instead it might work. You could be really
compulsive and use CLng(rowCnt - 1) or whatever vb.net uses for CLng.

- Jon
 
Jon said:
Well, rows go up to at least 65536, which is twice the largest integer.
Perhaps if you use Longs instead it might work. You could be really
compulsive and use CLng(rowCnt - 1) or whatever vb.net uses for CLng.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
Click to expand...

I'm only porting over 20 rows at this time, and it should never go over
100. So, I don't think that's the issue, though I could be wrong.

I think it's the syntax and sequencing of the code, though I'm finding
little on the web to help me, and nothing in books I have.
 
Post on top like everyone else does, so it's easier to follow the thread.

The syntax looks okay, but I'm using VBA. The VB.net code I've seen often
has added syntactical elements which I don't recognize.

The variable type may not care how many rows you're accessing, it it's
expecting a long to accommodate the largest possible row number. VBA is easy
about this, not being strongly typed, but maybe dot net is stricter.

One thing to check is whether the series is displayed before this command is
executed. Line and Scatter charts that do not display for whatever reason
(only error values in the range) cannot be fully accessed by VBA. You
usually can change to a column chart type first, fix the series, then change
back.

- Jon
 
Jon said:
Post on top like everyone else does, so it's easier to follow the thread.
Click to expand...

I believe this would be the only newsgroup where I was asked to top
post. Are you sure the net nanny inquisition knows what you're doing
here? ;-)

I'll look into what you said about the longs.
 
These forums have traditionally been top-posted, at least for the past 8-10
years.

- Jon
 
My reader automatically drops me to the bottom.

This code worked for me:

'set data range
oSeries = ws.ChartObjects(1).chart.seriescollection(1)
'names
oSeries.XValues = ws.Range("b" & rowEnrolling, "b" &
rowCnt - 1)
'values
oSeries.Values = ws.Range("f" & rowEnrolling, "f" &
rowCnt - 1)
 
Well, there are lots of syntax variations which should work. I guess VB.net
is a little picky.

- Jon
 
Back
Top