# Using Excel VBA to Create Reciprocal Formulas

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

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:

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
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:

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”)