Bon Mot
By Ivor Davies
Using the PrivateProfileString Property to Handle Europe's New Currency
In just a few months there's going to be a new kid on the block. He may seem like a small guy now — perhaps you've not even heard of him yet — but by the time we get into the next century, he'll have grown into a real heavyweight you won't want to ignore.
Starting January 1, 1999, companies and individuals will be able to pay their bills using the Euro, the new single currency of the European Union (EU). On January 1, 2002, Euro notes and coins will be issued, and by July of that year the Euro will have become the sole legal tender in all the participating states of the EU. More than 300 million people will use the new currency, forming one of the largest single-currency markets in the world. If you are trading with France, Germany, Italy and eventually the United Kingdom, the Euro is a currency you'll be referring to again and again.
The Euro and You
In recognition of the Euro's important role, and in honor of its beautiful symbol based on the Greek letter Epsilon (see FIGURE 1), I've prepared an easy method for incorporating it into your Word documents, and for converting any numerical amount from United States dollars into Euros. The technique uses the versatile power of the System.PrivateProfileString property to set and return the conversion rate.
FIGURE 1: The symbol for the new Euro.
Even if you're not yet interested in the Euro, you can still use the techniques presented here to convert to and from any currency. You can even use these techniques to add sales tax to prices in a Word document.
If you don't want to use the Euro symbol, substitute the currency symbol of your choice in everything that follows. If you do, download the new Tahoma font and install it following these simple instructions (see end of article for download details). First, open a blank Word document and select Insert | Symbol. In the resulting dialog box choose Tahoma in the Font textbox and Currency in the Subset textbox. The Euro symbol should be on the second line, about two-thirds of the way across to the right. Insert it into your document, highlight it, and create an AutoText entry for it called Euro. Discard the document and close Word to save your Normal.Dot template with the new AutoText entry.
The next thing you must do is check to see if you have a file on your system in your Windows folder called Winword7.INI. If it doesn't exist, open Notepad or any other text editor (don't use Word for this!) and create a new file in your Windows folder with that name and extension (you must specify the .INI extension). Next, whether or not you've had to create the file, open it, again using a text editor only, and add the following to the first empty line:
[My Private Settings]
Euro = 2.00Save the file and exit.
Now we're going to start the real work in Word 97. Open it and press A! to open the VBA editor. Select Insert | Procedure, and type in the following:
EuroChangeClick OK. The VBA editor then creates an empty function. Scroll down to it, and between the lines:
Public Function EuroChange()
End Functionenter the code shown in FIGURE 2.
Public Function EuroChange()
' Use variant type so our error message gets called.
Dim strEuro As Variant
Dim strResponse As String
' Get the existing exchange rate from the PPF.
strEuro = System.PrivateProfileString( _
"C:\windows\winword7.ini", _
"My private Settings", "Euro")
' Call a message box that shows existing rate (strEuro)
' and asks user if he or she wants to change it.
strResponse = MsgBox("The current rate is " & _
strEuro & vbCr & _
"Select 'Yes' to enter a new rate or 'No' to cancel", _
vbYesNo, "Euro-$ Rate")
' If the answer is Yes, an Input box is called to receive
' the new rate, which then changes the rate in the PPF.
If strResponse = vbYes Then
strEuro = _
InputBox("Input the new rate for the Euro.", _
" Amend Euro Rate")
Else
Exit Function
End If
' Test if input is numeric and without $ sign.
If IsNumeric(strEuro) Then
System.PrivateProfileString( _
"C:\windows\winword7.ini", "My private Settings", _
"Euro") = strEuro
Else
' If not, then displays a message and ends function.
MsgBox "Wrong data type. Use numerals only. " + _
"Do not use the $ sign.", vbCritical, _
"Input Error"
Exit Function
End If
End FunctionFIGURE 2: Users can view the existing exchange rate, then change it if necessary.
What you've created is a method that allows your users to view the existing exchange rate, then change it if necessary. You've also used the IsNumeric function to ensure that the input will accept only numeric data; otherwise the system would crash at the first typo — and well designed procedures shouldn't do that.
Now we must create a second function, using the same method as the first, but this time we'll call it EuroConvert. Between the lines:
Public Function EuroConvert()
End Functionenter the code shown in FIGURE 3.
Public Function EuroConvert()
Dim strEuro As String
Dim DblOV As Double
Dim lngNV As Long
Dim Range1 As String
Dim Trange1 As String
Dim dblRange1 As Double
Dim strD As Double
Dim strL As Long
' Point to the error handler.
On Error GoTo errhandleconvert
' Select the left-most word to the cursor and its
' preceding character so as to include the $ symbol if
' it exists.
Selection.MoveLeft Unit:=wdWord, Count:=1, _
Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, _
Extend:=wdExtend
' Set the active document, and the range we'll be
' operating on to the selection.
With ActiveDocument
Range1 = Selection.Range
' Determine if the $ is used.
If Left(Range1, 1) = "$" Then
' Count number of characters in the selected string.
strL = Len(Range1)
' Set the new number of characters as 1 less.
strD = (strL - 1)
' Set the new string as that number of characters
' from the right, thus cutting out the first left
' character.
dblRange1 = Right(Range1, strD)
Else
' If no $ is used, then whole string is used.
dblRange1 = Range1
End If
End With
DblOV = dblRange1
' Get the exchange rate from the PPF.
strEuro = System.PrivateProfileString( _
"C:\windows\winword7.ini", _
"My private Settings", "Euro")
' Multiply the PPF value by the value from the document
' Use "Clng" only if you want the result to be rounded to
' the nearest integer. If you don't want the calculation
' rounded, you must change the Dim statement for the
' variable lngNV from Long to Double.
lngNV = CLng(strEuro * DblOV)
' Insert the results back to document together with
' Euro symbol.
Selection.InsertAfter Text:=" "
Selection.InsertSymbol Font:="Tahoma", _
CharacterNumber:=8364, Unicode:=True
' Insert the new amount.
Selection.InsertAfter Text:=" " & lngNV & " "
' Reset text to original font (customise this to suit
' your template).
Selection.Font.Name = "Times New Roman"
Exit Function
' Error handling.
errhandleConvertexit:
Exit Function
errhandleconvert:
' If user has selected inappropriate data;
' Err.Number 13 = type mismatch.
If Err.Number = 13 Then
MsgBox "An error has occured." & vbLf & _
"You have tried to convert an inappropriate " + _
"symbol or letter." & vbLf & "Check the " + _
"location of the cursor and try again.", _
vbCritical, "Error"
Resume errhandleConvertexit
Else
' Otherwise something else is wrong.
MsgBox "An error has occured." & vbLf & Err.Number & _
" " & Err.Description & vbLf & _
"Please report this to your IT manager", _
vbCritical, "Error"
End If
Resume errhandleConvertexit
End FunctionFIGURE 3: Code for converting and inserting the Euro symbol.
Note that, on your system, the statement:
Selection.InsertSymbol Font:="Tahoma", _
CharacterNumber:=8364, Unicode:=Truemay not produce the results you want, or may even produce an error. This is because of the way Windows stores information about fonts. If you have a problem, insert the Euro symbol into a blank document and create an AutoText entry for it. Then, using the macro recorder, record a new macro that opens AutoText and inserts the Euro symbol. Stop the recorder, and examine the macro you've just created. Ignoring the other lines, if the one that begins:
Selection.Insert.Symbolis the same as the line shown in FIGURE 3, then everything is fine. If not, substitute the line in your macro for the one in this listing.
You Get the Gist
That's the meat and potatoes. The function selects the preceding string, strips out any non-numerical characters (or raises an error if it can't), retrieves the conversion rate, multiplies it by the input and pastes the resulting figure — along with the Euro symbol — back into the document in place of the original figure. And it does all this so quickly that users think it's magic. The reason it's so fast is that the Winword7.INI file is loaded into memory when Word opens, so access to it is virtually instantaneous.
All that remains is to give the user a way to access these goodies. Return to the VBA editor and go to the last line of the second of the two functions you've just created (again, see FIGURE 3). On the next line down, type:
Sub CallEuroChange()
Call EuroChange
End SubThen, on the next line, type:
Sub CallEuroConvert()
Call EuroConvert
End SubSave the file and return to Word. From the toolbar choose Tools | Customize and select the Commands tab in the Customize dialog box. Then select Macros in the Categories window. On the right-hand side you'll now see the two Sub procedures we've just created: CallEuroChange and CallEuroConvert. Drag these to your toolbar, then customize the appearance of the resulting buttons to suit your taste. The first calls the macro to set a new rate, the second to do the conversion. Now that they're in place, the job is done.
Seeing Is Believing
Try it out. Type in some text and an amount in U.S. dollars, say $23456 (see FIGURE 4). With the cursor at the end of the numerical sequence, click on the button that activates CallEuroConvert. Like magic, the amount will be replaced by the same amount times 2, and the $ by the Euro (see FIGURE 5). Next, activate CallEuroChange, enter a new rate, and repeat the calculation. The new rate will be used. If you open the Winword7.INI (and only open it with a text editor such as Notepad), you'll find whatever rate you've put in, sitting there waiting for you to use.
FIGURE 4: Enter an amount to test the conversion process ...
FIGURE 5: ... and quickly get the results.
ConclusionOf course, these functions can be used to convert any currency into another — US$ into CAN$, Pesos into US$, US$ into £, etc. It can also be used for any other simple calculation. Instead of converting currencies, you could use it to add sales tax to the base price. One of the beauties of using the PPF is that it can hold any number of different strings. You could add a new line for CAN$, or have a different line for different sales tax rates. You could use it for any number of currencies, tax bands, or even to increment invoice numbers. All you have to do is add a new heading to the Winword7.INI file, and change the reference to it in your function.
There are three serious contenders in the office-suite market: Microsoft, Corel, and Lotus. They all rely on business customers for their revenues, and all of them have a duty to ensure their products provide the up-to-date tools their users need. At the time of this writing, only one company offers its customers support for the Euro. Which one? You guessed it: Microsoft. Corel and Lotus leave you in the dark about how to incorporate the new Euro symbol into documents raised on their programs. Microsoft not only has a technical support area on the subject up and running now — with full details about how each of its programs will handle the new symbol — but it also has links to a new version of the Tahoma font incorporating the Euro that you can download from http://www.microsoft.com/msoffice/office/euro/euro1.asp. You'll find links to download the new font, and also a link to the main information site for the new Euro.
The Euro marks a new era for the citizens of the EU. I hope this project, in its own small way, will mark a new era in what Microsoft's Word can do for you.
Download source code for this article here.
Ivor Davies owns Impro-Data (www.Impro-Data.com), a software design and constancy company based in London, England. Impro-Data specializes in designing and implementing business problem-solving software based on the Office Suite, for small to medium-sized companies. Impro-Data will be opening an office in Calgary, Alberta, Canada in the summer of this year. Ivor can be contacted at ivordavies@dial.pipex.com.