「 Excel 」标签的归档

Set Border

通过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
article clipper remember Set Border
 
2010
12/22
分类
Unknown
标签


发表评论

Disable Functions Cut, Copy, Paste

// 注:以下程序只对2003有效
Attribute VB_Name = "Module1"
Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow)    ' cut
    Call EnableMenuItem(19, Allow)    ' copy
    Call EnableMenuItem(22, Allow)    ' paste
    Call EnableMenuItem(755, Allow)   ' pastespecial

'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
            Case Is = False
                .OnKey "^c", "CutCopyPasteDisabled"
                .OnKey "^v", "CutCopyPasteDisabled"
                .OnKey "^x", "CutCopyPasteDisabled"
                .OnKey "+{DEL}", "CutCopyPasteDisabled"
                .OnKey "^{INSERT}", "CutCopyPasteDisabled"
            Case Is = True
                .OnKey "^c"
                .OnKey "^v"
                .OnKey "^x"
                .OnKey "+{DEL}"
                .OnKey "^{INSERT}"
            End Select
    End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
        If cBar.Name <> "Clipboard" Then
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
        End If
    Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
    MsgBox "Sorry!  Cutting, copying and pasting have been disabled in this workbook!"
End Sub

Source:http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

article clipper remember Disable Functions Cut, Copy, Paste
 
2010
12/19
分类
Excel
标签
发表评论

Calendar

今年将至, 突然多了好多关于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的矩阵,如下图!

1,1 1,2 1,3 1,4 1,5 1,6 1,7
2,1 2,2 2,3 2,4 2,5 2,6 2,7
3,1 3,2 3,3 3,4 3,5 3,6 3,7
4,1 4,2 4,3 4,4 4,5 4,6 4,7
5,1 5,2 5,3 5,4 5,5 5,6 5,7
6,1 6,2 6,3 6,4 6,5 6,6 6,7
7,1 7,2 7,3 7,4 7,5 7,6 7,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不过是便于判断)

article clipper remember Calendar
 
2010
12/19
分类
Excel
标签

发表评论