Solved

Set number format to euro using vba

Posted on 2013-12-22
7
7,698 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 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
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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

617 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