Solved

Set number format to euro using vba

Posted on 2013-12-22
7
6,085 Views
Last Modified: 2013-12-23
Folks,
I have a VBA need to format a cell as EURO and I can't get there:
ActiveSheet.Range("C4:C8").Select
Selection.NumberFormat = CHAR(128)
0
Comment
Question by:Frank Freese
7 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39735325
CHAR(128)  is just a character, not a number format.  Number formats are in the Format Cells dialog box.  Here's the Microsoft page on the subject: http://msdn.microsoft.com/en-us/library/office/aa224873%28v=office.11%29.aspx
0
 

Author Comment

by:Frank Freese
ID: 39735327
Thanks, I've been to that site, however, it did not give me a way to show that the value is in Euro's and not dollars. Therefore, what's the number format for a Euro? Could I just copy the Euro symbol and replace $##,### with €##,### ?
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 167 total points
ID: 39735381
Here's the manual way to set it: http://office.microsoft.com/en-us/excel-help/format-values-in-euro-currency-style-HP005290977.aspx   The article says if the Euro symbol is not shown, then you need to add the Euro Currency Tools add-in program.

This page shows format strings although it does not expressly show one with the Euro: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx  But another page did show  €##,### as the correct string.
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 81

Accepted Solution

by:
byundt earned 167 total points
ID: 39735389
Yes. You should be able to copy one of the $ currency formats and change the $ to €.
Sub Euros()
ActiveSheet.Range("C4:C8").NumberFormat = "€##,###;-€##,###;-;@"
End Sub

Open in new window

0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 166 total points
ID: 39735800
There are multiple Euro currency formats in the Format Cells dialog box under the Currency Category.   They are about 13 page clicks down so not that easy to find.   As an option, you can turn on the macro recorder and capture one to use in your VBA.   I use multiple currencies daily and have the following 6 procedures in my personal.xlsb and reference them with icons on the quick access toolbar to quickly format selected cells as I work.   The one for Euro is first in the list.

Sub CurrStyle_Euro()
    Selection.NumberFormat = "[$€-2] #,##0_);[Red]([$€-2] #,##0)"
End Sub

Sub CurrStyle_Pounds()
    Selection.NumberFormat = "[$£-809]#,##0;[Red]-[$£-809]#,##0"
End Sub

Sub CurrStyle_Yen()
    Selection.NumberFormat = "[$¥-411]#,##0;[Red]-[$¥-411]#,##0"
End Sub

Sub CurrStyle_Won()
    Selection.NumberFormat = "[$" & ChrW(8361) & "-412]#,##0;[Red]-[$" & ChrW(8361) & "-412]#,##0"
End Sub

Sub CurrStyle_Dollar()
    Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"
End Sub

Sub CurrStyle_BrazilianReal()
    Selection.NumberFormat = "[$R$-416] #,##0;[Red][$R$-416] #,##0"
End Sub

Open in new window

0
 

Author Closing Comment

by:Frank Freese
ID: 39735925
thanks to everyone and Merry Christmas
0
 

Author Comment

by:Frank Freese
ID: 39735929
Note: I copied a Euro symbol and embedded in my number format seemed to be the easiest thing to do for me.
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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

914 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

13 Experts available now in Live!

Get 1:1 Help Now