5.10 Excel Meets the Euro

There are two functions, beginning with Excel 2000, for making Excel compatible with the new European currency the euro:

Starting with Excel 2000 the euro symbol can finally be input ”via Alt +0128 (U.S. keyboard: type numbers on the numeric keypad with Num Lock on), AltGr +4 in the UK, and for other countries see the on-line help under "euro" ”without installing one or another update.
The so-called euro add-in (file Eurotool.xla ) offers the worksheet and VBA euro conversion function EuroConvert as well as aids for formatting cells containing currency values.
Since Excel 2002 this Add-In also features the command ExtrasEuroumrechnung . With it, you can select a range of cells and change the currency valuation. All the cells must be copied to another location in the worksheet, where formulas are replaced by values. This command is probably unsuitable for converting a table with a preset layout.

Tip

You can set the currency symbol in the control panel in the countryspecific settings. This symbol will be used as the default in the formatting of currency amounts. It probably is not a good idea to change this setting to the euro, since then many amounts will be displayed as euros that really should be in pounds sterling, or German marks, for example. It makes more sense to format explicitly for the euro in new documents and tables that are being converted.

The Euroconvert Function

If the Euroconvert Function is inserted into a worksheet, the syntax looks as follows : = Euroconvert(number, source, target [, full_precision, triangulation_precision])

The source and target currencies are passed as character strings. Here the ISO currency codes are used, such as "DEM" for the German mark, "ATS" for the Austrian schilling, "IEP" for the Irish pound , and "EUR" for the euro. (A complete table appears in the Excel on-line help.) Here are some examples:

FORMULA	RESULT	NOTES
= Euroconvert(100, "EUR", "DEM")	195.58	100 ‚ = 195.58 DM
= Euroconvert(100, "DEM", "EUR")	51.13	100 DM = 51.13 ‚
= Euroconvert(100, "EUR", "ATS")	1376.03	100 ‚ = 1376 ATS
= Euroconvert(100, "ATS", "EUR")	7.27	100 ATS = 7.27 ‚
= Euroconvert(100, "DEM", "ATS")	703.55	100 DM = 703.55 ATS
= Euroconvert(100, "ATS", "DEM")	14.21	100 ATS = 14.21 DM

In general, Euroconvert returns only two decimal places. If you are converting small amounts, the result will be zero. (Note that not only is 0 displayed, it is the value stored internally, even if you then multiply the result by 1000.) For example, if you wished to convert 1 Austrian groschen into euros, the result will be 0 (Euroconvert(0.01, "ATS", "EUR") .

According to the on-line documentation, this behavior corresponds to the currency-specific rounding rules. However that may be, if you give TRUE as the fourth parameter, then this noteworthy rounding mode will be ignored, and instead, calculations will be made to full accuracy. Euroconvert(0.01, "ATS", "EUR", TRUE) demonstrates that an Austrian groschen is, in fact, worth a whopping 0.0007267283416786 euro.

It remains only to explain Euroconvert ˜s fifth parameter. It comes into play only when neither the source x nor target y is the euro. In such calculations, first x is converted to euros, and then this intermediate result is converted into y . The fifth parameter of Euroconvert determines the number of decimal places to which the intermediate result (not the final result) is to be rounded. If the parameter is omitted, then Euroconvert does not round the intermediate result, and thus calculates with maximum precision.

Installing Euroconvert in VBA Code

The Euroconvert function is, of course, available in VBA code. You have merely to set up a reference to the Eurotool library (Figure 5-17).

Figure 5-17: A reference to the EuroTool add-in

 Sub  eurotest  ()   MsgBox EUROCONVERT(100, "DEM", "EUR") End Sub

The Eurotool library provides a second function: ApplyEuroformatting formats all previously selected cells of the active worksheet into euro currency format (thousands separator, two decimal places, postfixed ‚ -Symbol). There are numerous additional functions (particularly in the version provided with Excel 2002), but not all of them are documented.

Problems with Euroconvert as a Worksheet Function

As already mentioned, the Euroconvert function is available only if the euro add in has been activated. The biggest problem with the use of add-ins is that when you pass Excel files to other users, you do not know whether the user has activated the add-in. Why such an important function whose code takes up only a few kilobytes of storage could not be integrated directly into Excel is a secret known only to Microsoft. (Perhaps the reason is that an add-in can be more easily brought up to date, for example, if the UK decides to join the euro zone.)

If the individual receiving your file in which you have used the Euroconvert function has not activated the euro add-in, then when the file is loaded two error alerts will appear. First, Excel 2000 announces in a rather cryptic way (Figure 5-18) that certain links to other workbooks need to be updated. (What is meant is the add-in file Eurotool.xla , but how on earth is the user supposed to know that?) If the user is lucky and chooses YES, then the result will be a confrontation with yet another alert, in which Excel maintains that the function Euroconvert was not found in Eurotool.xla . (Figure 5-19).

Figure 5-18: Excel asks somewhat cryptically whether the workbook with the euro add-in should be loaded.

Figure 5-19: Excel maintains that Euroconvert is not defined in Eurotool.xla.

In Excel 2002 these same problems occur. The error alerts look a bit different, but they are just as confusing as ever.

The reason for the second error alert remains completely opaque . Fortunately, however, there is a simple solution: Before passing on your file, switch into the development environment with Alt+F11 and establish a reference to EuroTool . (This library will be displayed automatically once the euro add-in has been activated; see Figure 5.17.)

If the file thus prepared is now opened on a computer on which the euro add-in has not been activated, everything functions correctly anyhow. (To be sure, the add-in remains inactive, but the add-in file is nonetheless loaded, and the Euroconvert function is available both in the worksheet and as VBA code.)

The only requirement is that the euro add-in be installed on the computer. If that is not the case, then there appears, alas, not the question whether the add-in should be installed, but an error alert, which gives no indication as to the cause of the trouble (Figure 5.20).

Figure 5-20: Error alert if the euro add-in has not been installed

Thus you have no choice but to advise the user somewhere at the beginning of the worksheet that this file uses the euro add-in and that the user should install it and preferably activate it. Welcome to the brave ( sort of) new world of Microsoft meets the euro!

Converting Excel Files to the Euro

By this time you are probably wishing that I would offer you a macro that at the push of a button would convert marks, pounds, and francs into euros. Unfortunately, that is impossible . The conversion of currency continues to be "made by hand" and accomplished only with the help of a participating human intelligence. The macros presented here, which are to be found in the file Euro.xls , should at least lighten your burden and speed you on your way.

Basics

In Figure 5-21 you see two tiny worksheets whose contents are equivalent: A price is calculated from a price per piece, number of pieces, and discount percentage. The difference in the two tables is in the formatting ”one of them has formatting for currency values, while the other does not.

Figure 5-21: Two worksheets before euro conversion

To convert both of these tables into euros, the value of only one cell (B3) needs to be changed. In Table 1 the format of cells B3 and E3 must be changed.

In Table 1 it should be theoretically possible to carry out an automatic euro conversion. The process would look something like this:

Search through all cells that contain a number (not a formula, date, character string) and are formatted as a currency value. Carry out the conversion from the given units into euros and change the formatting as required.
Search through all cells that contain a formula and that are formatted as a currency value. Change the formatting. (The formula itself, E3 in Figure 5.21, does not need to be altered . The result is automatically in euros if all base cells are euro values or valueless factors.)

Let us now proceed to some of the numerous reasons that a fully automated conversion will not work in practice:

In only a very few worksheets are all currency values formatted as such. Many tables look more like Table 2 in Figure 5.21 either to save space or simply for convenience. No program would be able to tell what needed to be converted and what not.
In many tables there are cells in which the source number is input not as a number (such as 1200 ), but as a formula (say, = 1000+200 ). Even if in general no changes in formulas need to be made, such formulas are the exception.
Sometimes, currency values are used directly in formulas. For example, suppose that in Figure 5.21 there was no column for unit price. Then the formula for the final price would look like this: = C3*1200*(1-D3) . This formula, too, would have to be altered.
In many tables values appear in a variety of currencies, some that are not euro currencies. This complexity exceeds the "intelligence" of every conversion program.
Many tables that unite date from several years should not be converted wholesale. Instead, only a part needs to be converted into euros, those entries later than a certain date (such as 1/1/1999).

In short, an automatic conversion is a pleasant dream, but it will not survive a reality check. The following macros should at least make the work a bit easier.

Converting Individual Cells

If a cell contains a currency value as a number, there are several options for conversion:

You can calculate the value in euros in a VBA program and insert the value directly into the cell:
```
 cell.Value = EUROCONVERT(cell.Value, currencyIso, "EUR", True) 
```
In a VBA program you can create the character string for a formula that inserts the Euroconvert worksheet function. The VBA instruction for this looks a bit complex:
```
 cell.Formula = "=Euroconvert(" & Str(cell.Value) & _       ", ""DEM"", ""EUR"", True)" 
```
The resulting formula looks as follows:

= Euroconvert(1200, "DEM", "EUR", TRUE)

For international versions you may expect some differences. Here is the German version:

= Euroconvert(1200; "DEM"; "EUR";WAHR)

Note that the property Formula expects the formula in the English-language fashion (period for decimal point, comma for thousands separator, True instead of WAHR, etc.), regardless of the Excel version used. For this reason cell.Value is changed into a character string with Str. It is only when you look at the formula in an international version of Excel that you will notice that country-specific features are in fact represented.

The advantage of this option are that later one can reconstruct how the conversion can be carried out. The original amount is part of the formula, that is, the conversion can be carried out repeatedly if necessary without rounding error (for example, if you notice that you were working in the wrong cell). However, there is also a disadvantage : The converted worksheet is now dependent for all time on the Euroconvert function. As mentioned above, this can lead to problems if you give the file to someone else who has not activated the euro add-in.
A third variant consists in constructing a formula that multiplies the given number by the conversion factor. The advantage is that the resulting worksheet is independent of Euroconvert . The disadvantage is that the rounding options that Euroconvert offers cannot be utilized. The VBA code looks like this:
```
 cell.Formula = "=" & Str(cell.Value) & "*" & _       Str(EUROCONVERT(1, "DEM", "EUR", True))       = 1200 * 0.511291881196218 
```
Instead of multiplying by Euroconvert(1, "DEM", "EUR", False ), you could also divide by Euroconvert(1, "EUR", "DEM", False ). The advantage is that in the resulting formula there will be only five decimal places, because the official conversion rate has been set with this degree of precision. The result should be the same in the limits of Excel's precision (16 places), but the resulting formula is easier to read. First the VBA code:
```
 cell.Formula = -"=" & Str(cell.Value) & "/" & _       Str(EUROCONVERT(1, "EUR", "DEM", True)) 
```
And here is the resulting formula:
```
 = 1200 / 1.95583 
```
If the cell contains a formula instead of a number, then the first of the three variants above is, of course, impossible. The other variants look as follows (with results based on the assumption that the starting formula is =1000+200):

  cell.Formula = -"=Euroconvert(" & Mid(cell.Formula, 2) & _       ", ""DEM"", ""EUR"", True)"

= Euroconvert(1000+200, "DEM", "EUR", TRUE)

   cell.Formula = -"=(" & Mid(cell.Formula, 2) & ") * " & _    Str(EUROCONVERT(1, "DEM", "EUR", True))

= (1000+200) * 0.511291881196218

or:

  cell.Formula = -"=(" & Mid(cell.Formula, 2) & ") / " & _       Str(EUROCONVERT(1, "EUR", "DEM", True))

= (1000+200) / 1.95583

Euro Formatting

There are many ways in which currency values can be formatted: with or without decimal places, with or without thousands separator, with red text to represent negative numbers, with the explicit display of the currency symbol either before or after the number (or none at all, since it is often clear that the value represents currency, or somewhere in the table is the text "values in thousands of U.S. dollars"), and so on.

The tool for euro formatting that is part of the Microsoft euro add-in pays no attention to any previous formatting of a cell, but simply replaces the format with another predefined format. The probability that precisely this format meets your requirements is small.

A more intelligent way of proceeding is to take into account the previous formatting of a cell and to adapt it. For this the property NumberFormatLocal must be used. If the old currency symbol is found in the formatting character string, then it is replaced with the euro symbol. The necessary code takes care of the situation in which the currency symbol appears in the formatting character string within quotation marks (which could be the case).

If the cell has not previously been formatted, then the number of decimal places will be limited to two. (This makes sense because otherwise , hitherto whole-number amounts would acquire upon euro conversion a large number of decimal places, making their representation unreadable.) The example here is for conversion of German marks to euros.

 Dim tmp$ If cell.NumberFormat = "General" Then   If IsNumeric(cell.Value) Then     cell.NumberFormat = "0.00"   End If ElseIf InStr(cell.NumberFormatLocal, "DM") <> 0 Then   tmp = cell.NumberFormatLocal   tmp = Replace(tmp, "DM", "   ")   tmp = Replace(tmp, """DM""", "   ")   cell.NumberFormatLocal = tmp End If

Tip

Working directly with NumberFormatLocal is not without its problems, not least because this property is most unsatisfactorily documented, so that one can only guess what effect a change in format will have. The procedure described here has proven effective in both the German and English versions of Excel, but it is difficult to say whether it will work in other current versions or in future versions. Background information on the property NumberFormatLocal and the related property NumberFormat can be found in the first section of Chapter 5.

Using the Euro Conversion Machine

The first step consists in opening the file Euro.xls and customizing it to your requirements. For this purpose, in Module1 there are three constants whose preset values are as follows:
```
 '  example file Euro.xls, Module1       Const currencyIso = "DEM"  '  Iso currency code for euro conversion       Const currencyFormat = "DM"  '  currency symbol for NumberFormat       Const convertOnlyCurrency = True  '  for TestAndMarkForEuroConversion  
```
This setting means that the program will carry out a conversion from German marks (DM) into euros, that in converting number formatting it searches for the character string DM , and that the marking function (see below) it marks only those numerical values that are formatted in DM . It also assumes a table like that in Figure 5.21. If your table is formatted as in Figure 5.21 but without a currency symbol, then you must set convertOnlyCurrency to False . (Then, however, cells such as C3 will also be marked for conversion, that is, some manual labor is going to be required.)
If these preliminary tasks have been accomplished, open the file to be converted and save it at once under a new name . (This is to ensure that the changes hold for the new file only, while the original file, if only as a control reference, remains as it was.)
Select the entire worksheet (mouse click on the upper left corner of the row and column labels). Then click on the first symbol in the euro toolbar. The program now attempts to recognize all cells suitable for conversion to euros and marks them with a red diagonal line. (The actual conversion does yet take place.)

In Figure 5-22 you can see what the little example table looks like after this step. Only cell B3 has been marked for the conversion to come.

Figure 5.22: Only cell has been marked for subsequent conversion to euros

Now the real work begins. You have to decide whether the selection made by the program is adequate. If too few cells have been marked, then you must select additional cells with the mouse and then mark with the second button on the euro toolbar. On the other hand, if too many cells have been marked, you need to select these cells and cancel their marking with the third button.

Note that as described at the outset, only cells with currency values need to be converted. In the case of cells with formulas the euro conversion proceeds automatically. For this reason E3 must not be marked.

When you are sure that the proper cells have been marked, click on the fourth button. Now all marked cells will be converted to euros. Simultaneously, the formatting of these cells will be changed. See Figure 5-23.

Figure 5-23: The table after euro conversion

If you in fact marked the correct cells, then the euro conversion is complete (Figure 5-24). (If you neglected to mark some cells, then simply repeat the above process: first mark cells, then convert them.)

However, in the last step result cells like E3 must be formatted. The correct amount in euros appears, but it is formatted as for German marks. The fifth button on the euro toolbar changes the format of all cells previously marked with the mouse. (Please note that with this button what takes place is merely an exchange of currency symbol (that is, DM ’ ‚ ). In the case of cells that are not formatted in DM , nothing changes (except that in the case of cells that have not been formatted, the number of decimal places will be limited to 2).

Figure 5-24: The table after formatting is complete

Note

Do not expect that euro conversion will always proceed so effortlessly as in this example. In reality, Excel tables are much more complicated, and euro conversion can take a great deal of time. The most important step is to compare the old and new tables after conversion is complete and ensure that indeed not too many, but also not too few, cells have been converted.

Program Code

TestAndMarkForEuroConversion is invoked by the first button on the euro toolbar. In the procedure a test is made as to whether any cells at all have been selected. If none have, then the procedure exits at once.

The following lines are for speed optimization. They deactivate the automatic recalculation of the worksheet after every change as well as screen updating (details in the last section of this chapter).

 ' tests for all previously selected cells whether ' a conversion is possible; if yes, ' they are marked for conversion by a red diagonal line Sub  TestAndMarkForEuroConversion  ()   Dim ar As Range, cell As Range   Dim usedrng As Range   Dim calcMode&, updateMode&   If Selection Is Nothing Then Exit Sub   If TypeName(Selection) <> "Range" Then Exit Sub   ' speed optimization   calcMode = Application.Calculation   updateMode = Application.ScreenUpdating   Application.Calculation = xlManual   Application.ScreenUpdating = False

The loops over Areas and Cells ensure that indeed every selected cell is analyzed , even if with Ctrl a multipartite range of cells was selected.

With Intersect the range to be analyzed is restricted to the actual area of the worksheet that is being used. Here again it is a question of speed optimization. Even if, for example, several columns have been selected (every column consists, after all, of 65536 cells), only the active cells are analyzed.

The actual test of which cells are to be marked consists of a sequence of simple tests. Is the cell empty? Does it contain a formula? and so on. Only if all criteria are fulfilled (or not fulfilled in the case of Not ) will the cell be marked. For this the auxiliary procedure MarkRangeForEuroConversion is called. (By the way, the program code is not, as is usual, indented for every If test, simply because the width of the pages of this book is insufficient. Since VBA tests are not optimized with And , the nested If construction is faster than a combination of conditionals with And together with a single If test.)

Note	To give you an idea of how fast (or slow) the program is, here is an example: The analysis of 25,000 filled cells (100 columns, 250 rows) takes 15 seconds on a Pentium II 400. Therefore, every attempt to optimize the program is worth making.

 Set usedrng = Selection.Worksheet.UsedRange   ' loop over all cells   For Each ar In Intersect(Selection, usedrng).Areas     For Each cell In ar.Cells       If Not IsEmpty(cell) Then                   ' not empty       If Not cell.HasFormula Then                 ' not a formula       If Not TypeName(cell.Value) = "Date" Then   ' not a date       If Not TypeName(cell.Value) = "String" Then ' not a character string       If InStr(cell.NumberFormat, "%") = 0 Then   ' not a % format       ' only cells with DM format       If convertOnlyCurrency Then         If InStr(cell.NumberFormatLocal, currencyFormat) 0 Then           MarkRangeForEuroConversion cell         End If       Else         MarkRangeForEuroConversion cell       End If       End If       End If       End If       End If       End If     Next   Next   Application.Calculation = calcMode   Application.ScreenUpdating = updateMode End Sub ' mark for conversion (red diagonal line) for range of cells Sub  MarkRangeForEuroConversion  (rng As Range)   Dim ar As Range, cell As Range   For Each ar In rng     For Each cell In ar.Cells       cell.Borders(xlDiagonalUp).LineStyle = xlContinuous       cell.Borders(xlDiagonalUp).Color = vbRed     Next   Next End Sub

The second button has associated to it the procedure MarkSelectionForEuroConversion . With it the previously selected cells are marked for conversion without a lengthy test. The procedure calls MarkRangeForEuroConversion after the above-described security test and speed optimization.

 Sub  MarkSelectionForEuroConversion  ()   Dim calcMode&, updateMode&   If Selection Is Nothing Then Exit Sub   If TypeName(Selection) "Range" Then Exit Sub   ' speed optimization   calcMode = Application.Calculation   updateMode = Application.ScreenUpdating   Application.Calculation = xlManual   Application.ScreenUpdating = False   MarkRangeForEuroConversion Intersect(Selection, _     Selection.Worksheet.UsedRange)   Application.Calculation = calcMode   Application.ScreenUpdating = updateMode End Sub

The procedure UnMarkSelectionForEuroConversion is assigned to the third button. It looks very similar to the procedure above. The actual work then takes place in UnMarkRangeForEuroConversion :

 ' to unmark cells for conversion Sub  UnMarkRangeForEuroConversion  (rng As Range)   Dim ar As Range, cell As Range   For Each ar In rng     For Each cell In ar.Cells       cell.Borders(xlDiagonalUp).LineStyle = xlLineStyleNone     Next   Next End Sub

The actual euro conversion takes place in ConvertNumberIntoEuro . This procedure tests, for all cells of the currently active worksheet, whether the cell has been marked for conversion (that is, with a red diagonal line). It you wish, you can restrict the test to a previously selected region of cells

 Sub  ConvertNumberIntoEuro  ()   Dim calcMode&, updateMode&   Dim cell As Range   If TypeName(ActiveWindow.ActiveSheet) <> "Worksheet" Then Exit Sub   ' speed optimization   calcMode = Application.Calculation   updateMode = Application.ScreenUpdating   Application.Calculation = xlManual   Application.ScreenUpdating = False   ' for all cells of the worksheet   For Each cell In ActiveWindow.ActiveSheet.UsedRange     If Not IsEmpty(cell) Then       If cell.Borders(xlDiagonalUp).LineStyle = xlContinuous Then         If cell.Borders(xlDiagonalUp).Color = vbRed Then

If a cell is encountered that is to be converted, the program then differentiates between formulas and numerical values. In the case of formulas the expression is placed in parentheses and divided by the Euroconvert conversion value for the respective currency. For numbers, a formula is constructed with an appropriate division. In each case this process produces a clear formula, which can effortlessly be transformed back to its original form. For the cell in question the conversion marking is then erased and a formatting in euros carried out.

 If cell.HasFormula Then             cell.Formula = "=(" & Mid(cell.Formula, 2) & ") / " & _               Str(EUROCONVERT(1, "EUR", currencyIso, True))             UnMarkRangeForEuroConversion cell             EuroNumberFormatRange cell           ElseIf IsNumeric(cell.Value) Then             cell.Formula = "=" & Str(cell.Value) & "/" & _               Str(EUROCONVERT(1, "EUR", currencyIso, True))             UnMarkRangeForEuroConversion cell             EuroNumberFormatRange cell           End If         End If       End If     End If   Next   Application.Calculation = calcMode   Application.ScreenUpdating = updateMode End Sub

The euro formatting procedure EuroNumberFormatRange tests whether the affected cells are formatted at all ( NumberFormat = "General" ). If they are not, then a formatting with two decimal places is executed. Cells that are already formatted will have their previous currency symbol changed to the euro symbol by NumberFormatLocal . Cells in which up to now no currency has been displayed retain their formatting.

 Sub  EuroNumberFormatRange  (rng As Range)   Dim tmp$   Dim ar As Range, cell As Range   For Each ar In rng.Areas     For Each cell In ar.Cells       If cell.NumberFormat = "General" Then         If IsNumeric(cell.Value) Then           cell.NumberFormat = "0.00"         End If       ElseIf InStr(cell.NumberFormatLocal, currencyFormat) <> 0 Then         tmp = cell.NumberFormatLocal         tmp = Replace(tmp, currencyFormat, "   ")         tmp = Replace(tmp, """" + currencyFormat + """", "   ")         cell.NumberFormatLocal = tmp       End If     Next   Next End Sub

The fifth button on the euro toolbar is assigned to EuroNumberFormat , whose only purpose in life is to call EuroNumberFormatRange after the usual process of speed optimization to carry out the formatting for the current selection.

 Sub  EuroNumberFormat  ()   ... speed optimization   EuroNumberFormatRange Intersect(Selection, _     Selection.Worksheet.UsedRange)   ... speed optimization End Sub

Room for Improvement

If Euro.xls were a commercial product, then there would be some work left to be done:

To supplement the five buttons on the toolbar, corresponding menu entries would be added.
Every conversion step would have to be reversible (Undo function).
Configuration details should be able to be set via a form.
There is no on-line help.
The mark for a cell to be converted ”a red diagonal line ”is arbitrary. (Not quite: The advantage of this method is that the marked cells are easily recognizable, and the content of the cell can be easily read.) For the unlikely case that in a table somewhere diagonal red lines have been used already, an alternative marking method should be made available.

However, the goal of this section was not to put euro tool programmers out of business, but to discuss some programming techniques. So, as a starting point for further development, these examples should suffice.