VBA批量绘制动态化数据图表

2024-03-18 10:12
文章标签 数据 图表 绘制 批量 vba 动态化

本文主要是介绍VBA批量绘制动态化数据图表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

对于大多数使用的VBA的人来说，处理excel工作簿中批量数据是日常工作中常见的情形，但与此同时，VBA在批量绘图方面同样有简洁快速的特点。

说起Excel画图表，考虑一个问题，excel图表的本质是什么？很多人会不假思索地回答，数据图形化的一种表达形式。是的，excel图表是图表框架加入原始数据形成的，这很容易理解，因为可以在excel不用任何数据就可以绘制excel图表，这表明图表中的框架不依附于数据可以独立存在。

有人可能会好奇，excel绘制图表就直接原始数据插入图表就可以了，为什么要讨论excel图表和数据的关系呢？因为在实际应用中，当已知excel图表形式，即知道要画什么图表，但是不知道原始数据，可以先画出框架，再填入数据源，有固定图表框架这种情况下，有一组新数据就会有一个新图表。

有了以上基础，可以参考以下实战案例：有8个工作簿，每一个工作簿都有若干行和列的数据，行坐标是日期，列坐标是数据项目名称，工作簿中包含有Avg. Best FFL, Avg. Defocus, Avg. DOF, Monitor MT,这四项的列坐标在不同工作簿的位置不一样，现需要在一张工作簿显示这四项最近一个月的数据。

分析以上需求可知，在一页显示8张工作簿的4项需要32张图表才能满足需求，若同时批量画出来必然不美观，此时可考虑动态图表，即点击某张工作簿标题就显示这张工作簿包含的四项内容；又因为需要显示最近一个月的数据，因此数据源需要及时更新，而四张图表则可以通过VBA代码批量搭框架加入数据源画出来。

Step 1: 将原始数据导入到多个空白工作簿作为数据源；

导入
数据源
空工作簿
多个数据源工作簿

Step 2: 建立空白图表框架，将工作簿的某列数据导入框架得到图表，重复4次即可得到一页4张；

数据
批量
图表框架
单个数据图表
多个数据图表

Step 3: 利用列表框动态触发事件，更新数据源可得到新的图表；

具体实现方法如下：


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'代码块1：这部分代码是利用子模块将多个工作簿数据集中在一张工作簿多个工作表。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Sub Daily_Monitor()
Application.DisplayAlerts = False
Dim wks As Worksheet, wks1 As Worksheet
For Each wks In ThisWorkbook.SheetsIf wks.Name <> "IMPRO Monitor Summary" And wks.Name <> "Dynamic Summary" Thenwks.DeleteEnd If
Next
Call Iolite_Post
Call Iolite_Pre
Call Quartz_Monitor
Call RADAR2_Monitor
Call Ammolite_Monitor
Call OVM7690_Monitor
Call Garnet_Monitor
Call Spinel_Monitor
For Each wks1 In ThisWorkbook.SheetsIf wks1.Name <> "IMPRO Monitor Summary" And wks1.Name <> "Ammolite_Pre_04" Thenwks1.Columns.Hidden = FalseEnd If
Next
Application.DisplayAlerts = True
End Sub''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'子代码模块：这部分代码是将每一张工作簿的原始数据复制到每一张命名相同的工作表。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
Sub Iolite_Post()
Dim ws As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Iolite\DAILY MONITOR\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Iolite\DAILY MONITOR\"
Workbooks.Open (pth & "Iolite Record.xlsx")
For Each ws In ActiveWorkbook.SheetsIf ws.Name = "CIP4T11-POST-850nm" Thenws.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Iolite_Post_11"
'    ElseIf ws.Name = "CIP4T02" Then
'        ws.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")
'        ThisWorkbook.ActiveSheet.Name = "Iolite_Post_02"End If
Next ws
Workbooks("Iolite Record.xlsx").Close 0
End SubSub Iolite_Pre()
Dim ws1 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Iolite\DAILY MONITOR\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Iolite\DAILY MONITOR\"
Workbooks.Open (pth & "Iolite IR850 Record.xlsx")
For Each ws1 In ActiveWorkbook.Sheets
'    If ws1.Name = "CIP4T03" Then
'        ws1.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")
'        ThisWorkbook.ActiveSheet.Name = "Iolite_Pre_03"If ws1.Name = "CIP4T05-PRE" Thenws1.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Iolite_Pre_05"ElseIf ws1.Name = "CIP4T08" Thenws1.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Iolite_Pre_08"End If
Next ws1
Workbooks("Iolite IR850 Record.xlsx").Close 0
End SubSub Quartz_Monitor()
Dim ws2 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Quartz\ENG\Daily Monitor\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Quartz\ENG\Daily Monitor\"
Workbooks.Open (pth & "Quartz Daily Monitor Record-v1.0(CIP4T03).xlsm")
For Each ws2 In ActiveWorkbook.SheetsIf ws2.Name = "Record" Thenws2.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Quartz_Pre_03"End If
Next ws2
Workbooks("Quartz Daily Monitor Record-v1.0(CIP4T03).xlsm").Close 0
End SubSub RADAR2_Monitor()
On Error Resume Next
Dim ws3 As Worksheet, ws4 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\OVM6211-RADA-R2\DAILY MONITOR\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\OVM6211-RADA-R2\DAILY MONITOR\"Workbooks.Open (pth & "RADA R2 Pre Record.xlsx")
For Each ws3 In ActiveWorkbook.SheetsIf ws3.Name = "CIP4T10" Thenws3.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "RADA-R2_Pre_10"End If
Next ws3
Workbooks("RADA R2 Pre Record.xlsx").Close 0ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\OVM6211-RADA-R2\DAILY MONITOR\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\OVM6211-RADA-R2\DAILY MONITOR\"
Workbooks.Open (pth & "RADA R2 Post Record.xlsx")For Each ws4 In ActiveWorkbook.SheetsIf ws4.Name = "CIP4T02" Thenws4.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "RADA-R2_Post_02"End If
Next ws4
Workbooks("RADA R2 Post Record.xlsx").Close 0
End SubSub Ammolite_Monitor()
On Error Resume Next
Dim ws5 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Ammolite\ENG\Daily Monitor\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Ammolite\ENG\Daily Monitor\"
Workbooks.Open (pth & "Ammolite Daily Monitor Record-v3.1.xlsm")For Each ws5 In ActiveWorkbook.SheetsIf ws5.Name = "Record" Thenws5.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Ammolite_Pre_04"End If
Next ws5
Workbooks("Ammolite Daily Monitor Record-v3.1.xlsm").Close 0
End SubSub OVM7690_Monitor()
Dim ws6 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\VMA001\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\VMA001\"
Workbooks.Open (pth & "OVM7690 Correlation Result.xlsx")
For Each ws6 In ActiveWorkbook.SheetsIf ws6.Name = "Monitor Record" Thenws6.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "7690_01"End If
Next ws6
Workbooks("OVM7690 Correlation Result.xlsx").Close 0
End SubSub Garnet_Monitor()
On Error Resume Next
Dim ws7 As Worksheet, ws9 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Garnet\ENG\Daily Monitor\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Garnet\ENG\Daily Monitor\"
Workbooks.Open (pth & "Garnet Daily Monitor Record-CIP4T15 &CIP4T12.xlsm")
For Each ws7 In ActiveWorkbook.SheetsIf ws7.Name = "Record" Thenws7.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Garnet_Pre_12"End If
Next ws7
Workbooks("Garnet Daily Monitor Record-CIP4T15 &CIP4T12.xlsm").Close 0
Workbooks.Open (pth & "Garnet Daily Monitor Record-CIP4T13.xlsm")
For Each ws9 In ActiveWorkbook.SheetsIf ws9.Name = "Record" Thenws9.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Garnet_Pre_13"End If
Next ws9
Workbooks("Garnet Daily Monitor Record-CIP4T13.xlsm").Close 0
End SubSub Spinel_Monitor()
Dim ws8 As Worksheet
ChDrive Z
ChDir "Z:\Fab-Transfer\Impro4 Raw-Data\Spinel\Macro\"
pth = "Z:\Fab-Transfer\Impro4 Raw-Data\Spinel\Macro\"
Workbooks.Open (pth & "Spinel Daily Monitor Record-V2.xlsm")
For Each ws8 In ActiveWorkbook.SheetsIf ws8.Name = "Record" Thenws8.Copy after:=Workbooks("IMPRO Monitor Summary.xlsm").Sheets("IMPRO Monitor Summary")ThisWorkbook.ActiveSheet.Name = "Spinel_Pre_14"End If
Next ws8
Workbooks("Spinel Daily Monitor Record-V2.xlsm").Close 0
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'代码块2：这部分代码是创建图表框架，然后定义工作簿的数据源，利用ListBox clik事件绘制图表
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
Private Sub ListBox1_Click()
Application.DisplayAlerts = False
Sheets("IMPRO Monitor Summary").ChartObjects.Delete
Dim shp1 As Shape, shp2 As Shape, shp3 As Shape, shp4 As Shape
Dim arr1, arr2, arr3, arr4, arr5, arr6, arr7, arr8
Dim k As Integer, i As Integer
Dim ser As Series, pt As Point
Dim ws As Worksheet
With Sheets("IMPRO Monitor Summary").ShapesSet shp1 = .AddChart(xlLineMarkers, 5, 0, 320, 220)Set shp2 = .AddChart(xlLineMarkers, 325, 0, 320, 220)Set shp3 = .AddChart(xlLineMarkers, 5, 220, 320, 220)Set shp4 = .AddChart(xlLineMarkers, 325, 220, 320, 220)
End With'    Iolite_x = Array("Iolite_Pre_03" Or "Iolite_Pre_05" Or "Iolite_Pre_08")
'    For x = 1 To 3
'    NextSelect Case ListBox1.ValueCase "Quartz_Pre_03"With shp1.Chart.SetSourceData Union(Sheets("Quartz_Pre_03").Range("I1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Quartz_Pre_03").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Quartz_Pre_03").Columns("I:I").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 0.0025 Or ser.Values(k) < -0.0025 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Quartz_Pre_03").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Quartz_Pre_03").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Quartz_Pre_03").Columns("P:P").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("Quartz_Pre_03").Range("Q1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Quartz_Pre_03").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Quartz_Pre_03").Columns("Q:Q").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0.01End WithWith shp4.Chart.SetSourceData Source:=Sheets("Quartz_Pre_03").Range("S1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Quartz_Pre_03").Range("S2:AI2")
'        arr2 = Sheets("Iolite_Pre_03").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1Next
'        j = 1
'        Dim xcht As ChartObject, xser As Series
'        For Each xcht In Sheets("IMPRO Monitor Summary").ChartObjects
'            For Each xser In xcht.Chart.SeriesCollection
'                xser.XValues = arr2(j, 1)
'                j = j + 1
'            Next
'        Next
'        cht.FullSeriesCollection.XValues = arr1
'''    cht1.Chart.Legend.Position = xlLegendPositionBottomCase "Iolite_Pre_05"With shp1.Chart.SetSourceData Union(Sheets("Iolite_Pre_05").Range("L1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_05").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_05").Columns("L:L").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 2.5 Or ser.Values(k) < -2.5 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Iolite_Pre_05").Range("J1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_05").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_05").Columns("J:J").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 0: .Axes(xlValue).MaximumScale = 0.01End WithWith shp3.Chart.SetSourceData Union(Sheets("Iolite_Pre_05").Range("K1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_05").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_05").Columns("K:K").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 34: .Axes(xlValue).MaximumScale = 44End WithWith shp4.Chart.SetSourceData Sheets("Iolite_Pre_05").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Iolite_Pre_05").Range("P2:AF2")
'        arr2 = Sheets("Iolite_Pre_05").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1NextCase "Iolite_Pre_08"With shp1.Chart.SetSourceData Union(Sheets("Iolite_Pre_08").Range("L1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_08").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_08").Columns("L:L").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 2.5 Or ser.Values(k) < -2.5 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Iolite_Pre_08").Range("J1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_08").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_08").Columns("J:J").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 0: .Axes(xlValue).MaximumScale = 0.01End WithWith shp3.Chart.SetSourceData Union(Sheets("Iolite_Pre_08").Range("K1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Pre_08").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Pre_08").Columns("K:K").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 34: .Axes(xlValue).MaximumScale = 44End WithWith shp4.Chart.SetSourceData Sheets("Iolite_Pre_08").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Iolite_Pre_08").Range("P2:AF2")
'        arr2 = Sheets("Iolite_Pre_08").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1NextCase "RADA-R2_Post_02"With shp1.Chart.SetSourceData Union(Sheets("RADA-R2_Post_02").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("RADA-R2_Post_02").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Post_02").Columns("B:B").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 2 Or ser.Values(k) < -2 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("RADA-R2_Post_02").Range("C1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("RADA-R2_Post_02").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Post_02").Columns("C:C").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End WithWith shp3.Chart.SetSourceData Union(Sheets("RADA-R2_Post_02").Range("D1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("RADA-R2_Post_02").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Post_02").Columns("D:D").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp3.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 5 Or ser.Values(k) < -10 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp4.Chart.SetSourceData Union(Sheets("RADA-R2_Post_02").Range("E2:I2"), Sheets("RADA-R2_Post_02").Range("E1048576").End(xlUp)(-28, 1).Resize(30, 5)).Axes(xlValue).MinimumScale = 0.26: .Axes(xlValue).MaximumScale = 0.31.HasTitle = True.ChartTitle.Text = "Post FFL In Different Field".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End WithCase "Iolite_Post_11"With shp1.Chart.SetSourceData Union(Sheets("Iolite_Post_11").Range("C1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Post_11").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Post_11").Columns("C:C").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 2 Or ser.Values(k) < -2 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Iolite_Post_11").Range("D1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Post_11").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Post_11").Columns("D:D").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End WithWith shp3.Chart.SetSourceData Union(Sheets("Iolite_Post_11").Range("E1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Iolite_Post_11").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Iolite_Post_11").Columns("E:E").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -10: .Axes(xlValue).MaximumScale = 0End Withk = 1For Each ser In shp3.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 5 Or ser.Values(k) < -10 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp4.Chart.SetSourceData Union(Sheets("Iolite_Post_11").Range("F2:J2"), Sheets("Iolite_Post_11").Range("F1048576").End(xlUp)(-28, 1).Resize(30, 5)).Axes(xlValue).MinimumScale = 0.22: .Axes(xlValue).MaximumScale = 0.27.HasTitle = True.ChartTitle.Text = "Post FFL In Different Field".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End With'    Case "Ruby2_Post_16"
'        With shp1.Chart
'            .SetSourceData Union(Sheets("Ruby2_Post_16").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Post_16").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Post_16").Columns("B:B").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5
'        End With
'        k = 1
'        For Each ser In shp1.Chart.FullSeriesCollection
'            For Each pt In ser.Points
'                If ser.Values(k) > 2.5 Or ser.Values(k) < -2.5 Then
'                    pt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)
'                End If
'                k = k + 1
'            Next
'        Next
'        With shp2.Chart
'            .SetSourceData Union(Sheets("Ruby2_Post_16").Range("C1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Post_16").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Post_16").Columns("C:C").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5
'        End With
'        With shp3.Chart
'            .SetSourceData Union(Sheets("Ruby2_Post_16").Range("D1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Post_16").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Post_16").Columns("D:D").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = 0: .Axes(xlValue).MaximumScale = 10
'        End With
'        k = 1
'        For Each ser In shp3.Chart.FullSeriesCollection
'            For Each pt In ser.Points
'                If ser.Values(k) > 5 Then
'                    pt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)
'                End If
'                k = k + 1
'            Next
'        Next
'        With shp4.Chart
'            .SetSourceData Union(Sheets("Ruby2_Post_16").Range("E2:I2"), Sheets("Ruby2_Post_16").Range("E1048576").End(xlUp)(-28, 1).Resize(30, 5))
'            .Axes(xlValue).MinimumScale = 0.265: .Axes(xlValue).MaximumScale = 0.295
'            .HasTitle = True
'            .ChartTitle.Text = "Post FFL In Different Field"
'            .Legend.Position = xlLegendPositionBottom
'            .Legend.Font.Size = 6
'            .Legend.Height = 30
'        End With
'
'    Case "Ruby2_Pre_06"
'        With shp1.Chart
'            .SetSourceData Union(Sheets("Ruby2_Pre_06").Range("L1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Pre_06").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Pre_06").Columns("L:L").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5
'        End With
'        k = 1
'        For Each ser In shp1.Chart.FullSeriesCollection
'            For Each pt In ser.Points
'                If ser.Values(k) > 2.5 Or ser.Values(k) < -2.5 Then
'                    pt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)
'                End If
'                k = k + 1
'            Next
'        Next
'        With shp2.Chart
'            .SetSourceData Union(Sheets("Ruby2_Pre_06").Range("J1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Pre_06").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Pre_06").Columns("J:J").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0
'        End With
'        With shp3.Chart
'            .SetSourceData Union(Sheets("Ruby2_Pre_06").Range("K1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("Ruby2_Pre_06").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("Ruby2_Pre_06").Columns("K:K").Range("a2")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = 45: .Axes(xlValue).MaximumScale = 55
'        End With
'        With shp4.Chart
'            .SetSourceData Sheets("Ruby2_Pre_06").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 17)
'            .Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3
'            .HasTitle = True
'            .ChartTitle.Text = "Monitor MTF"
'            .Legend.Position = xlLegendPositionBottom
'            .Legend.Font.Size = 6
'            .Legend.Height = 30
'        End With
'        arr1 = Sheets("Ruby2_Pre_06").Range("P2:AF2")
''        arr2 = Sheets("Ruby2_Pre_06").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)
'        i = 1
'        For Each ser In shp4.Chart.SeriesCollection
'            ser.Name = arr1(1, i)
'            i = i + 1
'        NextCase "RADA-R2_Pre_10"With shp1.Chart.SetSourceData Union(Sheets("RADA-R2_Pre_10").Range("L1048576").End(xlUp)(-13, 1).Resize(15, 1), _Sheets("RADA-R2_Pre_10").Range("A1048576").End(xlUp)(-13, 1).Resize(15, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Pre_10").Columns("L:L").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 3 Or ser.Values(k) < -3 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("RADA-R2_Pre_10").Range("J1048576").End(xlUp)(-13, 1).Resize(15, 1), _Sheets("RADA-R2_Pre_10").Range("A1048576").End(xlUp)(-13, 1).Resize(15, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Pre_10").Columns("J:J").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("RADA-R2_Pre_10").Range("K1048576").End(xlUp)(-13, 1).Resize(15, 1), _Sheets("RADA-R2_Pre_10").Range("A1048576").End(xlUp)(-13, 1).Resize(15, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("RADA-R2_Pre_10").Columns("K:K").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 25: .Axes(xlValue).MaximumScale = 30End WithWith shp4.Chart.SetSourceData Sheets("RADA-R2_Pre_10").Range("P1048576").End(xlUp)(-13, 1).Resize(15, 17).Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("RADA-R2_Pre_10").Range("P2:AF2")
'        arr2 = Sheets("RASA_Pre_10").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1Next'    Case "RASA_Pre_09"
'        With shp1.Chart
'            .SetSourceData Union(Sheets("RASA_Pre_09").Range("T1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("RASA_Pre_09").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("RASA_Pre_09").Columns("T:T").Range("a1")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005
'        End With
'        k = 1
'        For Each ser In shp1.Chart.FullSeriesCollection
'            For Each pt In ser.Points
'                If ser.Values(k) > 0.003 Or ser.Values(k) < -0.003 Then
'                    pt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)
'                End If
'                k = k + 1
'            Next
'        Next
'        With shp2.Chart
'            .SetSourceData Union(Sheets("RASA_Pre_09").Range("AA1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("RASA_Pre_09").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("RASA_Pre_09").Columns("AA:AA").Range("a1")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005
'        End With
'        With shp3.Chart
'            .SetSourceData Union(Sheets("RASA_Pre_09").Range("AB1048576").End(xlUp)(-28, 1).Resize(30, 1), _
'                Sheets("RASA_Pre_09").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1))
'            .HasLegend = False
'            .HasTitle = True
'            .ChartTitle.Text = Sheets("RASA_Pre_09").Columns("AB:AB").Range("a1")
'            .Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1
'            .Axes(xlCategory).TickLabels.Font.Size = 6
'            .Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005
'        End With
'        With shp4.Chart
'            .SetSourceData Sheets("RASA_Pre_09").Range("AD1048576").End(xlUp)(-28, 1).Resize(30, 17)
'            .Axes(xlValue).MinimumScale = -3: .Axes(xlValue).MaximumScale = 3
'            .HasTitle = True
'            .ChartTitle.Text = "Monitor MTF"
'            .Legend.Position = xlLegendPositionBottom
'            .Legend.Font.Size = 6
'            .Legend.Height = 30
'        End With
'        arr1 = Sheets("RASA_Pre_09").Range("AD1:AT1")
''        arr2 = Sheets("RASA_Pre_09").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)
'        i = 1
'        For Each ser In shp4.Chart.SeriesCollection
'            ser.Name = arr1(1, i)
'            i = i + 1
'        NextCase "Garnet_Pre_12"With shp1.Chart.SetSourceData Union(Sheets("Garnet_Pre_12").Range("I1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_12").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_12").Columns("I:I").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0.01End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 0.003 Or ser.Values(k) < -0.003 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Garnet_Pre_12").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_12").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_12").Columns("P:P").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("Garnet_Pre_12").Range("Q1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_12").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_12").Columns("Q:Q").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0.01End WithWith shp4.Chart.SetSourceData Sheets("Garnet_Pre_12").Range("S1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Garnet_Pre_12").Range("S2:AI2")
'        arr2 = Sheets("Garnet_Pre_12").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1NextCase "Garnet_Pre_13"With shp1.Chart.SetSourceData Union(Sheets("Garnet_Pre_13").Range("T1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_13").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_13").Columns("T:T").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0.01End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 0.003 Or ser.Values(k) < -0.003 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Garnet_Pre_13").Range("AA1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_13").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_13").Columns("AA:AA").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("Garnet_Pre_13").Range("AB1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Garnet_Pre_13").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Garnet_Pre_13").Columns("AB:AB").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.01: .Axes(xlValue).MaximumScale = 0.01End WithWith shp4.Chart.SetSourceData Sheets("Garnet_Pre_13").Range("AD1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Garnet_Pre_13").Range("AD1:AT1")
'        arr2 = Sheets("Garnet_Pre_12").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1NextCase "Spinel_Pre_14"With shp1.Chart.SetSourceData Union(Sheets("Spinel_Pre_14").Range("I1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Spinel_Pre_14").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Spinel_Pre_14").Columns("I:I").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 0.002 Or ser.Values(k) < -0.002 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Spinel_Pre_14").Range("P1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Spinel_Pre_14").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Spinel_Pre_14").Columns("P:P").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("Spinel_Pre_14").Range("Q1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Spinel_Pre_14").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Spinel_Pre_14").Columns("Q:Q").Range("a2").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp4.Chart.SetSourceData Sheets("Spinel_Pre_14").Range("S1048576").End(xlUp)(-28, 1).Resize(30, 17).Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End Witharr1 = Sheets("Spinel_Pre_14").Range("S2:AI2")
'        arr2 = Sheets("Garnet_Pre_12").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)i = 1For Each ser In shp4.Chart.SeriesCollectionser.Name = arr1(1, i)i = i + 1NextCase "Ammolite_Pre_04"With shp1.Chart.SetSourceData Union(Sheets("Ammolite_Pre_04").Range("T1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Ammolite_Pre_04").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Ammolite_Pre_04").Columns("T:T").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End Withk = 1For Each ser In shp1.Chart.FullSeriesCollectionFor Each pt In ser.PointsIf ser.Values(k) > 0.003 Or ser.Values(k) < -0.003 Thenpt.Format.Fill.ForeColor.RGB = vbRed                 'RGB(255, 0, 0)End Ifk = k + 1NextNextWith shp2.Chart.SetSourceData Union(Sheets("Ammolite_Pre_04").Range("AA1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Ammolite_Pre_04").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Ammolite_Pre_04").Columns("AA:AA").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp3.Chart.SetSourceData Union(Sheets("Ammolite_Pre_04").Range("AB1048576").End(xlUp)(-28, 1).Resize(30, 1), _Sheets("Ammolite_Pre_04").Range("B1048576").End(xlUp)(-28, 1).Resize(30, 1)).HasLegend = False.HasTitle = True.ChartTitle.Text = Sheets("Ammolite_Pre_04").Columns("AB:AB").Range("a1").Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = -0.005: .Axes(xlValue).MaximumScale = 0.005End WithWith shp4.ChartSet ws = Sheets("Ammolite_Pre_04")n = ws.Rows(1048576).End(xlUp).Row: m = n - 29.SetSourceData Union(ws.Range("AD1:AE1"), ws.Range("AD" & m & ":AE" & n), ws.Range("AI1"), _ws.Range("AI" & m & ":AI" & n), ws.Range("AM1:AT1"), ws.Range("AM" & m & ":AT" & n)).Axes(xlValue).MinimumScale = -5: .Axes(xlValue).MaximumScale = 5.HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30End With'        arr2 = Sheets("Ammolite_Pre_04").Range("A1048576").End(xlUp)(-28, 1).Resize(30, 1)
'        i = 1
'        For Each ser In shp4.Chart.SeriesCollection
'            ser.Name = arr1(1, i)
'            i = i + 1
'        NextCase "7690_01"shp1.Delete: shp2.Delete: shp3.DeleteWith shp4.Chart.SetSourceData Union(Sheets("7690_01").Range("E1048576").End(xlUp)(-28, 1).Resize(30, 5), Sheets("7690_01").Range("E1:I1")).HasTitle = True.ChartTitle.Text = "Monitor MTF".Legend.Position = xlLegendPositionBottom.Legend.Font.Size = 6.Legend.Height = 30.Axes(xlCategory).CategoryType = xlCategoryScale: .Axes(xlCategory).TickLabelSpacing = 1.Axes(xlCategory).TickLabels.Font.Size = 6.Axes(xlValue).MinimumScale = 44: .Axes(xlValue).MaximumScale = 56End WithEnd Select
Application.DisplayAlerts = True
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'代码块3：这部分代码利用Listbox GotFocus事件切换需要绘制图表的数据源绘制新的图表
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
Private Sub ListBox1_GotFocus()
Dim ar_item
Me.ListBox1.Clear
ar_item = Array("Iolite_Pre_05", "Iolite_Pre_08", "Iolite_Post_11", "Quartz_Pre_03", "RADA-R2_Post_02", "RADA-R2_Pre_10", _"Garnet_Pre_12", "Garnet_Pre_13", "Spinel_Pre_14", "Ammolite_Pre_04", "7690_01")For x = 0 To UBound(ar_item)Me.ListBox1.AddItem ar_item(x)Next
'With Me.ListBox1
'    .AddItem "Iolite_Pre_03"
'    .AddItem "Iolite_Pre_05"
'    .AddItem "Iolite_Pre_08"
'    .AddItem "Iolite_Post_02"
'    .AddItem "Iolite_Post_11"
'    .AddItem "Ruby2_Post_16"
'    .AddItem "Ruby2_Pre_06"
'    .AddItem "RASA_Pre_10"
'    .AddItem "RASA_Pre_09"
'    .AddItem "Garnet_Pre_12"
'    .AddItem "Spinel_Pre_14"
'    .AddItem "Ammolite_Pre_04"
'    .AddItem "7690_01"
'End With
End Sub

利用上述代码形成动态图表如下图所示，按钮可以更新数据源，利用滚动条可以动态选择想要查看的数据图表，至于横坐标的日期区间则可以代码动态选择数据源实现，纵坐标的范围则可以代码控制图标坐标轴区间实现。

值得注意的是，代码块1的代码可以写在Excel子模块里面，但是模块2，3的代码则必须写在工作表里面，这是因为触发事件的对象是工作表。
在这里插入图片描述
除此以外，代码变量的声名应该符合规则，或者取消代码强制变量声明；以上代码可以完成动态绘制多个数据的图表，其中，对于图表属性的定义以及触发事件的控制值得参考。

这篇关于VBA批量绘制动态化数据图表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/822015。

相关文章

大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
使用SecondaryNameNode恢复NameNode的数据

使用SecondaryNameNode恢复NameNode的数据

1）需求： NameNode进程挂了并且存储的数据也丢失了，如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2）故障模拟 （1）kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 （2）删除NameNode存储的数据（/opt/module/hadoop-3.1.4/data/tmp/dfs/na
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
Hadoop集群数据均衡之磁盘间数据均衡

Hadoop集群数据均衡之磁盘间数据均衡

生产环境，由于硬盘空间不足，往往需要增加一块硬盘。刚加载的硬盘没有数据时，可以执行磁盘数据均衡命令。（Hadoop3.x新特性） plan后面带的节点的名字必须是已经存在的，并且是需要均衡的节点。 如果节点不存在，会报如下错误： 如果节点只有一个硬盘的话，不会创建均衡计划： （1）生成均衡计划 hdfs diskbalancer -plan hadoop102 （2）执行均衡计划 hd
阅读更多...
【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯，先赞后看哦~🎈🎈 🏆 作者简介：景天科技苑 🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。 🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi
阅读更多...
烟火目标检测数据集 7800张 烟火检测 带标注 voc yolo

烟火目标检测数据集 7800张 烟火检测 带标注 voc yolo

一个包含7800张带标注图像的数据集，专门用于烟火目标检测，是一个非常有价值的资源，尤其对于那些致力于公共安全、事件管理和烟花表演监控等领域的人士而言。下面是对此数据集的一个详细介绍： 数据集名称：烟火目标检测数据集 数据集规模： 图片数量：7800张类别：主要包含烟火类目标，可能还包括其他相关类别，如烟火发射装置、背景等。格式：图像文件通常为JPEG或PNG格式；标注文件可能为X
阅读更多...
pandas数据过滤

pandas数据过滤

Pandas 数据过滤方法 Pandas 提供了多种方法来过滤数据，可以根据不同的条件进行筛选。以下是一些常见的 Pandas 数据过滤方法，结合实例进行讲解，希望能帮你快速理解。 1. 基于条件筛选行 可以使用布尔索引来根据条件过滤行。 import pandas as pd# 创建示例数据data = {'Name': ['Alice', 'Bob', 'Charlie', 'Dav
阅读更多...
【WebGPU Unleashed】1.1 绘制三角形

【WebGPU Unleashed】1.1 绘制三角形

一部2024新的WebGPU教程，作者Shi Yan。内容很好，翻译过来与大家共享，内容上会有改动，加上自己的理解。更多精彩内容尽在 dt.sim3d.cn ，关注公众号【sky的数孪技术】，技术交流、源码下载请添加微信号：digital_twin123 在 3D 渲染领域，三角形是最基本的绘制元素。在这里，我们将学习如何绘制单个三角形。接下来我们将制作一个简单的着色器来定义三角形内的像素
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2