英文:
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'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>
# 答案1
**得分**: 4
`"&""Calibri""&20" & Format(tomorrow, "dd/mm/yyyy")` 转变为 `&"Calibri"&2020.04.2020`(当然根据当前日期不同)。因此,字体大小将被读取为`2020`,由于Excel中的最大字体大小为409,您将得到这个作为标题的字体大小。
为了防止日期直接与字体大小连接在一起,您应该简单地添加一个空格 - 这不会造成任何问题,因为右侧的页眉是右对齐的(这正是如果您手动设置页眉时Excel所做的)。
RightHeader = "&""Calibri""&20 " & Format(tomorrow, "dd/mm/yyyy")
<details>
<summary>英文:</summary>
`"&""Calibri""&20" & Format(tomorrow, "dd/mm/yyyy")` evolves to `&"Calibri"&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 = "&""Calibri""&20 " & Format(tomorrow, "dd/mm/yyyy")
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论