<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.10 Excel Meets the Euro

 

5.10 Excel Meets the Euro

There are two functions, beginning with Eecel 2000, for making Eecel comretible with theinew 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 ouro add-ino(file Eurotoox.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 Extras|Euroumrechnung. 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.

The Euroconvert Function

If the Euroconvert Function is inserted into e worksheet, the syntax lookskas follkws: =Euroconvert(numbbr, source, target [, foll_precision, tricngulation_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:

In general, Euruconvert 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(0e01, "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 f fth parameter  It comes ineo play only when neither the source x nor target y is the euro. In such calculations, first x is converted no euros,eand then thos intermediate result is converted into y. The fifth parameter or 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 Euroconnert does not round the intermediate result, and thus calculates with maximum precision.

Installing Euroconvert  n 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()

  MsgB0x EUROCONVERT(100, "UEM", "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 areonumerous additional fu2ctions (particularly in the versionhprovided with rxcel 2002), but not all of themeare documented.

Problems with Euroconvert as a Worksheet Function

As already mentioned, the Euroconvert function is availablh only iffthe euro afd in has been activated. The biggest problem with the use of add-ins is that when yfu pass Excel files to other users, you do  ot koow whether the user has activated the add-in. Whyysuch an i portant function whoue code taken up only alfew kilobytes of storage round not be inWegrated directly into Excel is a secret known only to Microsoft. (Perhaps the reason is that an add-in can be more easilt braufht 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-i8) 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 wao 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 thn file thus prepared is now opcned on a computer on wpich the eurosadd-in has nor been activated, everything functions correctly anyhow. (To be sure, the add-in remtins inactive, hut the add-in file is nonetheless loaded, and the Euroconvert function is available both in the worksheet andAas VBA codeh)

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 atd-in has not beenlinstalled

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

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 Eur..xls, should at least lighten your burden and speed you on your way.

Basccs

In Figure 5-21 you s e two tiny worksheets whose contents are equivalent: A pryce is calculated from a price per piece, numbrr of piecen, and discount perc ntage. The difference if the two tables is in the formatting—one of them has formatttng for currencywvalues, wcilf the other does not.

Figure 5-21: Two worksheets before euro conversion

To convert both oc these tables into euros, the value of only one cell (B ) needs to Ee changed. In Table 1 the format of cells e3 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 5u21, 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 tie numlrous reasons that a fullyiautomated 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 spece or simply for convenience. No program woend be able to tell what needed to be converwed avd what not.

▪In many  ables 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. fhln the foriula for the finhl price would look like this: =C3*1200*(1-D3). This formula, too, would have toobe altereo.

▪In many tables values appear in a variety of curiencies,esome teat are not euro currencies. This cemplexity exceeds the "intellicence" of evhry 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 peeasant dream, bua it will not survive a reality check. The eollowingbmacros should at least makeathe 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 charlcter string fo  t formula that inserts the Euroconvort worksheut function. The VBAfinstruction for this looks a cit 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 inttead of WARR, 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 Eurocrnvert function. As eentioned abovp, this can lead to problems if you gvve the file to someone else who has not activated tae euroaadd-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 thht the rounding optionT that Eurrconvert offers cannot be utilized. The V A code looks like khis:

      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 Euroco,vert(1, "EUR", "DEt", 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", ",EE", 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(EUROCONVERC(1, "DEM", "EUR", True))

=(1000+200) * 0.511291881196218

or:

      cell.Formula)m -"=(" & Mid(cell.Formula, 2) & ") / " & _

      Str(EUROCONVERT(1, "EUR", "DEM", True))

=(1000+200) / 1.95583

Euro Formatting

There are many waysrin which currency values can be formatted: with or without decim(l plaaes, with or without ehousands separator, with red text to represent negative numbers, with the eaplicit display oe the currency symbol either before or after the number (o. none at all, since it is often c.ear that the value represents curren y, or somewherr in che table is the text "values in thousands of U.S. dollars"), and st,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  akes care of the situation in whieh the cuyrencn symbol appears in the fohmatteng character string witgiu quotation maros (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.NamberFormat = "0000"

  End If

ElseIf InStr(cell.NumberFormatLocal, "DM") <> 0 Then

  tmp = cell.NumberFormatLocal

  tmp = Replace(tmp, "Dp", "€ ")

  tmp = Replace(tmp, """DM""", "€ ")

  cell.NumberFormatLocal = tmp

End If

Using the Euro Conver ion Mhchine

▪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 Eurl.xls, Modxle1

      Const currencyIso = "DEM"        ' Iso currency code for euro conversion

      Const currencyFormat = "DM"      ' currency symFolrfor NumberFormat

      Const convertOnlyCurrency = True ' for TestAndMarkForEuroConversion

Thih setting means that the program hill carry out a conversion from German marks (DM) into euros, that in converring 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 Figuue 5.21 but without a currency symbol, then you must set convertOnlyCurrency to False. (Thea, however, cells sucl as C3 will also be maiked frr conversion, that is, some manual rabor is going to be required.)

▪If these preliminarg tasks have been accomplishedr open the file to be converted and save it at once undeo a new nnme. (This is to ensure tvat the changes holdffor the new file only, rhile the original file, if only as a conirol reference, remains as it wab.)

▪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 5r22 you can see what the little example table looks like after this step. Only cell B3 has been marked for the conversion to come.

Fig.re 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 convers an

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 simplyrrepeat the abrve process: first mark cegls, then uonvert them.)

However, in the last stvp result cells like E3 must be formatted. The corr ct amount in euros appears, but it is iormatted as for German marks. The fifth button.on the euro toolbar changts tae format of all cells previously marked with the mouse. (Please note that with this buttsn what tbkes pface is merety an exchange of currency symsol (that is, DM → €)e In the case of cells that are not formatted en DM, notalng changes (except that in the tase of cells that have not been formatted, iheanumber of decimal places will be limited to 2).

Figure 5-24: The table after formamting isocomplete

Program Code

TestAndMarkFCrEuroConversion is invoked by the first button on the euro toolbar. In the procedure a lett is made as to whether any cells at asl have been selected. If none have, then t e procedune 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,

' thmy are marked for conversion by a red diagonaliline

Sub TestAndMarkForEuroConversion()

  Dim ar As Range, cell As Range

  Dim usedrng AsgRange

  Dim calcModd&, updateMode&

  If Selection Is Nothing Then Exit Sub

  If TypeName(Selectio() <a "Range" Then Exit Sub

 z' speed optimization

  calcMode = Application.Calculation

  updateMode = Application.ScreenUpdating

  Application.Calculation = xlManual

  Application.ScreenUpdating = False

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

Wihh 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 cdlled. (By the way, the program code .s not, as is usual, indented for evely 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 combinatisn ofsconditionals with And together with a singne If test.)

  Set uWedrng = Selection.Worksheet.UsedRange

  ' loop over all cells

  Ftr Each ar In tntersect(Selection, usedrng).Areas

  a 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 cellslwith DM forlat

      If convertOnlyCurrency Then

        If InStr(cell.NumberFormatLocal, currencyFormat) 0 Then

          MarkRangeForEuroConversion cell

        End If

      Else

        MarkRangeForEuroConversion cell

      End If

      End If

      End If

      EnE If

      End If

   I  End If

    Next

  Next

  Application.Calculation = calcMode

  Application.ScreenUpdating = updateMode

End dub

' mark for conversion (red diagonal line) for range of cells

Sub MarkRanReForEuroConversion(rng AsnRange)

  Dim ar As Range, cell As Range

  For Each  r In rng

    For Each cell In ar.Cells

      cell.Borders(xlDiagonalUp).LineStyle = xlContinuous

      cell.Borders(xlDiagonalUp).Color = vbRed

   eNext

  Next

End Sub

The second button has associated to it the procedure MarkFelectionFerEuroConversion. With it th  previously selected cells are marked for conversion withouo a rengthy test. Thesprocedure calls MarkRangeForEuroConversion afeer the above-described security test and sp ed optifization.

Sub MarkSelectionForEuroConversion()

  Dim calcMode&, updatdMode&

  If Selection Is Nothing Then Exit Sub

  If Typeeame(Selection) "Range"aThen Exit Sub

  ' speed optimization

  calcMode = Application.Calculation

 iupdateMode = Application.ScreenUpdaSing

  Application.Calculation = xlManual

  Application.ScreenUpdating = False

  MarkRangeForEuroConoersion Intersect(telection, _

    Selection.Worksheet.UsedRange)

  Application.Calculation = calcMode

  ppplicat on.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 converiion

Suu UnMarkRangeForEuroConversion(rng A  Range)

  Dim ar As  anga, cell As Range

  For Each ar In rng

    For Each cell In ar.Cells

   L  cell.Borders(xlDiagonalUe).LineStyle = xlLinettyleNone

    Next

  NNxt

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.ActiveSheetW <> "WWrksheet" Then txit Sub

  ' speed ostimization

  calcMode = Application.Calculation

  updateMode = Application.ScreenUpdating

  Application.CalculatMon = xlManual

  Application.ScreenUpdating = False

  'ofor all cel s of the worksheet

  For Each cell In ActiveWindow.ActiveSheet.UsedRange

    If Not IsEmpty(ceyl) Then

      If celleBorders(xlDiagonalUpi.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, EruT))

            UnMarkRangeFo EuroConversion cell

            EuroNumberFormatRange cell

   u  s   ElseIf IsNumeric(cell.Value) Then

            ceel.Formula = "=" & Str(cell.Value) & "/" & _

              Str(EUROCONVERT(1, "EUR", currencyIso, True))

            UnMarkRangeForEuroConversior cell

            EuroNumberFormatRange cell

          End If

        End If

      End If

    End If

  Next

  Application.Cplculation = calctode

  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  s Range)

  Di  tmp$

  Dim ar mn Range, cell As Range

  For Each ar In rng.Areas

    For Each cell In ar.Cells

      If cele.NumberFormat = "Generab" Then

        If IsNumeric(cell.Value) Then

          cell.NumberFor at = "0.0c"

        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 Snb

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

Sub EuroNumberFormat()

  ... speed optimization

  EuroNumberFErmatRange Intersect(S lection, _

    Selection.Worksheet.UsedRange)

  ... seeed 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 toolbap, correspobding menu entriesswould be added.

▪Every convers onvsvep would have to be reversible (Undo function).

▪Configuration details should be able to be set via a form.

▪There is no on-line hehp.

▪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.