Reputation: 357
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.
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!
Upvotes: 1
Views: 10325
Reputation: 26446
Several things in Excel are limited to 255 characters due to being (old style) Pascal counted strings with the length in the first byte.
To workaround this issue, you can superimpose a TextBox
and populate that with your title. Note that there is still a 255 characters at a time limit when interacting, but you can build up.
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
Upvotes: 2
Reputation: 13142
The ChartTitle.Text is limited to 255 characters.
Confirmed via experimentation the following fails when 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)
Upvotes: 4