Delete and Replace All Embedded Charts in Workbook
I have written a VBA code that generates several charts but what I'd like to do is that each time I run the program the new chart comes and overwrites the old one, so I will always have the most accurate chart in my workbook. Also, When you'll see my code, I have included in every 'Phase' what I thought to be my solution to my problem in deleting and replacing each and every chart in the workbook:
Of course I customized the loop below for each chart
-----------------------------------------
For Each SumExpC In ThisWorkbook.Charts
If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")
------------------------------------------
Thank you so much for help. I will really appreciate any insight.
PS: the code is extremely redundant but it's important that I attach it all, So you can see all my embedded charts.
And Sorry for this long email.;)
'Phase 2 ---------------------------------------------------------
Sub SampleTransparencyReport()
Dim SumExpC As Chart 'Summary Exposure by Currency
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each SumExpC In ThisWorkbook.Charts
If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")
With SumExpC
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("P32:Q39"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Currency" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SumExpC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 384 'Range("A31").Top
.Left = 0 ' Range("A31").Left
.Height = 180
.Width = 280
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 3 ---------------------------------------------------------
Dim SumExpA As Chart 'Summary Exposure by Asset Class
'ActiveSheet.ChartObjects.Delete
For Each SumExpA In ThisWorkbook.Charts
If SumExpA.Name = "SumExpA" Then
SumExpA.Delete
End If
Next
Set SumExpA = ThisWorkbook.Charts.Add
Set SumExpA = SumExpA.Location(where:=xlLocationAsObject, Name:="Exposure I")
With SumExpA
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("M32:N40"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Asset Class" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SumExpA.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpA.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpA.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 384 'Range("E31").Top
.Left = 306 'Range("E31").Left
.Height = 180
.Width = 280
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
'Phase 4 ---------------------------------------------------------
'This MsgBox returns values for the Height, Width, Top & Left & Cell positions of a Chart in a Worksheet
' Dim SumExp As ChartObject
' Dim SumExpTop As Integer
' Dim SumExpLeft As Integer
' Dim SumExpTopLeftCell As String
'
' For Each SumExp In ActiveSheet.ChartObjects
' SumExpTop = SumExp.Top
' SumExpLeft = SumExp.Left
' SumExpWidth = SumExp.Width 'delete
' SumExpHeight = SumExp.Height 'delete
' SumExpTopLeftCell = SumExp.TopLeftCell.Address
' MsgBox (SumExp.Name & vbCr & "Width=" & SumExpWidth & vbCr & "Height=" & SumExpHeight & vbCr & "Top = " & SumExpTop & vbCr & "Left = " & SumExpLeft & vbCr & "Cell = " & SumExpTopLeftCell)
' Next
'Phase 5 ---------------------------------------------------------
Dim SectorBreakdownE As Chart 'Equity Sector Breakdown Exposures
'ActiveSheet.ChartObjects.Delete
For Each SectorBreakdownE In ThisWorkbook.Charts
If SectorBreakdownE.Name = "SectorBreakdownE" Then
SectorBreakdownE.Delete
End If
Next
Set SectorBreakdownE = ThisWorkbook.Charts.Add
Set SectorBreakdownE = SectorBreakdownE.Location(where:=xlLocationAsObject, Name:="Exposure II")
With SectorBreakdownE
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("P6:Q15"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SectorBreakdownE.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownE.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownE.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("A7").Top
.Left = 0 ' Range("A7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 6 ---------------------------------------------------------
Dim MarketCap As Chart 'Market Capitalization
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each MarketCap In ThisWorkbook.Charts
If MarketCap.Name = "MarketCap" Then
MarketCap.Delete
End If
Next
Set MarketCap = ThisWorkbook.Charts.Add
Set MarketCap = MarketCap.Location(where:=xlLocationAsObject, Name:="Exposure II")
With MarketCap
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("S6:T9"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Market Capitalization" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With MarketCap.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With MarketCap.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With MarketCap.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("D7").Top
.Left = 250 ' Range("D7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 7 ---------------------------------------------------------
Dim LiquidP As Chart 'Liquidity Profile
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each LiquidP In ThisWorkbook.Charts
If LiquidP.Name = "LiquidP" Then
LiquidP.Delete
End If
Next
Set LiquidP = ThisWorkbook.Charts.Add
Set LiquidP = LiquidP.Location(where:=xlLocationAsObject, Name:="Exposure II")
With LiquidP
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V6:W10"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Liquidity Profile" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With LiquidP.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With LiquidP.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With LiquidP.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("I7").Top
.Left = 490 ' Range("I7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 8 ---------------------------------------------------------
Dim SectorBreakdownC As Chart 'Credit Sector Breakdown
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each SectorBreakdownC In ThisWorkbook.Charts
If SectorBreakdownC.Name = "SectorBreakdownC" Then
SectorBreakdownC.Delete
End If
Next
Set SectorBreakdownC = ThisWorkbook.Charts.Add
Set SectorBreakdownC = SectorBreakdownC.Location(where:=xlLocationAsObject, Name:="Exposure II")
With SectorBreakdownC
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("P27:Q36"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SectorBreakdownC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("A27").Top
.Left = 0 ' Range("A27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 9 ---------------------------------------------------------
Dim RatingDistrib As Chart 'Credit Ratings Distribution
'ActiveSheet.ChartObjects.Delete '.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each RatingDistrib In ThisWorkbook.Charts 'Replacing Old Chart by New One
If RatingDistrib.Name = "RatingDistrib" Then
RatingDistrib.Delete
End If
Next
Set RatingDistrib = ThisWorkbook.Charts.Add
Set RatingDistrib = RatingDistrib.Location(where:=xlLocationAsObject, Name:="Exposure II")
With RatingDistrib
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("S27:T34"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Ratings Distribution" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With RatingDistrib.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With RatingDistrib.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With RatingDistrib.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("E27").Top
.Left = 250 ' Range("E27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 10 ---------------------------------------------------------
Dim DV01 As Chart 'DV01 by Maturity Bucket
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each DV01 In ThisWorkbook.Charts 'Replacing Old Chart by New One
If DV01.Name = "DV01" Then
DV01.Delete
End If
Next
Set DV01 = ThisWorkbook.Charts.Add
Set DV01 = DV01.Location(where:=xlLocationAsObject, Name:="Exposure II")
With DV01
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V27:W30"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "DV01 by Maturity Bucket" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With DV01.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With DV01.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With DV01.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("I27").Top
.Left = 490 ' Range("I27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 11 ---------------------------------------------------------
End With
End Sub
Of course I customized the loop below for each chart
-----------------------------------------
For Each SumExpC In ThisWorkbook.Charts
If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")
------------------------------------------
Thank you so much for help. I will really appreciate any insight.
PS: the code is extremely redundant but it's important that I attach it all, So you can see all my embedded charts.
And Sorry for this long email.;)
'Phase 2 ---------------------------------------------------------
Sub SampleTransparencyReport()
Dim SumExpC As Chart 'Summary Exposure by Currency
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each SumExpC In ThisWorkbook.Charts
If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")
With SumExpC
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("P32:Q39"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Currency" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SumExpC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 384 'Range("A31").Top
.Left = 0 ' Range("A31").Left
.Height = 180
.Width = 280
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 3 ---------------------------------------------------------
Dim SumExpA As Chart 'Summary Exposure by Asset Class
'ActiveSheet.ChartObjects.Delete
For Each SumExpA In ThisWorkbook.Charts
If SumExpA.Name = "SumExpA" Then
SumExpA.Delete
End If
Next
Set SumExpA = ThisWorkbook.Charts.Add
Set SumExpA = SumExpA.Location(where:=xlLocationAsObject, Name:="Exposure I")
With SumExpA
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("M32:N40"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Asset Class" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SumExpA.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpA.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SumExpA.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 384 'Range("E31").Top
.Left = 306 'Range("E31").Left
.Height = 180
.Width = 280
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
'Phase 4 ---------------------------------------------------------
'This MsgBox returns values for the Height, Width, Top & Left & Cell positions of a Chart in a Worksheet
' Dim SumExp As ChartObject
' Dim SumExpTop As Integer
' Dim SumExpLeft As Integer
' Dim SumExpTopLeftCell As String
'
' For Each SumExp In ActiveSheet.ChartObjects
' SumExpTop = SumExp.Top
' SumExpLeft = SumExp.Left
' SumExpWidth = SumExp.Width 'delete
' SumExpHeight = SumExp.Height 'delete
' SumExpTopLeftCell = SumExp.TopLeftCell.Address
' MsgBox (SumExp.Name & vbCr & "Width=" & SumExpWidth & vbCr & "Height=" & SumExpHeight & vbCr & "Top = " & SumExpTop & vbCr & "Left = " & SumExpLeft & vbCr & "Cell = " & SumExpTopLeftCell)
' Next
'Phase 5 ---------------------------------------------------------
Dim SectorBreakdownE As Chart 'Equity Sector Breakdown Exposures
'ActiveSheet.ChartObjects.Delete
For Each SectorBreakdownE In ThisWorkbook.Charts
If SectorBreakdownE.Name = "SectorBreakdownE" Then
SectorBreakdownE.Delete
End If
Next
Set SectorBreakdownE = ThisWorkbook.Charts.Add
Set SectorBreakdownE = SectorBreakdownE.Location(where:=xlLocationAsObject, Name:="Exposure II")
With SectorBreakdownE
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("P6:Q15"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SectorBreakdownE.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownE.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownE.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("A7").Top
.Left = 0 ' Range("A7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 6 ---------------------------------------------------------
Dim MarketCap As Chart 'Market Capitalization
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each MarketCap In ThisWorkbook.Charts
If MarketCap.Name = "MarketCap" Then
MarketCap.Delete
End If
Next
Set MarketCap = ThisWorkbook.Charts.Add
Set MarketCap = MarketCap.Location(where:=xlLocationAsObject, Name:="Exposure II")
With MarketCap
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("S6:T9"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Market Capitalization" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With MarketCap.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With MarketCap.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With MarketCap.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("D7").Top
.Left = 250 ' Range("D7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 7 ---------------------------------------------------------
Dim LiquidP As Chart 'Liquidity Profile
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each LiquidP In ThisWorkbook.Charts
If LiquidP.Name = "LiquidP" Then
LiquidP.Delete
End If
Next
Set LiquidP = ThisWorkbook.Charts.Add
Set LiquidP = LiquidP.Location(where:=xlLocationAsObject, Name:="Exposure II")
With LiquidP
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V6:W10"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Liquidity Profile" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With LiquidP.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With LiquidP.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With LiquidP.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 86 'Range("I7").Top
.Left = 490 ' Range("I7").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 8 ---------------------------------------------------------
Dim SectorBreakdownC As Chart 'Credit Sector Breakdown
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each SectorBreakdownC In ThisWorkbook.Charts
If SectorBreakdownC.Name = "SectorBreakdownC" Then
SectorBreakdownC.Delete
End If
Next
Set SectorBreakdownC = ThisWorkbook.Charts.Add
Set SectorBreakdownC = SectorBreakdownC.Location(where:=xlLocationAsObject, Name:="Exposure II")
With SectorBreakdownC
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("P27:Q36"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With SectorBreakdownC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With SectorBreakdownC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("A27").Top
.Left = 0 ' Range("A27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 9 ---------------------------------------------------------
Dim RatingDistrib As Chart 'Credit Ratings Distribution
'ActiveSheet.ChartObjects.Delete '.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each RatingDistrib In ThisWorkbook.Charts 'Replacing Old Chart by New One
If RatingDistrib.Name = "RatingDistrib" Then
RatingDistrib.Delete
End If
Next
Set RatingDistrib = ThisWorkbook.Charts.Add
Set RatingDistrib = RatingDistrib.Location(where:=xlLocationAsObject, Name:="Exposure II")
With RatingDistrib
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("S27:T34"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Ratings Distribution" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With RatingDistrib.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With RatingDistrib.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With RatingDistrib.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("E27").Top
.Left = 250 ' Range("E27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 10 ---------------------------------------------------------
Dim DV01 As Chart 'DV01 by Maturity Bucket
'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS
For Each DV01 In ThisWorkbook.Charts 'Replacing Old Chart by New One
If DV01.Name = "DV01" Then
DV01.Delete
End If
Next
Set DV01 = ThisWorkbook.Charts.Add
Set DV01 = DV01.Location(where:=xlLocationAsObject, Name:="Exposure II")
With DV01
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V27:W30"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "DV01 by Maturity Bucket" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line
With DV01.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With DV01.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With
With DV01.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With
With .Parent
.Top = 330 'Range("I27").Top
.Left = 490 ' Range("I27").Left
.Height = 186
.Width = 240
End With
With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With
'Phase 11 ---------------------------------------------------------
End With
End Sub
Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Categories
- 140.8K All Categories
- 103.6K Programming Languages
- 6.4K Assembler Developer
- 401 Assembly Code Share
- 239 Getting started in assembly
- 4.6K x86 Assembly
- 1.9K Basic
- 97 Qbasic
- 39.9K C and C++
- 5.6K Beginner C/C++
- 330 C/C++ on Linux/Unix
- 450 C/C++ Windows API
- 522 C++ Builder
- 253 C++ Game Development
- 3.3K C++ MFC
- 103 C++.NET
- 404 Visual C++
- 2.9K C#
- 7.9K Delphi and Kylix
- 334 Advanced Delphi
- 360 Delphi beginners
- 4 Haskell
- 9.7K Java
- 56 Enterprise JavaBeans
- 1.3K Java Beginners
- 304 Java Server Pages
- 4.1K Pascal
- 1.3K Perl
- 11 Perl 6
- 2K PHP
- 546 Python
- 37 Ruby
- 4.4K VB.NET
- 258 Advanced VB.Net
- 1.6K VBA
- 20.9K Visual Basic
- 767 Access databases and VB
- 831 Advance Visual Basic
- 1.2K Beginner VB
- 2.6K Game programming
- 315 Console programming
- 90 DirectX Game dev
- 1 Minecraft
- 112 Newbie Game Programmers
- 2 Oculus Rift
- 9K Applications
- 1.8K Computer Graphics
- 279 3D Graphics
- 129 DirectX
- 125 OpenGL
- 740 Computer Hardware
- 9 Cooling & Overclocking
- 3.4K Database & SQL
- 1.1K Access
- 91 ADO Programming
- 288 MySQL
- 358 Oracle
- 440 SQL-Server
- 535 Electronics development
- 1.6K Matlab
- 628 Sound & Music
- 25 DirectSound
- 257 XML Development
- 3.3K Classifieds
- 200 Co-operative Projects
- 198 For sale
- 190 FreeLance Software City
- 1.9K Jobs Available
- 603 Jobs Wanted
- 209 Wanted
- 2.9K Microsoft .NET
- 1.8K ASP.NET
- 1.1K .NET General
- 22 .NET WEB-Services
- 129 .NET WinForms
- 14 .NET XML
- 50 ADO.NET
- 144 C# & VB.NET School Support
- 3.4K Miscellaneous
- 4 Join the Team
- 354 Comments on this site
- 69 Computer Emulators
- 2.1K General programming
- 187 New programming languages
- 621 Off topic board
- 200 Mobile & Wireless
- 72 Android
- 126 Palm Pilot
- 338 Multimedia
- 154 Demo programming
- 184 MP3 programming
- 0 Bash scripts
- 27 Cloud Computing
- 1 Witsbits Go Cloud
- 53 FreeBSD
- 1.7K LINUX programming
- 1 Awk scripting
- 332 Linux Support
- 0 Sed scripting
- 370 MS-DOS
- 0 Shell scripting
- 321 Windows CE & Pocket PC
- 4.1K Windows programming
- 177 COM/DCOM
- 61 Networking And Security
- 17 Windows 2003 Server
- 6 Windows Vista
- 176 Windows XP
- 941 Software Development
- 417 Algorithms
- 68 Object Orientation
- 24 RUP & UML
- 92 Project Management
- 95 Quality & Testing
- 268 Security
- 63 Evil Scripting
- 81 Hacking
- 7.7K WEB-Development
- 1.8K Active Server Pages
- 61 AJAX
- 4 Bootstrap Themes
- 55 CGI Development
- 28 ColdFusion
- 224 Flash development
- 1.4K HTML & WEB-Design
- 1.4K Internet Development
- 131 Mobile Internet & Messaging
- 211 Wireless development
- 2.2K JavaScript
- 37 JQuery
- 304 WEB Servers
- 153 Apache
- 79 IIS
- 150 WEB-Services / SOAP