Lập trình VBA với Column: chuyển chỉ số cột thành chữ cái

Liên hệ QC

levanduyet

Hãy để gió cuốn đi.
Thành viên danh dự
Tham gia
30/5/06
Bài viết
1,798
Được thích
4,705
Giới tính
Nam
Đôi khi trong lập trình VBA các bạn cần chuyển số cột sang chữ. Hàm sau sẽ giúp các bạn:

Mã:
Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function
Sưu tầm

Lê Văn Duyệt
 
Em xin gửi thêm một cách, gọn và dùng được cho cả OF2007.
PHP:
Function StrCol(ColNum As Integer) As String
StrCol = Mid(Cells(1, ColNum).Address, 2, InStr(2, Cells(1, ColNum).Address, "$") - 2)
End Function
 
Upvote 0
Đóng góp thêm gia vị cho bữa tiệc

Mã:
Function StrCol(ColNum As Integer) As String
 Dim Loai0 As Integer
 Loai0 = ColNum Mod 26:              If Loai0 = 0 Then Loai0 = 26
 StrCol = Choose((ColNum - 1) \ 26 + 1, "", "A", "b", "C", "D", "E", "F", "G", "H", "i") _
      & Chr(64 + Loai0)
End Function
 
Lần chỉnh sửa cuối:
Upvote 0
Đôi khi trong lập trình VBA các bạn cần chuyển số cột sang chữ. Hàm sau sẽ giúp các bạn:

Mã:
Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function
Sưu tầm

Lê Văn Duyệt

Chạy không đúng trong Excel 2007 anh ạ!
 
Upvote 0
Mã:
Function StrCol(ColNum As Integer) As String
 Dim Loai0 As Integer
 Loai0 = ColNum Mod 26:              If Loai0 = 0 Then Loai0 = 26
 StrCol = Choose((ColNum - 1) \ 26 + 1, "", "A", "b", "C", "D", "E", "F", "G", "H", "i") _
      & Chr(64 + Loai0)
End Function

Code của Bác chỉ chạy được với ColNum <= 260
 
Upvote 0
Chủ đề này đã từng nói ít nhất 2 lần trên diển đàn rồi
Giãi pháp dùng công thức:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
Và giãi pháp VBA:
PHP:
Function Coleter(Clls As Range) As String
    Coleter = Replace(Cells(1, Clls.Column).Address(0, 0), 1, "")
End Function
Còn nếu thích kiểu khai báo biến như của Lê Văn Duyệt thì càng dể
PHP:
Function Coleter(ColIndex As Long) As String
    Coleter = Replace(Cells(1, ColIndex).Address(0, 0), 1, "")
End Function
Chắc ăn như bắp---> Chính xác luôn cho Excel2007 với 16384 cột (XFD)
 
Lần chỉnh sửa cuối:
Upvote 0
Chủ đề này đã từng nói ít nhất 2 lần trên diển đàn rồi
Giãi pháp dùng công thức:
Và giãi pháp VBA:
PHP:
Function Coleter(Clls As Range) As String
    Coleter = Replace(Cells(1, Clls.Column).Address(0, 0), 1, "")
End Function
Còn nếu thích kiểu khai báo biến như của Lê Văn Duyệt thì càng dể
PHP:
Function Coleter(ColIndex As Long) As String
    Coleter = Replace(Cells(1, ColIndex).Address(0, 0), 1, "")
End Function
Chắc ăn như bắp---> Chính xác luôn cho Excel2007 với 16384 cột (XFD)


Tuy nhiên vấn đề lấy tên cột làm gì?

Vì nếu đã viết trong VBA thì chúng ta xử lý theo số hay hơn, và tiện lợi hơn ký tự nhiều, đó cũng lý do trong VBA các hàm các phép xử lý liên quan nhận dạng cột đều thể hiện ở số thứ tự.
 
Upvote 0
Cám ơn các bạn đã đưa ra các giải pháp của mình.

Code tôi sưu tầm thì chắc chắn không dùng trong Excel 2007 được, cách của Bác SA cũng thế.

Cách của HoangDanh282vn, ndu96081631 thì dùng được cho Excel 2007. Chúng ta còn cách nào nữa không?

LVD
 
Lần chỉnh sửa cuối:
Upvote 0
Thực ra để làm ra được mục đích như hàm của anh Duyệt mà dùng hàm kết hợp Address của Excel thì quá dễ vì bản thân nó đã tự ra địa chỉ rồi mà :). Nếu chỉ để đạt được mục đích thì cách gì ngắn gọn hiệu quả thì ta dùng.

Em quan tâm cách anh Duyệt đã giới thiệu là không dùng hàm hỗ trợ của Excel, thể hiện thuật giải. Vẫn theo hướng này nếu viết được cho 2007 thì sẽ hay hơn nữa.
 
Lần chỉnh sửa cuối:
Upvote 0
Thực ra để làm ra được mục đích như hàm của anh Duyệt mà dùng hàm kết hợp Address của Excel thì quá dễ vì bản thân nó đã tự ra địa chỉ rồi mà :). Nếu chỉ để đạt được mục đích thì cách gì ngắn gọn hiệu quả thì ta dùng.

Em quan tâm cách anh Duyệt đã giới thiệu là không dùng hàm hỗ trợ của Excel, thể hiện thuật giải. Vẫn theo hướng này nếu viết được cho 2007 thì sẽ hay hơn nữa.
Mình chưa hiểu ý bạn lắm (ngay chổ màu đỏ)...
Mình ngầm hiểu là bạn đang nói đến các hàm trong bảng tính (tức dùng WorkSheetFunction)... nhưng các giãi pháp trình bày từ bài 1 đến cuối mình đâu thấy chổ nào dùng hàm của Excel nhỉ
Tuân có thể nói rõ hơn 1 chút được không!
Cãm ơn!
 
Lần chỉnh sửa cuối:
Upvote 0
Mình chưa hiểu ý bạn lắm (ngay chổ màu đỏ)...
Mình ngầm hiểu là bạn đang nói đến các hàm trong bảng tính (tức dùng WorkSheetFunction)... nhưng các giãi pháp trình bày từ bài 1 đến cuối mình đâu thấy chổ nào dùng hàm của Excel nhỉ
Tuân có thể nói rõ hơn 1 chút được không!
Cãm ơn!

Tức là không dùng tới Range.Address(), Cell.Address() anh ạ, vì Address() hỗ trợ để lấy địa chỉ rồi. Em hiểu ý của tác giả viết hàm ở bai 1 là thể hiện thuật toán. Vấn đề tiếp theo là thuật giải như thế nào để dùng cho cả Excel 2007 với tên cột tối cuối cùng là XFD.

=ColumnLetter(16384) = "XFD"
 
Lần chỉnh sửa cuối:
Upvote 0
Tức là không dùng tới Range.Address(), Cell.Address() anh ạ, vì Address() hỗ trợ để lấy địa chỉ rồi. Em hiểu ý của tác giả viết hàm ở bai 1 là thể hiện thuật toán. Vấn đề tiếp theo là thuật giải như thế nào để dùng cho cả Excel 2007 với tên cột tối cuối cùng là XFD.

=ColumnLetter(16384) = "XFD"
Ai chà... nếu dùng kiểu Convert số thành chử cho giống với Column letter thì.. khó đấy! Với Excel2007, tên cột là 3 ký tự lại càng khó!
Tuy nhiên thuật toán này cũng đáng để lưu ý lắm đây (dùng vào việc khác chứ không phải lấy tên cột)
Nếu vậy ta sẽ ra đề tài: Lấy tên cột tại 1 cell nào đó nhưng bắt buộc không được dùng Address
Thử xem nhé!
(Mời các cao thủ ra tay )
 
Upvote 0
Ý bác Tuân là muốn dùng chữ cái thay cho số thứ tự đây mà, bác tham khảo đoạn code này xem sao
Mã:
Function Num2Text(i As Double) As String
    Dim strRet As String
    Do While i > 0
        strRet = Chr((i Mod 26) + Asc("A") - 1) & strRet
        i = i \ 26
    Loop
    Num2Text = strRet
End Function
 
Upvote 0
Ý bác Tuân là muốn dùng chữ cái thay cho số thứ tự đây mà, bác tham khảo đoạn code này xem sao
Mã:
Function Num2Text(i As Double) As String
    Dim strRet As String
    Do While i > 0
        strRet = Chr((i Mod 26) + Asc("A") - 1) & strRet
        i = i \ 26
    Loop
    Num2Text = strRet
End Function

Phải nói đây cũng là một thuật giải rất hay! Cảm ơn rollover79! Các bác còn có cách nào nữa không?
 
Upvote 0
Vẫn cùng ý tưởng, nhưng em thử chơi đệ qui cái xem sao nhé, lâu lắm rồi ko đụng đến đệ qui.
Mã:
Function Num2Text(i As Double) As String
    If i <= 26 Then
        Num2Text = Chr(i + Asc("A") - 1)
    Else
        Num2Text = Num2Text(i \ 26) & Chr((i Mod 26) + Asc("A") - 1)
    End If
End Function
 
Upvote 0
Kết quả không đúng khi i=26*n
Num2Text(26) > A@
Num2Text(52) > B@
Lỗi tinh vi mà người bắt lỗi còn tinh vi hơn, hình như bác làm nghề tester thì phải, test lỗi này ngôn ngữ chuyên ngành có phải là test biên không bác?
Em xin sửa lại code cho cả 2 phương pháp vòng lặp và đệ qui như sau:
1. Vòng lặp
Mã:
Function Num2Text(i As Double) As String
    Dim strRet As String
    Do While i > 0
        strRet = Chr(((i - 1) Mod 26) + Asc("A")) & strRet
        i = (i - 1) \ 26
    Loop
    Num2Text = strRet
End Function
2. Đệ qui
Mã:
Function Num2Text(i As Double) As String
    If i <= 26 Then
        Num2Text = Chr(i + Asc("A") - 1)
    Else
        Num2Text = Num2Text((i - 1) \ 26) & Chr(((i - 1) Mod 26) + Asc("A"))
    End If
End Function
 
Upvote 0
Lỗi tinh vi mà người bắt lỗi còn tinh vi hơn, hình như bác làm nghề tester thì phải, test lỗi này ngôn ngữ chuyên ngành có phải là test biên không bác?
Vì mình cũng bị lỗi nay không khắc phục được. Trong bài 11 vừa đưa bài lên thì phát hiện lỗi này nên xóa code (nhưng không xóa được bài nên đành để lại tên hàm !).
Bài các bạn đưa lên thì tôi test lỗi này trước.
 
Upvote 0
Vì mình cũng bị lỗi nay không khắc phục được. Trong bài 11 vừa đưa bài lên thì phát hiện lỗi này nên xóa code (nhưng không xóa được bài nên đành để lại tên hàm !).
Bài các bạn đưa lên thì tôi test lỗi này trước.
À ra thế, vậy sao bác không để code rồi comment thêm trường hợp bị lỗi để mọi người cùng tham khảo có hay hơn không? Bài này thì em đã từng làm rồi, nhưng là làm trên Pascal, vậy code sửa lần này không biết có còn vấn đề gì nữa không ạ?
 
Upvote 0
Tôi cũng có 1 cách theo thuật toán hoàn toàn khác: "Chuyển đổi ColIndex từ hệ đếm cơ số 10 sang hệ đếm cơ số 26"
Ai học toán chắc không lạ về thuật toán chuyển đổi này (khỏi cần nhắc)
Tôi xin trình bày sơ lược toàn bộ quá trình tính toán:
1> Xác định ký tự thứ nhất (từ phải qua trái):
Ch3 = Chr(((ColIndex - 1) Mod 26) + 65)
2> Xác định ký tự thứ hai (từ phải qua trái):
Ch2 = Chr(((Int((ColIndex - 1) / 26) - 1) Mod 26) + 65)
3> Xác định ký tự thứ ba (từ phải qua trái):
Ch1 = Chr(((Int((Int((ColIndex - 1) / 26) - 1) / 26) - 1) Mod 26) + 65)
4> Xác định số lượng ký tự tồn tại thật sự:
Ta dùng logarithm cơ số 26 với số ColIndex để xác định số lượng ký tự... Do trong VBA không có Log cơ số 26 nên ta tùy biến bằng công thức:
Temp = Log(ColIndex - (ColIndex - 1) / 26) / Log(26)
Qty = Int(Temp) + 1
Ở đây tôi hình dung chắc chắn sẽ phải dùng logarithm nhưng lại không biết giãi thích thế nào cho các bạn khác hiểu (mong các cao thủ giõi sư phạm giãi thích thêm phần này)
5> Cuối cùng là tính kết quả:
ColLetter = Right(Ch1 & Ch2 & Ch3, Qty)
6> Toàn bộ code:
PHP:
Function ColLetter(ColIndex As Long) As String
  Dim Ch1 As String, Ch2 As String, Ch3 As String
  Dim Qty As Long, Temp As Double
  Ch3 = Chr(((ColIndex - 1) Mod 26) + 65)
  Ch2 = Chr(((Int((ColIndex - 1) / 26) - 1) Mod 26) + 65)
  Ch1 = Chr(((Int((Int((ColIndex - 1) / 26) - 1) / 26) - 1) Mod 26) + 65)
  Temp = Log(ColIndex - (ColIndex - 1) / 26) / Log(26)
  Qty = Int(Temp) + 1
  ColLetter = Right(Ch1 & Ch2 & Ch3, Qty)
End Function
Với cách tính toán trên, cho dù mai này bác Bill có phát triển bảng tính lên đến cở 1 triệu cột thì vẩn tính ra được như thường (Nếu như bác vẩn giữ cách đánh tên cột theo thứ tự như hiện nay)
Bài toán này không chỉ dùng để lấy Column Letter mà còn có thể ứng dụng vào nhiều việc khác (ví dụ đánh MÃ tự động) tùy theo khả năng phát triển của mổi người
Ghi chú: Nếu các bạn cãm thấy dùng hàm Log quá phức tạp thì có thể IF, chẳng hạn:
PHP:
 If ColIndex < 27 then
      Qty = 1
  ElseIf:
    ColIndex < 703 then
     Qty = 2
  Else:
     Qty = 3
 End If
(Tuy nhiên tôi chả bao giờ khoái mấy vụ IF này, trừ những trường hợp bất khả kháng)
Các bạn xem file đính kèm và góp ý thêm nhé (File được xây dựng trên Excel2007 để có thể test với chỉ số cột > 256)
 

File đính kèm

  • ColLetter.rar
    11.7 KB · Đọc: 50
Lần chỉnh sửa cuối:
Upvote 0
Web KT

Bài viết mới nhất

Back
Top Bottom