Sub multpoly()
Dim X1 As Range, Y1 As Range, Y2 As Range, Y3 As Range, Y4 As Range, Y5 As Range, Y6 As Range, Y7 As Range, i As Integer, W1 As String, W2 As String, W3 As String, W4 As String, W5 As String, W6 As String, W7 As String, title As String, xaxis As String, yxis As String, 輸入y軸 As String, X軸型態 As Integer, MOStype As String
'detail = "MYMAX "
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
W = 0
l = 0
Start = 0
stops = 0
MOStype = 0
X軸型態 = 0
數據數目 = 0
Row = 9
For i = 1 To 200
For z = 1 To Row
If Cells(i, z).Value = "VD" Then 'find VD
數據數目 = 數據數目 + 1
D = z
End If
If Cells(i, z).Value = "VG" Then 'find VG
G = z
End If
If Cells(i, 1).Value = 空格 And Cells(i + 1, 1).Value = 空格 And Cells(i + 2, 1).Value = 空格 And Cells(i + 3, 1).Value = 空格 Then
GoTo W
End If
Next
Next
W:
For i = 1 To 200
For z = 1 To Row
If Cells(i, z).Value = "VD" Then 'find VD
ff = z
If Cells(i + 2, z).Value = Cells(i + 3, z).Value Then 'find VG or VD
If Cells(i + 2, z).Value < 0 Then 'find type
MOStype = -1
Else
MOStype = 1
End If
X軸型態 = 1
Else
X軸型態 = 2
End If
End If
If Cells(i, z).Value = "VG" Then 'find VG
qq = z
If Cells(i + 2, z).Value = Cells(i + 3, z).Value Then 'find VG or VD
If Cells(i + 2, z).Value < 0 Then 'find type
MOStype = -1
Else
MOStype = 1
End If
X軸型態 = 2
Else
X軸型態 = 1
End If
End If
If Cells(i, 1).Value = 空格 And Cells(i + 1, 1).Value = 空格 And Cells(i + 2, 1).Value = 空格 And Cells(i + 3, 1).Value = 空格 Then
GoTo R
End If
Next
Next
R:
For i = 1 To 200
For z = 1 To Row
If Cells(i, z).Value = "VD" Then
Start = i + 2
GoTo S
End If
Next
Next
S:
dd = 0
For i = Start To 200
If Cells(i, 1).Value > dd Then
dd = Cells(i, 1).Value
Else
stops = i - 1
GoTo F
End If
Next
F:
k = 0
For i = 1 To 200
If Cells(i, 1).Value = 1 Then
k = k + 1
If k = 2 Then
GG = i - stops
GoTo A
End If
End If
Next
A:
''''''''''''''''
k = 0
For i = 1 To 200
If Cells(i, 1).Value = 1 Then
k = k + 1
If X軸型態 = 1 Then
If k = 1 Then
S1 = Cells(i, ff).Value
End If
If k = 2 Then
S2 = Cells(i, ff).Value
End If
If k = 3 Then
S3 = Cells(i, ff).Value
End If
If k = 4 Then
S4 = Cells(i, ff).Value
End If
If k = 5 Then
S5 = Cells(i, ff).Value
End If
If k = 6 Then
S6 = Cells(i, ff).Value
End If
If k = 7 Then
S7 = Cells(i, ff).Value
End If
If Cells(i, 1).Value = 空格 And Cells(i + 1, 1).Value = 空格 And Cells(i + 2, 1).Value = 空格 And Cells(i + 3, 1).Value = 空格 Then
GoTo B
End If
End If
If X軸型態 = 2 Then
If k = 1 Then
S1 = Cells(i, qq).Value
End If
If k = 2 Then
S2 = Cells(i, qq).Value
End If
If k = 3 Then
S3 = Cells(i, qq).Value
End If
If k = 4 Then
S4 = Cells(i, qq).Value
End If
If k = 5 Then
S5 = Cells(i, qq).Value
End If
If k = 6 Then
S6 = Cells(i, qq).Value
End If
If k = 7 Then
S7 = Cells(i, qq).Value
End If
If k = 8 Then
GoTo B
End If
End If
End If
Next
B:
'MsgBox ("MOStype" & MOStype)
' MsgBox ("X軸型態" & X軸型態)
' MsgBox ("數據數目" & 數據數目)
' MsgBox ("Start" & Start)
' MsgBox ("stops" & stops)
' MsgBox ("GG" & GG)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'MOStype = Application.InputBox("輸入type Ntype:1 Ptype:-1", "IV", "1")
'X軸型態 = Application.InputBox("輸入X軸type Vg:1,Vd:2", "IV", "1")
If MOStype = 1 And X軸型態 = 1 Then
W1 = "Vd= " & S1 & " V"
W2 = "Vd= " & S2 & " V"
W3 = "Vd= " & S3 & " V"
W4 = "Vd= " & S4 & " V"
W5 = "Vd= " & S5 & " V"
W6 = "Vd= " & S6 & " V"
W7 = "Vd= " & S7 & " V"
title = detail & "Id-Vg"
xaxis = "Vg (V)"
yaxis = "Id (A)"
輸入y軸 = "輸入abs(Id)"
Range("J3").FormulaR1C1 = 輸入y軸
Range("J5").FormulaR1C1 = "=abs(RC[-7])" 'abs(Id) A
Range("J5").AutoFill Destination:=Range("J5:J159"), Type:=xlFillDefault
End If
If MOStype = 1 And X軸型態 = 2 Then
W1 = "Vg= " & S1 & " V"
W2 = "Vg= " & S2 & " V"
W3 = "Vg= " & S3 & " V"
W4 = "Vg= " & S4 & " V"
W5 = "Vg= " & S5 & " V"
W6 = "Vg= " & S6 & " V"
W7 = "Vg= " & S7 & " V"
title = detail & "Id-Vd"
xaxis = "Vd (V)"
yaxis = "Id (uA/um)"
輸入y軸 = "輸入Id*E+6"
Range("J3").FormulaR1C1 = 輸入y軸
Range("J4").FormulaR1C1 = "=RC[-7]*1000000" 'Id*E+6 A->uA
Range("J4").AutoFill Destination:=Range("J4:J159"), Type:=xlFillDefault
End If
If MOStype = -1 And X軸型態 = 1 Then
W1 = "Vd= " & S1 & " V"
W2 = "Vd= " & S2 & " V"
W3 = "Vd= " & S3 & " V"
W4 = "Vd= " & S4 & " V"
W5 = "Vd= " & S5 & " V"
W6 = "Vd= " & S6 & " V"
W7 = "Vd= " & S7 & " V"
title = detail & "Id-Vg"
xaxis = "Vg (V)"
yaxis = "Id (A)"
輸入y軸 = "輸入abs(Id)"
Range("J3").FormulaR1C1 = 輸入y軸
Range("J4").FormulaR1C1 = "=ABS(RC[-7])" 'abs(Id)A
Range("J4").AutoFill Destination:=Range("J4:J159"), Type:=xlFillDefault
End If
If MOStype = -1 And X軸型態 = 2 Then
W1 = "Vg= " & S1 & " V"
W2 = "Vg= " & S2 & " V"
W3 = "Vg= " & S3 & " V"
W4 = "Vg= " & S4 & " V"
W5 = "Vg= " & S5 & " V"
W6 = "Vg= " & S6 & " V"
W7 = "Vg= " & S7 & " V"
title = detail & "Id-Vd"
xaxis = "Vd (V)"
yaxis = "Id (uA/um)"
輸入y軸 = "輸入abs(Id)*E+6"
Range("J3").Select
ActiveCell.FormulaR1C1 = 輸入y軸
Range("J4").FormulaR1C1 = "=ABS(RC[-7])*1000000" 'abs(Id)*E+6 A->uA
Range("J4").AutoFill Destination:=Range("J4:J159"), Type:=xlFillDefault
End If
'數據數目 = Application.InputBox("輸入數據數目", "IV", "1")
'Set X1 = Application.InputBox("輸入X1", "IV", "=R7C4:R27C4", Type:=8)
'Set Y1 = Application.InputBox(輸入y軸, "IV", "=R7C3:R27C3", Type:=8) '圖表範圍
If X軸型態 = 1 Then
Set X1 = Range(Cells(Start, G), Cells(stops, G))
End If
If X軸型態 = 2 Then
Set X1 = Range(Cells(Start, D), Cells(stops, D))
End If
Set Y1 = Range(Cells(Start, 10), Cells(stops, 10))
X1.Select
Charts.Add '加入圖表
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers '圖表形式
ActiveChart.SeriesCollection(1).XValues = X1
ActiveChart.SeriesCollection(1).Values = Y1
ActiveChart.Location Where:=xlLocationAsObject, Name:=Worksheets(1).Name '插入工作表sheet1
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = title 'title 名稱
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = xaxis 'x axis 名稱
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = yaxis 'y axis 名稱
End With
ActiveChart.ChartArea.Select ' 圖表區格式 Macro
' NEF User 在 2011/3/19 錄製的巨集
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "粗體"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
If MOStype = 1 And X軸型態 = 1 Then
ActiveChart.Axes(xlCategory).Select 'x axes
With ActiveChart.Axes(xlCategory)
.MinimumScale = -0.5
.MaximumScale = 1.5
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear 'x軸
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).Select 'y軸
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.00000000000001
.MaximumScale = 0.0001
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLogarithmic 'y軸取log
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlInside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
Selection.TickLabels.NumberFormatLocal = "0.E+00"
With ActiveChart.Axes(xlCategory) 'x軸
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End If
If MOStype = 1 And X軸型態 = 2 Then
ActiveChart.Axes(xlCategory).Select 'x axes
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 1.5
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlInside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).Select 'y axes
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnit = 10
.MajorUnit = 20
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlinear 'y軸
.DisplayUnit = xlNone
End With
With Selection
.MajorTickMark = xlInside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
Selection.TickLabels.NumberFormatLocal = "0"
With ActiveChart.Axes(xlCategory) 'x軸
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
End If
If MOStype = -1 And X軸型態 = 1 Then
ActiveChart.Axes(xlCategory).Select 'x axes
With ActiveChart.Axes(xlCategory)
.MinimumScale = -1.5
.MaximumScale = 0.5
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = True
.ScaleType = xlLinear 'x軸
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).Select 'y軸
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.00000000000001
.MaximumScale = 0.0001
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLogarithmic 'y軸取log
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlHigh
End With
Selection.TickLabels.NumberFormatLocal = "0.E+00"
With ActiveChart.Axes(xlCategory) 'x軸
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End If
If MOStype = -1 And X軸型態 = 2 Then
ActiveChart.Axes(xlCategory).Select 'x axes
With ActiveChart.Axes(xlCategory)
.MinimumScale = -1.5
.MaximumScale = 0
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = True
.DisplayUnit = xlNone
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlInside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).Select 'y axes
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnit = 5
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlinear 'y軸
.DisplayUnit = xlNone
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlHigh
End With
Selection.TickLabels.NumberFormatLocal = "0"
With ActiveChart.Axes(xlCategory) 'x軸
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
End If
ActiveChart.HasLegend = True
ActiveChart.SeriesCollection(1).Name = W1 ' 數據1條件
ActiveChart.PlotArea.Select '繪圖格式
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select '資料數列1格式
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If 數據數目 >= 2 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J32:J52"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入假的數據2
'Set Y2 = Application.InputBox("輸入Y2", "IV", "=D23:D52", Type:=8) '圖表範圍
dalta = stops - Start
Start = stops + GG
stops = Start + dalta
Set Y2 = Range("J" & Start & ":J" & stops)
ActiveChart.SeriesCollection(2).XValues = X1
ActiveChart.SeriesCollection(2).Values = Y2
ActiveChart.SeriesCollection(2).Name = W2 ' 數據2條件
ActiveChart.SeriesCollection(2).Select '資料數列2格式
With Selection.Border
.ColorIndex = 29
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
If 數據數目 >= 3 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J51:J71"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入數據3
Start = stops + GG
stops = Start + dalta
Set Y3 = Range("J" & Start & ":J" & stops)
'Set Y3 = Application.InputBox("輸入Y3", "IV", "=D23:D52", Type:=8) '圖表範圍
ActiveChart.SeriesCollection(3).XValues = X1
ActiveChart.SeriesCollection(3).Values = Y3
ActiveChart.SeriesCollection(3).Name = W3 ' 數據3條件
ActiveChart.SeriesCollection(3).Select '資料數列3格式
With Selection.Border
.ColorIndex = 7
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
If 數據數目 >= 4 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J73:J93"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入數據4
Start = stops + 5
stops = Start + dalta
Set Y4 = Range("J" & Start & ":J" & stops)
''Set Y4 = Application.InputBox("輸入Y4", "IV", "=D23:D52", Type:=8) '圖表範圍
ActiveChart.SeriesCollection(4).XValues = X1
ActiveChart.SeriesCollection(4).Values = Y4
ActiveChart.SeriesCollection(4).Name = W4 ' 數據4條件
ActiveChart.SeriesCollection(4).Select '資料數列4格式
With Selection.Border
.ColorIndex = 11
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
If 數據數目 >= 5 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J95:J115"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入數據5
Start = stops + 5
stops = Start + dalta
Set Y5 = Range("J" & Start & ":J" & stops)
'Set Y5 = Application.InputBox("輸入Y5", "IV", "=D23:D52", Type:=8) '圖表範圍
ActiveChart.SeriesCollection(5).XValues = X1
ActiveChart.SeriesCollection(5).Values = Y5
ActiveChart.SeriesCollection(5).Name = W5 ' 數據5條件
ActiveChart.SeriesCollection(5).Select '資料數列5格式
With Selection.Border
.ColorIndex = 8
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
If 數據數目 >= 6 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J117:J137"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入數據6
Start = stops + 5
stops = Start + dalta
Set Y6 = Range("J" & Start & ":J" & stops)
'Set Y6 = Application.InputBox("輸入Y6", "IV", "=D23:D52", Type:=8) '圖表範圍
ActiveChart.SeriesCollection(6).XValues = X1
ActiveChart.SeriesCollection(6).Values = Y6
ActiveChart.SeriesCollection(6).Name = W6 ' 數據6條件
ActiveChart.SeriesCollection(6).Select '資料數列6格式
With Selection.Border
.ColorIndex = 5
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
If 數據數目 >= 7 Then
ActiveChart.SeriesCollection.Add Source:=Worksheets(1).Range("J139:J159"), _
Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
:=False ' 匯入數據7
Start = stops + 5
stops = Start + dalta
Set Y7 = Range("J" & Start & ":J" & stops)
'Set Y7 = Application.InputBox("輸入Y7", "IV", "=D23:D52", Type:=8) '圖表範圍
ActiveChart.SeriesCollection(7).XValues = X1
ActiveChart.SeriesCollection(7).Values = Y7
ActiveChart.SeriesCollection(7).Name = W7 ' 數據7條件
ActiveChart.SeriesCollection(7).Select '資料數列7格式
With Selection.Border
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
End If
End Sub