Bạn sẽ định dạng dữ liệu trước hay sử dụng VBA để định dạng dữ liệu? Sẽ có 2 trường hợp, bảng tính đó cố định thì định dạng trước sẽ tối ưu hơn nhưng nếu bạn tạo nhiều bảng tính như nhau bằng VBA thì định dạng bằng VBA sẽ tối ưu hơn.
Excel vốn có nhiều định dạng có sẵn và cả định dạng tùy chỉnh, với VBA bạn cũng có thể định dạng tương tự. Và trong bài viết này, mình sẽ chia sẻ với bạn cách tạo các định dạng thường dùng bằng các câu lệnh VBA và ứng dụng vào bài toán tìm các giá trị theo màu nền Cells (mục 3).
Cách định dạng dữ liệu bảng tính Excel bằng VBA
⇒ Tham khảo bài viết tổng hợp định dạng tùy chỉnh để nắm các mã định dạng được sử dụng trong bài viết này.
1 Định dạng số
VBA hỗ trợ định dạng kiểu số tương tự như các kiểu định dạng số có sẵn và định dạng tùy chỉnh. Khi gắn một mã định dạng kiểu số thì Excel sẽ tự động định dạng kiểu Custom.
Ví dụ Định dạng số cho Cells
Sheets(1).Range("A1:A1").NumberFormat = "#.##"
Sheets(1).Range("B1:B1").NumberFormat = "0.00"
Code language: JavaScript (javascript)Ví dụ Định dạng số cho Range
Sheets(1).Range("A:A").NumberFormat = "#.##"
Sheets(1).Range("B1:H20").NumberFormat = "#%"
Sheets(1).Range("G:G").NumberFormat = "#.## $"
Code language: JavaScript (javascript)2 Định dạng thời gian
Cũng có chung thuộc tính với định dạng số nhưng định dạng thời gian sử dụng các ký tự riêng cho các thành phần như: ngày/ tháng/ năm và giờ/ phút/ giây.
2.1 Ngày tháng năm
Định dạng sử dụng các ký tự: y: năm | m: tháng | d: ngày.
Ví dụ
Sheets(1).Range("A:A").NumberFormat = "dd-mm-yyyy"
Code language: JavaScript (javascript)2.2 Giờ phút giây
Định dạng sử dụng các ký tự: h: giờ | m: phút | s: giây.
💡 m sẽ định dạng là phút khi đi với h và là tháng khi đi với y.
Ví dụ
Sheets(1).Range("A:A").NumberFormat = "hh:mm:ss"
Sheets(1).Range("B:B").NumberFormat = "hh:mm AM/PM"
Code language: JavaScript (javascript)3 Định dạng màu nền Cells
Trong VBA bạn có 2 cách để định dạng màu nền cho Cells.
Cách 1: Sử dụng mã màu Index.
Sheets(1).Range("A1:H10").Interior.ColorIndex = 3
Code language: JavaScript (javascript)Bảng mã màu INDEX bạn có thể tham khảo thêm tại đây hoặc chạy hàm VBA sau để lấy ColorIndex.
Sub indexcolor()
Dim i, j As Integer
Dim index As Integer
index = 1
For i = 1 To 7
For j = 1 To 8
Sheets(2).Cells(i, j).Value = index
Sheets(2).Cells(i, j).Interior.ColorIndex = index
index = index + 1
Next j
Next i
End Sub
Code language: PHP (php)👍 Kết quả: Chạy hàm trên bạn sẽ lấy được 56 màu tương ứng với ColorIndex.
Cách 2: Sử dụng 3 mã màu riêng là Red, Green và Blue. Mỗi màu sẽ có giá trị từ 0 →255.
Sheets(1).Range("A1:A10").Interior.Color = RGB(19, 40, 197)
Code language: JavaScript (javascript)💡 Bạn nên dùng cách 1, vì nó đơn giản và 56 màu cũng sẽ đủ để đáp ứng nếu bạn muốn định dạng màu cho Cells.
Ví dụ Tô màu đỏ số nếu giá trị = 11
👍 Để thực hiện ví dụ trên, bạn có thể tham khảo hàm sau.
Sub timso()
Dim i As Integer
Dim j As Integer
For i = 1 To 10
For j = 1 To 10
If Sheets(2).Cells(i, j).Value2 = 11 Then
Sheets(2).Cells(i, j).Interior.ColorIndex = 3
End If
Next j
Next i
End Sub
Code language: PHP (php)Ví dụ Đếm Cells có màu nền ColorIndex =3
Tiếp tục với ví dụ ở trên, bạn có thể lấy được màu nền của Cells và từ đó đếm được có bao nhiều Cells có ColorIndex =3.
Sub laymaunen()
Dim i As Integer
Dim j As Integer
Dim dem As Integer
For i = 1 To 10
For j = 1 To 10
If Sheets(2).Cells(i, j).Interior.ColorIndex = 3 Then
dem = dem + 1
End If
Next j
Next i
MsgBox "Co " & dem & " Cells co ColorIndex = 3"
End Sub
Code language: PHP (php)💡 Và từ kiểu định dạng này bạn có thể làm các ứng dụng tính tổng dựa theo màu nền của Cells.
4 Đường viền (Borders)
Excel có nhiều kiểu Border, nhưng khi sử dụng VBA để đơn giản nhất, bạn hãy Border toàn bộ viền của Cell bằng lệnh sau.
Sheets(1).Range("A:A").Borders.LineStyle = xlHairline
Code language: JavaScript (javascript)5 Định dạng Font
Định dạng Font có nhiều thuộc tính liên quan như tên Font, màu, in đậm, in nghiêng, kích cỡ. Và trong VBA bạn có thể định dạng Font với các thuộc tính như sau:
1-Màu Font
Sheets(1).Range("A1:H20").Font.ColorIndex = 3
Code language: JavaScript (javascript)2-In đậm
Sheets(1).Range("A1:H20").Font.Bold = False
Code language: PHP (php)3-In nghiêng
Sheets(1).Range("A1:H20").Font.Italic = False
Code language: PHP (php)4-Kích thước
Sheets(1).Range("A1:H20").Font.Size = 15
Code language: JavaScript (javascript)5-Tên font
Sheets(1).Range("A1:H20").Font.Name = "Open sans"
Code language: JavaScript (javascript)6 Cao của hàng và rộng của cột
Tùy thuộc vào khổ chữ và văn bản mà bạn sẽ điều chỉnh lại các tham số rộng của cột và cao của hàng cho phù hợp.
1-Cao của hàng
Sheets(1).Range("A1:D20").EntireRow.RowHeight = 30
Code language: JavaScript (javascript)2-Rộng của cột
Sheets(1).Range("A1:D20").EntireColumn.ColumnWidth = 20
Code language: JavaScript (javascript)7 Tự động làm cho vừa
Định dạng này sẽ tự động điều chỉnh cho chiều rộng của Cells cho phù hợp với văn bản.
Sheets(1).Range("A:A").EntireColumn.AutoFit
Code language: CSS (css)8 Căn chỉnh lề
Excel có 2 chiều căn chỉnh cho Cells, đó là căn chỉnh theo chiều dọc và theo chiều ngang tương ứng:
- HorizontalAlignment (ngang) bao gồm: xlCenter ( giữa), xlLeft (trái), xlRight (phải).
- VerticalAlignment (dọc) bao gồm: xlTop (trên), xlCenter (giữa), xlBottom (dưới).
1-Ngang giữa và dọc giữa
Sheets(1).Range("A1:D20").HorizontalAlignment = xlCenter
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter
Code language: JavaScript (javascript)2-Ngang trái và dọc giữa
Sheets(1).Range("A1:D20").HorizontalAlignment = xlLeft
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter
Code language: JavaScript (javascript)3-Ngang lề phải và dọc giữa
Sheets(1).Range("A1:D20").HorizontalAlignment = xlRight
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter
Code language: JavaScript (javascript)9 Ngắt dòng trong Cells
Định dạng ngắt dòng nếu văn bản dài hơn chiều rộng của Cells. Nếu bạn muốn hiển thị hết văn bản trong Cells kèm thuộc tính ngắt dòng thì bạn có thể sử dụng thêm thuộc tính Autofit vào sau.
Sheets(1).Range("A1:D20").WrapText = True
Sheets(1).Range("A1:D20").EntireColumn.AutoFit
Code language: PHP (php)Hoặc muốn hủy ngắt dòng:
Sheets(1).Range("A1:D20").WrapText = False
Code language: PHP (php)10 Gộp nhiều Cells thành một
Gộp nhiều Cells thành một là thuộc tính MergeCells. Và bạn sẽ sử dụng câu lệnh sau để cho phép MergeCells.
Sheets(2).Range("K10:N11").MergeCells = True
Code language: PHP (php)Hoặc hủy gộp Cells
Sheets(2).Range("K10:N11").MergeCells = False
Code language: PHP (php)💡 Ngoài ra, Excel còn rất nhiều định dạng mà mình không thể trình bày hết trong 1 bài viết được. Vậy, nếu bạn cần thêm định dạng thì bạn hãy sử dụng Record Macro để lấy các định dạng đó.
Kết luận
OK! Trên là 10 định dạng phổ biến được thể hiện dưới dạng các câu lệnh VBA. Qua đây bạn cũng có thể thấy, các dữ liệu trong Excel đều có thể sử dụng VBA định dạng được. Vì vậy, nếu kết hợp lại bạn có thể lập trình để tạo những FORM Excel mẫu tự động.
Hy vọng bài chia sẻ này hữu ích với những gì bạn đạng tìm kiếm.
