Hàm WITH-END WITH và các ví dụ FILE DIALOG trong VBA

Khi sử dụng VBA để định dạng dữ liệu cho nhiều nhóm Range trong Excel, bạn sẽ thấy các đối tượng Sheet và Range sẽ lặp lại với mỗi thuộc tính. Điều này sẽ làm cho hàm VBA của bạn thêm phần phức tạp và khó hiểu hơn.

Thay vào đó, nếu sử dụng hàm WITH – END WITH thì bạn sẽ nhóm được các thuộc tính vào chung một đối tượng. Vì thế, các khai báo sẽ gọn, dễ nhìn hơn rất nhiều. Và trong bài này, mình sẽ chia sẻ với bạn cách sử dụng hàm WITD – END WITH và ứng dụng vào khai báo FILE DIALOG để duyệt FILE và FOLDER bằng VBA.

Hàm WITH-END WITH và các ví dụ FILE DIALOG trong VBA

Hàm WITH – END WITH trong VBA

Cú pháp:

With objectExpression 
[ statements ] 
End With
Code language: VB.NET (vbnet)

Trong đó:

• objectExpression: là biểu thứ của đối tượng.
• statements: là các thuộc tính của đến đối tượng.
• End With: Kết thúc định nghĩa đối tượng trong With

Ví dụ: Để dễ hiểu hơn về cú pháp hàm With, bạn tham khảo 1 ví dụ định dạng FONT cho Range(“A1:H10”).

Set rg= Sheets(1).Range("A1:H10").Select
With rg.Font
.Name = "Roboto"
.Size = 14
.Bold=True
.Italic = False
End With
Code language: PHP (php)

• objectExpression: đối tượng là rg.Font
• statements: là các thuộc tính liên quan như Name (tên FONT), Size (khổ chữ), Bold (in đậm), Italic (in nghiêng). Các thuộc tính này sẽ lấy giá trị theo mặc định của Excel nếu bạn không khai báo trong hàm WITH.

Một số khai báo thường dùng với hàm WITH-END WITH

Hàm WITH-END WITH được sử dụng nhiều khi sử dụng VBA để định dạng dữ liệu, vì nó giúp nhóm được các thuộc tính của Cells hay Range, giúp các dòng lệnh dễ nhìn và dễ hiểu hơn.

Một cách đơn giản sử dụng hàm WITH-END WITH để nhóm thuộc tính cho định dạng là bạn sử dụng Record Macro để tạo nó.

Ngoài ra, một khai báo thường dùng WITH-END WITH mà bạn có thể sử dụng là khai báo cho hộp thoại FILEDIALOG.

FILEDIALOG cung cấp một hộp thoại tập tin tương tự như hộp thoại khi Open hay Lưu File trong các ứng dụng của Microsoft Office. Và dưới đây là một số cách khai báo FILEDIALOG để mở FOLDER và duyệt/ lọc File.

👉 Tham khảo chi tiết hơn về FILEDIALOG tại đây và đây.

Hộp thoại Dialog lấy đường dẫn Folder

Dim filedlg As FileDialog
Dim thumuc As String
Set dialog = Application.FileDialog(msoFileDialogFolderPicker)
With dialog
.Title = "Chon mot thu muc"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show = True Then
thumuc = .SelectedItems(1)
End If
End With
Set filedlg = Nothing
Code language: PHP (php)

Một số tùy chỉnh trong hàm trên:

• AllowMultiSelect: = True cho phép chọn nhiều FOLDER, =FALSE chỉ chọn được 1 FOLDER.
• InitialFileName: = Application.DefaultFilePath lấy đường dẫn mặc định của FILE hoặc sử dụng đích thư mục như “D:\”.

Hộp thoại Dialog lọc file theo định dạng

Dim filedlg As FileDialog
Set filedlg = Application.FileDialog(msoFileDialogFilePicker)
With filedlg 
.AllowMultiSelect = False
.InitialFileName = Application.ActiveWorkbook.Path & "\"
.Filters.Clear
.Filters.Add "Excel", "*.xls?"
If .Show = True Then
SelectedFile = .SelectedItems(1)
End If
End With
Set filedlg = Nothing
Code language: PHP (php)

Một số tùy chỉnh trong hàm trên:

• AllowMultiSelect: = True cho phép chọn nhiều FILE, =FALSE chỉ chọn được 1 FILE.
• InitialFileName: = Application.ActiveWorkbook.Path & “\” lấy đường dẫn hiện tại của FIlE.
• Filters.Add: thêm lọc theo định dạng. Có thể thêm nhiều lọc định dạng khác nhau.

Ví dụ lấy đường dẫn File Excel và Word được chọn vào Excel.

Trong ví dụ này, bạn cần mở hộp thoại FileDialog để lọc FILE (Excel: .xls?, Word: .docx, hoặc All Files) ⇒ chọn File ⇒ ghi đường dẫn vào Excel. Bạn có thể xây dựng một hàm như sau:

Public Sub luuduongdanfile()
Dim filedlg As FileDialog
Set filedlg = Application.FileDialog(msoFileDialogFilePicker)
With filedlg
.AllowMultiSelect = False
.InitialFileName = "D:\OneDrive\9.MMO\Post" & "\"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.Filters.Add "Excel", "*.xls?"
.Filters.Add "Word", "*.doc?"
If .Show = True Then
SelectedFile = .SelectedItems(1)
Sheets(1).Cells(Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1, 1).Value = SelectedFile
End If
End With
Set filedlg = Nothing
End Sub
Code language: PHP (php)

👍 Kết quả: Khi chạy hàm, bạn sẽ lọc được các file Word và Excel. Và mỗi khi chọn 1 File thì sẽ tự động ghi đường dẫn của File đó vào Excel.

Ví dụ lấy tất cả đường dẫn của File nằm trong một thư mục.

Ở ví dụ này, bạn sẽ cần sử dụng FileDialog để chọn thư mục ⇒ chọn thư mục xong, thực hiện truyền đường dẫn thư mục đó  tới một hàm cho chức năng lấy danh sách các file và ghi vào Excel. Bạn có thể thực hiện 2 hàm như sau:

“Hàm 1” mở FileDialog ⇒ chọn thư mục ⇒ truyền đường dẫn thư mục đến “Hàm 2”.

Public Sub layduongdanfolder()
Dim i As Integer
Dim demsofile As Long
Dim filedlg As FileDialog
Dim thumuc As String
Set Dialog = Application.FileDialog(msoFileDialogFolderPicker)
With Dialog
.Title = "Chon mot thu muc"
.AllowMultiSelect = False
.InitialFileName = "D:\"
If .Show = True Then
ListFilesFSO (.SelectedItems(1))
End If
End With
Set filedlg = Nothing
End Sub
Code language: PHP (php)

“Hàm 2” từ đường dẫn có được từ hàm layduongdanfolder(), thực hiện quét lấy đường dẫn từng File và ghi vào Excel.

Public Sub ListFilesFSO(ByVal sPath As String)
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
For Each oFile In oFolder.Files
Sheets(2).Cells(Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1, 1).Value = oFile
Next
Set oFile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
End Sub
Code language: JavaScript (javascript)

Cách sử dụng: Chạy hàm layduongdanfolder() ⇒ chọn đường dẫn ⇒ OK ⇒ kết quả.

👍 Kết quả: Bạn sẽ lấy được dành sách toàn bộ các File trong thư mục đã chọn (không bao gồm các thư mục con bên trong).

---

OK! Trên là cách sử dụng hàm WITH – END WITH và cách ứng dụng trong một số trường hợp cụ thể trong Excel. Hy vọng những thông tin trên hữu ích với bạn.

Chúc bạn một ngày làm việc vui vẻ!