more graphs 1

[drewdaman]

hello,

i am having a little trouble with some graphing.. in a previous post, i had posted this code to create a graph using arrays:

Sub graph(ByRef xvals() As Double, ByRef yvals() As Double, gsoffset As Double)
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"


     
    Dim MyNewSrs As Series
    Set MyNewSrs = ActiveChart.SeriesCollection.NewSeries
    With MyNewSrs
        .Name = "Fred"
        .Values = yvals
        .XValues = xvals
        
    End With
End Sub

this works... sort of.. ie:

it creates a graph with teh same scale on the x and y axes. i want the scale on the x axis to go from 0 to 6000, incremements of 1000. this is fine. the problem is that it uses the same scale on the y axis.. i want the scale to go from 0 to 2 (possibly in increments of 0.2).. but it goes from 0 to 6000! so you can't really see the graph.. when i manually change teh scale for the y axis, the graph is fine.

i recorded a macro to change the scale.. and added that code to the method (after the last line of the above code- before the "end sub"). but it doesn't work either. actually, it worked once, and then never again!

this is the full code with the recorded macro lines added:

Sub graph(ByRef xvals() As Double, ByRef yvals() As Double, gsoffset As Double)
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"


     
    Dim MyNewSrs As Series
    Set MyNewSrs = ActiveChart.SeriesCollection.NewSeries
    With MyNewSrs
        .Name = "Fred"
        .Values = yvals
        .XValues = xvals
        
    End With
    
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = 0
        .MaximumScale = gsoffset
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
End Sub

any ideas on how can i set the scale properly?

thanks!

 

[SkipVought]

I dunno -- it works fine for me???

What x & y values are you using?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

well.. if i change the scale on the graph manually after i create it with code, it works fine for me too

i just made a small change.. and i think i've got it.. but i'm not 100% sure yet.. will confirm later...

i think :
.MinimumScaleIsAuto = 0

should be:
.MinimumScale = 0

 

[SkipVought]

Alot depends on your data; min/max values and THEN how the parameters scale that data.

Like I stated, the code, as is, AND the data I used, seemed to work fine.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

thanks for the reply.. what i mentioned preivously worked.

but now i have another problem.. i am using hte graph funciton i posted to create several graphs. it works fine for my first case. but in the second case, it crashes saying: "unable to set the values property of the series class". i am not sure what this error is.. it crashes on teh lien:

.Values = yvals

ie in teh snippet:
With MyNewSrs
'.Name = "Fred"
.Values = yvals
.XValues = xvals

End With

the strange thing is, i'm doing exactly the same thing in both cases, just passing different data.. i have printed out the data in the arrays i'm passing to the graphing function and they are perfect..

again, the error is: "unable to set the values property of the series class".. i have searched the web.. but not found anything useful!

any ideas on what this means?

thanks!

 

[SkipVought]

You are sure stingy with DATA!

My crystal ball must be cloudy!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

hehe.. sorry... here is the data i'm using:

x axis, y axis
500000 0.797702616
600000 0.95712098
700000 1.116569897
800000 1.276161307
900000 1.435956347
1000000 1.595405233
1100000 1.754385965
1200000 1.91424196
1300000 2.074688797
1400000 2.234137623
1500000 2.392344498
1600000 2.553626149
1700000 2.711496746
1800000 2.870264064
1900000 3.03030303
2000000 3.18877551

 

[drewdaman]

i might also add that i printed the arrays that i am passing to the graphing function, and they hold the correct values... so i'm not messing up there..

 

[SkipVought]

I ran your graph function using your data.

Of course, you only posted ONE SET, while your explanation claimed that you have TWO SETS and on the SECOND SET it cratered.

I ran the code 4 times, back-to-back, without a problem??????

But you ALSO falied to provide me with ANOTHER piece of data... the gsoffset
[red][highlight yellow]
Cummon! Let's get ALL THE DATA ON THE TABLE! I'm frankly getting tired of pulling teeth![/highlight][/red]

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

sorry..

data set that works:
x, y
1000 0.319081047
1500 0.47862157
2000 0.638162093
2500 0.797702616
3000 0.95724314
3500 1.116783663
4000 1.276324186
4500 1.43586471
5000 1.595405233


gsoffset: that is a variable i use to calculate the maximum value that i would like on the y scale. i set it to the maximum y value in the data set plus the largest incremement between two consecutive values in the data set.
so, in the working case it is: 1.749
in the non working case it is: 3.35


hope that is enough info.. can't think of anything else.. but if you do, plz let me know!

thanks!

 

[drewdaman]

if i manually set the data in teh arrays, it works..

but i have printed the ararys i fill up programatically.. and they are indentical.. and it does not work! so i'm not sure what is going on!

 

[SkipVought]

Both charts produce fine.

Please post the code that you use to load your arrays and call graph.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

so.. this is the main sub that starts everything:

Sub test()
    Dim xs(15) As Double
    Dim ys(15) As Double
    Dim con(2) As Long
    con(0) = 2500
    'con(0) = 500000
    con(1) = 50
    con(2) = 100
    
    Call getGraphArrays(1, xs(), ys(), con())
End Sub

the following code is called by the above function and calls the graphing function (and a few others which i will paste as well)

Private Sub getGraphArrays(ByRef varyfac As Integer, ByRef xvals() As Double, ByRef yvals() As Double, ByRef holdconst() As Long)
    Range("A2").Select
    Dim firstrow As Integer
    Dim increment As Integer
    Dim graphscaleoffset As Double
    Dim maxinarr As Double
    
    Select Case varyfac
        Case 0 'case vary wafer cost
            'MsgBox ("wc")
            firstrow = findfirstrow(1000, holdconst(0), holdconst(1), holdconst(2))
            Cells(firstrow, 1).Select
            xvals(0) = CDbl(ActiveCell.Value)
            yvals(0) = CDbl(ActiveCell.Offset(0, 8).Value)
            increment = 16 * 5 * 3
            For i = 1 To 8
                Cells(ActiveCell.Row + increment, 1).Select
                xvals(i) = ActiveCell.Value
                yvals(i) = ActiveCell.Offset(0, 8).Value
                'MsgBox (CStr(xvals(i)) + "," + CStr(yvals(i)))
            Next i
            maxinarr = findmaxinarray(yvals(), 8, graphscaleoffset)
            Call graph(xvals(), yvals(), maxinarr + graphscaleoffset)
            
  
        Case 1 'case vary gate count
            'MsgBox ("GC")
            firstrow = findfirstrow(holdconst(0), 500000, holdconst(1), holdconst(2))
            Cells(firstrow, 1).Select
            xvals(0) = ActiveCell.Offset(0, 1).Value
            yvals(0) = ActiveCell.Offset(0, 8).Value
            increment = 5 * 3
            For i = 1 To 15
                Cells(ActiveCell.Row + increment, 1).Select
                xvals(i) = ActiveCell.Offset(0, 1).Value
                yvals(i) = ActiveCell.Offset(0, 8).Value
                'MsgBox (CStr(xvals(i)) + "," + CStr(yvals(i)))
            Next i
            
            maxinarr = findmaxinarray(yvals(), 15, graphscaleoffset)
        
            Call graph(xvals(), yvals(), maxinarr + graphscaleoffset)
        Case 2 'case vary yield
            MsgBox ("yield")
        Case 3
            MsgBox ("kfact")
    End Select
    
End Sub

two other called functions:

Function findmaxinarray(ByRef arr() As Double, ByRef maxindex As Integer, ByRef maxdiff As Double) As Double
    Dim curmax As Integer
    curmax = 0
    Dim diff As Double
    diff = 0
    
    For i = 0 To maxindex
        If arr(i) > arr(curmax) Then
            curmax = i
        End If
        
        If i > 0 Then
            If Abs(arr(i) - arr(i - 1)) > diff Then
                diff = Abs(arr(i) - arr(i - 1))
            End If
        End If
            
    Next i
    maxdiff = diff
    findmaxinarray = arr(curmax)
End Function

Function findfirstrow(ByRef wc As Long, ByRef gc As Long, ByRef y As Long, ByRef kf As Long) As Integer

    Dim temp As Integer
    temp = 16 * 5 * 3
    Cells(2, 1).Select
    Dim currow As Integer
    currow = ActiveCell.Row
    
    Do While (ActiveCell.Value <> wc)
        Cells(currow + temp, 1).Select
        currow = ActiveCell.Row
    Loop
    
    temp = 5 * 3
    
    Do While ((ActiveCell.Offset(0, 1).Value) <> gc)
        Cells(currow + temp, 1).Select
        currow = ActiveCell.Row
        currow = ActiveCell.Row
    Loop
    
    temp = 3
    
    Do While ((ActiveCell.Offset(0, 2).Value) <> y)
        Cells(currow + temp, 1).Select
        currow = ActiveCell.Row
        currow = ActiveCell.Row
    Loop
    
    temp = 1
    
    Do While ((ActiveCell.Offset(0, 3).Value) <> kf)
        Cells(currow + temp, 1).Select
        currow = ActiveCell.Row
        currow = ActiveCell.Row
    Loop
    findfirstrow = currow
    
End Function

also, please note that this is not hte final code.. i'm just tryign to get basic structures set up right now before incorporating it in my real app. you will find some hard coded values in there... the reason that they are hard coded is because i'm just setting up the structure right now.. don't have access to them... don't worry about things being hard coded.. i will deal with it once this is done!

 

[drewdaman]

case 0 in the getgrapharrays works if i modify test to be:

Sub test()
    Dim xs(15) As Double
    Dim ys(15) As Double
    Dim con(2) As Long
    'con(0) = 2500
    [b]con(0) = 500000[/b]
    con(1) = 50
    con(2) = 100
    
    Call getGraphArrays([COLOR=red]0[/color], xs(), ys(), con())
End Sub

 

[drewdaman]

i can send you the actual data on the sheet by email if you like.. its 8 x over 2000 rows, so i don't want to paste it here

 

[SkipVought]

Check out How Can I Make My Code Run Faster? faq707-4105

By excessive use of Select, you slow down you process and can experience other problems related the what object is ACTIVE. I try to stay away that.

I have not had a chance to look at your code. I'll be out of pocket until tomorrow.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

yes.. i realize that.. but i don't really have time to do it that way now.. this thing needs to get done quick.. and as you can see it is a little bit out of my area of expertise! don't really care about efficiency.. just results.. i didnt' realize you could do it that way.. i woudl have done it from the start if i had.. it is much better.. just don't have time to change it!

look forward to hearing from you tomorrow!

 

[drewdaman]

soemthing odd just happened.

i tried instead of using an array indexed from 0-15, an array indexed from 0-13. the graph works. but if i change it to 0-14 or 0-15, there is the same problem :s

of course, i modified parts of the code.. in getgrapharrays:

        Case 1 'case vary gate count
            'MsgBox ("GC")
            firstrow = findfirstrow(holdconst(0), 500000, holdconst(1), holdconst(2))
            Cells(firstrow, 1).Select
            xvals(0) = ActiveCell.Offset(0, 1).Value
            yvals(0) = ActiveCell.Offset(0, 8).Value
            increment = 5 * 3
            For i = 1 To [COLOR=red]13[/color]
                Cells(ActiveCell.Row + increment, 1).Select
                xvals(i) = ActiveCell.Offset(0, 1).Value
                yvals(i) = ActiveCell.Offset(0, 8).Value
                'MsgBox (CStr(xvals(i)) + "," + CStr(yvals(i)))
            Next i
            
            maxinarr = findmaxinarray(yvals(), [COLOR=red]13[/color]
, graphscaleoffset)
        
            Call graph(xvals(), yvals(), maxinarr + graphscaleoffset)

and in test:

Sub test()
    Dim xs([COLOR=red]13[/color]) As Double
    Dim ys([COLOR=red]13[/color]
) As Double
    Dim con(2) As Long
    con(0) = 2500
    con(1) = 50
    con(2) = 100
    
    Call getGraphArrays(1, xs(), ys(), con())
End Sub

seems to be a problem when i use an array of size larger than 14 (ie indexed 0-13).. but when i set the values manually in the following function (16 values in an array indexed 0-15) it works.. so i'm really confused!

Sub la()
    Dim x(15) As Double
    Dim y(15) As Double
    
    x(0) = 500000
    x(1) = 600000
    x(2) = 700000
    x(3) = 800000
    x(4) = 900000
    x(5) = 1000000
    x(6) = 1100000
    x(7) = 1200000
    x(8) = 1300000
    x(9) = 1400000
    x(10) = 1500000
    x(11) = 1600000
    x(12) = 1700000
    x(13) = 1800000
    x(14) = 1900000
    x(15) = 2000000
    
    y(0) = 0.797702616
    y(1) = 0.95712098
    y(2) = 1.116569897
    y(3) = 1.276161307
    y(4) = 1.435956347
    y(5) = 1.595405233
    y(6) = 1.754385965
    y(7) = 1.91424196
    y(8) = 2.074688797
    y(9) = 2.234137623
    y(10) = 2.392344498
    y(11) = 2.553626149
    y(12) = 2.711496746
    y(13) = 2.870264064
    y(14) = 3.03030303
    y(15) = 3.18877551

Call graph(x(), y(), 3.35)
End Sub

please hlep me! i'm so lost!

 

[drewdaman]

bump

 

[SkipVought]

I would not use a hard value for max index. rather use the UBound function to return the max index for any array

for i = LBound(MyArray) to UBound(MyArray)

Next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}