Chuyển đổi số cột Excel thành chữ cái bằng VBA – 3 phương pháp đã chứng minh

Triển khai macro VBA là phương pháp hiệu quả, nhanh nhất và an toàn nhất để chạy bất kỳ thao tác nào trong Excel. Bài viết này sẽ chỉ cách chuyển đổi số cột thành chữ cái trong Excel bằng VBA.

Tải file thực hành

Bạn có thể tải file Excel thực hành miễn phí từ đây.

3 phương pháp triển khai VBA chuyển số cột thành chữ cái trong Excel

Phần này, bạn sẽ học cách chuyển một số cột cụ thể thành chữ cái, số cột do người dùng nhập thành chữ cái và tạo UDF (Hàm do người dùng định nghĩa) chuyển số cột thành chữ cái trong Excel bằng VBA.

1. VBA chuyển số cột cụ thể thành chữ cái trong Excel

Các bước chuyển một số cột cụ thể thành chữ cái trong Excel với VBA như sau.

Các bước:

• Nhấn Alt + F11 hoặc vào tab Developer -> Visual Basic mở Visual Basic Editor.

• Trong cửa sổ mã, từ thanh menu, nhấp Insert -> Module.

• Sao chép mã sau dán vào cửa sổ mã.

Sub ColNumToLetter() 
    Dim ColNumber As Long 
    Dim ColLetter As String 
    'Input Column Number 
    ColNumber = 200 
    'Convert To Column Letter 
    ColLetter = Split(Cells(1, ColNumber).Address, "$")(1) 
    'Display Result 
    MsgBox "Column " & ColNumber & " = Column " & ColLetter 
End Sub

Mã sẵn sàng chạy.

Mã này chuyển số cột 200 thành địa chỉ chữ cái tương ứng.

• Nhấn F5 hoặc từ menu Run -> Run Sub/UserForm. Hoặc nhấp biểu tượng Play nhỏ trên thanh sub-menu.

Hộp tin nhắn Excel hiển thị địa chỉ chữ cái (GR) của số cột (ví dụ 200) nhập trong mã.

Đọc thêm: VBA dùng Range dựa trên số cột trong Excel (4 phương pháp)

Bài đọc tương tự

• [Đã sửa] Excel hiển thị số cột thay vì chữ cái (2 giải pháp)
• Cách chuyển chữ cái cột thành biểu đồ số trong Excel (4 cách)

2. VBA chuyển số cột thành chữ cái với Hàm do người dùng định nghĩa (UDF) trong Excel

Các bước chuyển số cột thành chữ cái với UDF trong Excel với VBA như sau.

Các bước:

• Cùng cách trước, mở Visual Basic Editor từ tab Developer và Insert Module trong cửa sổ mã.
• Trong cửa sổ mã, sao chép mã sau dán vào.

Public Function NumToLtr(ColNum) 
    NumToLtr = Split(Cells(1, ColNum).Address, "$")(1) 
End Function

Đây không phải Sub Procedure để chạy VBA, đây là tạo Hàm do người dùng định nghĩa (UDF). Vì vậy, sau khi viết mã, thay vì nhấp Run từ menu, nhấp Save (Lưu).

• Quay lại worksheet quan tâm, viết hàm vừa tạo bằng VBA (Hàm NumToLtr ở dòng đầu mã) và trong ngoặc hàm NumToLtr, truyền số tham chiếu ô muốn chuyển thành chữ cái (trường hợp này truyền Ô B5).

Công thức cuối cùng:

=NumToLtr(B5)

• Nhấn Enter.

Bạn nhận được chữ cái tương ứng (A) của số cột (1) trong tập dữ liệu.

• Kéo hàng xuống bằng Fill Handle áp dụng UDF cho các ô còn lại chuyển thành chữ cái.

Với câu lệnh GoTo

Thủ tục trên cũng làm được với câu lệnh GoTo.

• Mã VBA chuyển số cột thành chữ cái với GoTo:

Public Function Num_Letter(ByVal iColNum As Integer) As String 
    Dim SLetter As String 
    On Error GoTo iError 
    Num_Letter = Left(Cells(1, iColNum).Address(False, False), _ 
    Len(Cells(1, iColNum).Address(False, False)) - 1) 
    Exit Function 
    iError: 
    Call MsgBox(Err.Number & " - " & Err.Description) 
End Function

• Như trên, gọi UDF trong tập dữ liệu, truyền số tham chiếu ô làm đối số, nhấn Enter và kéo hàng để chuyển số cột thành chữ cái trong Excel với GoTo trong VBA.

Với vòng lặp Do-While

Do-While Loop là cách khác chuyển số cột thành chữ cái trong VBA Excel.

• Mã VBA chuyển số cột thành chữ cái với Do-While Loop:

Function NumberToLetter(iCol As Long) As String 
    Dim a As Long 
    Dim b As Long 
    a = iCol 
    NumberToLetter = "" 
    Do While iCol > 0 
        a = Int((iCol - 1) / 26) 
        b = (iCol - 1) Mod 26 
        NumberToLetter = Chr(b + 65) & NumberToLetter 
        iCol = a 
    Loop 
End Function

• Tương tự, gọi UDF trong tập dữ liệu, truyền số tham chiếu ô làm đối số, nhấn Enter và kéo hàng để chuyển số cột thành chữ cái trong Excel với Do-While Loop trong VBA.

Đọc thêm: Excel VBA: Thiết lập Range theo hàng và số cột (3 ví dụ)

3. VBA thay đổi số cột thành chữ cái từ đầu vào người dùng trong Excel

Các bước thay đổi số cột thành chữ cái từ đầu vào người dùng trong Excel với VBA như sau.

Các bước:

• Cùng cách trước, mở Visual Basic Editor từ tab Developer và Insert Module trong cửa sổ mã.
• Trong cửa sổ mã, sao chép mã sau dán vào.

Sub GetNumberFromUser() 
    Dim ColNumber As Long 
    Dim ColLetter As String 
    'User Input: Column Number 
    ColNumber = InputBox("Please Enter a Column Number") 
    'Convert the user-entered Column Number To Column Letter 
    ColLetter = Split(Cells(1, ColNumber).Address, "$")(1) 
    'Display the Result 
    MsgBox "Column Number: " & ColNumber & _ 
    " = Column Letter: " & ColLetter 
End Sub

Mã sẵn sàng chạy.

• Run macro.
• Hộp tin nhắn Excel xuất hiện yêu cầu số cột. Nhập Số cột muốn chuyển, nhấn OK.

Bạn nhận kết quả chuyển đổi số cột (ví dụ 200) thành địa chỉ chữ cái (GR).

Đọc thêm: VBA dùng Range dựa trên số cột trong Excel (4 phương pháp)

Kết luận

Bài viết này chỉ cách chuyển số thành chữ cái trong Excel với VBA. Hy vọng rất hữu ích. Thoải mái hỏi nếu có thắc mắc về chủ đề.

Các bài viết liên quan

• Cách đổi tên cột từ ABC sang 1 2 3 trong Excel
• Cách chuyển chữ cái cột thành biểu đồ số trong Excel (4 cách)
• Cách tham chiếu ô theo hàng và số cột trong Excel (4 phương pháp)
• Excel VBA: Chọn nhiều cột (3 phương pháp)
• Cách trả về số cột khớp trong Excel (5 cách hữu ích)