Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Set number format to euro using vba

Posted on 2013-12-22
7
Medium Priority
?
8,196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 84

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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 668 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
byundt earned 668 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 664 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

721 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