|
代码如下:
- Private Sub Workbook_Open()
- Sheets("Macro1").Visible = False
- End Sub
- Sub ddd()
- Dim sht As Object
- For Each sht In Sheets
- ThisWorkbook.Names.Add sht.Name & "!auto_activate", "=macro1!$a$2", False
- Next
- End Sub
- Sub 散点图加标签()
- '定义极值与交点
- If Sheets("data").Cells(2, 1).Value = "" Then
- imsg = MsgBox("工具检测到-X轴最小值-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-X轴最小值-", Buttons:=vbOKOnly, Title:="请注意!"
- tempxbottom = Sheets("data").Cells(3, 7).Value
- Sheets("data").Cells(2, 1).Value = Sheets("data").Cells(3, 7).Value
- End If
- Else
- tempxbottom = Sheets("data").Cells(2, 1).Value
- End If
- If Sheets("data").Cells(2, 2).Value = "" Then
- imsg = MsgBox("工具检测到-X轴最大值-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-X轴最大值-", Buttons:=vbOKOnly, Title:="请注意!"
- tempxup = Sheets("data").Cells(2, 7).Value
- Sheets("data").Cells(2, 2).Value = Sheets("data").Cells(2, 7).Value
- End If
- Else
- tempxup = Sheets("data").Cells(2, 2).Value
- End If
- If Sheets("data").Cells(2, 3).Value = "" Then
- imsg = MsgBox("工具检测到-X轴交点-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-X轴交点-", Buttons:=vbOKOnly, Title:="请注意!"
- tempxmiddle = Sheets("data").Cells(4, 7).Value
- Sheets("data").Cells(2, 3).Value = Sheets("data").Cells(4, 7).Value
- End If
- Else
- tempxmiddle = Sheets("data").Cells(2, 3).Value
- End If
-
- If Sheets("data").Cells(4, 1).Value = "" Then
- imsg = MsgBox("工具检测到-Y轴最小值-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-Y轴最小值-", Buttons:=vbOKOnly, Title:="请注意!"
- tempybottom = Sheets("data").Cells(3, 8).Value
- Sheets("data").Cells(4, 1).Value = Sheets("data").Cells(3, 8).Value
- End If
- Else
- tempybottom = Sheets("data").Cells(4, 1).Value
- End If
- If Sheets("data").Cells(4, 2).Value = "" Then
- imsg = MsgBox("工具检测到-Y轴最大值-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-Y轴最大值-", Buttons:=vbOKOnly, Title:="请注意!"
- tempyup = Sheets("data").Cells(2, 8).Value
- Sheets("data").Cells(4, 2).Value = Sheets("data").Cells(2, 8).Value
- End If
- Else
- tempyup = Sheets("data").Cells(4, 2).Value
- End If
- If Sheets("data").Cells(4, 3).Value = "" Then
- imsg = MsgBox("工具检测到-Y轴交点-没有设置,请确认是否设置相关值?", vbYesNo)
- If imsg = 6 Then
- MsgBox prompt:="请在左上角输入相应值!", Title:="请注意!"
- End
- End If
- If imsg = 7 Then
- MsgBox prompt:="将使用默认值-Y轴交点-", Buttons:=vbOKOnly, Title:="请注意!"
- tempymiddle = Sheets("data").Cells(4, 8).Value
- Sheets("data").Cells(4, 3).Value = Sheets("data").Cells(4, 8).Value
- End If
- Else
- tempymiddle = Sheets("data").Cells(4, 3).Value
- End If
-
- '扫描标签与XY值
- tempcount = 0
- For k = 1 To 500
- kkk = 0
- tempa = 0
- tempb = 0
- tempc = 0
- If Sheets("data").Cells(6 + k - 1, 1).Value = "" Then
- tempa = 100
- End If
- If Sheets("data").Cells(6 + k - 1, 2).Value = "" Then
- tempb = 10
- End If
- If Sheets("data").Cells(6 + k - 1, 3).Value = "" Then
- tempc = 1
- End If
- kkk = tempa + tempb + tempc
- If kkk = 100 Then
- MsgBox "标签中第" & (6 + k - 1) & "行为空值,请输入!"
- End
- End If
- If kkk = 10 Then
- MsgBox "X值中第" & (6 + k - 1) & "行为空值,请输入!"
- End
- End If
- If kkk = 1 Then
- MsgBox "Y值中第" & (6 + k - 1) & "行为空值,请输入!"
- End
- End If
- If kkk = 11 Or kkk = 110 Or kkk = 101 Then
- MsgBox "第" & (6 + k - 1) & "行有两个空值,请输入!"
- End
- End If
- If kkk = 0 Then
- tempcount = tempcount + 1
- End If
- Next
- '选择数据区域
- Xvalue = "=data!R6C2:R" & 6 + tempcount - 1 & "C2"
- Yvalue = "=data!R6C3:R" & 6 + tempcount - 1 & "C3"
-
-
- ActiveSheet.ChartObjects("图表 1").Activate
- ActiveChart.SeriesCollection(1).Select
- ActiveChart.SeriesCollection(1).XValues = Xvalue
- ActiveChart.SeriesCollection(1).Values = Yvalue
- For k = 1 To tempcount
- ActiveChart.SeriesCollection(1).DataLabels.Select
- ActiveChart.SeriesCollection(1).Points(k).DataLabel.Select
- Selection.Text = "=DATA!R" & 6 + k - 1 & "C1"
- Next
-
- '
- ActiveSheet.ChartObjects("图表 1").Activate
- ActiveChart.Axes(xlValue).Select
- With ActiveChart.Axes(xlCategory)
- .MinimumScale = tempxbottom
- .MaximumScale = tempxup
- .MinorUnitIsAuto = True
- .MajorUnitIsAuto = True
- .Crosses = xlCustom
- .CrossesAt = tempxmiddle
- .ReversePlotOrder = False
- .ScaleType = xlLinear
- .DisplayUnit = xlNone
- End With
- ActiveChart.Axes(xlCategory).Select
- With ActiveChart.Axes(xlValue)
- .MinimumScale = tempybottom
- .MaximumScale = tempyup
- .MinorUnitIsAuto = True
- .MajorUnit = 5
- .Crosses = xlCustom
- .CrossesAt = tempymiddle
- .ReversePlotOrder = False
- .ScaleType = xlLinear
- .DisplayUnit = xlNone
- End With
-
- End Sub
复制代码
|
|