EXCEL MACROS VÀ LẬP TRÌNH VBA - Viblo

Excel Macros là gì?

Excel Macros là những chương trình nhỏ giúp lặp đi lặp lại những thao tác từ đơn giản đến phức tạp một cách tự động trong Excel. Ngoài Excel Macros, chúng ta còn có thể gặp Word Macro, PowerPoint Macro, Outlook Macro …

VBA là gì?

VBA viết tắt của Visual Basic for Applications, là một ngôn ngữ lập trình đi kèm với một số phần mềm của Microsoft, trong đó có Excel, Word, PowerPoint, Outlook, … VBA - Visual Basic for Application là một ngôn ngữ lập trình, ngôn ngữ của nó là Visual Basic nhưng nó được tích hợp vào trong ứng dụng (Application)

VBA làm gì ?

VBA cho phép người ta viết các mã lệnh có tác động tới nhiều đối tượng để làm tăng tính tự động hoá trong ứng dụng của mình, làm những việc mà các công cụ sẵn có trong ứng dụng chưa có, chẳng hạn như có thể viết hàm tính thuế, hay các bài toán tính toán phức tạp mà công thức có sẵn của Excel không thể đáp ứng được .

Lập trình VBA và Excel Macros

Bài viết này mô tả cách thay đổi UserForms lập trong Microsoft Excel. Nó bao gồm các mẫu và Microsoft Visual Basic cho macro Applications (VBA) cho bạn biết cách tận dụng khả năng UserForms và cách sử dụng điều khiển ActiveX™ có sẵn cho UserForms như:

• Bộ điều khiển Label
• Điều khiển Hộp văn bản
• Kiểm soát CommandButton
• Kiểm soát ListBox
• Kiểm soát ComboBox
• Khung điều khiển
• Kiểm soát OptionButton
• Điều khiển hộp kiểm
• Kiểm soát ToggleButton
• Kiểm soát TabStrip
• Kiểm soát multiPage
• Thanh điều khiển
• Kiểm soát SpinButton
• Kiểm soát RefEdit
• Điều khiển ảnh

Để bắt đầu với Excel Macros nói riêng hay Macros nói chung và ngôn ngữ lập trình VBA, chúng ta sẽ đi mở trình soạn thảo VBA. Để mở trình soạn thảo VBA, các bạn hãy theo từng bước sau đây

Set up

1. Trong Excel, bấm chuột phải vào Ribbon và chọn mục Customize the Ribbon

2. Trong cửa sổ Excel Options, chọn thẻ Developer

3. Chỉnh sửa Macro Security để có thể chạy file có chưa Excel có chứa code VBA: trong thẻ Developer > Nhóm Code > Bấm Macro Security

4. Chỉnh Macro Security Settings như sau

5. Lưu file

6. Chọn thẻ Developer > Insert: Chọn chèn vào 1 button như sau

7. Click chuột phải vào button để thực hiện viết hàm cho button đó

8. Cửa sổ VBA hiển thị, và công việc của bạn bây giờ là code. Trong ví dụ này mình chỉ ví dụ button đó sẽ thực hiện công việc ghi text "TEST" vào cell "A1"

Private Sub CommandButton1_Click() Range("A1").Value = "TEST" End Sub

9. Kết quả sau khi chạy Macro là 1 thông báo trong Excel như thế này

Bộ điều khiển Label

Label kiểm soát chủ yếu được sử dụng để mô tả các điều khiển trên một UserForm. Các thuộc tính thường được sử dụng bao gồm thuộc tính Font.Size và thuộc tính ForeColor ...

Private Sub CommandButton1_Click() With Label1 ' Set the text of the label. .Caption = "LABEL" ' Automatically size the label control. .AutoSize = True .WordWrap = False ' Set the font used by the Label control. .Font.Name = "Times New Roman" .Font.Size = 18 .Font.Bold = False ' Set the font color to blue. .ForeColor = RGB(0, 0, 255) End With End Sub

Làm thế nào để sử dụng một điều khiển Label

1. Chọn thẻ Developer > Chèn vào 1 label
2. Chèn thêm CommandButton1
3. Ở chế độ Design, click chuột phải vào button và chọn "View Code"
4. Nhập code như trên
5. Đóng cửa sổ VBA và đảm bảo chế độ design đã được tắt
6. Click vào button vào quan sát Label

Điều khiển Hộp văn bản

Hộp văn bản dùng để thu thập đầu vào của người dùng

Private Sub CommandButton1_Click() If TextBox1.Text <> "thAnhly" Then MsgBox "Password is Incorrect. Please try again." TextBox1.Text = "" Else MsgBox "Hi Thanh Ly!" End If End Sub

Làm thế nào sử dụng điều khiển hộp văn bản

1. Chọn thẻ Developer > Chèn vào 1 textbox
2. Ở chế độ Design, chọn Properties để edit thuộc tính textbox
3. Set giá trị của PasswordChar = * (Khi user nhập text vào textbox thì sẽ hiển thị thay bằng dấu *)
4. Chèn thêm CommandButton1
5. Ở chế độ Design, click chuột phải vào button và chọn "View Code"
6. Nhập code như trên
7. Đóng cửa sổ VBA và đảm bảo chế độ design đã được tắt
8. Thử nhập password đúng và sai để kiểm tra kết quả
9. Click vào "Sign In " button

Kiểm soát CommandButton

CommandButton thường được sử dụng để thực thi các sự kiện của VBA khi click chuột vào command button.

Private Sub CommandButton1_Click() red = Int(Rnd * 255) green = Int(Rnd * 255) blue = Int(Rnd * 255) CommandButton1.BackColor = RGB(red, green, blue) End Sub

Làm thế nào sử dụng CommandButton

1. Chọn thẻ Developer > chèn thêm CommandButton1
2. Ở chế độ Design, click chuột phải vào button và chọn "View Code"
3. Nhập code như trên
4. Đóng cửa sổ VBA và đảm bảo chế độ design đã được tắt
5. Click vào button và xem kết quả

Kiểm soát ListBox

Mục đích kiểm soát ListBox là cho người dùng với một danh sách các lựa chọn. Bạn có thể lưu trữ danh mục cho một điều khiển ListBox trên trang tính Excel. Để xác định điều khiển ListBox với dải ô trên trang tính, sử dụng thuộc tính RowSource . Khi bạn sử dụng thuộc tính MultiSelect , bạn có thể thiết lập điều khiển ListBox chấp nhận nhiều lựa chọn.

Cách lấy giá trị của item đang được chọn trong Listbox

1. Khởi động Excel
2. Chọn tab Developer > Visual Basic Editor
3. Insert userform
4. Insert Listbox vào userform
5. Double click vào listbox
6. Nhập đoạn code sau

Private Sub ListBox1_Click() MsgBox ListBox1.Value End Sub

7. Run Sub/UserForm sẽ thấy listbox hiển thị

Điều khiển listbox với multi items

1. Trong ô A1:A5 trên Sheet1, nhập các giá trị cho listbox
2. Trên menu Công cụ , trỏ tới Developer > Visual Basic Editor.
3. Insert userform
4. Insert Listbox vào userform
5. Click chuột phải vào listbox chọn Properties và nhập như sau
6. Insert thêm commandbutton vào Userform
7. Double click để nhập sự kiện cho button

Sub CommandButton1_Click () ' Loop through the items in the ListBox. For x = 0 to ListBox1.ListCount - 1 ' If the item is selected... If ListBox1.Selected(x) = True Then ' display the Selected item. MsgBox ListBox1.List(x) End If Next x End Sub

8. Run Sub/UserForm
9. Chọn một hoặc nhiều mục trong danh sách.
10. Bấm CommandButton1. ==> Sau khi bạn bấm CommandButton1, mỗi items trong ListBox mà bạn chọn xuất hiện trong một dialog riêng. Sau khi tất cả các mục đã chọn xuất hiện trong hộp thư, UserForm tự động đóng.

Sử dụng thuộc tính RowSource

1. Trong ô A1:A5 trên Sheet1, nhập các giá trị cho listbox
2. Trên menu Công cụ , trỏ tới Developer > Visual Basic Editor.
3. Insert userform
4. Insert Listbox vào userform
5. Insert thêm commandbutton vào Userform
6. Double click để nhập sự kiện cho button

Private Sub CommandButton1_Click() ListBox1.RowSource = "=Sheet1!A1:A5" End Sub

7. Run Sub/UserForm => ListBox1 được xác định với các giá trị trong ô A1:A5 trên Sheet1.

Listbox và biến mảng

1. Trên menu Công cụ , trỏ tới Developer > Visual Basic Editor.
2. Insert userform
3. Insert Listbox vào userform
4. Chèn 1 module

Sub PopulateListBox() Dim array1 As Variant Dim Ctr As Integer MyArray = Array("QA", "DEV FE", "brSE", "Comtor", "DEV BE") For Ctr = LBound(MyArray) To UBound(MyArray) UserForm1.ListBox1.AddItem MyArray(Ctr) Next UserForm1.Show End Sub

5. Chọn Tool > Macro > PopulateListBox > Run
6. Thêm commandbutton vào userform với action sau

Private Sub CommandButton1_Click() For i = 1 To ListBox1.ListCount ListBox1.RemoveItem 0 Next I End Sub

7. Chọn Tool > Macro > PopulateListBox > Run
8. Click Commandbutton ==> Tất cả các item trong listbox dược xóa

Sử dụng giá trị theo hàng ngang

Sub PopulateListWithHorizontalRange() For Each x In Sheet1.Range("A1:E1") UserForm1.ListBox1.AddItem x.Value Next UserForm1.Show End Sub

Làm thế nào để trả nhiều giá trị điều khiển ListBox với nhiều cột dữ liệu

1. Nhập dữ liệu vào file excel như sau:

2. Mở Visual Basic Editor 3. Insert Label 4. Insert listbox 5. Thiết lập các giá trị của listbox như sau: 6. Nhập action cho listbox như bên dưới:

Private Sub ListBox1_Change() Dim SourceData As Range Dim Val1 As String, Val2 As String, Val3 As String Set SourceRange = Range(ListBox1.RowSource) Val1 = ListBox1.Value Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value Label1.Caption = Val1 & " " & Val2 & " " & Val3 End Sub

7. Run Sub/UserForm

Continue...