Lập trình Userform-VBA tạo mục nhập có danh sách tìm kiếm với BSSearchEngine - Add-in A-Tools v10.1 (1 người xem)

[Nguyễn Duy Tuân]

Nhập liệu nâng cao trong Add-in A-Tools cho phép tìm và lọc trên sheet. Từ phiên bản v10.1 cho phép người dùng lập trình với class BSSearchEngine để tạo cửa sổ tìm kiếm này với điều khiển - control trên Userform. Tốc độ nhanh dù hàng trăm ngàn dòng. BSSearchEngine cung cấp phong phú các thủ tục, thuộc tính, sự kiện để tùy biến giao diện và hành động.

Khi cài A-Tools bạn mở tập tin tại đường dẫn dưới đây để xem mã nguồn ví dụ đầy đủ:

(*) File mã nguồn ví dụ: "C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\BSSearchEngine - Create DropDown List on control\BSSearchEngine - DropDown List.xlsm"
(*) Bài viết gốc tại đây

Chủ đề này tôi sẽ hướng dẫn từng bước lập trình từ cơ bản đến nâng cao với BSSearchEngine để tạo tính năng tìm và lọc với control trên Userform giống như chức năng Nhập liệu nâng cao trên sheet.

Video phần 01 - Tạo mục tìm và lọc đơn giản

Dòng lệnh tối thiểu để tạo cửa sổ tìm và lọc với control (các code trong chủ đề này là ngôn ngữ VBA, dùng định dạng C# để hình thức đẹp hơn)

+ Bước 1: Nhúng thư viện AddinATools.dll

Trong cửa sổ lập trình VBA, chọn Project bạn muốn lập trình, vào menu Tools -> References... tick chọn "AddinATools.dll"

​

+ Bước 2: Tạo Userform và chèn một control TextBox

Control phục vụ để nhập liệu là TextBox. Bạn có thể sử dụng các control ComboBox, BSEdit hoặc control nào cho phép nhập liệu.

​

Nhấp đúp chuột vào Userform - Vào chế độ soạn thảo code trong Userform. Copy đoạn code dưới đây dán vào.

Private WithEvents SE_MAHH As BSSearchEngine

Private Sub UserForm_Initialize()
    Set SE_MAHH = New BSSearchEngine 'Khởi tạo
    SE_MAHH.Create txtMAHH, Range("DMHH") 'Nhập thông tin cho BSSearchEngine
    SE_MAHH.BoundColumn = 1 'Ngầm định là 1 - Chỉ ra vị trí cột giá trị lấy về control
    'EditControl: TextBox, ComboBox, BSEdit, Handle to Edit
    'DataSource: Excel Range (high speed!)
    '            Array2D
    '            ADODB.Recordset
End Sub

'Sự kiện nhận giá trị chọn từ danh sách. Lấy giá trị trong mảng điền vào các control khác.
Private Sub SE_MAHH_OnAfterUpdate(RowIndex As Variant, Values As Variant, ByVal Text As String)
    lblTenHH.Caption = Values(1, 2) 'Tên hàng
    txtDVT.Text = Values(1, 4) 'Đơn vị tính
End Sub

Giải thích mã nguồn

+ BSSearchEngine: là class thực hiện tìm và lọc dữ liệu khi thay đổi dữ liệu trên control nhập liệu.

Sub BSSearchEngine.Create(EditControl, DataSource, [DataHasHeaderRow As Boolean = True], [ShowHeader As Boolean = True])

là thủ tục kết nối control nhập liệu và dữ liệu nguồn:
EditControl: có thể là: Handle of the editor control; BSAC.BSEdit Control; MSForms.TextBox, MSForms.ComboBox
DataSource: có thể là Excel Range, Array2D, ADODB.Recordset
DataHasHeaderRow: ngầm định là True, chỉ ra dữ liệu nguồn có dòng tiêu đề.
ShowHeader: ngầm định là True, chỉ ra cửa sổ tìm kiếm hiển thị dòng tiêu đề không.

Trong mã nguồn:
SE_MAHH.Create txtMAHH, Range("DMHH")
control txtMAHH sẽ được kích hoạt cửa sổ tìm kiếm và lọc, dữ liệu nguồn tại vùng NAME "DMHH" - Range("DMHH")

+ BSSearchEngine.BoundColumn: là số nguyên, ngầm định là 1 chỉ ra vị trí cột trong dữ liệu nguồn giá trị được lấy về control nhập liệu.

Muốn tương tác lệnh trong khi thực hiện các hành động thì phải lập trình sự kiện cho BSSearchEngine.
Sự kiện OnAfterUpdate() chạy ngay sau khi giá trị được cập nhật vào control. Cấu trúc sự kiện là

Sub OnAfterUpdate(RowIndex, Values, Text As String)

+ RowIndex: là mảng 1D chứa tạo độ các dòng được chọn.
+ Values: là mảng 2D chứa giá trị đã chọn từ danh sách.
+ Text: là chuỗi được nhập vào control.


Nếu bạn muốn chọn và nhận về sự kiện này nhiều dòng thì phải khai báo thuộc tính MultiInputColumns.
+ BSSearchEngine.MultiInputColumns = True cho phép chọn và nhận về nhiều dòng giá trị.

Kết quả khi chạy

​

Bài viết tiếp theo tôi sẽ thêm ví dụ và giải thích thêm...

 

[Nguyễn Duy Tuân]

Đã bổ sung nội dung bài #1

 

[Nguyễn Duy Tuân]

Bài chia sẻ này tôi hướng dẫn các bạn thay đổi màu sắc của từng dòng trong cửa sổ tìm và lọc của Nhập Liệu Nâng Cao trên Userform.

Để thay đổi màu trên nhập liệu nâng cao chúng ta dùng các thuộc tính, RowColor1, RowColor2:
BSSearchEngine.RowColor1: nhận màu dòng lẻ, giá trị là chuỗi hexa color hoặc kiểu Long trả về bởi hàm RGB.
BSSearchEngine.RowColor2: nhận màu dòng chẵn, giá trị là chuỗi hexa color hoặc kiểu Long trả về bởi hàm RGB.

Mã nguồn VBA như sau:

Option Explicit
Private WithEvents SE_DATA As BSSearchEngine

Private Sub UserForm_Initialize()
    Set SE_DATA = New BSSearchEngine
    SE_DATA.Create txtSearch, Range("KHO")
    SE_DATA.BoundColumn = 12 'Lay cot ROW
    SE_DATA.RowColor1 = RGB(250, 210, 198) ' or "#FAD2C6" 'Màu dòng lẻ
    SE_DATA.RowColor2 = "#F7B49F" ' Or RGB(247, 180, 159) 'Màu dòng chẵn
End Sub

'Sự kiện bắt khi giá trị nhập vào control, lấy tọa độ dòng để mở lại phiếu, chứng từ.
Private Sub SE_DATA_OnAfterUpdate(RowIndex As Variant, Values As Variant, ByVal Text As String)
    If Text <> "" Then
        If IsNumeric(Text) Then
            LoatDuLieu Text 'Tai phieu tu sheet "KHO"
        End If
    End If
End Sub

​

Bài viết còn tiếp ...

 

[Cá ngừ F1]

Hay thế anh ơi
Hiện tại e vẫn đang dùng User Form mà trước anh NDU có làm, để nhập nhanh hoặc tìm kiếm từ Sheets

 

[Nguyễn Duy Tuân]

Hay thế anh ơi
Hiện tại e vẫn đang dùng User Form mà trước anh NDU có làm, để nhập nhanh hoặc tìm kiếm từ Sheets

View attachment 311665

Hi em. Có gì dùng đó quan trọng đáp ứng đạt yêu cầu công việc của em là được. BSSearchEngine chính là thư viện tạo ra Nhập Liệu Nâng cao của A-Tools từ cách đây 10 năm bây giờ mới tạo interface trong thư viện để cho phép lập trình VBA hoặc các ngôn ngữ lập trình khác đấy. Khi nào rảnh, có hứng em thử trải nghệm với BSSearchEngine với dữ liệu nguồn khoảng 1.000.000 dòng (một triệu dòng) xem tốc độ thế nào nhé .

 

[Nguyễn Duy Tuân]

Bài viết này tôi hướng dẫn dùng ADO để lấy dữ liệu bằng câu lệnh SQL

Với giải pháp này bạn có thể lấy dữ liệu từ bên ngoài như SQL Server, MySQL, Oracle, Access, Excel,.... chỉ cần bạn nắm được cấu trúc của ConnectionString. Sau đây là các bước làm và những lưu ý.

+ Bước 1: Trong VBA, vào menu Tools -> References... chọn "Microsoft ActiveX Data Object..." Bạn chọn version nào cũng được.

+ Bước 2: Mở Userform dán đoạn code dưới đây:

Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private SE_DMHH As BSSearchEngine

Private Sub UserForm_Initialize()
    Dim ExcelFile As String
    ExcelFile = ThisWorkbook.FullName
    'Setup Recordset - DataSource:
    Set cnn = New ADODB.Connection
        cnn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ExcelFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
    cnn.Open
    'Set rst = cnn.Execute("SELECT * FROM DMHH") 'CAUTION: DO NOT USE THIS!
    Set rst = New ADODB.Recordset
    'rst.CursorLocation = adUseClient
    rst.Open "SELECT * FROM DMHH", cnn, adOpenStatic 'fine!
    '----------------------------
    'Setup Search Engine:
    Set SE_DMHH = New BSSearchEngine
    SE_DMHH.Create TextBox1, rst
    '--------------------
End Sub

'Giải phóng bộ nhớ khi đóng Userform
Private Sub UserForm_Terminate()
    If Not rst Is Nothing Then
        rst.Close
    End If
    If Not cnn Is Nothing Then
        cnn.Close
    End If
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Bây giờ chạy Userform ta được màn hình như sau:

​

Lưu ý: việc chạy SQL để lấy về Recordset ta có thể dùng lệnh

Set rst = cnn.Execute("SELECT * FROM DMHH")

Nếu bạn chỉ lấy dữ liệu một lần rồi đóng thì hoàn toàn được nhưng Recordset dùng cho BSSearchEngine được đọc đi đọc lại nhiều lần nên lệnh trên không tương thích. Bạn cần tạo Recordset như ví dụ đã nêu, tức phải là

rst.Open "SELECT * FROM DMHH", cnn, adOpenStatic

Bài viết sau tôi sẽ trình bày kỹ hơn sự khác nhau khi dùng DataSource là Excel Range, Array2D, Recordset.

 

[Nguyễn Duy Tuân]

Sự khác nhau với các loại DataSource trong BSSearchEngine

Dòng lệnh tạo nhập liệu nâng cao trên Userform kết nối control nhập liệu và nguồn dữ liệu - DataSource theo cấu trúc dưới đây:

Sub BSSearchEngine.Create(EditControl, DataSource, [DataHasHeaderRow As Boolean = True], [ShowHeader As Boolean = True])

DataSource có thể là một trong ba loại dưới đây:
+ Excel Range:
Là đối tượng vùng trên bảng tính. Sử dụng loại này tốc độ của BSSearchEngine chạy nhanh nhất.
Ví dụ Range("A4:H100000"), hoặc có thể là NAME Range("DMKH")
Khi dùng kiểu range thì định dạng ở các cột được sử dụng cho hiển thị trong danh sách. Bạn có thể dùng Format Cells để định dạng các cột ngày tháng, cột tiền tệ, số lượng, khi mở màn hình nhập liệu nâng cao nó sẽ định dạng giống trên bảng tính Excel.

+ ADODB.Recordset:
Là đối tượng tạo ra bởi chạy câu lệnh SQL. Đây là kiểu đối tượng truy vấn tới các loại CSDL bên ngoài. Bạn có thể kết nối tới Excel, Access, SQL Server, MySQL,...
Ví dụ: bạn xem ví dụ bài #6
Tên các tường được dùng để làm tên các cột khi hiển thị. Định dạng cửa sổ phụ thuộc vào kiểu dữ liệu ở các trường - Field. Các trường kiểu Currency, Double,Single, Float tự động được định dạng kiểu #,##0. Ví dụ 100000 hiển thị là 100,000. Kiểu DateTime được hiển thị với định dạng ShortDate , mà Windows đang mặc định.

+ Array2D:
Là mảng 2D - hai chiều gồm cả dòng và cột (1D sẽ lỗi). Đây alf kiểu dữ liệu tự do mà bạn thu thập bằng các cách nào đó. Ví dụ lấy mảng dữ liệu trên bảng tính, bằng hàm tạo mảng.
Ví dụ: Range("A4:H100000").Value. Với dữ liệu trên bảng tính bạn không nên dùng Array mà nên chỉ định Range tức thay vì dùng Range("A4:H100000").Value bạn dùng Range("A4:H100000") để đạt tốc độ nhanh hơn và lấy được định dạng hiển thị trên cửa sổ tìm kiếm.
Bạn có thể lấy dữ liệu từ Google Sheets, Excel Online bằng hàm BS_CLOUD của Add-in A-Tools. Ví dụ:

Dim bf As New BSFunctions
BSSearchEngine.Create(EditControl, bf.bs_Cloud(FileID, "Sheet!A4:H10000"))
Set bf = Nothing

Kỹ thuật lập trình lấy dữ liệu từ Google Sheets, Excel Online với Add-in A-Tools với loại video hướng dẫn này

Liên kết: https://www.youtube.com/watch?v=b3u_veD6jXM​

Khi dùng DataSource là Array2D người dùng phải tự định dạng hiển thị các cột nếu cần.

Bài viết sau tôi sẽ hướng dẫn lập trình sự kiện để định dạng, chỉnh sửa thông tin từng cột trong danh sách tìm kiếm qua sự kiện OnGetColumn.

​

 

[Nguyễn Duy Tuân]

Kỹ thuật lập trình với BSSearchEngine để tùy biến định dạng cột trong danh sách nhập liệu nâng cao

Kỹ thuật này bạn nên áp dụng khi dữ liệu nguồn - DataSource là Array2D vì nó thuộc dữ liêu jtuwj do, không định kiểu như Recordset, Excel Range hoặc tùy ý hiển thị giao diện.

Để tùy biến hiển thị cột trong danh sách tìm kiếm chúng ta lập trình với sự kiện OnGetColumn và tùy chỉnh thuộc tính trong tham số IBSColumnInfo truyền vào.

Cấu trúc sự kiện:

Private Sub EditControl_OnGetColumn(ByVal ColumnInfo As AddinATools.IBSColumnInfo)
'Tùy chỉnh thuộc tính trong tham số ColumnInfo...
End Sub

Các thuộc tính của lớp IBSColumnInfo là

​

+ Alignment: nhận các hằng số là Center , LeftJustify , RightJustify
+ Caption: là chuỗi thay đổi tên cột
+ CheckBox: kiểu Boolean (True/False), hiển thị checkbox, áp dụng trường Boolean (chạy trong tương lai)
+ Color: nhận giá trị màu RGB kiểu Long hoặc chuỗi màu Hexa như là "#F4B084"
+ DisplayPicture: kiểu Boolean, hiển thị ảnh (chạy trong tương lai)
+ FiedType: báo cho BSSearchEngine kiểu dư liệu là gì, các kiểu quy định: ftBoolean, ftCurrency, ftDate, ftDateTime, ftDouble, ftInteger, ftString, ftTime, ftUnknown
+ FormatStr: kiểu String, mô tả biểu thức định dạng số hoặc thời gian. Ví dụ "#,##0" hoặc áp dụng với thời gian "dd-mm-yyyy hh:mm"
+ ImageIndex: kiểu Long, là chỉ số icon trong BSImageList để hiển thị biểu tượng/icon trong cột (chạy trong tương lai)
+ Index: kiểu Long, vị trí của cột trong dữ liệu nguồn
+ MultiLine: kiểu Boolean, cho phép nội dung tự xuống dòng khi tràn quá độ rộng cột.
+ Position: kiểu Long, cho phép thay đổi vị trí hiển thị cột. Ví dụ trong nguồn vị trí là 10 ta có thể hiển thị ở vị trí 4.
+ Width: kiểu Long, thiết lập độ rộng cột.

Ví dụ dưới đây cho phép:

+ Thay đổi màu nền cửa sổ tìm kiếm
+ Định dạng các cột
+ Hiển thị thêm menu "Thong tin tac gia" khi nhấp chuột phải.

Private WithEvents SE_DMHH As BSSearchEngine

Private Sub UserForm_Initialize()
    'Khoi tao doi tuong tim kiem
    Set SE_DMHH = New BSSearchEngine
    SE_DMHH.Create ComboBox1, Range("KHO")
    SE_DMHH.BoundColumn = 12 'Giá trị nhần về từ danh sách ở vị trí cột 12
    'DataSource: Array2D, Excel Range, Recordset
    SE_DMHH.RowColor1 = RGB(255, 248, 229)
    SE_DMHH.RowColor2 = "#FFE9AB"
    'Tạo mới menu khi nhấp chuột phải trong danh sách tìm kiếm.
    SE_DMHH.CreateMenuItem "Thong tin tac gia", , , 1000 'ID là 1000 hoặc gí trị bất kỳ dùng để nhận diện khi nhấp chuột.
    SE_DMHH.MenuCreateItem = True 'Hiển thị menu "Tạo mới".
    SE_DMHH.MenuViewItem = True 'Hiển menu "Xem/Sửa"
End Sub
'---------------------------------------------------------------------
'Sự kiện chạy lần lượt các cột để user thiết lập thông tin cột.
'Dựa vào ColumnInfo.index để biết sự kiện đang chạy cho cột nào.
Private Sub SE_DMHH_OnGetColumn(ByVal ColumnInfo As AddinATools.IBSColumnInfo)
    Debug.Print ColumnInfo.Caption, ColumnInfo.index
    If ColumnInfo.index = 2 Then
        ColumnInfo.FieldType = ftDateTime
        ColumnInfo.FormatStr = "dd-mm-yyyy hh:mm"
    ElseIf ColumnInfo.index = 9 Then
        ColumnInfo.FieldType = ftDouble
        ColumnInfo.FormatStr = "#,##0"
    ElseIf ColumnInfo.index = 11 Then
        ColumnInfo.Position = 2 'Cột gốc 11 xuất hiện vị trí thứ 2.
        ColumnInfo.Color = "#F4B084" ' màu cam đậm
    End If
End Sub
'---------------------------------------------------------------------
'Sự kiện chạy khi nhấp chuột vào menu chuột phải.
Private Sub SE_DMHH_OnMenuItemClick(ByVal index As Long, ByVal ID As Long, Cancel As Boolean)
    If ID = 1000 Then 'ID 1000 là ID tạo mới "Thong tin tac gia"
        MsgBox "Menu đã được kích hoạt.", vbInformation
    End If
End Sub
'---------------------------------------------------------------------
'Sự kiện chạy khi không tìm thấy dữ liệu
Private Sub SE_DMHH_OnNotFound(ByVal Text As String)
    MsgBox "Khong tim thay gia tri """ & Text & """", vbCritical
End Sub

Khi chạy Userform ta được kết quả như màn hình dưới đây

(Để ý cột vị trí 11 xuất hiện ở vị trí thứ 2, màu cam đậm; menu chuột phải thêm mục "Thong tin tac gia")​

Các thuộc tính khai báo cho phép ẩn các cột hoặc chỉ hiển thị các cột là:
+ BSSearchEngine.ListInvisibleCols : chuỗi liệt kê các vị trí cột được ẩn. Ví dụ "3,5,7" (cột vị trí 3,5,7 bị ẩn).
+ BSSearchEngine.ListVisibleCols: chuỗi liệt kê các vị trí cột được hiển thị.
+ BSSearchEngine.ListPicCols: chuỗi liệt kê các vị trí cột hiển thị ảnh (chạy trong tương lai).

Bài viết sau tôi sẽ hướng dẫn thiết lập chọn một hay nhiều dòng dữ liệu, chỉ định các cột lấy về...