'Following is the code to align 2 shapes. Just paste the following code
'and call AlignShapes function to align shapes with each other

Option Explicit

Enum enmAlignTo
    aln1Left = 1
    aln2Right = 2
    aln3Top = 3
    aln4Bottom = 4
    aln5VerticalCenter = 5
    aln6HorizontalCenter = 6
End Enum

Sub AlignShapes(shpAlignMe As Shape, shpAlignWith As Shape, enmAlign As enmAlignTo)
    Select Case enmAlign
    Case aln1Left
        shpAlignMe.Left = shpAlignWith.Left
    Case aln2Right
        shpAlignMe.Left = shpAlignWith.Left + (shpAlignWith.Width - shpAlignMe.Width)
    Case aln3Top
        shpAlignMe.Top = shpAlignWith.Top
    Case aln4Bottom
        shpAlignMe.Top = shpAlignWith.Top + (shpAlignWith.Height - shpAlignMe.Height)
    Case aln5VerticalCenter
        shpAlignMe.Top = shpAlignWith.Top + ((shpAlignWith.Height - shpAlignMe.Height) / 2)
    Case aln6HorizontalCenter
        shpAlignMe.Left = shpAlignWith.Left + ((shpAlignWith.Width - shpAlignMe.Width) / 2)
    End Select
End Sub

Source: http://excelexperts.com/Align-Shapes-using-VBA

学习点:利用Enum定义一个类型,将无意义的1,2,3,4,5,6转换为该类型的一个属性,更利于理解.

 

通过Union将个Range连接在一起,一次过添加border, 减少与Worsheet之间的交换.

Function SetBoarder(rng)

    rng.Borders(xlEdgeLeft).Weight = xlMedium
    rng.Borders(xlEdgeTop).Weight = xlMedium
    rng.Borders(xlEdgeBottom).Weight = xlMedium
    rng.Borders(xlEdgeRight).Weight = xlMedium

End Function

Sub FormatBoarder()
Dim rng As Range
Dim iRow As Integer
Dim iCol As Integer
Dim iStartRow As Integer, iEndRow As Integer, iStartCol As Integer, iEndCol As Integer

iStartRow = 3
iEndRow = 20
iStartCol = 4
iEndCol = 10

For iRow = iStartRow To iEndRow
    If Rows(iRow).RowHeight = 37 Then
        For iCol = iStartCol To iEndCol Step 2
            If Columns(iCol).ColumnWidth = 8.38 Then
                Set rng = Union(Cells(iRow - 1, iCol), Cells(iRow, iCol), Cells(iRow + 1, iCol), _
                            Cells(iRow - 1, iCol).Offset(0, 1), Cells(iRow, iCol).Offset(0, 1), Cells(iRow + 1, iCol).Offset(0, 1), _
                            Cells(iRow - 1, iCol).Offset(0, -1), Cells(iRow, iCol).Offset(0, -1), Cells(iRow + 1, iCol).Offset(0, -1))
                Call SetBoarder(rng)
            End If
        Next
    End If

Next iRow
End Sub

 

今年将至, 突然多了好多关于Excel Calendar的 template 教程. 每个人都会有每个的做法, 我也弄了一个,不过对比下面的这方法,实在甘拜下风!

=IF(MONTH(DATE(Year,Month,1))<>MONTH(DATE(Year,Month,1)-(WEEKDAY(DATE(Year,Month,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”",DATE(Year,Month,1)-(WEEKDAY(DATE(Year,Month,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

将此公式复制与一个6*7的矩阵中即可,简单方便!

如12月:

=IF(MONTH(DATE(Year,12,1))<>MONTH(DATE(Year,12,1)-(WEEKDAY(DATE(Year,12,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”",DATE(Year,12,1)-(WEEKDAY(DATE(Year,12,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

原理:

MONTH(DATE(Urtea,1,1)-(WEEKDAY(DATE(Urtea,1,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) 构造一个6*7的矩阵,如下图!

得出每一个格的日期所属月份,与该月的月份对比,若相等,则显示,否则为空.

MONTH(DATE(Urtea,1,1)-(WEEKDAY(DATE(Urtea,1,1),2)-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

(WEEKDAY(DATE(Urtea,1,1),2)-1) 此处计算该月1日与(1,1)这个单元格的距离.

{0;1;2;3;4;5}*7 此处计算6个星期,其中首星期无需加7.

{1,2,3,4,5,6,7}-1 此处分别为每个星期内的,周一到周日加上1-7天. -1是为了扣回从(1,1)起算的差距. 换而言之就是加+0, +1， +2, etc…

Source: http://www.todoexcel.com/calendario-laboral/

同时可以利用条件格式,把节假日, 假期等都用颜色标记起来.

方法: 将节假日,假期等分别存于一个列表中,命名!

=COUNTIF(Festival,H10)>0.9 (其实只要大于1，就表示改天是Festival,0.9不过是便于判断)

 

谨以此网站向Godick致敬!

特别鸣谢:

Tony Tsang!

Twitter: @boxup