VBA macros under MicrosoftExcel2013: Generating Program modification Orders

Attention:

1. This macro is written and debugged under MicrosoftOfficeStandard2013. may require appropriate modifications in other versions of Office

2. Save Excel table with macro, should be saved in *.XLSM format (macro-enabled Workbook)

About the use of Benhong

Now that you have modified several modules of a program, each module modifies several files, and now you want to summarize the modified file information with an Excel table. Each module (Sheet) in the workbook should have a title, each file in the Change order must have a filename, svn address, modification instructions This macro is used to quickly generate a table structure, so as long as the use of a fool to add text to the table can be

After the macro runs:

Macro usage: Call init to start the macro, want to add a sheet, only need to be added in the init routine of a addpage routine, followed by the addition of sheet name, you can.

Dim sheetnum as integer ' Initialize Excel table Sub init ()     sheetnum = 0          ' Create four tables     AddPage  monproxy      AddPage  "Monproxytool"     AddPage  "Monservice"      AddPage  "Monclient"     end sub ' Add page   input: The name of the page to be created Sub addpage ( sheetname as string)      ' Check last table      ' rule: the first table is called Sheet1, and after renaming, The newly generated table will be named sheet1     ' and renamed to Sheet1 name, then the newly created table will be named Sheet2 by default, sheet3...     if sheetnum = 0 then        sheets ("Sheet1") . Name = sheetname    else        sheets (" Sheet " & sheetnum). name = sheetname    end if    sheetnum = sheetnum + 1         ' Select the renamed form      Sheets (SheetName). select         ' Set table contents     DecorateSheet  sheetname         ' Create a new form after this form     Sheets.Add  After:=activesheetend sub ' Set table contents Sub decoratesheet (sheetname as string)      range ("A1"). select         ' Sets the column width     columns ("A:A"). Columnwidth = 24    columns ("B:b"). Columnwidth = 45    columns ("C:c"). columnwidth = 75         ' Set line height     rows (" 1:1 "). rowheight = 75         ' header line configuration      ' A1-C1 is the header row     range ("A1:c1"). select     ' header row configuration     with selection        . horizontalalignment = xlcenter        . verticalalignment = xlcenter        . wraptext = false        . orientation = 0        . addindent = false        . indentlevel = 0        . shrinktofit = false        . readingorder = xlcontext        . mergecells = true    end with     ' header line font configuration      with selection.font        . name =  "Song-Body"          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     ' Set header line text      activecell.formular1c1 = sheetname         ' time line configuration       ' A2-C2 is the time line     range ("A2:c2"). select     ' header line configuration     With Selection         . horizontalalignment = xlcenter        . verticalalignment = xlcenter        . wraptext = false        . orientation = 0        . addindent = false        . indentlevel = 0        . shrinktofit = false        . readingorder = xlcontext        . mergecells = true    end with     ' header line font configuration      with selection.font        . name =  "Song-Body"          size = 11        . strikethrough = false        . superscript = false        . subscript = false        . outlinefont = false        . shadow = false        . underline = xlunderlinestylenone        . themecolor = xlthemecolorlight1        . tintandshade = 0        . themefont = xlthemefontminor    end with     ' Set header line text     activecell.formular1c1 = cdate (format$ (now,  "yyyy-mm-dd  hh:mm "))      ' table body parts      ' table title column 1: File name     range (" A3 ") . select    activecell.formular1c1 =  "File name"     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          ' table header column 2:SVN address     range ("B3"). select    activecell.formular1c1 =  "SVN on Address"     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          ' table title column 3: Modify Description     range ("C3"). select    activecell.formular1c1 =  "Modification Instructions"     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          ' Sets the entire cell border format     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         ' Add a column to the left, for aesthetics (1. The table can be in the center section; 2. The left border of the table will be displayed)     range ("A4"). Select    selection.entirecolumn.insert , copyorigin:=xlformatfromleftorabove     columns ("A:a"). Columnwidth = 2.63end sub

END

VBA macros under MicrosoftExcel2013: Generating Program modification Orders