CODEWITHSUNDEEP
excelvbaauto-update
pickledpickles
pickledpickles

Reputation: 1

Issues on making an excel macro auto update upon changing cells

I am trying to us a macro to execute a Riemann sum and place the answer in an excel spreadsheet, but I cannot seem to make the cells in the spreadsheet auto update upon a changing cell.

Can someone help me troubleshoot this issue?

Here is my code:

Sub RecCoil()

Dim l, radius, D, y, z, H, Temp, Temp1, Temp2, wires, Temp3, f1, f2, B_at_a, F_at_a, B_Avg, F_Avg, a, mass, u_s, I_rails, I_coil, Func, Func1, Func2 As Double
Dim x1, x2, D_Exp, Steps, A_Exp As Integer


l = Cells(1, 3)
radius = Cells(10, 3)
D = Cells(2, 3)
H = Cells(3, 3)
a = Cells(9, 3)
mass = Cells(7, 3)
u_s = Cells(8, 3)
I_rails = Cells(4, 3)
I_coil = Cells(5, 3)

Steps = 10 ^ 4
R = radius * Steps
D_Exp = (D - radius) * (Steps / 5)
A_Exp = a * (Steps / 10)
Temp1 = 0
Temp2 = 0
Temp3 = 0
Temp = 0
Friction = mass * u_s * 9.81
wires = H / radius


For S = 0 To l * (Steps / 10)
    x1 = -S
    x2 = (l * Steps / 10) - S
    Temp2 = 0

    For j = R To D_Exp
        y = 5 * j / Steps
        Temp1 = 0

        For k = -(wires / 2) To (wires / 2)
            z = k
            Temp = 0

            For i = x1 To x2
                x = i / Steps
                f1 = x ^ 2 + z ^ 2
                f2 = D - y
                Func1 = (y / ((y ^ 2 + f1) ^ (3 / 2)))
                Func2 = ((f2) / ((f2 ^ 2 + f1) ^ (3 / 2)))
                Func = Func1 + Func2
                Temp = Temp + Func
            Next i
            Temp1 = Temp1 + Temp

        Next k
        Temp2 = Temp2 + Temp1

    Next j
    If (S = A_Exp) Then
        B_at_a = (Temp2 * 10 ^ -7 * I_coil)
        Cells(1, 7) = B_at_a
        F_at_a = (I_rails * D * B_at_a) - (Friction)
        If (F_at_a > 0) Then
            Cells(2, 7) = F_at_a
        Else
            Cells(2, 7) = 0
        End If
    End If
    Temp3 = Temp3 + Temp2

Next S

B_Avg = (Temp3 * 10 ^ -7 * I_coil) / (l * Steps)
Cells(4, 7) = B_Avg
F_Avg = (I_rails * D * B_Avg) - (Friction)
If (F_Avg > 0) Then
    Cells(5, 7) = F_Avg
Else
    Cells(5, 7) = 0
End If

End Sub

Private Sub WorkSheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Call RecCoil
    Application.EnableEvents = True

End Sub

Thank you for all your help and patience on this problem:)

Upvotes: 0

Views: 282

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

close the file and restart it. Or just type application.enableevents=true, in your immediate window (altF11 and ctrl+G).

i think you had once an error in RecCoil, and the change event doing enableevents=false, when you resumed, it was still in false state, so no events could occur...

Also make sure you are not in Break mode (debugging in VBE) : hit the Stop button (square).

if still not working, you can add a button, from the developer ribbon, wich launchs RecCoil.

Upvotes: 0

Bernard Saucier
Bernard Saucier

Reputation: 2260

Try renaming the second function to 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Also, make sure it is in the "ThisWorkbook" code object.

Upvotes: 1

Related Questions