January 16, 2022

Using Excel VBA to Create Reciprocal Formulas

How to use worksheet change event in Excel VBA to create reciprocal formulas. Watch the video below:

Macro to develop reciprocal formulas

How can we convert millimeter (mm) to feet (ft) and vice versa. For example, we can enter a meter value in a cell and use the convert formula to get a result in inches. The image below describes the conversion formula:

Convert meters to inches using the convert formula
Using the CONVERT formula in Excel to get the meter value in inches

If we try to enter a value in the formula cell, the formula is lost. Also we cannot change the inches value to derive a reciprocal meter value. How do we solve such a problem? We can use a worksheet change event to get the value in feet or inches when we enter a meter value and we can get a meter value if we enter a new inches value in the neighboring cell. Now we have achieved reciprocity! The following macro or VBA code will automate the conversion process:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address
Case “$I$3”
[J3] = [I3] * 25.4
Case “$J$3”
[I3] = [J3] / 25.4
End Select
Application.EnableEvents = True
End Sub

The image below shows how the macro is connected to the worksheet change event:

Macro code to create reciprocal formula using worksheet change event
Macro or VBA code connected to the worksheet

In a similar fashion you can create a reciprocal currency converter:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim Myrate As Long
Myrate = Range(“K3”)
Select Case Target.Address
Case “$I$3”
[J3] = [I3] / Myrate
Case “$J$3”
[I3] = [J3] * Myrate
End Select
Application.EnableEvents = True
End Sub

Reciprocal Formula for Currency Conversion
Reciprocal Formula for Currency Conversion

Further Reading:

Currency Data Type