MicrosoftExcel2013下的VBA宏：生成程序修改单

注意：

1.这段宏是在MicrosoftOfficeStandard2013下撰写和调试的。用在其他Office版本中可能需要适当修改

2.保存带有宏的Excel表格，应该保存为*.xlsm格式（启用宏的工作簿）

关于本宏的用途

现在修改了一个程序的若干个模块，每个模块修改了若干个文件，现在要用一个EXCEL表格总结修改的文件信息。工作簿中每个模块（Sheet）都要有标题，修改单中每个文件都要有文件名、SVN上地址、修改说明本宏用于快速生成一个表格结构，这样在之后使用时只要傻瓜化地向表格中添加文字就可以了

宏运行后的效果图：

宏的用法：调用Init启动宏，想要多加一个Sheet，只需要在Init例程中添加一个AddPage例程，后面加上添加Sheet的名称，就可以了。

Dim SheetNum As Integer

'初始化EXCEL表格
Sub Init()

    SheetNum = 0
    
    '创建四个表格
    AddPage "MonProxy"
    AddPage "MonProxyTool"
    AddPage "MonService"
    AddPage "MonClient"
    
End Sub

'添加页面 输入：要创建的页面名
Sub AddPage(SheetName As String)

    '选中最后一个表格
    '规律：第一个表格叫Sheet1，改名后，新生成的表格会被默认命名为Sheet1
    '再改Sheet1名后，再新创建表格，依次会被默认命名为Sheet2、Sheet3...
    If SheetNum = 0 Then
        Sheets("Sheet1").Name = SheetName
    Else
        Sheets("Sheet" & SheetNum).Name = SheetName
    End If
    SheetNum = SheetNum + 1
    
    '选中改名后的表格
    Sheets(SheetName).Select
    
    '设定表格内容
    DecorateSheet SheetName
    
    '在本表格后创建新表格
    Sheets.Add After:=ActiveSheet

End Sub

'设定表格内容
Sub DecorateSheet(SheetName As String)

    Range("A1").Select
    
    '设置列宽
    Columns("A:A").ColumnWidth = 24
    Columns("B:B").ColumnWidth = 45
    Columns("C:C").ColumnWidth = 75
    
    '设置行高
    Rows("1:1").RowHeight = 75
    
    '标题行配置
    'A1-C1为标题行
    Range("A1:C1").Select
    '标题行配置
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    '标题行字体配置
    With Selection.Font
        .Name = "宋体"
        .Size = 36
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    '设定标题行文字
    ActiveCell.FormulaR1C1 = SheetName
    
    '时间行配置
    'A2-C2为时间行
    Range("A2:C2").Select
    '标题行配置
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    '标题行字体配置
    With Selection.Font
        .Name = "宋体"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    '设定标题行文字
    ActiveCell.FormulaR1C1 = CDate(Format$(Now, "yyyy-mm-dd hh:MM"))

    '表格正文部分

    '表格标题列1：文件名
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "文件名"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("A4:A33").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    '表格标题列2：SVN上地址
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "SVN上地址"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("B4:B33").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    '表格标题列3：修改说明
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "修改说明"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("C4:C33").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    '设置整个单元格边框格式
    Range("A1:C33").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    '左侧再加一列，为了美观（1.表格可以位于居中部分;2.表格左侧边框线会得以显示）
    Range("A4").Select
    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").ColumnWidth = 2.63

End Sub

END

转载于:https://my.oschina.net/Tsybius2014/blog/346078