731 note

Range  選取範圍：
A                B               C               D                E

產品編號	產品名稱	出廠公司	出廠日期	價格
p1001	java	oracle	2001/11/2	5000
p1002	photoshop	adobe	2012/12/4	3000
p1003	office	microsoft	2014/1/5	2000

1選取A2儲存格

.Range.("A2").select

2.選取A2到E4儲存格

.Range.("A2:E4").select

3.選取第二筆資料

Range(cells(3,1),cells(3,5)).select

4.選取產品名稱欄位

Range("B:B").select

5.選取產品名稱~出廠日期

Range("B:C").select

-----------------------
for 語法
Dim i as Integer
for i=1 To 10 step 1
[程式區塊]
next

ex1 跑十次自動加1可省略step 1
Dim j as integer
for i=1 To 10
  [程式區塊]
next

ex2
1+2.....+100=5050
Sub numtotal()
    Dim i As Integer
    Dim total As Integer
    total = 0
    For i = 1 To 100 Step 1
        total = total + i
    Next
    MsgBox "1+2+...+100=" + CStr(total)

End Sub

ex3
Sub cellnum()
    Dim i As Integer
 
    For i = 1 To 10 Step 1
     Sheets(2).Cells(i, i).Value = i
    Next
    Worksheets("工作表2").Activate
End Sub

----------------
基本控制項：Label,TexrBox,commandButton
在工作表的設計模式使用active控制項
國文 ：    英文：
屬學：
計算              清除
總分：     平均：

Private Sub CommandButton1_Click()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim total As Integer
    Dim avg As Single
 
   If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then
        a = CInt(TextBox1.Value)
        b = CInt(TextBox2.Value)
        c = CInt(TextBox3.Value)
        total = a + b + c
        TextBox4.Value = CStr(total)
     
        avg = total / 3
        TextBox5.Value = CStr(Format(avg, ".00"))
    Else
        MsgBox "資料不完整請重新輸入"
        If TextBox1.Value = "" Then
            TextBox1.Activate
        ElseIf TextBox2.Value = "" Then
            TextBox2.Activate
        ElseIf TextBox3.Value = "" Then
            TextBox3.Activate
        End If
     
    End If
 
 
End Sub




Private Sub CommandButton2_Click()
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
 
End Sub

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

Private Sub CommandButton1_Click()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim total As Integer
    Dim avg As Single
   
    Dim rcount As Integer
    rcount = Worksheets("工作表1").UsedRange.Rows.Count
    MsgBox rcount
   
   If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then
        a = CInt(TextBox1.Value)
        b = CInt(TextBox2.Value)
        c = CInt(TextBox3.Value)
        total = a + b + c
        TextBox4.Value = CStr(total)
       
        avg = total / 3
        TextBox5.Value = CStr(Format(avg, ".00"))
       
'        Sheets(1).Cells(rcount + 10, 1).Value = TextBox1.Value
'        Sheets(1).Cells(rcount + 10, 2).Value = TextBox2.Value
'        Sheets(1).Cells(rcount + 10, 3).Value = TextBox3.Value
'        Sheets(1).Cells(rcount + 10, 4).Value = TextBox4.Value
'        Sheets(1).Cells(rcount + 10, 5).Value = TextBox5.Value
    Else
        MsgBox "資料不完整請重新輸入"
        If TextBox1.Value = "" Then
            TextBox1.Activate
        ElseIf TextBox2.Value = "" Then
            TextBox2.Activate
        ElseIf TextBox3.Value = "" Then
            TextBox3.Activate
        End If
       
    End If
   
   
End Sub




Private Sub CommandButton2_Click()
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
   
End Sub