Hi All ,
Please see the below code .I have debug the code but not able to find the problem .Can anyone please help me.
Sub drawRisk(excel As Variant, prior As String)
Dim rthis, rlast As Variant
Dim s As Variant
Dim i As Integer
Dim iserie As Integer
On Error Goto ErrorHandler
With excel.officeappl
Set s = .sheets(“PrioScen”)
.Charts.Add(.sheets(5))
If prior = “Yes” Then
Set rthis = s.Range(s.cells(5,2),s.cells(5+irowmax,2)) 'this year risk data
Set rlast = s.Range(s.cells(5,7),s.cells(5+irowmax,7)) 'last year risk data
.ActiveSheet.Name = “PrioCat”
Else
Set rthis = s.Range(s.cells(5,4),s.cells(5+irowmax,4)) 'this year risk data
Set rlast = s.Range(s.cells(5,9),s.cells(5+irowmax,9)) 'last year risk data
.ActiveSheet.Name = “NonPrioCat”
End If
Print "Create diagram " + .ActiveSheet.Name
With excel.ActiveWorkbook.ActiveSheet.ActiveChart
.SizeWithWindow = True
.ChartType = 51
.HasTitle = True
If prior = “Yes” Then
.ChartTitle.Text = “Prioritized Risk Scenarios per Risk Category”
Else
.ChartTitle.Text = “Non Prioritized Risk Scenarios per Risk Category”
End If
Call excel.pagesetup
With .PlotArea 'change the background color
With .Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .Interior
.ColorIndex = 2
.PatternColorIndex = 1
End With
End With
Call .SetSourceData(rthis,2) 'use this year data serie xlColumns
rlast.Copy 'add last year data serie
Call .SeriesCollection.Paste(2,False,False, False,True)
.ApplyDataLabels(2) 'show lables
For iserie = 0 To 1 'rework labels
With .SeriesCollection(iserie+1)
.XValues = risks
.Name = xValues(iserie)
If iserie = 0 Then
.border.LineStyle = xlContinuous
Else
.border.LineStyle = xlDot
End If
.Interior.ColorIndex = xlNone
For i = 0 To irowmax
With .Points(i+1)
.interior.ColorIndex = getRiskColorIndex(Cstr(risks(i)))
.Interior.Pattern = getRiskPatternIndex(Cstr(risks(i)))
If prior = “Yes” Then
’ .DataLabel.Characters.Text = Cstr(i) ’ .DataLabel.Characters.Text + “xxx” + Chr(13) + Format(s.cells(i+5,3).value, “0.0%”)
.DataLabel.Text = .DataLabel.Text + Chr(13) + Format(s.cells(i+5,3+5*iserie).value, “0.0%”)
Else
’ .DataLabel.Characters.Text = .DataLabel.Characters.Text + Chr(13) + Format(s.cells(i+5,5).value, “0.0%”)
.DataLabel.Text = .DataLabel.Text + Chr(13) + Format(s.cells(i+5,5+5*iserie).value, “0.0%”)
End If
End With
Next
End With
Next
End With
End With
SingleExit:
Exit Sub
ErrorHandler:
Call ProcessError(“”,“UICtrlPrioRisk”,“risktables.drawrisk”,Nothing)
Resume SingleExit
End Sub
This is not generating the required Chart. Please help.
thanks in Advance!