Cách định Dạng Dữ Liệu Bảng Tính Excel Bằng VBA

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.