Error when setting Worksheet.PageSetup.XxxFooter

2019-06-04 18:56发布

问题:

I'm using Excel 2007. Here's the code:

Dim ws As Worksheet, lf As String, cf As String, rf As String
...
rf = "&""-,Italic""&K7F7F7F" & "Some text"
...
ws.PageSetup.LeftFooter = lf
ws.PageSetup.CenterFooter = cf
ws.PageSetup.RightFooter = rf

I get the following error on the last line.

Run-time error '1004': Unable to set the RightFooter property of the PageSetup class

The LeftFooter and CenterFooter properties get set just fine. However, if I change the order in which the properties are set, then the last one, be it LeftFooter or CenterFooter triggers the error, and the previous two always get set successfully. So that tells me that it's not an error in the syntax for the footer or anything.

Note that I'm setting some font to italic and a different color in all three footer sections. If I remove the part about changing to italic and color (only on the last of the footer sections), then it executes without error.
EDIT Also, if I remove only the italic part, then the code executes without error./EDIT

I was getting this error at first, then played around with variables and concatenation until finally it worked, but now it's broken again, and I cannot figure out the problem.

This question seems possibly related, but I'm not sure. I tried the fix that worked for that guy (using a Replace that does nothing really except return a new string), but I still get the same error.

I've tried using no concatenation to set lf, cf, and rf, merging variables, and a lot of other stuff (in desperation) that should not have an effect anyway, and I keep getting this error. Does anyone have any idea what the problem could be?

UPDATE
I never found an answer to my question, so I just removed the italic font style, and the rest was working. I was happy enough. Now, though, I've had to create the header and footer for a landscape sheet (the other was portrait). The header and footer are the exact same as for the portrait sheet excep that some text in the center footer section is longer. The rest is the exact same. The entire procedure to create the footer was copied and only the one piece of text was changed. Now I'm getting the same error as before (without the italic part). As before, it occurs on the last footer section being set. Note that the portrait footer is still functioning properly. This is driving me nuts, and I'm losing precious time on a matter that you'd think would be fairly straightforward.

Can anyone replicate the error?

Here's the full code:

Private Const LINE_CLR As String = "&K538ED5" ' RGB(83, 142, 213)
'Private Const TEXT_STYLE AS STRING = "&""-,Italic"""
Private Const TEXT_STYLE As String = ""
Private Const TEXT_CLR As String = "&K7F7F7F" ' RGB(127, 127, 127)
Private Const FTR_INDENT_RT As String = "          "
Private Const FTR_INDENT_LT As String = "          "

' *****************************************************************************
Private Function HOR_LINE_PORTRAIT() As String
    HOR_LINE_PORTRAIT = String(128, "_")
End Function

' *****************************************************************************
Private Function HOR_LINE_LANDSCAPE() As String
    HOR_LINE_LANDSCAPE = String(191, "_")
End Function

' *****************************************************************************
Public Sub CreateFooter_Landscape( _
        ByRef ws As Worksheet, _
        ByVal txt1 As String, _
        ByVal txt2 As String, _
        ByVal txt3 As String)
    Dim txtL As String, txtC As String, txtR As String
    Dim lf As String, cf As String, rf As String ' left, center, and right footer

    txtL = "Some text1: " & txt1
    txtC = "Some text2: " & txt2
    txtR = "Some text3 " & txt3

    lf = TEXT_STYLE & TEXT_CLR & FTR_INDENT_LT & txtL
    cf = LINE_CLR & HOR_LINE_LANDSCAPE & Chr(10) & TEXT_STYLE & TEXT_CLR & txtC
    rf = TEXT_STYLE & TEXT_CLR & txtR & FTR_INDENT_RT

    ws.PageSetup.LeftFooter = lf
    ws.PageSetup.CenterFooter = cf
    ws.PageSetup.RightFooter = rf
End Sub

回答1:

I still don't have a definitive answer on this, but in my trials I've noticed a few things (above all that setting headers and footers via VBA is very quirky):

  1. I had a horizontal bar in the center footer section (in the form of a string of underscores) that spanned the entire page width. I noticed that if I greatly reduced the width of the bar that the error disappeared. However, the bar needs to span the entire page width, so this is not an acceptable solution, just an observation

  2. I had a separate procedure to set the header and the footer of sheets. I passed the text for the footer via parameters of the procedure. I noticed, however, that if I left everything else the same and just overwrote the parameter variable (when concatenating text) with a hard-code value that the error disappeared. Again, this is not an option, because the value cannot be hard-coded.

  3. Also, in concatenating strings, I called a function directly (that returned a String). However, if I replaced the function call with a local variable, the the error disappeared. I did make this change, but due to the other factors, I still get the error.

I'll try to update this answer if I find anything else, or get lucky with a "real" answer.

UPDATE
I ended up just converting the horizontal bar from underscores to an image and I got it to work. So I guess the underscores in the center section, but overlapping the left and right sections was causing some sort of problem. I haven't tried to add the italic style back in yet, and I don't think I will. It's working, and I've spent WAY too much time on this.