VBA KeyDown

輸入完按 Enter 跳至下一個輸入框

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then
                TextBox2.SetFocus
        End If
End Sub

Reference :
Excel VBA textbox判斷是否有按Enter鍵-彰化一整天的Blog留言版
如何利用 Enter 鍵作為 Tab 鍵? / Visual Basic 6.0/VBA / 程式設計俱樂部

VBA Functions pass ByVal / ByRef

Reference :
VBA Functions and Subroutines

VBA 驗證 E-Mail

 Reference :
VBA Express : Excel - Validating an email

VBA MsgBox Yes No

If MsgBox("刪除此筆資料？", vbYesNo, "Message") = vbYes Then
    users.del(i)
Else
    Exit Sub
End If

Reference :
Text return in a MsgBox

VBA OptionButton 群組

VBA OptionButton 群組的話不是用 Tag,  而是 GroupName

VBA 字典 (Dictionary)

Dim datas
Set datas = CreateObject("Scripting.Dictionary")

datas.add "Name", "QQQ"
datas.add "Birthday", "1911/1/1"

For Each key In datas.KEYS
    MsgBox key & "=>" & datas.Item(key)
Next

Reference :
VBA中Dictionary对象使用小结 - 日积月累 - 博客大巴
TechBookReport - VBA Dictionary Object

VBA Function return Dictionary

Set datas = myfunc
MsgBox TypeName(datas)

Sub Function myfunc()
    Dim D
    Set D = CreateObject("Scripting.Dictionary")
    D.Add "Name", "xxx"
    D.Add "Birthday", "1911/1/1"

    Set myfunc = D
End Function

Reference :
Using the Dictionary Class in VBA
vba: return dictionary from function
vba: return dictionary from function
Dictionary物件的認識與應用

VBA SQL last insert ID

Reference :
How to get id of newly inserted record using Excel VBA?

VBA typeof 物件型態

MsgBox TypeName(the_object)

引數不為選擇性 (optional)

Function Test2(Optional V As Variant, Optional L As Long = -1) As String
...

End Function

Reference :
Optional Parameters To Procedures

VBA ADODB.Recordset to array

Public Function staffIndex(Optional staffName As String)   
    ' 搜尋使用者資料
    Set myDB = New clsADODBopen
    sqlStr = "SELECT 識別碼, 姓名 FROM staffs WHERE 姓名 LIKE '%" + staffName + "%' ORDER BY 識別碼"
    myDB.subOpen sqlStr

    staffIndex = myDB.theRST.GetRows(myDB.theRST.RecordCount)
End Function

VBA 二維陣列 2-Dimensional Array

Sub SampleArray()
    Dim Data(1 To 3, 1 To 4)
    Dim Sum As Single
    Dim iRow As Integer, iCol As Integer

    For iRow = LBound(Data, 1) To UBound(Data, 1)
        For iCol = LBound(Data, 2) To UBound(Data, 2)
            Data(iRow, iCol) = Cells(iRow, iCol)
            MsgBox "Data(" & iRow & "," & iCol & ") = " & Data(iRow, iCol)
            Sum = Sum + Data(iRow, iCol)
            MsgBox "Sum = " & Sum
        Next iCol
    Next iRow

    MsgBox "The final Sum = " & Sum
End Sub

Reference :
2-Dimensional Array - Excel 2003 VBA
2 dimensional array...Please help!

VBA switch ... case

switch ... case -> Select Case

Dim Number
Number = 8 ' Initialize variable.

Select Case Number ' Evaluate Number.
     Case 1 To 5 ' Number between 1 and 5, inclusive.
        Debug.Print "Between 1 and 5"
    ' The following is the only Case clause that evaluates to True.
    Case 6, 7, 8 ' Number between 6 and 8.
        Debug.Print "Between 6 and 8"
    Case 9 To 10 ' Number is 9 or 10.
        Debug.Print "Greater than 8"
    Case Else ' Other values.
        Debug.Print "Not between 1 and 10"
End Select

Reference :

Switch Case statements

VBA 驗證模組

Validate Integer、Number、Date、Text、AlphaNumeric




Reference :
VBA Validation Module

VBA 字串取代 replace string

num2week = Replace(numStr, "1", "Mon")

VBA 分割字串 split string

Sub SplitValue()
    Dim avarSplit As Variant
    Dim intIndex As Integer
    avarSplit = Split(Range("A1").Value, ",")
    For intIndex = LBound(avarSplit) To UBound(avarSplit)
        MsgBox "Item " & intIndex & " is " & avarSplit(intIndex) & _
        " which is " & Len(avarSplit(intIndex)) & " characters long", vbInformation
    Next
End Sub 

Reference :

Split Function (Visual Basic)
String Split in vba

VBA 擷取字串 sub string

MsgBox MID( "Lloveba ",2,4)

MsgBox MID( "abcd,", 1, Len("abcd,") - 1)

VBA 表列出 list CheckBox

CheckBox 全選範例

For I = 1 To 7
    Me.Controls("DayCheckBox" & I).value = True
Next

CheckBox Tag 方式

Dim ctrl As Control

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CheckBox" Then
        If ctrl.Tag = "Time" Then
            MsgBox ctrl.Name & " = " & ctrl.value
        End If
    End If
Next

Set ctrl = Nothing

Reference :
Loop through checkboxes
Excel VBA: Loop Through Controls on a UserForm. Textbox, ComboBox, CheckBox etc
如何抓取動態產生的控制項(ex.textbox)

VBA ListBox ColumnWidths

ListBox.ColumnCount = 3

ListBox.ColumnWidths = "50;150;100" 

Reference :

Changing Listbox Columnwidths

How to: Change the Column Widths of a Multi-Column List Box

VBA Bind Keys (keybinding) in a Form

(1) 雖然有 Form_KeyDown 事件, 但若 Form 上面有其他按鈕或輸入框, 則 Form_KeyDown 事件不會被觸發到

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
         Case vbKeyW
             BackColor = QBColor(15)
         Case vbKeyF
             ForeColor = QBColor(5)
         Case vbKeyM
             MsgBox "Added more keys...", vbInformation
    End Select
End Sub

(2) 另外一種則是在 UserForm_Activate 事件中持續使用 GetAsyncKeyState 捕獲 keybord state, 但 VBA 內建沒有延遲函式 (Sleep、Delay、Timer 等), 則持續迴圈會耗用 CPU, 或設法加入 Sleep 函式 Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

Private Sub UserForm_Activate()
    Do
        If GetAsyncKeyState(vbKeyControl) And GetAsyncKeyState(vbKeyA) Then MsgBox "你按了Ctrl+A"
    DoEvents ' 避免持續占用 CPU
    If s = True Then Exit Do
    Loop
End Sub

(3) 最後的方法則是使用在表單載入時 UserForm_Initialize 執行系統函式 SetTimer


模組  moduleKeyBinding

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

Public Function keyCapture()
    ' 偵測是否按下 Ctrl + d
    If GetAsyncKeyState(vbKeyControl) And GetAsyncKeyState(vbKeyD) Then
        MsgBox "進入 Develop 模式"
   
        Application.Visible = True
    End If
End Function

表單 TheMainMenuForm

Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Private Sub UserForm_Initialize()
    SetTimer 0, 1, 1000, AddressOf moduleKeyBinding.keyCapture
End Sub

Reference :
How do i bind a key?
[VB]&[VBA] 如果判別是否按了ctrl+a的鍵?
[VBA] 使用Timer
完整功能的VBA Timer类