Excel VBA右页眉字体大小设置为20 – Excel决定大小409更好。

huangapple go评论132阅读模式
英文:

Excel VBA Right Header set Font Size to 20 - Excel decides size 409 is better

问题

I'm trying to make a simple macro to change the date displayed on the right header to be 'tomorrow's' date. When I try to set the default format of my headers and footers to 20 and then run the macro, Excel changes the font size to 11. But when i specify in the VBA code to change to 20, Excel increases the font size to 409!
The current script that I'm using is as follows:

Sub tomorrow()

Dim today As Date

Dim tomorrow As Date

today = Date

tomorrow = Date + 1

With ActiveSheet.PageSetup

    .DifferentFirstPageHeaderFooter = False
    .RightHeader = "&""Calibri""&20" & Format(tomorrow, "dd/mm/yyyy")

End With
End Sub

Can anyone spot where I'm going wrong? I just need the font size to be 20, it seems so simple, but I'm stumped... Thanks in advance!

<details>
<summary>英文:</summary>

I&#39;m trying to make a simple macro to change the date displayed on the right header to be &#39;tomorrow&#39;s&#39; date. When I try to set the default format of my headers and footers to 20 and then run the macro, Excel changes the font size to 11. But when i specify in the VBA code to change to 20, Excel increases the font size to 409!
The current script that I&#39;m using is as follows:


    Sub tomorrow()
    
    Dim today As Date
    
    Dim tomorrow As Date
    
    today = Date
    
    tomorrow = Date + 1
    
    With ActiveSheet.PageSetup
    
        .DifferentFirstPageHeaderFooter = False
        .RightHeader = &quot;&amp;&quot;&quot;Calibri&quot;&quot;&amp;20&quot; &amp; Format(tomorrow, &quot;dd/mm/yyyy&quot;)
        
    End With
    End Sub


Can anyone spot where I&#39;m going wrong? I just need the font size to be 20, it seems so simple, but I&#39;m stumped... Thanks in advance!


</details>


# 答案1
**得分**: 4

`&quot;&amp;&quot;&quot;Calibri&quot;&quot;&amp;20&quot; &amp; Format(tomorrow, &quot;dd/mm/yyyy&quot;)` 转变为 `&amp;&quot;Calibri&quot;&amp;2020.04.2020`(当然根据当前日期不同)。因此,字体大小将被读取为`2020`,由于Excel中的最大字体大小为409,您将得到这个作为标题的字体大小。

为了防止日期直接与字体大小连接在一起,您应该简单地添加一个空格 - 这不会造成任何问题,因为右侧的页眉是右对齐的(这正是如果您手动设置页眉时Excel所做的)。 

    RightHeader = &quot;&amp;&quot;&quot;Calibri&quot;&quot;&amp;20 &quot; &amp; Format(tomorrow, &quot;dd/mm/yyyy&quot;)

<details>
<summary>英文:</summary>

`&quot;&amp;&quot;&quot;Calibri&quot;&quot;&amp;20&quot; &amp; Format(tomorrow, &quot;dd/mm/yyyy&quot;)` evolves to `&amp;&quot;Calibri&quot;&amp;2020.04.2020` (of course depending on the current date). As as result, the Font size will be read as `2020`, and as the maximum font size in Excel is 409, you will get that as font size of your header.

To prevent that the day is concatenated directly with the font size, you should simply add a space - this will do no harm as the right header is right aligned anyhow (this is exactly what Excel does if you set the header manually).

    RightHeader = &quot;&amp;&quot;&quot;Calibri&quot;&quot;&amp;20 &quot; &amp; Format(tomorrow, &quot;dd/mm/yyyy&quot;)


</details>



huangapple
  • 本文由 发表于 2023年4月19日 22:00:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76055433.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定