VBA 代码错误：Excel 中的图表标题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13182413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Code Error: Chart Title in Excel
提问by Nupur
I have a sas code that writes text (Lets call is "sas text") in an excel file and then VBA creates the graph in excel and copy the "sas text" in the title. Whenver the "sas text" length is samll the VBA works fine, but whenever the "SAS text" length is long, VBA gives a run time error - "VBA, method "text" of object error.
我有一个在 excel 文件中写入文本的 sas 代码（我们称之为“sas 文本”），然后 VBA 在 excel 中创建图形并复制标题中的“sas 文本”。只要“sas 文本”长度很小，VBA 就可以正常工作，但是只要“SAS 文本”长度很长，VBA 就会给出运行时错误——“VBA，对象错误的方法“文本”。
My code is:
我的代码是：
Sub FormatChart()
Let Title = Sheets("Sheet1").Cells(2, 1)
Let Title1 = Sheets("Sheet1").Cells(2, 2)
Let Title2 = Sheets("Sheet1").Cells(2, 3)
Let Title3 = Sheets("Sheet1").Cells(2, 4)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).ReversePlotOrder = True
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = Title & Title3 & Chr(10) & Title1 & "to " & Title2 & ": People with 25 or more visits" & Chr(10) & "Source: www.xxxxxxxxxxx.xxx.xxx"
With ActiveChart.ChartTitle.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
End With
With ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
End With
With ActiveChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
End With
With ActiveChart.PlotArea.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
Range("S4").Select
ActiveWindow.SmallScroll Down:=48
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.Select
Selection.delete
ActiveWindow.SmallScroll Down:=45
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.SmallScroll Down:=-45
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.SmallScroll Down:=-54
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Width = 500
ActiveChart.ChartArea.Height = 1000
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 100
ActiveChart.Axes(xlValue).MajorUnit = 20
ActiveChart.Axes(xlValue).MajorUnit = 10
With ActiveChart.SeriesCollection(1)
.Interior.Color = RGB(0, 51, 153)
End With
ActiveSheet.ChartObjects("Chart 1").Activate
Dim c As Chart
Dim s As Series
Dim iPoint As Long
Dim nPoint As Long
Set c = ActiveChart
Set s = c.SeriesCollection(1)
nPoint = s.Points.Count
For iPoint = 1 To nPoint
If s.XValues(iPoint) = "MINNESOTA STATE AVERAGE " Then
s.Points(iPoint).Interior.Color = RGB(80, 116, 77)
End If
If s.XValues(iPoint) = "NATIONAL AVERAGE " Then
s.Points(iPoint).Interior.Color = RGB(80, 116, 77)
End If
Next iPoint
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Cut
Sheets("Chart1").Select
ActiveChart.Paste
End Sub
On debugging, it highlights this line of the code
在调试时，它突出显示这行代码
" ActiveChart.ChartTitle.Text = Title & Title3 & Chr(10) & Title1 & "to " & Title2 & ": People with 25 or more visits" & Chr(10) & "Source: www.xxxxxxxxxxx.xxx.xxx"
"
Thanks much!
非常感谢！
回答by Daniel
The ChartTitle.Text is limited to 255 characters.
ChartTitle.Text 限制为 255 个字符。
Confirmed via experimentation the following fails when x = 256.
通过实验确认当 x = 256 时以下失败。
Sub Macro2()
'
Dim x as long
With ActiveChart
.HasTitle = True
For x = 1 To 500
.ChartTitle.Text = Characters(x)
Next
End With
End Sub
Function Characters(x As long)
dim charCount as long
For charCount = 1 To x
Characters = Characters & "."
Next
End Function
My advice would be to truncate your title ChartTitle.Text = Left("your built string",255)
我的建议是截断你的标题 ChartTitle.Text = Left("your built string",255)
回答by A. Webb
Several things in Excel are limited to 255 characters due to being (old style) Pascal counted strings with the length in the first byte.
由于是（旧式）Pascal 计数字符串，Excel 中的一些内容被限制为 255 个字符，其长度在第一个字节中。
To workaround this issue, you can superimpose a TextBoxand populate that with your title. Note that there is still a 255 characters at a time limit when interacting, but you can build up.
要解决此问题，您可以叠加 aTextBox并用您的标题填充它。请注意，交互时仍然有255个字符的时间限制，但您可以建立。
The following will not work:
以下将不起作用：
Sub WillNotWork()
Const LongString As String = _
"Pi = 3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679821480865132823066470938446095505822317253594081284811174502841027019385211055596446229489549303819644288109756659334461284756482337867831652712019091456485669234603486104543266482133936072602491412737245870066063155881748815209209628292540917153643678925903600113305305488204665213841469519415116094330572703657595919530921861173819326117931051185480744623799627495673518857527248912279381830119491298336733624406566430860213949463952247371907021798609437027705392171762931767523846748184676694051320005681271452635608277857713427577896091736371787214684409012249534301465495853710507922796892589235420199561121290219608640344181598136297747713099605187072113499999983729780499510597317328160963185950244594553469083026425223082533446850352619311881710100031378387528865875332083814206171776691473035982534904287554687311595628638823537875937519577818577805321712268066130019278766111959092164..."
Dim title As TextFrame
Set title = ActiveSheet.Shapes("Text Box 1").TextFrame
title.Characters.Text = LongString 'fails silently
End Sub
But this will:
但这将：
Sub Works()
Const LongString As String = _
"Pi = 3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679821480865132823066470938446095505822317253594081284811174502841027019385211055596446229489549303819644288109756659334461284756482337867831652712019091456485669234603486104543266482133936072602491412737245870066063155881748815209209628292540917153643678925903600113305305488204665213841469519415116094330572703657595919530921861173819326117931051185480744623799627495673518857527248912279381830119491298336733624406566430860213949463952247371907021798609437027705392171762931767523846748184676694051320005681271452635608277857713427577896091736371787214684409012249534301465495853710507922796892589235420199561121290219608640344181598136297747713099605187072113499999983729780499510597317328160963185950244594553469083026425223082533446850352619311881710100031378387528865875332083814206171776691473035982534904287554687311595628638823537875937519577818577805321712268066130019278766111959092164..."
Dim title As TextFrame
Set title = ActiveSheet.Shapes("Text Box 1").TextFrame
title.Characters.Text = ""
For i = 0 To Len(LongString) \ 255
title.Characters(i * 255 + 1).Insert Mid$(LongString, 255 * i + 1, 255)
Next
End Sub
