CODEWITHSUNDEEP
excelvba
gordon613
gordon613

Reputation: 2952

Adding a Hebrew Autocorrect entry using VBA in Excel

I can add an English Autocorrect entry using VBA in Excel

Application.AutoCorrect.AddReplacement What:="helo", Replacement:="hello"

However if the replacement word is Hebrew then it doesn't work (nothing is added)

aHebrewWord = Range("C1").Value
Application.AutoCorrect.AddReplacement What:="helo", Replacement:=aHebrewWord

I know that VBA does work with Hebrew, even though you can't actually see Hebrew in VBA (source declaring a unicode string in vba in excel) - for instance the following function works fine:

function getReverse(aHebrewWord)
  getReverse=StrReverse(aHebrewWord)
end function

How can I add a Hebrew Autocorrect entry using VBA?

Upvotes: 2

Views: 347

Answers (2)

Avish
Avish

Reputation: 4626

There shouldn't be anything preventing VBA from using one string instead of another; your code should work.

The problem, if it exists, might be with the way you're obtaining aHebrewWord.

Upvotes: 1

z̫͋
z̫͋

Reputation: 1571

The VBA editor expects VBA files to be encoded in Windows-1252, which is a 8-bit codepage and does not support hebrew.

You can either build your string as a concatenation of wide character code :

'this replace 'hello' to 'שלום'
Application.AutoCorrect.AddReplacement What:="hello", Replacement:=ChrW(&H05E9) & ChrW(&H05DC) & ChrW(&H05D5) & ChrW(&H05DD)

Or you can convert a Windows-1252 string which is a binary equivalent of the unicode string:

Application.AutoCorrect.AddReplacement What:="hello", Replacement:=StrConv("éÜÕÝ", vbFromUnicode)

Use notepad to convert the string: copy-paste the unicode string, save the file as unicode (not utf-8) and open it as ASCII (which is in fact Windows-1252),then copy-paste it into the VBA editor without the first two characters (ÿþ), which is the BOM marker.

Upvotes: 0

Related Questions