Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9166
  • Last Modified:

Set number format to euro using vba

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
Frank Freese
Asked:
Frank Freese
3 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Frank FreeseAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
Technology Partners: 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!

 
byundtCommented:
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
 
Jerry PaladinoCommented:
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
 
Frank FreeseAuthor Commented:
thanks to everyone and Merry Christmas
0
 
Frank FreeseAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now