Newbie charting help

[rjb2001]

Hi,
Just started using VBA and I need a bit of advice how to create a specific type of chart.

I have two arrays 'doserate' and 'bendingcurrent' - the maximum number of elements in these arrrays is 41 and I have another variable 'pos' which indicates the last non zero element in both the arrays.
I want to plot a chart with the x axis showing the 'bendingcurrent' values between the values of bendingcurrent(1) to bendingcurren('pos') and y axis showing 'doserate' between the values of doserate(1) to doserate('pos').

I want an x-y scatter chart with smoothed lines and I would like the x-axis scaling only between the values of bendingcurrent(1) and bendingcurren('pos')

Help!

[mancubus]

hi rjb2001.
wellcome to VBAX.

here is stg to play around with.


Option Base 1

Sub ChtAdd()
'http://vbaexpress.com/forum/showthread.php?t=39487

Dim cht As ChartObject
Dim bendingcurrent As Variant, doserate As Variant
Dim arrXVal() As Variant, arrVal() As Variant
Dim i As Integer, pos As Integer, j As Integer

bendingcurrent = Range("A1:A10").Value 'whatever
doserate = Range("B1:B10").Value 'whatever

pos = 4 'whatever
ReDim arrXVal(pos), arrVal(pos)

j = 1
For i = 1 To pos
ReDim Preserve arrXVal(j)
arrXVal(j) = bendingcurrent(i, 1)
ReDim Preserve arrVal(j)
arrVal(j) = doserate(i, 1)
j = j + 1
Next i

Set cht = ActiveSheet.ChartObjects.Add(Top:=0, Left:=0, Width:=350, Height:=200)
'ADJUST
With cht.Chart
.ChartType = xlXYScatterSmooth
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = arrXVal
.Values = arrVal
End With
End With

End Sub

[rjb2001]

Thanks for that, but I'm having a little problem adapting it to do what I need. If I show you my first bit of code you may have a better idea where I am going wrong:

Sub CreateGraph()
'
' CreateGraph Macro
'
Dim doserate(1 To 41) As Variant
Dim bendingcurrent(1 To 41) As Variant


pos = 1

For rcv = 27 To 67 'read cell values in cells 27 to 67

If Range("C" & rcv).Value <> 0 Then 'If the dose rate contains a non-zero value ie a valid reading then....

doserate(pos) = Range("C" & rcv) 'Copy the non-zero doserate into the array
bendingcurrent(pos) = Range("A" & rcv) 'Copy the corresponding bending current into the array
pos = pos + 1 'Increase the array position by one

End If 'If dose rate IS zero, don't do anything and...

Next '...look in the next cell

Range("u1").Value = pos - 1 'TEST Show number of readings in array
For test = 1 To (pos - 1) 'TEST Select all non-zero elements of array
Range("S" & test).Value = doserate(test) 'TEST Show doserates values
Range("T" & test).Value = bendingcurrent(test) 'TEST Show bending current values
Next 'Test
On the excel sheet the user enters data (doserate) in the range C27 to C67. C47 is the mid point and the user enters a variable number of values that drop away either side of this up to a maximum of 20 steps in either direction (C27 & C67) but the number of steps either side of C47 can be uneven (eg may be 7 values on one side of C47 and 12 on the other). Column C will always be an integer value.
The value in column A (bendingcurrent) is a non-integer value and calculated when the user first inputs the nominal running values.

My bit of code (which probably isn't the most elegant solution, but this is my first foray into VBA!) simply selects discards all the zero value cells (ie those that the user hasn't entered any data into).

You can ignore the last five lines of code - I am just using them during testing to make sure I get all the relevant data.

[krishnak]

Are we not supposed to copy the values in the arrays?
Why are the cell addresses loaded in the arrays?

doserate(pos) = Range("C" & rcv) 'Copy the non-zero doserate into the array
bendingcurrent(pos) = Range("A" & rcv) 'Copy the corresponding bending current into the ar

[rjb2001]

Hi - I'm new to doing this so I may be going around it the wrong way, so bear with me!

The only cells that can be guaranteed to have a value are A27:A67 (because they are caluculated within the sheet) and C47 as these are normal running values input by the user; C27:C46 will have values in but how many is unknown, the same applies for C48:C67.

FYI - The user goes to the m/c, enters the value from column A into the m/c and then reads the o/p from the m/c and enters that value into column C and continues this process until the o/p from the m/c drops to zero - you can't predict how many steps (1-20 on each side of the normal running value) will be required to produce a zero o/p or at what step point it will happen. A47 and C47 are the normal running values for the m/c.

I don't wish to plot any zero values cells so needed some way to weed all those values out.

I couldn't figure out how to set the X and Y series to the required cells using variables - I thought it would be easy, something like:Chart.SeriesCollection(1).XValues = ("A"&firstnonzerocell&":A"&lastnonzerocell)
Chart.SeriesCollection(1).YValues = ("C"&firstnonzerocell&":C"&lastnonzerocell) But that didn't work, so I simply assigned the values to an array.

Thanks for the help guys.

Rob.

[mancubus]

i'm not sure i'm understanding your request.

but if you want to create a chart from arrays in ActiveSheet, and load data into arrays from cells the values of which are not equal to 0, then you may try below


Sub test()

Dim cht As ChartObject
Dim doserate() As Variant, bendingcurrent() As Variant
Dim pos As Integer, rcv As Integer

pos = 0
For rcv = 27 To 67
If Range("A" & rcv) <> 0 Then
pos = pos + 1
ReDim Preserve bendingcurrent(1 To pos)
bendingcurrent(pos) = Cells(rcv, "A")
End If
Next

pos = 0
For rcv = 27 To 67
If Range("C" & rcv) <> 0 Then
pos = pos + 1
ReDim Preserve doserate(1 To pos)
doserate(pos) = Cells(rcv, "C")
End If
Next

Set cht = ActiveSheet.ChartObjects.Add(Top:=0, Left:=0, Width:=350, Height:=200)
With cht.Chart
.ChartType = xlXYScatterSmooth
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = bendingcurrent
.Values = doserate
End With
End With

End Sub

[rjb2001]

:thumb Got there in the end between us!

The (semi)final code is as follows (just need to tweek the graph cosmetically):
Sub CreateGraph()
'
' CreateGraph Macro
'

Dim cht As ChartObject
Dim doserate() As Variant, bendingcurrent() As Variant
Dim pos As Integer, rcv As Integer
Dim maxcurrent As Variant, mincurrent As Variant

mincurrent = 0
maxcurrent = 0
pos = 0
For rcv = 27 To 67
If Range("C" & rcv) <> 0 Then
pos = pos + 1

Select Case mincurrent

Case Is = 0
mincurrent = Cells(rcv, "A").Value

Case Else
maxcurrent = Cells(rcv, "A").Value

End Select

ReDim Preserve bendingcurrent(1 To pos)
bendingcurrent(pos) = Cells(rcv, "A")
ReDim Preserve doserate(1 To pos)
doserate(pos) = Cells(rcv, "C")

End If
Next






Set cht = ActiveSheet.ChartObjects.Add(Top:=375, Left:=300, Width:=400, Height:=400)
With cht.Chart
.ChartType = xlXYScatterSmooth
.SeriesCollection.NewSeries

With .SeriesCollection(.SeriesCollection.Count)
.XValues = bendingcurrent
.Values = doserate
End With
.Axes(xlCategory).MinimumScale = (mincurrent - Range("E47").Value)
.Axes(xlCategory).MaximumScale = (maxcurrent + Range("E47").Value)
End With

End Sub

Cheers guys!
:bow:

PS
Any recommendation for beginners any books? - I'm struggling with some of the commands and syntaxes - not a big fan of the 'for dummies' series but any recommendations gratefully received.