vba 如何使用 Excel 宏模块格式化 Excel 电子表格单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7523421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to format excel spreadsheet cells using Excel Macro Module?
提问by Hopeless Imbecile
I am totally new to MS Excel and VBA Macro. I wanna know how to format cells or have an overall control on the spreadsheet using Macro VB. I have here a very simple code just to illustrate what I want to know.
我对 MS Excel 和 VBA 宏完全陌生。我想知道如何使用宏 VB 设置单元格格式或对电子表格进行整体控制。我这里有一个非常简单的代码只是为了说明我想知道的。
macro_format_test.xlsm:
macro_format_test.xlsm：
Private Sub Worksheet_Activate()
Me.Unprotect
Me.Cells.ClearContents
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Cells(1, 1) = "ID"
ws.Cells(1, 2) = "Name"
ws.Cells(1, 3) = "Address"
Me.Protect
End Sub
In the codes...
在代码...
ws.Cells(1, 1) = "ID"
ws.Cells(1, 2) = "Name"
ws.Cells(1, 3) = "Address"
I want those to appear bold, italized, and centered. I also want to set he column width using codes. How am I gonna do that??? Please help. You can also give me some references regarding this because everytime I try google all I can see are how to add UserForm which I don't need as of now. Thanks in advance.
我希望那些看起来粗体、斜体和居中。我还想使用代码设置列宽。我要怎么做？？？请帮忙。你也可以给我一些关于这个的参考，因为每次我尝试谷歌时，我只能看到如何添加我现在不需要的用户表单。提前致谢。
回答by PaulStock
With Range("A1:C1")
.HorizontalAlignment = xlCenter
.Font.Italic = True
.Font.Bold = True
.EntireColumn.ColumnWidth = 15
End With
By the way, the easiest way to figure out what the macro would be for something like this is to Click the Record Macrobutton, make your changes manually, and then examine the code that was produced for you. It isn't always the most efficient code, and you usually have to tweak it slightly, but it should give you a basic idea.
顺便说一下，找出用于此类事件的宏的最简单方法是单击“录制宏”按钮，手动进行更改，然后检查为您生成的代码。它并不总是最有效的代码，您通常需要稍微调整它，但它应该给您一个基本的想法。
回答by brettdj
I presume you want to run this on the sheet this is activated, so your unprotecting of the current sheet looks inconsistent with you then defining Sheet1 as the sheet to work on
我想您想在已激活的工作表上运行它，因此您对当前工作表的取消保护看起来与您不一致，然后将 Sheet1 定义为要处理的工作表
But as you did use a sheet variable ws, then you would apply Paul's formatting part as below to be consistent with your code (ie to work on a worksheet stored in ws). Also writing a one shot array to a range is more efficient than a cell by cell write. Not visible in your 3 cell example, but it will be noticeable when writing 1000's of values
但是当您确实使用了工作表变量 ws 时，您将应用 Paul 的格式部分如下以与您的代码保持一致（即处理存储在 ws 中的工作表）。此外，将一次性阵列写入一个范围比逐个单元写入更有效。在您的 3 个单元格示例中不可见，但在写入 1000 个值时会很明显
Private Sub Worksheet_Activate()
Me.Unprotect
Me.Cells.ClearContents
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim MyArray()
MyArray = Array("ID", "Name", "Address")
With ws.Range(ws.[a1], ws.[c1])
.Value = MyArray
.HorizontalAlignment = xlCenter
.Font.Italic = True
.Font.Bold = True
.EntireColumn.ColumnWidth = 15
End With
Me.Protect
End Sub
