Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Footer Problem (length?) (XL 97/2K)

    I have a spreadsheet that seems to work properly, except for one niggling thing.... (ain't that always the way?)

    The sheet runs through a number of possible scenarios, and it is convenient to identify the active scenarios in the footer. I have included code in the VBA
    <pre>For i = 1 To MAXSCENARIOS
    If USESCENARIOS(i) = True Then stLFooter = stLFooter & ": " & ScenNames(i)
    Next
    With ActiveSheet.PageSetup
    .LeftFooter = Format(Now, "Mmmm d, 'yy") & " - " & "&T" & Chr(13) & stLFooter
    End With
    </pre>


    This worked fine when I had three or four active scenarios - however, it crashes with the message that it "cannot set the LeftFooter property of the PageSetup object" when I have more scenarios active. Footers have a maximum length of 255 characters - but checking on the length of stLFooter in break mode returns a length of ~104 characters or so. Even after appending the date and time information it should still be able to insert it into the footer...

    Am I missing something?

    Thanks for any help anyone can provide.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Problem (length?) (XL 97/2K)

    When it gets the runtime error, I am assuming when you debug it is at the line of code

    .LeftFooter = Format ....

    In the immediate window what do you get when you enter:
    ?len(Format(Now, "Mmmm d, 'yy") & " - " & "&T" & Chr(13) & stLFooter)

    Or even what is:
    ?Format(Now, "Mmmm d, 'yy") & " - " & "&T" & Chr(13) & stLFooter

    It seems to not like the length or the text string itself

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Footer Problem (length?) (XL 97/2K)

    Hi Steve - thanks for your suggestions: first: yes - you are right - the VBA routine halts on the ".LeftFooter = Format...." line. It seems willing to accept the line when there are a limited number of scenarios - when there are three active scenarios it produces the footer correctly (or at least, as I expect it to) - something like:
    <font color=blue>
    February 22, '04 - 1:56pm
    3 Active Scenarios: Full Recovery: Partial Recovery, Late: No Recovery
    Portfolio Type: Random
    </font color=blue>
    (the last line is added by a part of the routine that I didn't include in my original post). The line breaks (from the chr (13) in the text string) are inserted properly. With more scenarios active, the routine fails. When it has failed a <font color=blue>"?len(Format(Now, "Mmmm d, 'yy") & " - " & "&T" & Chr(13) & stLFooter"</font color=blue> in the immediate window returns a value of 175, and <font color=blue>"?Format(Now, "Mmmm d, 'yy") & " - " & "&T" & Chr(13) & stLFooter"</font color=blue> returns the expected footer entry.

    I'm stumped...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Footer Problem (length?) (XL 97/2K)

    I did a test, and could create footers like this up to 253 characters in length. Does any of the scenario names contain a character sequence that could confuse Excel? (There are several special codes with & such as the &T you are using.)

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Footer Problem (length?) (XL 97/2K)

    Thanks for the suggestion, Hans. I changed the code very slightly, to:
    <pre>....
    .LeftFooter = Format(Now, "Mmmm d, 'yy - h:mm am/pm") & Chr(13) & stLFooter
    ....
    </pre>

    in part to get away from that special character sequence "&T" - It didn't seem to make any difference. There are no characters in the scenario names that should be troublesome - they are all letters, spaces or dashes - no ampersands or underscores or anything else unusual. I might try using a "trim" function when I load the scenario name strings into the "ScenNames()" array - it seems unlikely, but perhaps I have a leading or trailing space (or even more unusual character) that is causing confusion for Excel.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Problem (length?) (XL 97/2K)

    If you enter the text directly into the footer dialog does it give you an error?

    Does every scenario item give you an error or just particular ones?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Footer Problem (length?) (XL 97/2K)

    <hr>If you enter the text directly into the footer dialog does it give you an error?<hr>Yes, it does. I changed all the scenario names to shorter variations, and it still fails (it may get one more scenario in the list - I am starting to forget...). When it fails if I get the value of stLFooter from the immediate window and then try to insert it directly in the footer (after resetting the VBA routine) I get the error message that the footer cannot be longer than 255 characters - but <font color=blue>len(stLFooter)</font color=blue> in the immediate window returns a value like 175 or so.

    I have tried a couple of different things:
    <UL><LI>since the value returned by a function can be shorter than the function itself, I moved the "date" function out of the assignment to the .LeftFooter property; <font color=blue>Format(Now, "Mmmm d, 'yy - h:mm am/pm")</font color=blue> is 39 characters long, while <font color=red>"February 22, '04 - 10:52 pm"</font color=red> is only 27. Now the .LeftFooter doesn't "see" the longer function, only the returned string.
    <LI>I tried defining stLFooter as a defined-length (255 character) string - all that did was to require a trim() function around each use. I guess I don't understand (or use) defined-length strings very well
    <LI>I tried inserting a line break after each scenario name in case it was the length on a single line that was a problem
    <LI>I tried taking out the line breaks, in case it was the <font color=blue>chr(13)'s</font color=blue> that were causing the hiccup[/list]None of these things seemed to make any difference, although the length of the stLFooter was a little different each time it crashed, due to slighlty different text included. It might be possible to narrow-down the problem by increasing the length of the scenario names by one character at a time, but I am not sure it is worth the effort.

    <hr>Does every scenario item give you an error or just particular ones?<hr>It seems to be the length - not a particular text string in the scenario names. After changing the names to something quite different, it would still fail, and at about the same place, although by going to very short names I could get them all in - unfortunately, the names get short enough that they are not as much use as I had hoped. I will keep poking at the problem a bit, but I think I may end up just indicating the number of active scenarios, and letting the users try to figure out which ones are running on their own. Thanks for your suggestions, Steve, and Hans, as well.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Problem (length?) (XL 97/2K)

    Do you have a right and center footer also?
    The TOTAL length of the footer (Left + center + right) must be <255.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Footer Problem (length?) (XL 97/2K)

    <hr>Do you have a right and center footer also?<hr>
    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> That'll be the problem! I have a right footer, as well as the left footer - its just that I am setting the right footer first, then the left (and kerplunk!). When I comment out the LeftFooter assignment, everything works because the other footer is relatively short.
    The error message when you enter an over-long footer refers to the 'length of the footer' not exceeding 255 - I interpreted that to mean 'the length of the footer that you are trying to enter' cannot be >255. Oh well, I am not the first person to trip over Microsoft's documentation and help functions. I will see what I can strip out of the right footer to give me more room, and whether I can retain some meaning in the scenario names. It might be worth an error-handler to test for an over-long footer, and replace if required - but that is a little fussy for something that is just intended as a quick reference.... Perhaps some of the tombstone stuff can go in the header, instead

    Thanks for your help, Steve!

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Problem (length?) (XL 97/2K)

    Don't be embarrassed.

    I started playing with the footer length and based on the message, thought I would test the "total length concept" it and lo and behold, it it the total length not the individual lengths.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •