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 ThenSheets("Sheet1").Name = SheetNameElseSheets("Sheet" & SheetNum).Name = SheetNameEnd IfSheetNum = SheetNum + 1'选中改名后的表格Sheets(SheetName).Select'设定表格内容DecorateSheet SheetName'在本表格后创建新表格Sheets.Add After:=ActiveSheetEnd Sub'设定表格内容
Sub DecorateSheet(SheetName As String)Range("A1").Select'设置列宽Columns("A:A").ColumnWidth = 24Columns("B:B").ColumnWidth = 45Columns("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 = TrueEnd With'标题行字体配置With Selection.Font.Name = "宋体".Size = 36.Strikethrough = False.Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlUnderlineStyleNone.ThemeColor = xlThemeColorLight1.TintAndShade = 0.ThemeFont = xlThemeFontMinorEnd WithSelection.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 = TrueEnd With'标题行字体配置With Selection.Font.Name = "宋体".Size = 11.Strikethrough = False.Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlUnderlineStyleNone.ThemeColor = xlThemeColorLight1.TintAndShade = 0.ThemeFont = xlThemeFontMinorEnd With'设定标题行文字ActiveCell.FormulaR1C1 = CDate(Format$(Now, "yyyy-mm-dd hh:MM"))'表格正文部分'表格标题列1：文件名Range("A3").SelectActiveCell.FormulaR1C1 = "文件名"With Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorLight2.TintAndShade = 0.599993896298105.PatternTintAndShade = 0End WithRange("A4:A33").SelectWith Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorLight2.TintAndShade = 0.799981688894314.PatternTintAndShade = 0End With'表格标题列2：SVN上地址Range("B3").SelectActiveCell.FormulaR1C1 = "SVN上地址"With Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorAccent1.TintAndShade = 0.599993896298105.PatternTintAndShade = 0End WithRange("B4:B33").SelectWith Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorAccent1.TintAndShade = 0.799981688894314.PatternTintAndShade = 0End With'表格标题列3：修改说明Range("C3").SelectActiveCell.FormulaR1C1 = "修改说明"With Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorLight2.TintAndShade = 0.599993896298105.PatternTintAndShade = 0End WithRange("C4:C33").SelectWith Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorLight2.TintAndShade = 0.799981688894314.PatternTintAndShade = 0End With'设置整个单元格边框格式Range("A1:C33").SelectSelection.Borders(xlDiagonalDown).LineStyle = xlNoneSelection.Borders(xlDiagonalUp).LineStyle = xlNoneWith Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeTop).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeRight).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlInsideVertical).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous.ColorIndex = xlAutomatic.TintAndShade = 0.Weight = xlThinEnd With'左侧再加一列，为了美观（1.表格可以位于居中部分;2.表格左侧边框线会得以显示）Range("A4").SelectSelection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAboveColumns("A:A").ColumnWidth = 2.63End SubEND
转载于:https://my.oschina.net/Tsybius2014/blog/346078
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！