VBA excel - change two cell's values based on each other, when either cell is changed

Question

I trying to make a currency converter on excel.

Cell B1 = exchange rate
cell B2 = base currency amount
cell B3 = counter currency amount.

Example:

B1 = 1.3, B2 = Euro amount, B3 = USD amount.

All three cells are connected to one another by a formula B3 = B1*B2.

I want to be able to enter a value at B2, and B3 automatically updates itself (B3 = B2*B1)

When I enter value at B3, and B2 updates itself (B2 = B3/B1).

The below code is the best I can come up with, but it creates an out of stack space error because (I guess, it goes into an infinite loop (when B2 is updated, B3 updates itself, which in turn updates B2 again...).

Any suggestion?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim EUR As Range
    Set EUR = Range("b2")

    Dim USD As Range
    Set USD = Range("b3")

    If Not Application.Intersect(EUR, Range(Target.Address)) Is Nothing Then
        Sheets("sheet1").Range("B3").Value = Range("B2") * Range("B1")
    Else
        If Not Application.Intersect(USD, Range(Target.Address)) Is Nothing Then
            Sheets("sheet1").Range("B2").Value = Range("B3") / Range("B1")
        End If
    End If
End Sub

try to keep the values in integers first, and then calculate. — Guy P, Jan 22 '13 at 07:18
Dear Guy, thanks for your comment, but even if they are all integers it still won't work. All cells are supposed to be numbers, not formula. — user1586765, Jan 22 '13 at 07:41
See this http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 — Siddharth Rout, Jan 22 '13 at 07:53
@user1586765 will you be downloading `Curr rates` from a third party data vendor? If so solutions could be proposed accordingly. — bonCodigo, Jan 22 '13 at 07:56
did any of the time we spent writing answers help? if so you can either up-vote attempts or mark one of them as correct — whytheq, Jan 24 '13 at 08:29
Does this answer your question? [Why MS Excel crashes and closes during Worksheet\_Change Sub procedure?](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — ivan_pozdeev, Apr 13 '21 at 16:17

score 1 · Answer 1 · answered Jan 22 '13 at 08:01

1

If the crash is indeed because of an infinite loop, you could try disabling Excel events in the beginning of your macro, and re-enabling them in the end.

Snipplet, based of this post:

Application.EnableEvents = False

' Your code

Application.EnableEvents = True

answered Jan 22 '13 at 08:01

shinjin

2,858
5
29
44

1

Seems like you missed my link in the comment that I posted above? :) – Siddharth Rout Jan 22 '13 at 08:50
1

Seems that comments are not answers, @SiddharthRout . If your link answers the question, you should've posted its gist as an answer, or flagged the question as a duplicate – ivan_pozdeev Apr 13 '21 at 16:16

score 0 · Answer 2 · answered Jan 22 '13 at 09:10

something like this would be a bit easier to read. You might need to play around with the formulas as I'm not 100% sure what you're doing:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$2" Then
    Range("B3") = Range("B2") * Range("B1")
End If

If Target.Address = "$B$3" Then
    Range("B2") = Range("B3") / Range("B1")
End If

End Sub

VBA excel - change two cell's values based on each other, when either cell is changed

2 Answers2