Hàm VBA dịch văn bản giữa các ngôn ngữ trong Excel

 Trong Googlesheet có hàm GOOGLETRANSLATE hỗ trợ người dùng dịch văn bản từ ngôn ngữ này sang ngôn ngữ khác. Tuy nhiên, trong Excel thì lại không có sẵn hàm dịch

Bài viết này chia sẻ code Function VBA cho phép dịch giữa các ngôn ngữ:

YÊU CẦU KHI DÙNG HÀM

Máy tính phải có kết nối mạng Internet

HƯỚNG DẪN ĐƯA HÀM TRANSLATE VÀO FILE EXCEL

Bước 1: Vào cửa sổ VBA

Mở file Excel và nhấn tổ hợp Alt+F11 (hoặc Alt+Fn+F11) để vào cửa sổ VBA

Bước 2: Chèn Module

Trong cửa sổ VBA vào thẻ Insert => Chọn Module

Bước 3: Paste đoạn code dưới đây vào nơi soạn thảo code trong Module

Function Translate(sText, FromLang, ToLang) Dim p1, p2, URL, resp Const DIV_RESULT$ = "<div class=""result-container"">" Const URL_TEMPLATE$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q=" URL = URL_TEMPLATE & WorksheetFunction.EncodeURL(sText) URL = Replace(URL, "[to]", FromLang) URL = Replace(URL, "[from]", ToLang) resp = WorksheetFunction.WebService(URL) p1 = InStr(resp, DIV_RESULT) If p1 Then p1 = p1 + Len(DIV_RESULT) p2 = InStr(p1, resp, "</div>") Translate = Mid$(resp, p1, p2 - p1) End If End Function

Bước 4: Trở lại cửa sổ Excel và nhập hàm TRANSLATE

Ví dụ: Ô A1 nhập chữ Hello. Thì công thức tại ô B1 là:

=TRANSLATE(A1,"En","Vi")

Bước 5: Save as file sang dạng cho phép lưu trữ Macro như: .xls (Excel 97-2003), .xlsm (Excel Enable Macro Workbook), .xlsb (Excel Binary Workbook)

MỘT SỐ KÝ HIỆU NGÔN NGỮ THÔNG DỤNG

"En" - Tiếng Anh

"Zh" - Tiếng Trung

"Vi" - Tiếng Việt

"Ja" - Tiếng Nhật

"Ko" - Tiếng Hàn

"Fr" - Tiếng Pháp

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Hàm VBA lấy thông tin từ Comment trong ô Excel

 Trong nhiều tình huống nhập liệu, người dùng đưa những số liệu, thông tin vào trong Comment để mục đích ghi chú, nhưng sau này lại có thể dùng chính dữ liệu đó để xử lý và tính toán. Do vậy cần lấy thông tin từ trong Comment ra thành dữ liệu ô Excel

Bài viết sau sẽ hướng dẫn sử dụng code VBA để lấy comment trong ô Excel

Bước 1: Nhấn tổ hợp phím Alt+F11 để vào cửa sổ VBA.

Trong cửa sổ VBA hiển thị thì vào Insert => Module

Bước 2: Copy đoạn code dưới đây vào cửa sổ VBA

Function GetComment(cl As Range) As String

    GetComment = cl.Comment.Text

End Function

Bước 3: Trở lại bảng tính Excel và dùng hàm GetComment như hàm Excel thông thường

Và khi đó có thể lồng ghép thêm hàm khác của Excel đi cùng

Ví dụ: =MID(GetComment(A1),FIND(":",GetComment(A1))+1,99)

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hàm VBA tính tổng theo màu trong Excel

 Trong một số trường hợp người dùng nhập liệu và tô màu theo ô và muốn thống kê tính tổng theo màu sắc.

Bài viết dưới đây sẽ chia sẻ về hàm VBA tính tổng theo điều kiện màu sắc như sau:

Bước 1: Mở file Excel có số liệu cần tính tổng theo màu sắc và nhấn Alt+F11 để vào môi trường VBA. Tại cửa sổ môi trường VBA thì vào menu Insert=> Module

Bước 2: Copy đoạn code dưới đây vào cửa sổ của Module

Function SumifColor(rng As Range, vc As Range)

    Dim cl As Range, ct As Double

    ct = 0

    For Each cl In rng

        If cl.Interior.ColorIndex = vc.Interior.ColorIndex Then

            If IsNumeric(cl.Value) Then ct = ct + cl.Value

        End If

    Next

    SumifColor = ct

End Function

Bước 3: Trở lại cửa sổ bảng tính excel và gõ hàm SumifColor và truyền vào 2 tham số. 

- Tham số đầu tiên là vùng cần tính tổng theo màu

- Tham số thứ 2 là ô có chứa màu điều kiện

Ví dụ: Từ A1:A100 là vùng cần tính tổng, D1 là ô màu điều kiện thì công thức là

=SumifColor(A1:A100,D1)

Bước 4: Save As lại file excel sang dạng Excel enable macro workbook (xlsm) hoặc Excel binary (xlsb) hoặc Excel 97-2003 (xls) để lưu trữ macro cho file

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hàm VBA đếm theo màu trong Excel

 Trong một số trường hợp người dùng nhập liệu và tô màu theo ô và muốn thống kê đếm theo màu sắc.

Bài viết dưới đây sẽ chia sẻ về hàm VBA đếm theo điều kiện màu sắc như sau:

Bước 1: Mở file Excel có số liệu cần đếm theo màu sắc và nhấn Alt+F11 để vào môi trường VBA. Tại cửa sổ môi trường VBA thì vào menu Insert=> Module

Bước 2: Copy đoạn code dưới đây vào cửa sổ của Module

Function CountifColor(rng As Range, vc As Range)

    Dim cl As Range, ct As Long

    ct = 0

    For Each cl In rng

        If cl.Interior.ColorIndex = vc.Interior.ColorIndex Then ct = ct + 1

    Next

    CountifColor = ct

End Function

Bước 3: Trở lại cửa sổ bảng tính excel và gõ hàm CountifColor và truyền vào 2 tham số. 

- Tham số đầu tiên là vùng cần đếm theo màu

- Tham số thứ 2 là ô có chứa màu điều kiện cần đến

Ví dụ: Từ A1:A100 là vùng cần đếm, D1 là ô màu điều kiện thì công thức là

=CountifColor(A1:A100,D1)

Bước 4: Save As lại file excel sang dạng Excel enable macro workbook (xlsm) hoặc Excel binary (xlsb) hoặc Excel 97-2003 (xls) để lưu trữ macro cho file

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hàm VBA bỏ dấu Tiếng Việt trong Excel

 Trong xử lý dữ liệu, nhiều trường hợp cần loại bỏ dấu để phục vụ các công việc khác như: Loại bỏ dấu trong họ tên để tạo Email, import vào các cơ sở dữ liệu yêu cầu không có dấu,...Để chuyển dữ liệu Tiếng Việt về không dấu thì thường dùng Unikey, EVkey, một số Add-in hoặc VBA. 

Bài viết dưới đây sẽ chia sẻ hàm VBA để loại bỏ dấu Tiếng Việt trong Excel như sau:

Bước 1: Mở file Excel nhấn Alt+F11 (hoặc Alt+Fn+F11) để vào môi trường VBA. Trong giao diện VBA, vào Menu Insert chọn Module

Bước 2: Copy đoạn code dưới đây vào cửa sổ soạn thảo Module

Function BoDau(text As String) As String Dim CoDauThuong, CoDauHoa, KhongDau, BoDau1 As String, Char As String, Code As Long, i As Long, n As Long CoDauThuong = Array(225, 224, 7843, 227, 7841, 259, 7855, 7857, 7859, 7861, 7863, 226, 7845, 7847, 7849, 7851, 7853, 273, 233, 232, 7867, 7869, 7865, _ 234, 7871, 7873, 7875, 7877, 7879, 237, 236, 7881, 297, 7883, 243, 242, 7887, 245, 7885, 244, 7889, 7891, 7893, 7895, 7897, 417, _ 7899, 7901, 7903, 7905, 7907, 250, 249, 7911, 361, 7909, 432, 7913, 7915, 7917, 7919, 7921, 253, 7923, 7927, 7929, 7925) CoDauHoa = Array(193, 192, 7842, 195, 7840, 258, 7854, 7856, 7858, 7860, 7862, 194, 7844, 7846, 7848, 7850, 7852, 272, 201, 200, 7866, 7868, 7864, _ 202, 7870, 7872, 7874, 7876, 7878, 205, 204, 7880, 296, 7882, 211, 210, 7886, 213, 7884, 212, 7888, 7890, 7892, 7894, 7896, 416, _ 7898, 900, 7902, 7904, 7906, 218, 217, 7910, 360, 7908, 431, 7912, 7914, 7916, 7918, 7920, 221, 7922, 7926, 7928, 7924) KhongDau = Array(97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 97, 100, 101, 101, 101, 101, 101, _ 101, 101, 101, 101, 101, 101, 105, 105, 105, 105, 105, 111, 111, 111, 111, 111, 111, 111, 111, 111, 111, 111, 111, _ 111, 111, 111, 111, 111, 117, 117, 117, 117, 117, 117, 117, 117, 117, 117, 117, 121, 121, 121, 121, 121) BoDau1 = "" For i = 1 To Len(text) Char = Mid(text, i, 1) Code = AscW(Char) If Code <= 122 Then BoDau1 = BoDau1 & Char Else: For n = 0 To 66 If Code = CoDauThuong(n) Then BoDau1 = BoDau1 & ChrW(KhongDau(n)) ElseIf Code = CoDauHoa(n) Then BoDau1 = BoDau1 & ChrW(KhongDau(n) - 32) Exit For End If Next If Len(BoDau1) < i Then BoDau1 = BoDau1 & Char End If End If Next BoDau = BoDau1 End Function

Bước 3: Trở lại giao diện Excel và gõ hàm. Ví dụ: =BoDau(A1) thì kết quả trả về là dữ liệu có dấu tại ô A1 sẽ thành không dấu.

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hàm VBA lấy link liên kết từ Hyperlink trong ô Excel

 Tính năng Hyperlink được người dùng khai thác nhất nhiều để thực hiện tạo liên kết tới 1 file khác, sheet khác, ô khác,...và sau đó người dùng có thể phát sinh mong muốn lấy đường link đó ra từ liên kết đã tạo.

Bài viết sau sẽ hướng dẫn cách dùng VBA để bổ sung thêm hàm lấy liên kết từ Hyperlink đã tạo trong ô Excel

Bước 1: Mở file Excel lên và nhấn tổ hơp phím Alt+F11 (Alt+Fn+F11) để vào môi trường VBA=> Vào menu Insert chọn Module

Bước 2: Copy đoạn code dưới đây vào cửa sổ Module

Function Getlink(Cl As Range) As String

    Getlink = Cl.Hyperlinks(1).Address

End Function

Bước 3: Trở lại màn hình Excel, ở 1 ô nào đó =Getlink(ô cần lấy link). Ví dụ: =Getlink(A1)

Vì Getlink là 1 hàm tạo ra từ VBA nên người dùng có thể kết hợp với các hàm khác của Excel để cắt ghép chuỗi, tạo lại liên kết, ... như: LEFT, RIGHT, MID, FIND, HYPERLINK,...

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết