Đô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
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
Đô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
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
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ự.
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.
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!
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.
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.
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 )
Ý 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
Ý 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
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
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
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
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.
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 ạ?
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):
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:
Ở đâ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ả:
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)
. Nếu chỉ để đạt được mục đích thì cách gì ngắn gọn hiệu quả thì ta dùng. 
