编写了一个宏,用 Excel 绘制带有标记的线,它在单个宏 xlsm 文件中运行良好。但是当我尝试将其转换为 Excel 插件(xlam 文件)时,它出现了很多错误。所有错误都与 X 和 Y 坐标的格式、图表标题的位置、字体类型和大小以及添加文本的位置、字体类型和大小有关。不确定是什么原因,需要知道它们的正确格式。任何调试建议或帮助,非常感谢。请参阅错误消息和我的完整宏代码,如下所示。谢谢。
错误消息是运行时错误'-21474627161 (800004003)':对象不再有效。
单击调试后,代码“.left=358”以黄色突出显示。
但是你用Excel检查过,绘图是在没有图表标题和添加文本(我想要的)的情况下绘制的,并且坐标的格式不是我试图设置的。同样,所有这些错误仅发生在 xlam 文件中,该宏与 xlms fie 配合良好。
Sub strain_plot()
sh_rows = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
For i = 1 To sh_rows
If ActiveSheet.Cells(i, 1).Value < 0.000001 Then
ActiveSheet.Cells(i, 1).Value = 1000000000# * ActiveSheet.Cells(i, 1).Value
End If
Next i
ii = sh_rows
c_name = "chart1"
On Error GoTo err:
ActiveWorkbook.ActiveSheet.ChartObjects(c_name).Delete
err:
Set ch = ActiveWorkbook.ActiveSheet.ChartObjects.Add(330, 120, 480, 270) 'set graph position and size
ch.Name = c_name
With ch.Chart
For iii = 1 To 2
.SeriesCollection.NewSeries
.SeriesCollection(iii).Values = Range(ActiveWorkbook.ActiveSheet.Cells(1, iii + 1), ActiveWorkbook.ActiveSheet.Cells(ii, iii + 1))
.SeriesCollection(iii).XValues = Range(ActiveWorkbook.ActiveSheet.Cells(1, 1), ActiveWorkbook.ActiveSheet.Cells(ii, 1))
.SeriesCollection(iii).ChartType = xlLineMarkers
Next iii
.SeriesCollection(1).Name = "[110]"
.SeriesCollection(1).MarkerStyle = 2
.SeriesCollection(1).MarkerSize = 12
.SeriesCollection(1).MarkerForegroundColor = RGB(255, 0, 0)
.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 0, 0)
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
.SeriesCollection(2).Name = "[001]"
.SeriesCollection(2).MarkerStyle = 2
.SeriesCollection(2).MarkerSize = 12
.SeriesCollection(2).MarkerForegroundColor = RGB(96, 96, 96)
.SeriesCollection(2).MarkerBackgroundColor = RGB(96, 96, 96)
.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(96, 96, 96)
With .Legend
.IncludeInLayout = False
.Position = xlLegendPositionRight
.AutoScaleFont = False
.Font.Size = 14
.Top = 25
.Left = 392
.Width = 72
.Height = 40
End With
With .ChartArea.Fill
.Visible = msoTrue
.ForeColor.SchemeColor = 33
.Solid
End With
With .SeriesCollection(1).Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0) 'red
.Transparency = 0
End With
With .SeriesCollection(2).Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(96, 96, 96) 'grey
.Transparency = 0
End With
.HasTitle = True
With .ChartTitle
.Text = ActiveWorkbook.ActiveSheet.Cells(5, 8)
.Left = 358
.Top = 236
With .Font
.Name = "Tahoma"
.Size = 10
End With
End With
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = "Position(nm)" 'X-axis title
.TickLabels.Font.Size = 10 'X-axis coordinate number size
.AxisTitle.Font.Size = 14 'X-axis title word font size
.TickMarkSpacing = 3
.TickLabelSpacing = 5
.TickLabels.NumberFormatLocal = "#,##0._);[red](#,##0.)"
.TickLabels.NumberFormatLocal = "#,##0_);[red](#,##0)"
.TickLabels.NumberFormatLocal = "0_);[red](0.)"
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = "Strain" 'Y-aixs title
.AxisTitle.Font.Size = 14 'y-axis title word font size
'Minimum value of Y axis
.Axes(xlValue).MinimumScale = -0.005
.Axes(xlValue).TickLabels.NumberFormatLocal = "0.0%"
End With
End With
Dim thechartobj As ChartObject
Set thechartobj = ActiveWorkbook.ActiveSheet.ChartObjects(ch.Name)
Dim thechart As Chart
Set thechart = thechartobj.Chart
Dim thetextbox As Shape
Set thetextbox = thechart.Shapes.AddTextbox(msoTextOrientationHorizontal, 688, 372, 122, 20)
With thetextbox.TextFrame.Characters
.Text = ActiveSheet.Cells(6, 8)
With .Font
.Name = "tahoma"
.Size = 10
.Bold = msoTrue
End With
End With
End Sub