How can I convert between 2 currencies USD and AED @rate of 3.68 throughout the rest of my workbook but only in selected cell ranges on each sheet?

Question

For example. I want my intro sheet "Main Sheet" to have an option to switch the workbook between currencies. USD and AED at the rate of 3.68. Some cells are referencing other cells in different sheets, so I don't want to change the cell references, I only need to calculate the rate in specific cells within each sheet.

How can I accomplish this preferably using a check box or button for easy converting from the start. I'm using excel for Mac. Thank you

Answer 1

I'm going to assume that you want to have the conversion on the input cell and not all of your cells are formulas and that a lot of the cells you want to convert are values. You should seriously consider the answer to split out input vs display, it will be much more foolproof and protected from any logic that may break your workbook.

If you're keen on this pathe then do the following, but, before you do ... BACKUP YOUR WORKBOOK. Any tests I've done with the below code are not breaking but I don't have your workbook, therefore, I make no guarantees.

Firstly, you need a cell that gives you the current exchange rate. You need to give that cell a named range of ExchangeRate.

In my workbook, that cell contains a formula ...

=IF(B1="USD",1,3.68)

It looks like this ...

... and cell B1 has a validation attached to it that allows you to select from 2 currencies, AED or USD.

You said you want to be able to ensure that only a selection of cells will be converted. To make sure we ring fence just those cells, you need to create a named range ON EACH SHEET that includes all of those cells.

The name of that range needs to be called CellsToConvert and you can do that through the Name Manager. When creating the named range, make sure you specify the worksheet you're creating it for, do not selected the "Workbook" option.

... the below shows the sporadic range I used on the first sheet. All coloured cells a part of that range. The green cells contain values and the yellow cells contain formulas.

At the end of the day, that range can be huge and across different sheets but it should work.

Now, add the following code into the ThisWorkbook object within the VBA editor ...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim objCell As Range, dblExRate As Double, strFormula As String, objSheet As Worksheet
    Dim strNewFormula As String, strOpeningChar As String, bIsFormula As Boolean
    Dim objCells As Range, strError As String, strExRateRangeName As String

    strExRateRangeName = "ExchangeRate"

    dblExRate = Range(strExRateRangeName)

    Application.EnableEvents = False

    For Each objSheet In Worksheets
        On Error Resume Next

        strError = ""

        Err.Clear

        Set objCells = objSheet.Range("CellsToConvert")

        strError = Err.Description

        On Error GoTo 0

        If strError = "" Then
            For Each objCell In objCells
                strFormula = objCell.FormulaR1C1
                bIsFormula = False

                ' Check to make sure this field contains a formula.
                If Left(strFormula, 1) = "=" And objCell.NumberFormat <> "@" Then
                    bIsFormula = True
                End If

                If dblExRate = 1 Then
                    ' Base currency selected.
                    ' Check to see if the cell contains a formula, if it does,
                    ' convert it back to a value
                    If bIsFormula Then
                        ' It's a formula and the cell is not set to text, proces it back
                        ' to its original value, that could still be a formula.

                        ' Remove all of the exchange rate components we would've added as
                        ' a part of this routine.
                        strNewFormula = Replace(strFormula, ") * " & strExRateRangeName, "")

                        ' Check to see if the formula has changed against the previous statement,
                        ' if it has, then it contained the custom additions, otherwise, it didn't.
                        If strFormula <> strNewFormula Then
                            strNewFormula = Mid(strNewFormula, 3)

                            ' Check to see if the new value is numeric, if it is, remove the leading
                            ' equals sign as it wasn't originally a formula, or, at least it doesn't
                            ' need to be a formula.
                            If IsNumeric(strNewFormula) Then
                                objCell.Value = strNewFormula
                            Else
                                objCell.FormulaR1C1 = "=" & strNewFormula
                            End If
                        End If
                    End If
                Else
                    ' Something other than the base currency has been selected.
                    strNewFormula = objCell.FormulaR1C1

                    If InStr(1, strNewFormula, strExRateRangeName, vbTextCompare) = 0 Then
                        If bIsFormula Then strNewFormula = Mid(objCell.FormulaR1C1, 2)

                        objCell.FormulaR1C1 = "=(" & strNewFormula & ") * " & strExRateRangeName
                    End If
                End If
            Next
        End If
    Next

    Application.EnableEvents = True
End Sub

... once you've done all of the above, it should work for you. Performance could be tested if the workbook is large but that's something you'll need to check for yourself.

If you change a cell and it's within one of those ranges AND the currency of USD is not selected, you'll see the input value changed to a formula after you hit enter. That's pretty neat when you think about it but may not be for you.

One last thing to note, if your range contains broken links, the calculation for that sheet will fail and my code will not notify you of that.

This adds another option for you but is riskier than the first answer. There's nothing like options. :-)