by Allen Wyatt
(last updated November 15, 2014)
George has a single workbook that he needs to submit information using a Latin America currency format like this: 238.769,76. Short of changing his global settings George has not been able to come up with a way to handle this. He wonders if there is a way to change the currency format for only a single workbook.
The "global settings" of which George speaks are technically referred to by Microsoft as "regional settings." These are, indeed, handled by Windows directly, so any change there affects all programs operating within Windows, including Excel.
There is a way to temporarily change the settings by using a VBA macro to do the magic. If you tie this macro to the opening and closing of a particular workbook, then your needs might be met adequately. Here are the macros:
Dim oldDecimalSep As String Dim oldThousandsSep As String Private Sub Workbook_Open() oldDecimalSep = Application.International(xlDecimalSeparator) oldThousandsSep = Application.International(xlThousandsSeparator) Application.DecimalSeparator = "," Application.ThousandsSeparator = "." Application.UseSystemSeparators = False End Sub
Private Sub Workbook_ BeforeClose(Cancel As Boolean) Application.DecimalSeparator = oldDecimalSep Application.ThousandsSeparator = oldThousandsSep Application.UseSystemSeparators = False End Sub
To use these macros, open the workbook you want to use the differing format separators. Then, open the Visual Basic Editor and double-click the ThisWorkbook object in the Project window. Put the macro code into the code window and save everything.
Now, whenever you open the workbook, Excel stores the existing separator information and changes it to use commas and periods as George wants. When the workbook is closed, the old separator information is restored.
There is one gottcha with this code: While the workbook is open, any other workbook also open will use the different formatting. The trick, then, is to only work with the "special" workbook alone, knowing that when you close it everything will return to normal.
There is one thing to remember when using code like this: You are only changing how the numbers you see are formatted. You may not even need to do it, because of the way that Windows handles regional settings. In other words, you might see a number appear like this in Excel: 1,234,567.89. When you send that workbook to a user in a country that uses different regional settings, she would see it like this: 1.234.567,89. In other words, Windows takes care of displaying the numbers correctly for the region of the world in which they are displayed.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13307) applies to Microsoft Excel 2007, 2010, and 2013.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Excel allows you to adjust not only the horizontal alignment of values in a cell, but also the vertical alignment. This ...Discover More
Want to format your data tables in a hurry? It's easy to do if you use the built-in table formatter provided in Excel.Discover More
Excel is continually trying to figure out what type of data is being stored in a cell. If it can interpret a value as a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.