2

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?

Community
  • 1
  • 1
gordon613
  • 2,770
  • 12
  • 52
  • 81
  • There shouldn't be anything preventing VBA from using one string instead of another. Are you sure the issue is with the string being Hebrew? How do you define `aHebrewWord`? – Avish Apr 10 '14 at 21:30
  • I think you are right... I think perhaps the problem is that I should have done ActiveSheet.Range("C1").value – gordon613 Apr 11 '14 at 13:24
  • Hi Avish. If you write a version of your comment as an answer I will accept it hopefully. – gordon613 Apr 24 '14 at 14:11
  • Done, although I'm not sure "your problem lies elsewhere" is a good answer. – Avish Apr 24 '14 at 14:19

2 Answers2

1

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.

Avish
  • 4,516
  • 19
  • 28
0

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.

z̫͋
  • 1,531
  • 10
  • 15
  • Thank you for your hard work writing a very specific answer. I am sure that this will work. But I think the answer is as @Avish said, and as I wrote in my comment above, that I was just passing in the wrong parameter to the autocorrect function. – gordon613 Apr 13 '14 at 12:06