810 note

vba:
1.變數宣告
2.資料型態
3.vb敘述
4.陣列
5.副程式架構
6.函數架構=>類別

===========
this Workbook
sheet1
sheet2
sheet3
modul
表單
類別物件型態

====================
試算表基礎物件
Application   應用程式
   worksheet   活頁簿
        range
           cell
===================

Sub Auto_Open()

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

-----------------------------
寫在module裡

Sub Auto_Close()

    MsgBox "謝謝使用系統"

End Sub

Sub Auto_Open()

    MsgBox "歡迎使用系統"

    CommandBars("cell").Reset  '使用前先reset

    CommandBars("cell").Controls(1).BeginGroup = True   '使用group,分開內定之功能

    With CommandBars("cell").Controls.Add(before:=1)

     .Caption = "自訂功能巨集一"

     .OnAction = "hope1"

    End With

   

    With CommandBars("cell").Controls.Add(before:=2)

     .Caption = "自訂功能巨集二"

     .OnAction = "hope2"

    End With

End Sub

Public Sub hope1()

    MsgBox "副程式執行區塊"

End Sub

Public Sub hope2()

    MsgBox "this is hope world"

End Sub

-----------------------------------------------
do....while
     do while  condition
    [code area]
loop

ex: 1+2+3....+10=55
Private Sub addsum()
    Dim total As Integer
    Dim i As Integer
    i = 1
    Do While i <= 10
        total = total + i
        i = i + 1
    Loop
    MsgBox total
End Sub
===================
Public Function fun1()
    MsgBox "函數架構一"
End Function
Public Function fun2(ByVal a As inteter, ByVal b As Integer)
    Dim c As Integer
    c = a + b
    MsgBox c
End Function
Public Function fun3(ByVal x As inteter, ByVal y As Integer) As Integer
    Dim total As Integer
    total = x + y
    fun3 = total
 
End Function
===================
建立類別元件方式
Dim comp1 as new Ani
comp1.函數()
程式執行流程
                     request(事件要求)
試算表====按鈕事件====類別元件
                     response(回應要求)
======================================
Private Sub CommandButton3_Click()
    Dim sn As String
    sn = CommandButton3.Caption
    If sn = "走勢圖" Then
        CommandButton3.Caption = "清除"
        Range("f2:f6").SparklineGroups.Add Type:=xlSparkLine, SourceData:="c2:f6"
    Else
        CommandButton3.Caption = "走勢圖"
        Range("f2:f6").SparklineGroups.Clear
    End If
 
End Sub