current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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!

share|improve this question

2 Answers 2

up vote 4 down vote

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)

share|improve this answer
4  
+1 or Left$("your built string",252) & "..." –  Alex K. Nov 1 '12 at 17:59
up vote 2 down vote

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
share|improve this answer
    
+1, That's a fairly good work around if having the super long title is necessary, but it'd probably require a lot of playing around to get it to display properly. –  Daniel Cook Nov 1 '12 at 18:22
    
The text box didnt work either. I beleive I have to go with limiting the number of characters. Thanks A. Webb and Daniel for your time. :) –  Nupur Nov 2 '12 at 14:05
    
@Nupur A text box can work because it can hold much more than 255 characters. You do, however, still have to respect the 255 character limit in interacting with it. See my edited answer for code examples. –  A. Webb Nov 2 '12 at 14:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.