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

Topic

New Reply

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

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

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.

Reply | Quote

Replies

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

Reply | Quote

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:

February 22, ’04 – 1:56pm
3 Active Scenarios: Full Recovery: Partial Recovery, Late: No Recovery
Portfolio Type: Random

(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 “?len(Format(Now, “Mmmm d, ‘yy”) & ” – ” & “&T” & Chr(13) & stLFooter” in the immediate window returns a value of 175, and “?Format(Now, “Mmmm d, ‘yy”) & ” – ” & “&T” & Chr(13) & stLFooter” returns the expected footer entry.

I’m stumped…

Reply | Quote

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.)

Reply | Quote

Thanks for the suggestion, Hans. I changed the code very slightly, to:

....
.LeftFooter = Format(Now, "Mmmm d, 'yy - h:mm am/pm") & Chr(13) & stLFooter
....

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.

Reply | Quote

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

Reply | Quote

---

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

---

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 len(stLFooter) in the immediate window returns a value like 175 or so.

I have tried a couple of different things:

• 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; Format(Now, “Mmmm d, ‘yy – h:mm am/pm”) is 39 characters long, while “February 22, ’04 – 10:52 pm” is only 27. Now the .LeftFooter doesn’t “see” the longer function, only the returned string.
• 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
• I tried inserting a line break after each scenario name in case it was the length on a single line that was a problem
• I tried taking out the line breaks, in case it was the chr(13)’s 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.

  ---

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

  ---

  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.

Reply | Quote

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

Steve

Reply | Quote

[indent]

---

Do you have a right and center footer also?

---

[/indent] 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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

[indent]

---

Do you have a right and center footer also?

---

[/indent] 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!

Reply | Quote

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

Steve

Reply | Quote

---

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

---

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 len(stLFooter) in the immediate window returns a value like 175 or so.

I have tried a couple of different things:

• 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; Format(Now, “Mmmm d, ‘yy – h:mm am/pm”) is 39 characters long, while “February 22, ’04 – 10:52 pm” is only 27. Now the .LeftFooter doesn’t “see” the longer function, only the returned string.
• 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
• I tried inserting a line break after each scenario name in case it was the length on a single line that was a problem
• I tried taking out the line breaks, in case it was the chr(13)’s 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.

  ---

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

  ---

  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.

Reply | Quote

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

Reply | Quote

Thanks for the suggestion, Hans. I changed the code very slightly, to:

....
.LeftFooter = Format(Now, "Mmmm d, 'yy - h:mm am/pm") & Chr(13) & stLFooter
....

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.

Reply | Quote

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.)

Reply | Quote

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:

February 22, ’04 – 1:56pm
3 Active Scenarios: Full Recovery: Partial Recovery, Late: No Recovery
Portfolio Type: Random

(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 “?len(Format(Now, “Mmmm d, ‘yy”) & ” – ” & “&T” & Chr(13) & stLFooter” in the immediate window returns a value of 175, and “?Format(Now, “Mmmm d, ‘yy”) & ” – ” & “&T” & Chr(13) & stLFooter” returns the expected footer entry.

I’m stumped…

Reply | Quote

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

Reply | Quote