Solved

Excell macro to translate curency numbers into test in Arabic

Posted on 2011-09-28
10
262 Views
Last Modified: 2012-05-12
I have an excell sheet which contains currencies amount, i need a macro that can translate this currencies into Text, but the problem is that the currency is in Saudi riyals & i need the text to be in arabic
0
Comment
Question by:Hassanay
  • 4
  • 4
  • 2
10 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 36715490
It could be as simple as using formatting to supply the format you are looking for :

select the cells you want to format
right-mouse-click
select cell properties
in the 'number' tab, select "Custom" category
in the "type" section you can supply an arbitrary layout, such as :

standard saudi currency format (eg 123.45 ¿.¿ )
[$¿.¿.¿-401] #.##0,00_-

custom format (eg SAR 123.45)
[$SAR] #.##0,00_-

custom format (eg bladibla 123.45)
"bladibla " #.##0,00_-

0
 
LVL 19

Expert Comment

by:akoster
ID: 36715503
The copy-pasting of the riyal formatting did not work as expected, to use the standard saudi currency format use these steps :

select the cells you want to format
right-mouse-click on the selected cells
select cell properties
in the 'number' tab, select "Currency" category
in the 'symbol' section, choose appropriate symbol from the list (Arabic - saudi arabia)
click OK
0
 

Author Comment

by:Hassanay
ID: 36715617
maybe you got me wrong, i want to translate number to text, ex: 100..it must give me one hundred riyals,bit i want the text in Arabic not english
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36715792
VBA does not have a built-in way to do this.  There are various examples of user-defined functions if you Google it, e.g. http://www.ozgrid.com/VBA/ValueToWords.htm, but this is obviously in English and I guess it would not be simple to translate it to Arabic, unless numbers are read out  with exactly the same structure as in English which seems unlikely.  It might be worth taking a look at the code to see if you could adapt it - my Arabic is not good enough, I'm afraid.
0
 

Author Comment

by:Hassanay
ID: 36715875
thankx, i got the module, but the prblem when am trying to replace the english text to rabaic but it is giving me ????,,ex: thousand.....must be ¿¿¿  but it is writing ???

is there any suggestion?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36716234
Hi - this is a problem because the VBA editor does not handle unicode strings directly - you can store them in strings, but you won't be able to see the values, and if you use the immediate window or debugging you'll always see ???.

The way I've done this before is to put the language strings onto a sheet in the workbook (which you could hide), then read them from the sheet into VBA string variables - in the attached example I have read them into an array.  The example demonstrates how you could get the strings for the numbers 1-10 into the array, and puts up a dialog box to show that the values are really there (Msgbox shows them as ??? too).

You would obviously need to do this with all the numbers you need.  I apologise if the numbers I have used aren't right - I just got them from the web.  I hope someone is not playing a trick and they're all rude words. arabic-nos.xlsm
0
 

Author Comment

by:Hassanay
ID: 36716479
sorry but i cant see any macro of function in the attached
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36716559
It's called GetNumberStrings in modArabic - you should be able to find it in the Macros list on the developer tab.  Not sure what version of Excel you have, but you may need to enable macros when you open, or if it's 2010 allow content from the web in the bar atthe top of the worksheet
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36717672
This looks to be a very comprehensive article about doing this in Arabic, although it's for C# code, it may have some ideas you can use: http://www.codeproject.com/KB/cs/NumberToWord.aspx
0
 

Author Closing Comment

by:Hassanay
ID: 37265734
Thats ok, but i have to do a lot of work here, i hoped it will work as the english one!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Fixing a embedded format 7 29
Export Query data to excel file 14 37
display excel spreadsheet fields only has certain string 2 10
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now