Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    applying exchange rates (xl97 / xl2000)

    I have a financial model that will now be used internationally and one of the many things that needs to be dealt with is the currency rate. Any cells that contain currency need to have the appropriate exchange rate applied to them.The user inputs the US to Euro (or Pound Sterling or whatever) from an InputBox that is triggered from a set of radio buttons. This is then stored in a named variable for the model to use.

    I now have to apply this rate to all the currency cells on all the worksheets. I would have thought there was an easy way to do this in Excel but I can't find it. What I am doing now is applying the exchange rate at each sheet's activate event. I also reformat the cell's display for USD or Euro or Pound as appropriate. Is there a more efficient way to do this?

    Deb <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: applying exchange rates (xl97 / xl2000)

    There's no built in conversions that I know of. Use a change event or a userform as trigger, and either name all the ranges to be converted and apply your conversion code, or call something like this (which is untested and in turn needs it's own conversion and currency format pseudofunctions, and may be sloppier than you already have):

    Sub CurrConv()
    Dim shtWS As Worksheet, rngCL As Range
    For Each shtWS In ActiveWorkbook
    If Not Worksheets(ActiveSheet.Name).ProtectContents Then
    For Each rngCL In shtWS.UsedRange
    If rngCL.NumberFormat = "something" Then
    rngCL.Value = new_currency_convert_calc(rngCL.Value)
    rngCL.NumberFormat = new_currency_format
    End If
    End If
    Next shtWS
    End Sub

    Would it work to keep several of the major currencies in view for the user, and just have a series of ranges which show any other single currency they select?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: applying exchange rates (xl97 / xl2000)

    I thought the answer would be, "no Excel has now to automate currency conversions." RATS! It seems like an obvious feature of a tool that lives on numbers to do that.

    Your code is pretty similar to what I did. I have US$ to Euro and US$ to Pounds Sterling. The user enters the exchange rates and when the sheet is activated, I do the conversion. I needed this because my company's sales catalog lists US$ but the end user will be entering numbers in their own currency so we need to let them see the US$ pricing too.

    Thnx, Deb <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •