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

繼續閱讀