VBA / 初階課程筆記-小撇步

常用到的VBA編寫技巧

 

◎VBA函式線上說明 (Visual Basic for Applications)

◎取得列數

Execel

儲存格(“XX”) Ctrl + ↓ 到有資料的最後一列

VBA

r=Range(“XX”).End(xlDown).Row
 

備註：如果只有工作表的欄位名稱，會得到Excel工作表最大列數1048576

可以用來判斷是否除了欄位名稱之外有無任何資料

 

◎取得欄數

Execel

儲存格(“XX”) Ctrl + → 到有資料的最後一欄

VBA

c=Range(“XX”).End(xlToRight).Column
 

備註：

Excel工作表最大欄位數16384

 

◎選取所有資料

Execel

點選儲存格 Ctrl + Shift + →  +↓ 

VBA

Range(“XX”).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

 

◎迴圈順序

先列         後欄
由上而下  由左而右
外迴圈     內迴圈

A1 A2 A3~~B1 B2 B3

For 列= __ to __       '外迴圈
      For 欄= __ to __ '內迴圈
      Next
Next
 

◎VBA對話方塊

X=Application.InputBox(“____”,type:____)
type1:1→指定輸入內容為數字型態,但是在VBA會是字串型態

數字文字型態轉換為數值型態
X=CINT(Application.InputBox(“____”,type:=____))

備註：Application.InputBox 方法 (Excel)

 

◎VBA訊息方塊

MsgBox (“_________”)

備註：MsgBox 函式

 

◎刪除資料

儲存格Cells(X,Y)=””

範圍刪除Range(“XX”:”YY”).ClearContents 

表單 Name=””

反向迴圈刪除資料，可以避免刪除之後的空白儲存格自動補位造成需要重新計算資料筆數
 

◎表單

Name→表單項目在VBA裡的名稱
表單標題 Name.caption
標籤呈現的內容 Name.caption
文字方塊的內容/值 Name.Text

備註：表單設定與功能說明

 

◎如何在VBA直接在儲存格輸出字串公式

原本公式內的字串 “A”→””A””

公式內放入參數→”~” & 參數 & “~”，拆開成前後兩段字串

例如：

原本VBA程式碼

Private Sub 標準體重1()
    For i = 4 To 13
      If Cells(i, "J") > 0.05 Or Cells(i, "J") < -0.05 Then
        Cells(i, "M") = "不標準"
      Else: Cells(i, "M") = "標準"
      End If
    Next
End Sub

輸出字串公式

Private Sub 標準體重_excel輸出字串公式()
    For i = 4 To 13
      Cells(i, "M") = " =IF(AND(J" & i & "<5%,J" & i &">-5%),""標準"",""不標準"") "
    Next
End Sub

備註：吳老師教學部落格-05 如何直接用VBA輸出原本寫好的公式

 

◎在VBA輸出字串加入換行

ASCII → Char(10) 

備註：ASCII表

 

◎在VBA使用工作表的內建函數

Application.worksheetFunction.Excel內建函數 ( )

備註：在 Visual Basic 中使用 Excel 工作表函數

 

◎在VBA輸出0開頭的數字

以文字格式輸出，在數字前面加上 ‘單引號 等於文字格式

例如：cells(r,c)="'10123456789"

繼續閱讀

VBA / 初階課程筆記-3

105.03.17 第3次上課 (範例-02邏輯函數.xls)

 

01_AND函數與轉為VBA說明

Excel  AND函數  OR函數

AND(條件1 , 條件2 ) 兩者皆成立時回傳 True

OR(條件1 , 條件2 ) 其中之一成立時回傳 True

配合 IF 判斷式

If ( AND(條件1 , 條件2) , 成立時執行 ,不成立時執行)

轉為VBA 程序

Sub _____()
      If  條件1 And 條件2 Then
          成立時執行
      Else:不成立時執行
      End IF
End Sub

轉為VBA 自訂函數

Function ______( A , B )
         If  A~陳述式 And B~陳述式 Then
             成立時執行
         Else:不成立時執行
         End If
End Function

例題

E2==IF(AND(B2>20,D2>80000),"優","劣")

 

 

VBA

Private Sub 銷售情況()
      For i = 2 To 7
      If Cells(i, "B") > 20 And Cells(i, "D") > 80000 Then
        Cells(i, "F") = "優"
      Else: Cells(i, "F") = "劣"
      End If
      Next
End Sub

Private Function 銷售業績(銷售量, 銷售金額)
      If 銷售量 > 20 And 銷售金額 > 80000 Then
        銷售業績 = "優"
      Else: 銷售業績 = "劣"
      End If
End Function

 

02_增減體重範例與VBA解法

K4==IF(OR(J4>5%,J4<-5%),"不標準","標準")

L4==IF(AND(J4<5%,J4>-5%),"標準","不標準")
 

VAB

Private Sub 標準體重1()
    For i = 4 To 13
    If Cells(i, "J") > 0.05 Or Cells(i, "J") < -0.05 Then
         Cells(i, "M") = "不標準"
    Else: Cells(i, "M") = "標準"
    End If
    Next
End Sub

Private Function 標準體重函數(百分比)
      If 百分比 > 0.05 Or 百分比 < -0.05 Then
         標準體重函數 = "不標準"
      Else: 標準體重函數 = "標準"
      End If
End Function
 

03_如何反向刪除條件為標準的列

刪除資料，利用反向迴圈，避免後一列自動補上要計算列數的問題

正向迴圈 For i = 4 to 13

反向迴圈 For i = 13 to 4 Step-1
 

04_安養中心函數邏輯與解答

F5==IF(AND(D5>=70,RIGHT(E5,2)=”北市”),”符合條件”,”不符合條件”)

G5==IF(AND(D5>=70,OR(E5=”台北市”,E5=”新北市”)),”符合條件”,”不符合條件”)
 

05_安養中心VBA與表單製作

VBA程序與自訂函數

Private Sub 資格判定()
      For i = 5 To Range("A4").End(xlDown).Row
      If Cells(i, "D") >= 70 And Right(Cells(i, "E"), 2) = "北市" Then
        Cells(i, "H") = "符合條件"
      Else: Cells(i, "H") = "不符合條件"
      End If
      Next
End Sub

Private Function 條件函數(年齡, 戶籍地)
      If 年齡 >= 70 And Right(戶籍地, 2) = "北市" Then
        條件函數 = "符合條件"
      Else: 條件函數 = "不符合條件"
      End If
End Function
 

自訂表單

 

06_如何產生可以自動更新的下拉清單

Private Sub B01_Click()
    r = Sheets("綜合練習2").Range("A4").End(xlDown).Row + 1
    Sheets("綜合練習2").Cells(r, "A") = txt01.Text
    Sheets("綜合練習2").Cells(r, "B") = txt02.Text
    Sheets("綜合練習2").Cells(r, "C") = txt03.Text
    Sheets("綜合練習2").Cells(r, "D") = "=DATEDIF(C" & r & ",$F$2,""Y"")"
    '下拉選單
    Sheets("綜合練習2").Cells(r, "E") = cb01.Text
    Sheets("綜合練習2").Cells(r, "F") = 條件函數(Sheets("綜合練習2").Cells(r, "D"), Sheets("綜合練習2").Cells(r, "E"))
    '清空表單資料
    txt01.Value = ""
    txt02.Value = ""
    txt03.Value = ""
    cb01.Value = ""
    End Sub

Private Sub UserForm_Activate()
    '利用迴圈抓取資料 AddItem加入選單選項
    For i = 1 To Sheets("戶籍地").Range("A1").End(xlDown).Row
      cb01.AddItem Sheets("戶籍地").Cells(i, "A")
    Next
End Sub

備註

下拉選單 新增選項
XXX.AddItem 範圍.內容

 

07_補充兩個工作表範圍比對解答 (範例_兩個工作表範圍比對.xlsm)

Public Sub 工作表比較()
    Call 清除設定
    For i = 4 To 9
      For j = 3 To 14
        If Sheets(1).Cells(i, j) <> Sheets(2).Cells(i, j) Then
          '儲存格增加底色
         Cells(i, j).Interior.Color = RGB(255, 128, 128)
          '儲存格增加備註
          Cells(i, j).AddComment "去年度:" & Sheets(2).Cells(i, j)
        End If
      Next
    Next
End Sub

Sub 清除設定()
    For i = 4 To 9
      For j = 3 To 14
        If Sheets(1).Cells(i, j) <> Sheets(2).Cells(i, j) Then
          'RGB(255,255,255)-白色
          Cells(i, j).Interior.Color = RGB(255, 255, 255)
          '儲存格移除備註
          Cells(i, j).ClearComments
        End If
     Next
    Next
End Sub

繼續閱讀

VBA / 初階課程筆記-2

105.03.10 第2次上課 (範例-02邏輯函數.xls)

 

01_兩重邏輯與多重邏輯函數判斷

Excel

IF=(邏輯判斷式 , 成立時執行 , 不成立時執行)

第二重、第三重~~的邏輯判斷就是在前一重的「不成立時執行」要進行的部分

例題：分數80以上為 A，70~79為 B，60~69為 C，不及格為 D

G2=IF(E2>=80,"A",IF(E2>=70,"B",IF(E2>=60,"C","D")))

可以拆解成

G2=IF(E2>=80,”A”,_______________

                              IF(E2>=70,”B”,_______________

                                                    IF(E2>=60,”C”, “D”)))
 

02_兩重邏輯轉為VBA

Private Sub 成績()
    r = Range("A1").End(xlDown).Row
    For i = 2 To r
       If Cells(i, "E") >= 60 Then
         Cells(i, "I") = "及格"
       Else
         Cells(i, "I") = "不及格"
       End If
    Next
End Sub
 

03_多重邏輯轉為VBA說明

VBA

IF     判斷式1  then
        判斷式1  成立時執行
ElseIf 判斷式1不成立時，判斷式2  then
        判斷式2  成立時執行
Else:  都不成立時執行
End If

Private Sub 成績多重()
    '取得目前有資料的列數=總共有多少列
    r = Range("A1").End(xlDown).Row
    '第一列是標題欄
    For i = 2 To r
        If Cells(i, "E") >= 80 Then
           Cells(i, "K") = "A"
        ElseIf Cells(i, "E") >= 70 Then
           Cells(i, "K") = "B"
        ElseIf Cells(i, "E") >= 60 Then
           Cells(i, "K") = "C"
        Else: Cells(i, "K") = "D"
        End If
    Next
End Sub

 

04_如何利用InputBox取得不同的合格分數

Private Sub 成績()
    'InputBox
    x = Application.InputBox("請輸入合格分數", Type:=1)
    Cells(6, "J") = "合格分數=" & x
    r = Range("A1").End(xlDown).Row
    For i = 2 To r
      If Cells(i, "E") >= x Then
        Cells(i, "I") = "及格"
      Else
        Cells(i, "I") = "不及格"
      End If
    Next
End Sub

備註

Application.InputBox 方法 (Excel) 的參數說明
 

05_如何分享自訂函數給其他人(增益集)

檔案另存成 excel增益集→儲存在excel增益集資料夾

檔案→選項→增益集→excel增益集 執行，勾選增益集的名稱(檔案名稱)→之後excel在插入函數，使用者定義就會有新增的自訂函數
 

06_如何取消增益集

開發人員→增益集，取消勾選，或者從瀏覽中找到檔案再刪除
 

07_如何改為VBA的自訂函數

Private Function 成績函數(平均成績)
    If 平均成績 >= 60 Then
       成績函數 = "合格"
    Else: 成績函數 = "不合格"
    End If
End Function

Private Function 成績函數多重(平均成績)
    If 平均成績 >= 80 Then
       成績函數多重 = "A"
    ElseIf 平均成績 >= 70 Then
       成績函數多重 = "B"
    ElseIf 平均成績 >= 60 Then
       成績函數多重 = "C"
    Else: 成績函數多重 = "D"
    End If
End Function
 

08_如何設計表單將EXCEL當成資料庫存資料

流程

1)插入表單

插入→自訂表單

2)設計表單欄位與按鈕

 

3)撰寫VBA程式碼

在按鈕點兩下，就會進入點擊按鈕的程序編輯

詳細的表單設計與資料賦值可以參考VBA表單設計

 

09_如何啟動表單(按鈕與當EXCEL開啟)

1)按鈕啟動，指定巨集

新增程序

表單.show

Private Sub 啟動表單()
        home.Show
End Sub

2)excel開啟時啟動

 

繼續閱讀