Hola! estoy bastante atascado con un modulo de clase..... necesitaria que me echen una mano para saber cual es el funcionamiento excato de este modulo de clase..... muchas gracias.
El codigo es:
Private ParentSheet As Worksheet
Private This As chart
Private nIndex As Long
Private nSeries As Long
Private nPoints As Long
Private dLeft As Double
Private dTop As Double
Private dWidth As Double
Private dHeight As Double
Private nGapRows As Long
Private dChartHeight As Double
Private dChartWidth As Double
Private dRowHeight As Double
Private dColumnWidth As Double
Private ChartType As XlChartType
Private VDArray As New DataTable
Private bSucceeded As Boolean
Public Sub SetDisplayedData(DataSource As DataTable)
VDArray.Assign DataSource
nSeries = VDArray.CountColumns
nPoints = VDArray.CountRows
dLeft = (VDArray.FirstColumn - 2) * dColumnWidth
dTop = (nGapRows + VDArray.LastRow) * dRowHeight
bSucceeded = VDArray.Succeeded
End Sub
Public Sub SetReferenceSheet(sheet As Worksheet, Optional ColumnWidth As Double = 1, Optional RowHeight As Double = 1)
Set ParentSheet = sheet
dColumnWidth = ColumnWidth
dRowHeight = RowHeight
End Sub
Public Property Get ReferenceSheet() As Worksheet
Set ReferenceSheet = ParentSheet
End Property
Public Property Get CountSeries() As Long
CountSeries = nSeries
End Property
Public Property Get CountPoints() As Long
CountPoints = nPoints
End Property
Public Property Let Top(coordinate As Double)
ParentSheet.ChartObjects(nIndex).Top = dTop = coordinate
End Property
Public Property Get Top() As Double
Top = dTop
End Property
Public Property Let Gap(value As Double)
dGap = value
ParentSheet.ChartObjects(nIndex).Top = dTop + value
End Property
Public Property Get Gap() As Double
Gap = dGap
End Property
Public Property Let Height(dimension As Double)
ParentSheet.ChartObjects(nIndex).Height = dHeight = dimension
End Property
Public Property Get Height() As Double
Height = dChartHeight
End Property
Public Property Let Width(dimension As Double)
ParentSheet.ChartObjects(nIndex).Width = dWidth = dimension
End Property
Public Property Get Width() As Double
Width = dChartWidth
End Property
Public Sub OutputToExcel()
On Error GoTo ErrorHandler
Err.Clear
If Not bSucceeded Then Exit Sub
CreateNewChart
BoundToData
FormatOuterSpace
FormatInnerSpace
FormatWalls
FormatAxes
PositionChart dLeft, dTop, dWidth, dHeight
Exit Sub
ErrorHandler:
bSucceeded = False
Application.DisplayAlerts = False
If Not This Is Nothing Then This.Delete
Application.DisplayAlerts = True
End Sub
Private Sub CreateNewChart()
nIndex = ParentSheet.ChartObjects.Count + 1
Set This = Charts.Add(After:=ActiveWorkbook.Sheets("Vega bucketed greeks"))
This.Name = "Volatilities chart"
This.Visible = xlSheetVeryHidden
End Sub
Private Sub BoundToData()
Dim SrCol As SeriesCollection
Dim Values() As Double
Dim Names() As String
Set SrCol = This.ChartGroups(1).SeriesCollection
' Constants
nFirstRow = VDArray.FirstRow
nFirstColumn = VDArray.FirstColumn
nCaptionWidth = VDArray.CaptionWidth
nCaptionHeight = VDArray.CaptionHeight
nRowHeight = VDArray.RowHeight
nColumnWidth = VDArray.ColumnWidth
FRow = nFirstRow + nCaptionHeight + nRowHeight + 2
FColumn = nFirstColumn + nCaptionWidth + nColumnWidth + 2
' X axis labels
ReDim Names(0 To nPoints - 1)
For i = 0 To nPoints - 1
Names(i) = ParentSheet.Cells(FRow, nFirstColumn + nCaptionWidth + 1).value
FRow = FRow + nRowHeight + 1
Next i
' Bound to data
For i = 1 To nSeries
ReDim Values(0 To nPoints - 1)
FRow = nFirstRow + nCaptionHeight + nRowHeight + 2
For j = 0 To nPoints - 1
v = ParentSheet.Cells(FRow, FColumn).value
If IsNumeric(v) Then Values(j) = v * 100 Else Values(j) = 0
FRow = FRow + nRowHeight + 1
Next j
If i > SrCol.Count Then SrCol.NewSeries
SrCol.Item(i).Name = ParentSheet.Cells(nFirstRow + nCaptionHeight + 1, FColumn).value
SrCol.Item(i).Values = Values
SrCol.Item(i).XValues = Names
FColumn = FColumn + nColumnWidth + 1
Next i
End Sub
Private Sub FormatInnerSpace()
With This.PlotArea
.Border.LineStyle = xlContinuous
.Border.Weight = xlMedium
.Interior.Pattern = xlSolid
.Interior.ColorIndex = 47
End With
End Sub
Private Sub FormatOuterSpace()
' Global settings
With This
.ChartType = ChartType
.HasTitle = True
End With
' Chart area
With This.ChartArea
.Font.ColorIndex = 2
.Interior.Pattern = xlSolid
.Interior.ColorIndex = 11
.Border.LineStyle = xlContinuous
.Border.Weight = xlMedium
End With
' Legend area
With This.Legend
.Interior.Pattern = xlSolid
.Interior.ColorIndex = 47
.Font.ColorIndex = 44
End With
' Title area
With This.ChartTitle
.Text = "Text"
.Font.ColorIndex = 44
.Font.Bold = True
.Font.Size = 12
End With
End Sub
Private Sub FormatAxes()
With This
' X axis
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Swaption life time"
' Y axis
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Text = "Swap life time"
' Z axis
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Discrepancy, %"
.Axes(xlValue).AxisTitle.Orientation = 90
End With
End Sub
Private Sub FormatWalls()
End Sub
Private Sub PositionChart(Optional Left As Double = 0, Optional Top As Double = 0, Optional Width As Double = 400, Optional Height As Double = 700)
This.Visible = xlSheetVisible
' This.Location Where:=xlLocationAsObject, Name:="Black-76 versus Hull-White"
' With ParentSheet.ChartObjects(nIndex)
' .Left = Left
' .Top = Top
' .Width = Width
' .Height = Height
' End With
End Sub
Private Sub Class_Initialize()
nIndex = 1
dLeft = 0
dTop = 0
nGapRows = 1
dWidth = 700
dHeight = 300
dChartHeight = 400
dChartWidth = 700
dRowHeight = 0
dColumnWidth = 0
ChartType = xlSurface
bSucceeded = False
End Sub
