VBA với siêu liên kết (Hyperlink) trong Excel

(Thủ thuật VBA Excel)

- Phần mềm Office đã trở nên quá quen thuộc với người dùng, với nhiều lĩnh vực từ: giáo dục, tài chính, ngân hàng, CNTT, xây dựng, kinh doanh… Mặc dù mỗi lĩnh vực có những đặc thù khác nhau nhưng Office vẫn có thể đáp ứng tất cả các yêu cầu của người dùng. Đặc biệt đối với Excel, nó được sử dụng vào những công việc từ đơn giản đến phức tạp  như: tính toán lương, bảng giá, kế toán, dự toán xây dựng. Với Excel người sử dụng không những có thể sử dụng những hàm có sẵn do Microsoft viết mà tự mình viết ra các hàm để xử lý, giải quyết những công việc, bài toán thực tế.

Bài viết này sẽ giới thiệu với mọi người cách tận dụng sức mạnh của VBA khi làm việc với các siêu liên kết (Hyperlink).

1. Xóa toàn bộ các siêu liên kết các ô được chọn

Sao chép đoạn mã  phía dưới vào Workbook, sau đó chọn các ô cần xóa liên kết và chạy Macro này.

Sub RemoveHyperlinks()
     Selection.Hyperlinks.Delete
End Sub

2. Xóa toàn bộ các siêu liên kết trong Sheet hiện tại

Nếu bạn muốn loại bỏ tất cả các siêu liên kết trên Sheet hiện tại , sử dụng các Macro sau đây

Sub RemoveHyperlinksOnActiveSheet()
Cells.Hyperlinks.Delete
End Sub

3. Lấy ra địa chỉ của siêu liên kết điền vào ô liền kề bên phải

Bạn có thể sử dụng một Macro để trích xuất địa chỉ từ mỗi siêu liên kết trên một bảng tính, và lưu  nó vào ô liền kề bên phải.

Sub ExtractHyperlink()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
    Next
End Sub

4. Trích xuất các URL hoặc địa chỉ email từ một siêu liên kết
Nếu bạn có một siêu liên kết, để biết được chính xác URL đó là gì ta phải để chuột vào siêu liên kết đó hoặc kích chuột phải chọn Edit Hyperlink. Nhưng nếu bạn có một danh sách các siêu liên kết thì ta không thể thực hiện thủ công được vì sẽ mất thời gian, vậy có hàm nào trong Excel để làm được việc này không?
Hiện trong Excel không có hàm để trích xuất các URL hoặc địa chỉ email từ một siêu liên kết. Bạn có thể tạo ra hàm riêng để trích xuất địa chỉ.

Sao chép đoạn mã phía dưới vào trong bảng tính của bạn, sau đó sử dụng hàm GetURL trong bảng tính, giống như bất kỳ các hàm khác.

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

Ví dụ, để tìm URL cho một siêu liên kết trong ô B10, sử dụng công thức này:
= GetURL (B10)

5. Chuyển đổi văn bản URL để liên kết có thể click
Giả sử bạn có nhiều URL trong bảng tính của bạn, nhưng các URL không có liên kết, và bây giờ bạn cần phải chuyển đổi tất cả các url đến các siêu liên kết để có thể click, Bạn có thể nhấp đúp chuột vào từng cái một để chuyển đổi, nhưng điều này sẽ tốn thời gian nếu có rất nhiều các URL. Làm thế nào bạn có thể chuyển đổi nhiều URL đến các siêu liên kết để có thể click trong Excel?

Nếu bạn muốn loại thực hiện việc này , sử dụng các Macro sau đây

Sub AddHyperlinks()
    Dim Cell As Range
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

Sau khi đã có Macro bên trên, quay trở lại màn hình làm việc Excel, bôi đen toàn bộ các danh sách cần chuyển đổi và chạy Macro.

Tin học văn phòng

VBA với ký tự số trong Excel

(Thủ thuật VBA trong Excel)

-Trong Excel khi nhập những giá trị số và ấn Enter, Excel sẽ tự động căn chỉnh các giá trị này sang bên phải của ô. Đối với những giá trị số khi nhập xong, Excel sẽ tự động loại bỏ số 0 đằng trước các dãy số, ví dụ như số điện thoại chẳng hạn. Để Excel không loại bỏ số 0 đằng trước, người sử dụng thường thêm dấu ‘ trước khi đánh dãy số. Đối với những dãy số như vậy người ta thường gọi là ký tự số. Hoặc khi ta nhập các số xen lẫn giữa các ký tự thì đây cũng được coi là các ký tự số, hoặc các số được ngăn cách bởi các ký tự đặc biệt.

Trong công việc thường ngày ta sẽ phải gặp một số tình huống phải thao tác hoặc xử lý với các ký tự số, nếu như sử dụng các hàm có sẵn trong Excel có thể sẽ không thể giải quyết triệt để được mong muốn của chúng ta. Hôm nay Kênh phần mềm việt xin giới thiệu với mọi người một vài trường hợp sử dụng VBA để thao tác với ký tự số trong Excel.

1. Đếm số chữ số trong chuỗi ký tự số

Giả sử bạn có 1 chuỗi các số như sau: 17ABC6D8H432

Bây giờ bạn muốn đếm xem trong chuỗi  này có bao nhiêu số, để làm được việc này bạn sử dụng hàm VBA dưới đây.

Function CountNumber(str As String)
    Dim mlen As Long
    Dim i As Long
    Dim iCount As Long
    'Neu chuoi =0 thi khong xu ly
    If Len(str) = 0 Then Exit Function
    'Xoa bo cac ky tu trang o dau va cuoi
    str = Trim(str)
    'Dem so ky tu chuoi
    mlen = Len(str)
    iCount = 0
    For i = 1 To mlen
        If IsNumeric(Mid(str, i, 1)) Then
            iCount = iCount + 1
        End If
    Next
    CountNumber = iCount
End Function

2. Tìm giá trị lớn nhất trong chuỗi ký tự số

Giả sử bạn có 1 chuỗi các số như sau: 12,45,66,7888,34,56,39,17,98,25

Bây giờ bạn muốn tìm số lớn nhất trong chuỗi các số này, để làm được việc này bạn sử dụng hàm VBA dưới đây.

Function HighestNumber(R As Range)
    Dim x As Variant, M As Double, i As Long, ct As Long
    Set R = R.CellS(1, 1)
    x = Split(R.Value, ",")
    For i = LBound(x) To UBound(x)
        If IsNumeric(x(i)) Then
            ct = ct + 1
            If x(i) > M Then M = x(i)
        End If
    Next i
    If ct = 0 Then
        HighestNumber = CVErr(xlErrNA)
    Else
        HighestNumber = M
    End If
End Function

Nếu các ký tự số trong chuỗi được ngăn cách bởi dấu cách 12 45 66 7888 34 56 39 7 98 25
Thì đoạn Code  x = Split(R.Value, ",") được thay thế bằng  x = Split(R.Value, " ")

Tin học văn phòng

Tự xây dựng hàm làm việc với màu trong Excel

(Thủ thuật VBA trong Excel)

- Để tính tổng,  trung bình, đếm số lượng theo một điều kiện ta thường sử dụng các hàm như: SUMIF, AVERAGEIF, COUTIF. Tuy nhiên những hàm này được sử dụng để tính toán, thống kê theo điều kiện là các giá trị như: số, ký tự, ngày tháng…  Trong thực tế công việc có những trường hợp ta phải tính toán, thống kê không theo giá trị mà theo định dạng  như: màu chữ, màu nền ô. Ví dụ như bạn muốn tính tổng các ô trong một vùng có màu chữ là đỏ, hay tính trung bình của những ô có màu nền là vàng. Với những trường hợp này  trong Excel hiện chưa có hàm nào để thực hiện công việc này. Vậy phải tính toán thủ công nếu gặp phải trường hợp như này sao? 

Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người cách tự viết hàm để có thể tính tổng, tính trung bình, đếm số lượng theo màu chữ, màu nền ô. Dưới đây là các bước thực hiện.

1. Khởi động Microsoft Excel.

2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.

Vào Menu Inser\Module

3. Nhập toàn bộ đoạn mã phía dưới sau vào màn hình bên phải

'Ham dem so luong theo mau nen chu
 Function CountCellsByBackColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    cntRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.ColorIndex Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
    CountCellsByBackColor = cntRes
End Function

 'Ham tinh tong gia tri theo mau nen chu
Function SumCellsByBackColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
    sumRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.ColorIndex Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
    SumCellsByBackColor = sumRes
End Function

'Ham tinh trung binh gia tri theo mau nen chu
Function AverageCellsByBackColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
    Dim i As Long
    sumRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.ColorIndex Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
            i = i + 1
        End If
    Next cellCurrent
    AverageCellsByBackColor = sumRes / i
End Function

'Ham dem so luong theo mau  chu
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    cntRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.ColorIndex Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
    CountCellsByFontColor = cntRes
End Function

'Ham tinh tong gia tri theo mau chu
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
    sumRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.ColorIndex Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
    SumCellsByFontColor = sumRes
End Function

'Ham tinh trung binh gia tri theo mau chu
Function AverageCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
     Dim i As Long
    sumRes = 0
    indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.ColorIndex Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
            i = i + 1
        End If
    Next cellCurrent
    AverageCellsByFontColor = sumRes / i
End Function

4. Quay trở lại màn hình Excel và sử dụng các hàm vừa tạo
a. Thao tác với màu chữ
- Đếm số lượng
Cú pháp: = CountCellsByFontColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByFontColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
- Tính tổng
Cú pháp: = SumCellsByFontColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng)
- Tính trung bình
Cú pháp: = AverageCellsByFontColor (Vùng cần tính TB, Vùng điều kiện cần tính TB)
b. Thao tác với màu nền
- Đếm số lượng
Cú pháp: = CountCellsByBackColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByBackColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
- Tính tổng
Cú pháp: = SumCellsByBackColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng)
- Tính trung bình
Cú pháp: = AverageCellsByBackColor (Vùng cần tính TB, Vùng điều kiện cần tính TB)

Tin học văn phòng

Tự động chuẩn hóa chuỗi khi Enter trong Excel

(Thủ thuật VBA trong Excel)

- Trong bài viết “Hàm chuẩn hóa chuỗi trong Excel”, Kênh phần mềm việt đã giới thiệu với mọi người cách viết hàm để chuẩn hóa chuỗi trong Excel. Hàm này sẽ rất có ích đối với người dùng khi Copy dữ liệu từ một nguồn khác và dữ liệu này chưa được chuẩn hóa theo mong muốn của người sử dụng. Đó là trường hợp Copy dữ liệu từ một nguồn khác, còn việc tự động chuẩn hóa ký tự nhập vào mỗi khi ấn Enter hoặc chuyển ô?. Việc tự động chuẩn hóa này sẽ giúp người sử dụng  giúp ngắn được thời gian nhập liệu khi phải làm việc với các danh sách học sinh, học viên, nhân sự, khách hàng….  khi không còn phải quan tâm đến việc đánh ký tự chữ hoa ở đầu mỗi từ hoặc bị thừa các khoảng trắng.

Hôm nay Kênh phần mềm việt sẽ giới thiệu với các bạn cách sử dụng hàm chuẩn hóa chuỗi để tự động hóa trong quá trình nhập liệu. Hàm này sẽ thực hiện các công việc: xóa bỏ các ký tự trống ở đầu và cuối, Xóa bỏ từ 2 ký tự trống liên tiếp ở giữa các từ, Viết hoa chữ cái đầu tiên tại mỗi từ mỗi khi người sử dụng ấn Enter hoặc chuyển ô. Nếu muốn kiểm soát việc tự động này theo những vùng hoặc những cột mong muốn, bạn hoàn toàn có thể thực hiện được.

1. Khởi động Microsoft Excel.
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
Vào Menu Inser\Module

3. Nhập toàn bộ đoạn mã sau vào

Function Chuanhoachuoi(str As String) As String
    Dim sChuoi As String
    Dim mlen As Long
    Dim i As Long
    'Neu chuoi =0 thi khong xu ly
    If Len(str) = 0 Then Exit Function
    'Xoa bo cac ky tu trang o dau va cuoi
    str = Trim(str)
    'Dem so ky tu chuoi
    mlen = Len(str)

    'Loai bo hai ki tu trong lien tiep
    For i = 1 To mlen
        If Mid(str, i, 1) = " " And Mid(str, i + 1, 1) = " " Then
            str = Replace(str, "  ", " ")
            i = i - 1
        End If
    Next

     For i = 1 To mlen
        ' Chuyen cac ky tu dau tien mot tu sang chu hoa
        If Mid(str, i, 1) = " " Then
            sChuoi = sChuoi & " " & UCase(Mid(str, i + 1, 1))
            i = i + 1
        Else
            'Chuyen chu cai dau tien cua cau sang chu hoa
            If i = 1 Then
                sChuoi = UCase(Mid(str, 1, 1))
            Else
                sChuoi = sChuoi & LCase(Mid(str, i, 1))
            End If
        End If
     Next
    Chuanhoachuoi = sChuoi
End Function

Kích nút Save để lưu lại, bạn có thể xem thêm bài viết cách lưu file Excel khi chứa Macro hoặc hàm.

4. Kích đúp vào tên Sheet mà bạn muốn thực hiện công việc tự động chuẩn hóa

- Tại mục (General) chọn WorkSheet
- Tại mục (Declarations) chọn Change

Nhập đoạn Code phía dưới vào màn hình soạn thảo

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim str As String
     If Not (Intersect(Target, Range("$F:$F")) Is Nothing) Then
str = Chuanhoachuoi(Target.Value)
Target = str
     End If
End Sub

Trong đó:
- Range("$F:$F"): Chỉ cho phép tự động chuẩn hóa tại cột F
- Range("$F:$G"): Cho phép tự động chuẩn hóa từ cột F đến cột G
- Range("$F:$F,$I:$J"): Cho phép tự động chuẩn hóa cột F và cột J
- Range("E8:E9,E11:E14"): Cho phép tự động chuẩn hóa từ ô E8 đến E9 và từ ô E11 đến ô E14

Bây giờ mỗi khi người sử dụng nhập các chuỗi ký tự ở những vùng cho phép tại Sheet vừa nhập đoạn Code trên, Excel sẽ tự động chuẩn hóa chuỗi ký tự đã nhập vào.

Tin học văn phòng

Sao chép thiết lập Page Setup sang Sheet khác trong Excel 2007

(Sao chép thiết lập Page Setup trong Excel)

- Các công việc thiết lập thông số trong mục Page Setup như: cỡ giấy, lề trái, lề phải, hướng giấy, Header trên, Header dưới không phải là việc làm khó. Tuy nhiên nếu như ta phải thiết lập chúng ở nhiều Sheet khác nhau thì ngoài việc mất thời gian, nó còn tạo sự nhàm chán cho người sử dụng.

Vậy có cách nào chỉ cần thiết lập chuẩn các thông số trong mục Page Setup ở một Sheet rồi sao chép các thông số này áp dụng cho 1 hoặc nhiều Sheet mong muốn không? Giống như chức năng sao chép định dạng Format Painter.

Hôm nay Kênh phần mềm việt sẽ hướng dẫn mọi người cách sao chép các thiết lập Page Setup từ một Sheet sang các Sheet khác.

- B1: Thiết lập thông số Page Setup chuẩn cho 1 Sheet

- B2: Giữ phím Ctrl, nhấp vào tên các Sheet sao chép thiết lập, để chọn toàn bộ các Sheet  trên File, kích chuột phải Select All Sheets.

- B3: Chọn Tab Page Layout\Margins\OK

Như vậy các thiết lập Page Setup của Sheet nguồn sẽ được sao chép cho tất cả các Sheet đã được chọn.

Chú ý: Nếu như bạn chỉnh sửa lại các thông số trong mục Page Setup tại bất kỳ một Sheet, bao gồm cả Sheet nguồn lẫn Sheet được sao chép thì các thông số này sẽ được tự động thay đổi tại tất cả các Sheet.

Tin học văn phòng