P
Pierre
Hi all,
I have a problem I cannot solve.. I think it is an EXCEL BUG !!
Could somebody have a quick lok at my spreadsheet.. it should take onl
few minutes... otherwise, here is the details of my concern....
I have 2 series (Credit and Volatility) on the same graph.
Date Credit Volatility B/S Flag
25/03/04 30 42 B
25/04/04 35 45 S
25/05/04 34 43
25/06/04 38 42 S
I want to change the shape of specific points : put a big green squar
when there is a "B" flag, or put a Big red square when there is a "S
flag (see the spreadhsset enclosed)
Everything is fine for the second serie but the first one generat
completely random markerstyle and color instead of a red or gree
square !
I am calling the same function for both (see in the VBA code) !!
PLEASE..... HELP !!!!!!
Is it a bug or am I becoming mad ???????
Here is the VBA code... long but easy to read...
Public Sub GenerateGraph()
'extract the number of elements for the graph
nbData = wsGraph.Range("GRAPH_NB_DATA")
'Generate the X, Y1 and Y2 strings to generate the graph
X_String = "=Graphics!R3C4:R" & nbData + 2 & "C4"
Y1_String = "=Graphics!R3C5:R" & nbData + 2 & "C5"
Y2_String = "=Graphics!R3C6:R" & nbData + 2 & "C6"
'Now, create the graph with the string generated above
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = X_String
ActiveChart.SeriesCollection(1).Values = Y1_String
ActiveChart.SeriesCollection(2).XValues = X_String
ActiveChart.SeriesCollection(2).Values = Y2_String
'At this point, set the first serie with a uniform and clean
'line (with a triangle for each point and color 32=Blue)
'(Macro below...)
SetGraphPoint 1, 32, xlTriangle
'At this point, set the first serie with a uniform and clean
'line (with a square for each point and color 7=magenta)
SetGraphPoint 2, 7, xlSquare
'And now the BUG !!!
'change the pattern if there is a B in the column G
'and put a green big square
'or put a red big square if there is a S
For i = 1 To nbData
If wsGraph.Cells(2 + i, 7) = "B" Then
'Do the "green conversion" for the first serie '
'(Macro below...)
SetGraphPoint_BUY 1, i
'Do the "green conversion" for the second serie
SetGraphPoint_BUY 2, i
ElseIf wsGraph.Cells(2 + i, 7) = "S" Then
'Do the "red conversion" for the first serie
'(Macro below...)
SetGraphPoint_SELL 1, i
'Do the "red conversion" for the second serie
SetGraphPoint_SELL 2, i
End If
Next i
'OK, it is finished !!
wsGraph.Range("A1").Select
Application.ScreenUpdating = True
End Sub
'Here we set the irGraph-th graphic to be with the irColor color an
with the irMarkerStyle Marker
' in order to have a clean and uniform graph
Private Sub SetGraphPoint(irGraph, irColor, irMarkerStyle)
ActiveChart.SeriesCollection(irGraph).Select
With Selection
.Border.ColorIndex = irColor
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = irColor
.MarkerForegroundColorIndex = irColor
.MarkerStyle = irMarkerStyle
.Smooth = True
.MarkerSize = 6
.Shadow = False
End With
End Sub
'Here I modify the irPoint-ht point in the irGraph-th graph with a bi
green square marker with a black cross
Private Sub SetGraphPoint_BUY(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoint).Select
With Selection
.MarkerBackgroundColorIndex = 4
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlX
.MarkerSize = 8
.Shadow = False
End With
End Sub
'Here I modify the irPoint-ht point in the irGraph-th graph with a bi
red square marker with a black star
Private Sub SetGraphPoint_SELL(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoint).Select
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlStar
.MarkerSize = 8
.Shadow = False
End With
End Su
+-------------------------------------------------------------------
|Filename: TEST.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2809 |
+-------------------------------------------------------------------+
I have a problem I cannot solve.. I think it is an EXCEL BUG !!
Could somebody have a quick lok at my spreadsheet.. it should take onl
few minutes... otherwise, here is the details of my concern....
I have 2 series (Credit and Volatility) on the same graph.
Date Credit Volatility B/S Flag
25/03/04 30 42 B
25/04/04 35 45 S
25/05/04 34 43
25/06/04 38 42 S
I want to change the shape of specific points : put a big green squar
when there is a "B" flag, or put a Big red square when there is a "S
flag (see the spreadhsset enclosed)
Everything is fine for the second serie but the first one generat
completely random markerstyle and color instead of a red or gree
square !
I am calling the same function for both (see in the VBA code) !!
PLEASE..... HELP !!!!!!
Is it a bug or am I becoming mad ???????
Here is the VBA code... long but easy to read...
Public Sub GenerateGraph()
'extract the number of elements for the graph
nbData = wsGraph.Range("GRAPH_NB_DATA")
'Generate the X, Y1 and Y2 strings to generate the graph
X_String = "=Graphics!R3C4:R" & nbData + 2 & "C4"
Y1_String = "=Graphics!R3C5:R" & nbData + 2 & "C5"
Y2_String = "=Graphics!R3C6:R" & nbData + 2 & "C6"
'Now, create the graph with the string generated above
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = X_String
ActiveChart.SeriesCollection(1).Values = Y1_String
ActiveChart.SeriesCollection(2).XValues = X_String
ActiveChart.SeriesCollection(2).Values = Y2_String
'At this point, set the first serie with a uniform and clean
'line (with a triangle for each point and color 32=Blue)
'(Macro below...)
SetGraphPoint 1, 32, xlTriangle
'At this point, set the first serie with a uniform and clean
'line (with a square for each point and color 7=magenta)
SetGraphPoint 2, 7, xlSquare
'And now the BUG !!!
'change the pattern if there is a B in the column G
'and put a green big square
'or put a red big square if there is a S
For i = 1 To nbData
If wsGraph.Cells(2 + i, 7) = "B" Then
'Do the "green conversion" for the first serie '
'(Macro below...)
SetGraphPoint_BUY 1, i
'Do the "green conversion" for the second serie
SetGraphPoint_BUY 2, i
ElseIf wsGraph.Cells(2 + i, 7) = "S" Then
'Do the "red conversion" for the first serie
'(Macro below...)
SetGraphPoint_SELL 1, i
'Do the "red conversion" for the second serie
SetGraphPoint_SELL 2, i
End If
Next i
'OK, it is finished !!
wsGraph.Range("A1").Select
Application.ScreenUpdating = True
End Sub
'Here we set the irGraph-th graphic to be with the irColor color an
with the irMarkerStyle Marker
' in order to have a clean and uniform graph
Private Sub SetGraphPoint(irGraph, irColor, irMarkerStyle)
ActiveChart.SeriesCollection(irGraph).Select
With Selection
.Border.ColorIndex = irColor
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = irColor
.MarkerForegroundColorIndex = irColor
.MarkerStyle = irMarkerStyle
.Smooth = True
.MarkerSize = 6
.Shadow = False
End With
End Sub
'Here I modify the irPoint-ht point in the irGraph-th graph with a bi
green square marker with a black cross
Private Sub SetGraphPoint_BUY(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoint).Select
With Selection
.MarkerBackgroundColorIndex = 4
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlX
.MarkerSize = 8
.Shadow = False
End With
End Sub
'Here I modify the irPoint-ht point in the irGraph-th graph with a bi
red square marker with a black star
Private Sub SetGraphPoint_SELL(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoint).Select
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlStar
.MarkerSize = 8
.Shadow = False
End With
End Su
+-------------------------------------------------------------------
|Filename: TEST.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2809 |
+-------------------------------------------------------------------+