Too many line continuations

[PWD]

Good afternoon. This is in Excel 2010. I am trying to create 90+ named ranges in a workbook that correspond to a list of named text boxes in PowerPoint. (I will then populate each cell with the value from its corresponding text box). I thought I'd split the lines so I could see the names without scrolling across but I got to the stage where I got the message

Too many line continuations

Here is an example of what I was doing to name the ranges:-

    'This is from PowerPoint Slide 12
    
        BNames = Array("AnyTotalBrandRecall", "AnyLikeability", "AnyTalkability" _
    , "AnyTouchScreen", "AnyBetterOpinion", "AnyEncourages")
    
    For a = 0 To UBound(BNames)
    
        ActiveWorkbook.Names.Add Name:=BNames(a), RefersToR1C1:=Cells(a + 2, 3) 'Row 2 Column 3, (C2)

    Next a

Then from PowerPoint I run this to populate the ranges with values:-

    RNames = Array("AnyTotalBrandRecall", "AnyLikeability", "AnyTalkability" _
    , "AnyTouchScreen", "AnyBetterOpinion", "AnyEncourages")
        
        With ActivePresentation.Slides.Range("Slide 12")
            
            For Each RName In RNames
    
            Set MyText = .Shapes("Textbox " & RName)
            
            LenText = Len(MyText.TextFrame.TextRange) 'Length of text
    
            ScoreNumber = Left((MyText.TextFrame.TextRange), LenText - 1) 'Removes the "%"
            
            XLApp.Range(RName) = ScoreNumber
            
            Next RName


        End With

' Clean up
    Set XLApp = Nothing

So, my basic question is, "What's the "best" way of naming a large number of ranges from any particular list?

Any other incidental, "You dork!! Why don't you do this instead??" Would be gratefully received. (I know that forum users aren't backward in coming forward with suggestions )

On reflection, I suppose I could do it all from within Powerpoint; that would be a bit tidier.

Many thanks,
D€$

 

[MakeItSo]

Put em all in a text file, one at each line.
The read that textfile in a loop via "readline".



[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Andrzejek]

Are you getting this error on this line:

BNames = Array("AnyTotalBrandRecall", "AnyLikeability", "AnyTalkability" _
    , "AnyTouchScreen", "AnyBetterOpinion", "AnyEncourages")

because I tried this and it worked just fine (Excel 2003):
[tt]
Dim BNames As Variant
Dim a As Integer

BNames = Array("AnyTotalBrandRecall", _
"AnyLikeability", _
"AnyTalkability", _
"AnyTouchScreen", _
"AnyBetterOpinion", _
"AnyEncourages", _
"AnotherLikeability", _
"AnotherTalkability", _
"AnotherTouchScreen", _
"AnotherBetterOpinion", _
"AnotherEncourages")

For a = LBound(BNames) To UBound(BNames)
Debug.Print BNames(a)
Next a
[/tt]

Have fun.

---- Andy

 

[PWD]

Textfile. Hmmm, OK, worth a go. I suppose I could create a named range containing all those names? That might make it easier to manage too. (Although that would prevent me doing it all from within PowerPoint )

Andy, I thought someone would say that! The code was just an example; when I get past about 20 something continuations, that's when I get the message. I just didn't want to bore the post with all those lines!

Many thanks,
D€$

 

[MakeItSo]

Yeah I know.
I like working with arrays too, but when the number of entries gets too much, I usuall switch over to either a plain text file or an XML or an XLS or an Access DB or whatever...


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Andrzejek]

If you have your heart set on keeping the list in the code (I would keep it somewhere else), you can do that:

Dim BNames As Variant
Dim a As Integer
Dim str As String

str = "AnyTotalBrandRecall,AnyLikeability,"
str = str & "AnyTalkability,AnyTouchScreen,"
str = str & "AnyBetterOpinion,AnyEncourages,"
str = str & "AnotherLikeability,"
str = str & "AnotherTalkability,AnotherTouchScreen,"
str = str & "AnotherBetterOpinion"

BNames = Split(str, ",")

For a = LBound(BNames) To UBound(BNames)
    Debug.Print BNames(a)
Next a

Practically no limits of how many lines you may have

Have fun.

---- Andy

 

[PWD]

Thanks Andy. If I was just doing this within Excel I could put each name in cell and create a named range then have:-

For each nm in Range("MyRange")

Do stuff

Next nm

But there's no equivalent in PowerPoint is there?

Sigh!

I've got round it by splitting up the list and running the code several times.

Many thanks,
D€$

 

[Andrzejek]

You can have just a simple text file and read the data from it.

Something like:
[tt]
[blue]MyTextFile.txt[/blue]
AnyTotalBrandRecall
AnyLikeability
AnyTalkability
AnyTouchScreen
AnyBetterOpinion
AnyEncourages
AnotherLikeability
AnotherTalkability
AnotherTouchScreen
AnotherBetterOpinion[/tt]

and you can have a long, long, long list to be used anywhere.

Have fun.

---- Andy