Solved

Set number format to euro using vba

Posted on 2013-12-22
7
5,807 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 82

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 82

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 80

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 48
Excel 2013 Problem 12 48
Using EXCEL to count the number of Rows 4 46
Populate data based ona  criteria 6 19
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 use a scrolling table in Microsoft Excel using the INDEX function.

758 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

17 Experts available now in Live!

Get 1:1 Help Now