CODEWITHSUNDEEP
vbaexcel
medo ampir
medo ampir

Reputation: 1910

VBA function cannot return arabic string in excel 2007?

i had a VBA function that returns arabic string (it is for converting a number into arabic writen string)

i was using it on excel 2007 on windows xp

but when i changed to windows 7 the function started to return some thing like this:

ÝÞØ ÎãÓÉ ÂáÇÝ æ ËãÇäãÇÆÉ æ ÃÑÈÚÉ æ æÃÑÈÚæä æ 59 áÇÛíÑ

i can write arabic in vba editor but can't view it in excel cell.

Upvotes: 3

Views: 7829

Answers (3)

M.M.Rame
M.M.Rame

Reputation: 113

in VB Editor: - 1- click tools 2- Select Options 3- click Editor Format 4- Change font to(Courier New (Arabic)) 5- click OK now you can write in both English and Arabic I hope this will solve your problem

Upvotes: 0

user3367687
user3367687

Reputation: 1

Under Window 7 OS 

STEP 1: GOTO CONTROL PANEL AND SELECT REGION AND LANGUAGE
STEP 2: SELECT ADMINISTRATIVE TAB  
        CHOOSE BUTTON SYSTEM LOCAL LANGUAGE
        COMPUTER WILL SHOW YOU A SCREEN TO SELECT ANY ARABIC LANG
STEP 3: SELECT YOUR LOCAL LANGUAGE  CLOSE ALL 
       NOW YOU GOTO VBA EDITOR PAGE YOU WILL BE HAPPY

Upvotes: 0

jdh
jdh

Reputation: 1655

Excel stores strings in Unicode. The string your are using appears to be using the ANSI Arabic (Windows) codeset. In your previous windows XP setup, you probably had this language settings enabled and haven't set it up in Windows 7. If I save your string to a text file, and import it in Excel using the Data (import text) wizard, specifiying the input code page as ANSI Arabic, it returns: فقط خمسة آلاف و ثمانمائة و أربعة و وأربعون و 59 لاغير (Which Google translates equates to: "Only five thousand and eight hundred and forty-four and 59 to change my"). You probably would want to convert your old text from a specific code page (suspected ANSI Arabic) to the more flexible UTF-8 to completely avoid require setting up individual code pages.

Its unclear where you are getting your Arabic strings from. Maybe a database or maybe hardcoded in your old VBA code. Ideally its from a database, and you can do a one time conversion there (to convert those strings to Unicode). Then your VBA code can consistently use Unicode as the native encoding.

Alternatively, if you have to dynamically convert the ANSI Arabic strings in your code, you can use the vba function StrConv() using a paramter to specify what encoding your string is in (from this table). This routine is a little unfriendly, in that you have to convert the input string to a byte array first. Here's an example of how to do this conversion:

Function convertANSIArabic2Unicode(inputStr As String) As String
Dim n As Integer
Dim i As Integer
Dim inBytes() As Byte
Dim sUnicode As String

' Convert input string to byte array
n = Len(inputStr)
ReDim inBytes(n + 1)

For i = 1 To n
    inBytes(i) = AscB(Mid(inputStr, i, 1))
Next

' Convert byte array to unicode using Arabic coding
sUnicode = StrConv(inBytes, vbUnicode, &H401)

' remove starting null
iPos = InStr(sUnicode, Chr(0))
If iPos > 0 Then sUnicode = Mid(sUnicode, iPos + 1)

convertANSIArabic2Unicode = sUnicode

End Function

Where it would be called like this:

Dim xStr As String
xStr = "ÝÞØ ÎãÓÉ ÂáÇÝ æ ËãÇäãÇÆÉ æ ÃÑÈÚÉ æ æÃÑÈÚæä æ 59 áÇÛíÑ"
ActiveCell = convertANSIArabic2Unicode(xStr)

Would result in the cell displaying:

فقط خمسة آلاف و ثمانمائة و أربعة و وأربعون و 59 لاغير

Upvotes: 11

Related Questions