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

729 note

if 類型一
if     條件式  then
   [程序區塊]
end if

if 類型二
if  條件式   x>=10 and y<=20 then
   [程序區塊1]
else
   [程式區塊2]
end if

例
1.x=20    y=30  =>執行區塊2
2.x=25    y=10  =>執行區塊1

if 類型三
if  條件式   x>=90 then
   [程序區塊1]
elseif    條件式  x>=80  then
   [程式區塊2]
elseif    條件式 x>=70   then
   [程式區塊3]
else
   [程式區塊4]
end  if

架構圖
                      資料互通
試算表
Application
Workbooks
Worksheets
Sheets
Cells
Range
Charts

VBA
1.Label
2.TexttBox
3.CheckBox
4.OptionButton
5.CommandButton
6.ScrollBar
7.ComboBox
8.ListBox
9.Image


模組表單
工作表1
worksheets("工作表1"),sheet("工作表1"),sheets(1)
Cells(1,1)
 選取Sheets(1).Cells(1,1).select
取值Dim s as String
        s=Sheets(1).Cells(1,1).Value
給值
      Sheets(1).Cells(1,1).Value=5600
----------------------------------------------------------------
Sub cellselect()
'    Sheets(1).Cells(2, 1).Select
'    Dim s As String
'    s = Sheets(1).Cells(2, 1).Value
'    MsgBox s
    Dim sump As Integer
    sump = Sheets(1).Cells(2, 5).Value + Sheets(1).Cells(3, 5).Value + Sheets(1).Cells(4, 5).Value
'    MsgBox sump
    Sheets(1).Cells(5, 5).Value = sump
End Sub
-------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim p As Integer
    If Target.Column = 5 And Target.Row >= 2 And Target.Row < 5 Then
        p = Target.Value
        Sheets(1).Rows(Target.Row).Select
    '    MsgBox p
        If p >= 4000 Then
            MsgBox CStr(p) + "大於等於4000"
        Else
            MsgBox CStr(p) + "未達預設值"
        End If
    End If
End Sub

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

Sub info()
    Dim rcount As Integer
    Dim fcount As Integer
 
    rcount = Worksheets("工作表1").UsedRange.Rows.Count
    fcount = Worksheets("工作表1").UsedRange.Columns.Count
    MsgBox CStr(rcount) + ":" + CStr(fcount)
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

726 note

老師     羅金榮
1.開發環境
  excell=>開發人員
檔案=>選項=>自訂功能區=>開發人員  打勾=>確定
2.開發人員
visual Basic
插入=>模組


巨集    試算表 +visual basic=>邏輯程式+表單

VBAproject
Microsoft Excell 物件 OLE物件即嵌入式物件
      ThisWorkbook 目前正在工作的試算表
       工作表ㄧ   sheet1
       工作表二   sheet2
       工作表三   sheet3
模組=>邏輯程式
表單(插入自動表單)UserForm

開一資料夾VBAProject  => 存檔vbabasic1  類型  Excell活頁簿  x.xlsm副檔名


模組
1.模組由副程式構成
2.副程式語法
     Sub 副程式名稱()
        [程式區塊]
     End Sub
3.輸出對話方塊
   Sub test1()
    MsgBox "第一支vba程式"
   End Sub

*開發人員=>聚集安全性=>巨集設定=>啟動所有巨集
*檔案=>選項=>

表單
Sub test2()
    UserForm1.Show
End Sub


變數]
Dim 變數名稱  as 資料型態(Bite,Interger.Long,Single,Double)
Dim  a as Integer

資料型態
數字(Byte,Integer.Long,Single,Double)
String  字串
Boolean  布林
Variant=>自訂資料型態


Workbooks=>Worksheets->sheet1,sheet2,sheet3

Sub test3()
    Dim rcount As Integer
    rcount = Worksheets.Count
    MsgBox CStr(rcount)
End Sub
*CStr(integer)
*CInt(String)

sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  MsgBox "你選到第" & Target.Row & "列第" & Target.Column & "欄"
End Sub

Workbook
Private Sub Workbook_Open()
    MsgBox "歡迎使用這個應用程式"
End Sub
-----------------------------
workbooks
worksheets
UserForm
Charts
Sheets
Cells
Range
-----------
VBA運算子
1.算術運算子:=,-,*,/,mod
2.關係運算子
3.邏輯運算子:and,or,not
and :if ....then 條件式