常用到的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"