805 note

錯誤處理
1.Err.clear
  將日誌錯誤刪除
2.刪除試算表所有空白列
         總行列數：1048576
         A:A1048576
3on error resume next
   忽略error繼續執行

Private Sub Workbook_Open()
    Err.Clear
    On Error Resume Next
 
    Dim r As Double
    r = Sheets(1).Rows.Count
'    MsgBox r
    Sheets(1).Range("A1:A" & r).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
     
End Sub
4.on error goto label
.
.
.
.
label:
.
.
workbooks(filename).save
--------------------------------------------
VBA一維陣列
宣告：
    Dim  陣列名稱(數量) Integer
ex: dim s(2) as integer
-----------------------------------------------
Private Sub CommandButton1_Click()
'    Dim s(2) As Integer
'    s(0) = 30
'    s(1) = 40
'    s(2) = 50
'    MsgBox LBound(s) & ":" & UBound(s)
 
    On Error GoTo label1
    Dim data(4) As String
    Dim i As Integer
    For i = LBound(data) To UBound(data)
        data(i) = Sheets(1).Cells(3, i + 1).Value
    Next
   exit sub
'    MsgBox data(1)
label1:
    MsgBox "系統程式錯誤，請檢察索引值"
End Sub
----------------------------------
Private Sub CommandButton2_Click()
    Dim data(4) As String
    Dim cscore As Integer
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
 
 
    For i = 2 To 6
        If Sheets(1).Cells(i, 3).Value < 60 Then
           MsgBox Sheets(1).Cells(i, 3).Value
            For j = LBound(data) To UBound(data)
                Sheets(2).Cells(2 + i, j + 1).Value = Sheets(1).Cells(i, j + 1).Value
            Next
        End If
    Next
 
   Call dspace
End Sub

Private Sub dspace()
  On Error Resume Next
    r = Sheets(2).Rows.Count
    Sheets(2).Range("A1:A" & r).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
------------------
選擇性
select case 變數
case   值1
.
.
case 值2
.
.

case else
.
.
end select

-------------------------
msg=msgbox(string1,vbYesNo,
string2)
-----------
0Sub Auto_Close()
    MsgBox "謝謝使用系統"
End Sub
Sub Auto_Open()

    MsgBox "歡迎使用系統"
    CommandBars("cell").Reset
    With CommandBars("cell").Controls.Add
     .Caption = "自訂功能巨集一"
    End With
End Sub

--------------------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    msg = MsgBox("確定要離開試算表嗎", vbYesNo, "登出")

    If msg = vbYes Then

        Cancel = False

    Else

        Cancel = True

    End If

End Sub