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"

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 = "#.## $"

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"

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"

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

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

👍 Kết quả: Chạy hàm trên bạn sẽ lấy được 56 màu tương ứng với ColorIndex.

dinh-dang-excel-vba-170-2

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)

💡 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

dinh-dang-excel-vba-170-1

👍 Để 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

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

💡 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

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:

'màu Font
Sheets(1).Range("A1:H20").Font.ColorIndex = 3
'in đậm
Sheets(1).Range("A1:H20").Font.Bold = False
'in nghiêng
Sheets(1).Range("A1:H20").Font.Italic = False
'kích thước
Sheets(1).Range("A1:H20").Font.Size = 15
'tên font
Sheets(1).Range("A1:H20").Font.Name = "Open sans"

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.

' Cao của hàng
Sheets(1).Range("A1:D20").EntireRow.RowHeight = 30
' Rộng của cột
Sheets(1).Range("A1:D20").EntireColumn.ColumnWidth = 20

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

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).
'
Sheets(1).Range("A1:D20").HorizontalAlignment = xlCenter
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter
'ngang trái và dọc giữa
Sheets(1).Range("A1:D20").HorizontalAlignment = xlLeft
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter
'ngang lề phải và dọc giữa
Sheets(1).Range("A1:D20").HorizontalAlignment = xlRight
Sheets(1).Range("A1:D20").VerticalAlignment = xlCenter

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

Hoặc muốn hủy ngắt dòng:

Sheets(1).Range("A1:D20").WrapText = False

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

Hoặc hủy gộp Cells

Sheets(2).Range("K10:N11").MergeCells = False

💡 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 đó.


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.

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

BÌNH LUẬN

Please enter your comment!
Please enter your name here