How to add vba to my team from excel?

Question

Sub CapNhatCotI()

Dim wsTongHop As Worksheet

Dim wsWebChinh As Worksheet

Dim lastRowTongHop As Long

Dim lastRowWebChinh As Long

Dim i As Long

Dim dictI As Object

Dim key As String

Dim valueHTML As String



' Xác d?nh các sheet

Set wsTongHop = ThisWorkbook.Sheets("Tonghop")

Set wsWebChinh = ThisWorkbook.Sheets("Webchinh")

' Xác d?nh dòng cu?i cùng có d? li?u

lastRowTongHop = wsTongHop.Cells(wsTongHop.Rows.Count, 3).End(xlUp).Row

lastRowWebChinh = wsWebChinh.Cells(wsWebChinh.Rows.Count, 2).End(xlUp).Row

' Ð?t d?nh d?ng text cho c?t I

wsTongHop.Columns(9).NumberFormat = "@"

' Kh?i t?o Dictionary

Set dictI = CreateObject("Scripting.Dictionary")

' Luu d? li?u t? sheet Webchinh vào Dictionary

For i = 2 To lastRowWebChinh

    key = Trim(wsWebChinh.Cells(i, 2).Value) ' C?t B c?a Webchinh làm khóa

    ' Ki?m tra n?u key không r?ng

    If key <> "" And Not IsEmpty(key) Then

        valueHTML = wsWebChinh.Cells(i, 9).Value ' L?y d? li?u c?t I

        If Not IsError(valueHTML) And valueHTML <> "" Then ' Ki?m tra d? li?u h?p l?

            dictI.Add key, XoaTheHTML(valueHTML) ' Xóa HTML và d?nh d?ng n?i dung

        End If

    End If

Next i

' Ði?n d? li?u vào c?t I c?a Tonghop

For i = 2 To lastRowTongHop

    key = Trim(wsTongHop.Cells(i, 3).Value) ' L?y giá tr? t? c?t C c?a Tonghop

    ' Ki?m tra n?u key không r?ng

    If key <> "" And Not IsEmpty(key) Then

        If dictI.Exists(key) Then

            wsTongHop.Cells(i, 9).Value = dictI(key) ' Ði?n d? li?u dã x? lý

        Else

            wsTongHop.Cells(i, 9).Value = "Khong tim thay"

        End If

    Else

        wsTongHop.Cells(i, 9).Value = "Khong tim thay"

    End If

Next i

MsgBox "D? li?u dã du?c c?p nh?t vào c?t I c?a Tonghop!", vbInformation

End Sub

Function XoaTheHTML(ByVal htmlText As String) As String

Dim tmpText As String



' Ki?m tra n?u d? li?u h?p l?

If Not IsError(htmlText) And Not IsEmpty(htmlText) And htmlText <> "" Then

    tmpText = Replace(htmlText, "<h3>", vbLf) ' Xu?ng dòng dúng m?t l?n

    tmpText = Replace(tmpText, "</h3>", vbLf)

    tmpText = Replace(tmpText, "<p>", vbLf)

    tmpText = Replace(tmpText, "</p>", "")

    tmpText = Replace(tmpText, "<ul>", vbLf)

    tmpText = Replace(tmpText, "</ul>", "")

    tmpText = Replace(tmpText, "<li>", "- ")

    tmpText = Replace(tmpText, "</li>", vbLf)

    tmpText = Replace(tmpText, "<strong>", "")

    tmpText = Replace(tmpText, "</strong>", "")

    tmpText = Replace(tmpText, "<figure>", "")

    tmpText = Replace(tmpText, "</figure>", "")

    tmpText = Replace(tmpText, "<figcaption>", vbLf)

    tmpText = Replace(tmpText, "</figcaption>", vbLf)

    tmpText = Replace(tmpText, "<br>", vbLf)

    ' Lo?i b? kho?ng tr?ng du th?a và nhi?u l?n xu?ng dòng liên ti?p

    Do While InStr(tmpText, vbLf & vbLf) > 0

        tmpText = Replace(tmpText, vbLf & vbLf, vbLf)

    Loop

    

    tmpText = Trim(tmpText) ' Xóa kho?ng tr?ng th?a d?u/cu?i

Else

    tmpText = "Khong tim thay"

End If

' Tr? v? n?i dung dã du?c làm s?ch

XoaTheHTML = tmpText

End Function

Answer 1

Hi Khánh Hoà Nguyễn Lê

Thank you for posting your question in the Microsoft Q&A forum. 

Just to make sure I understand correctly: 

• Are you asking how to use Excel VBA when working together with your coworkers? 
• Or are you asking how to use Excel VBA while working together through Microsoft Teams? 

Knowing this will help me give you the most relevant and helpful guidance!