Google translate hiccup

[arishy]

Background:
I have several financial forms in Arabic and I need to translate the Financial labels to English.
Google Translate was not specific enough; so I decided to use my own dictionary.
VBA has this powerful Dictionary Object.

The problem(s):
1. VBA Editor does not like Arabic characters ( I changed Editor Format in options to Courier New (Arabic))
2. So, I decided to use a worksheet range to create the Dictionary. Excel sheet is happy with Arabic Characters

Public Sub created()
With CreateObject("scripting.dictionary")
.Add "range", Sheet1.Range("A4:B12")
MsgBox .Item("range").Rows.Count
End With
End Sub

3. I cannot debug or use MsgBox ...I got ??? when I try to access the key content ( Arabic characters)
That was problem one

Problem Two:
How do I use a function in the worksheet to key in Arabic Key to get the translated English word
The function will work with an active range of Arabic words

Your help will be greatly appreciated; especially with problem TWO

 

[Chihiro]

VBA IDE can't display Arabic characters. It's limitation of the IDE.

I.E. No Debug.Print or MsgBox.

Although, if you want to translate Arabic to English. I'd imagine that you would have Arabic as key and English as Item.

Ex: Where B2:B4 hold Arabic and C2:C4 hold corresponding English translation.

جبنه​	Cheese​
جرذ​	Rat​
كلب​	Dog​

Sub Demo()
Dim rng As Range
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
For Each cel In Range("B2:B4")
    dic(cel.Text) = cel.Offset(, 1).Text
Next
Debug.Print dic([T1].Text)
End Sub

Copy and paste one of Arabic word into Cell T1 and run code. It will return corresponding English word.

Though, instead of using VBA. You can simply use formula.

 

[arishy]

Great response.
While waiting for a response, I did google research and got the solution for the IDE limitation. I need to add the Arabic Language to my keyboard.
Once w10 has it, I can input Arabic in the Editor AND use Debug.print to output Arabic. As for MsgBox there are a few solutions bit complex, The reason is MsgBox is a windows function. One clever solution is to build your own MsgBox in a form.
As for your second remark, I used the following VBA

'https://www.codegrepper.com/code-examples/vb/excel+vba+translate+text
' ok 100%
Function ARtranslate$(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]", ToLang)
    url = Replace(url, "[from]", FromLang)
    resp = WorksheetFunction.WebService(url)
    p1 = InStr(resp, DIV_RESULT)
    If p1 Then
        p1 = p1 + Len(DIV_RESULT)
        p2 = InStr(p1, resp, "</div>")
        ARtranslate = Mid$(resp, p1, p2 - p1)
    End If
End Function

BUT my problem, as stated in the title; was I needed a specialized Financial Dictionary to get accurate results.
That is why I thought VBA Dictionary is A solution because of the ease of maintaining it.

I will defintly look deeply to your solution, and report back ( very soon).

Since your are familliar with dictionary object ( Thanks God for that); I came accross strange problem:
If I use mydict.add in VBA to populate the dictionary I can use mydict(key).
BUT if I use excel range ( the easier way) to populate my dictionary as stated in the code above I get Nothing and if I do myDict.Exists I get FALSE
I tried dict.CompareMode equasl vbBinaryCompare

I do not think Arabic is the issue( because I used none Arabic Dictionary), It is a suttle bug I can not trace it. Can you help?

 

[arishy]

You are GENIUS ( The most difficult design is a simple design)
You solved ALL my problems in a few lines.
Can you please prove me wrong in my last remark regarding dic.add and using range

 

[Chihiro]

Dictionary can hold in it's item any object, array, data etc.

Key must be data type (i.e. String, number etc).

So by adding range object (i.e. more than 1 cell) as item. You will end up needing additional process to read content of the range. Defeating purpose of this dictionary object.

To keep it simple, you'd always want Key, Value pair and use iteration to fill dictionary.

You can speed up things a bit (if dictionary is going to be large), by using variant array. Load data from range to array, then iterate over it to fill dictionary. On my machine, it's about 3x faster than reading cell content directly.

Ex: 100k rows takes about 0.12 sec on my machine.

Sub Demo()
Dim ar
Dim dic As Object
Dim sTime As Single, eTime As Single
sTime = Timer
ar = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
Set dic = CreateObject("Scripting.dictionary")

For i = 1 To UBound(ar)
    dic(ar(i, 1)) = ar(i, 2)
Next
eTime = Timer
Debug.Print eTime - sTime & " dic has " & dic.Count & " items"
End Sub

For more on dictionary object, link below is a good resource.
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

 

[arishy]

Thank you again for sharing your findings.
Your point is load and clear.
I am heading now to "The Complete Guide" ....Wish me luck
Best regards