vba - 我在 Excel 中制作语言翻译 VBA 案例时遇到问题

Question

我试图制作 Vba 案例来为充满房屋项目的文档列表制作自动语言翻译代码，但有些行有多个项目，我需要 vba 在同一个单元格中单独翻译每个项目，我找到的解决方案是写每一种可能性(在翻译中顺序无关紧要)这是我使用的行:

Sub Traduccione()
Select Case activecell.Offset.Value
    Case "Cadeiras"
    Selection.Value = "Chairs"
Case "Cadeira"
Selection.Value = "Chair"
Case "Criado mudo", "Criado-mudo"
Selection.Value = "Night stand"
Case "Mesa"
Selection.Value = "Table"
Case "Mesas", "mesas"
Selection.Value = "Tables"
Case "Mesa de canto"
Selection.Value = "End table"
Case "Mesinha"
Selection.Value = "Small table"
Case "Cabeceira", "cabeceira"
Selection.Value = "Headboard"
Case "Cabeceiras", "cabeceiras" 
'the following lines are an example of my struggle:
Case "Mochila, documentos e roupas", "Mochila, roupas e documentos", "Documentos, mochilas e roupas", "Documentos, roupas e mochilas", "Roupas, mochilas e documentos", "Roupas, documentos e mochilas"
Selection.Value = "Bags, documents and clothes"

Case "Travesseiro, bolsas, sapatos e roupas", "Travesseiro, bolsas, roupas e sapatos", "Travesseiro, sapatos, bolsas e roupas", "Travesseiro, sapatos, roupas e bolsas", "Travesseiro, roupas, bolsas e sapatos", "Travesseiro, roupas, sapatos e bolsas", "Bolsas, travesseiro, sapatos e roupas", "Bolsas, travesseiro, roupas e sapatos", "Bolsas, sapatos, travesseiro e roupas", "Bolsas, sapatos, roupas e travesseiro", "Bolsas, roupas, travesseiro e sapatos", "Bolsas, roupas, sapatos e travesseiro", "Sapatos, travesseiro e bolsas, roupas", "Sapatos, travesseiro, roupas e bolsas", "Sapatos, bolsas, travesseiro e roupas", "Sapatos, bolsas, roupas e travesseiro", "Sapatos, roupas, travesseiro e bolsas", "Sapatos, roupas, bolsas e travesseiro", "Roupas, travesseiro, bolsas e sapatos", "Roupas, travesseiro, sapatos e bolsas", "Roupas, bolsas, travesseiro e sapatos", "Roupas, bolsas, sapatos e travesseiro", "roupas, sapatos, travesseiro e bolsas", "Roupas, sapatos, bolsas e travesseiro"
Selection.Value = "Pillow, bags, shoes and clothes"
       End Select

End Sub

此列表包含 1000 多个项目，这只是让您聪明的头脑理解的示例。

我想知道是否有更好的方法来做到这一点，因为我找不到更好的解决方案，我认为应该有更好的方法来做到这一点，但我就是找不到，如果有人有类似的问题或知道如何让这项工作更容易，你能分享一下吗？你会让我的生活更轻松。

我是这里和编码方面的新手，所以如果我犯了一个奇怪的错误，请耐心等待:b

感谢各位阅读。

Answer 1

这是一个使用字典对象的示例，字符串 Replace功能。这不会尝试翻译任何不在字典中的单词。

Sub foo()

Dim translate As Object 'Scritping.Dictionary

Set translate = CreateObject("Scripting.Dictionary")

' Define your translation terms
' here I use lower-case for everything, assuming that case-sensitivity does not matter
    translate("cadeira") = "chair"
    translate("cadeiras") = "chairs"
    translate("criado mudo") = "night stand"
    translate("criado-mudo") = "night stand"
    translate("mesa") = "table"
    translate("mesas") = "tables"

    ' etc...
    ' Add more translation items as needed

Dim spWords As String
Dim enWords As String

spWords = LCase(ActiveCell.Value)

For Each spWord In translate.Keys()
    If InStr(spWords, spWord) Then
        enWords = Replace(Replace(spWords, spWord, translate(spWord), InStr(spWords, spWord)), " e ", "and")
        ActiveCell.Offset(0, 1).Value = enWords

    End If
Next
End Sub