Using Hebrew in a Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I need to be able to write hebrew letters to an excel spreadsheet from my
macro. I already have hebrew support installed on Windows XP.
What is the simplest way to do this?
I noticed I can't type in hebrew withen the Macro - is there a way to hard
code any text?

Thank you
 
Whilst you cannot type Hebrew (or rather any non-ANSI text) in the VBE, you
can work with its Unicode numbers, either Hex or decimal and the ChrW
function.
If you are bring this Hebrew text into VBA from outside (a text file or some
other app) look into using Byte arrays and StrConv.

Dim Unicodes As Variant
Dim i As Long

Const SomeHebrewHexCodes As String = "05D0,05E0,05D2,05E6"

For Each Unicodes In Split(SomeHebrewHexCodes, ",")
Range("A1").Offset(i, 0).Value = ChrW("&H" & Unicodes)
i = i + 1
Next

You can get the code for any letter on the worksheet with:

Public Function GetUnicodeValue(argText As Variant, _
WhichLetter As Long, _
Optional AsDecimal As Boolean = True) As
Variant

GetUnicodeValue = AscW(Mid(argText, WhichLetter, 1))
If AsDecimal = False Then GetUnicodeValue = Hex(GetUnicodeValue)

End Function

NickHK
 
I found the solution on another site. I quote " it's a Unicode display problem. If a user wants to type Arabic, French, and English in a non-Unicode Arabic application. The user should choose one of the Arabic system locales."

And the solution for Windows 7 and similar for other Windows is:
1. Go to Control Panel > "Clock, Region and Language" > "Region and Language"
2. Select Administrative tab > "Change system locale..." button and then select "Hebrew (Israel)".
 
benalt said:
I found the solution on another site. I quote " it's a Unicode display problem. If a user wants to type Arabic, French, and English in a non-Unicode Arabic application. The user should choose one of the Arabic system locales."

And the solution for Windows 7 and similar for other Windows is:
1. Go to Control Panel > "Clock, Region and Language" > "Region and Language"
2. Select Administrative tab > "Change system locale..." button and then select "Hebrew (Israel)".
Click to expand...
With that solution, does it change your entire system to Hebrew, or keep it in English?
 
YMSeatt said:
With that solution, does it change your entire system to Hebrew, or keep it in English?
Click to expand...

I'm not sure, as it was a long time ago that I messed around with that and never ended up doing that. When I want easy access to Hebrew in Excel, I wrote a macro to handle it where I can just put in how the Hebrew word sounds in English and it produces the Hebrew letters. I can then copy and paste by value to use whereever I want or keep it as is (see below). I had to make some decisions like making a=aleph and A=Ayin. Or Q for Kuf and K for Kof, with c for Tzadi (cadi). If you want to use it and prefer a different association you can mess with it yourself:

' Pass in a transliterated word and output the Hebrew word in Unicode
Function HebWord(word As String) As String
Dim letter(65 To 122) As Integer
Dim hWord As String, hebLetter As String, midWord As String

letter(97) = 1488 ' a = aleph
letter(101) = 1488 ' e = aleph
letter(98) = 1489 ' b = beis, veis
letter(103) = 1490 ' g = gimel
letter(100) = 1491 ' d = daled
letter(104) = 1492 ' h = hei
letter(118) = 1493 ' v = vov
letter(111) = 1493 ' o = vov (oh)
letter(117) = 1493 ' u = vov (ooh)
letter(122) = 1494 ' z = zayin
letter(72) = 1495 ' H = hes
letter(116) = 1496 ' t = tes
letter(121) = 1497 ' y = yud
letter(75) = 1498 ' K = kof sofit
letter(107) = 1499 ' k = kof
letter(108) = 1500 ' l = lamed
letter(77) = 1501 ' M = mem sofit
letter(109) = 1502 ' m = mem
letter(78) = 1503 ' N = nun sofit
letter(110) = 1504 ' n = nun
letter(115) = 1505 ' s = samech
letter(65) = 1506 ' A = ayin
letter(80) = 1507 ' P = pei sofit
letter(112) = 1508 ' p = pei
letter(67) = 1509 ' C = cadi sofit
letter(99) = 1510 ' c = cadi
letter(113) = 1511 ' q = kuf
letter(114) = 1512 ' r = reish
letter(83) = 1513 ' S = shin/sin
letter(84) = 1514 ' T = tof/sof

For char = 1 To Len(word)
midWord = Mid(word, char, 1)

Select Case Asc(midWord)
Case 65 To 90, 97 To 122
hebLetter = letter(Asc(midWord))
If hebLetter <> 0 Then hWord = hWord + ChrW(hebLetter)
Case Else
hWord = hWord + midWord
End Select
Next char

HebWord = hWord
End Function

I actually have this in a XLAM file which Excel refers to automatically (by going to File-->Options-->Add-ins-->Excel Add-ins, activate the XLAM add-in you created.)

I noticed just now that File > Options > Language indicates that you can add an additional (secondary) language to Excel. The first is the language Excel will use to display everything, and it could be that the additional language allows for other languages to be displayed after that. It says "Office display language. Buttons, menus, and other controls will show in the first available language on this list."
 
Back
Top