How to create a function or sub to handle hysteresis

[mathisp64]

Greetings Everyone,
This is my first post. I have used Excel functions for sometime, but I am new to VBA programming. I mostly use Excel for electronic circuit equations and converting raw test data to useable data that can be plotted.

I have question regarding programming a hysteris function or sub. The problem is: Cell A1 is assigned a value of 0% until Cell B1 is equal to or greater than 2%. Cell A1 will remain 1% until Cell B1 is less than 1%, below this value cell A1 returns to a value of 0%.

I can't find a way to directly input an Excel function into a cell for this problem. I wrote a simple VBA sub that partially works, but just isn't cutting it.

Any assistance would be greatly appreciated.

 

[wolverineb]

I think this works. You'll have to test it out.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 2 Then 'B1 is what changed
        Dim dPreviousValue As Double, _
            dNextValue As Double
        dNextValue = Me.Range("B1").Value
        Application.EnableEvents = False
        Application.Undo
        dPreviousValue = Me.Range("B1").Value
        Application.Undo
        Application.EnableEvents = True
        If dPreviousValue < 0.02 And dNextValue >= 0.02 Then
            Me.Range("A1").Value = 0.01
        ElseIf dPreviousValue >= 0.01 And dNextValue < 0.01 Then
            Me.Range("A1").Value = 0
        End If
    End If
End Sub

 

[tusharm]

You can solve this with a simple formula and iterative calculations. First enable iterative calculations (in 2007 Office button | Excel Options | Formulas | Calculation Options section. Select 'Enable iterative calculation' and set Max Iterations to 1.

Now, in A1 enter the formula =IF(A1=0,IF(B1>2%,1,A1),IF(B1<1%,0,A1))

In 2003 or earlier see Tools | Options...

Greetings Everyone,
This is my first post. I have used Excel functions for sometime, but I am new to VBA programming. I mostly use Excel for electronic circuit equations and converting raw test data to useable data that can be plotted.

I have question regarding programming a hysteris function or sub. The problem is: Cell A1 is assigned a value of 0% until Cell B1 is equal to or greater than 2%. Cell A1 will remain 1% until Cell B1 is less than 1%, below this value cell A1 returns to a value of 0%.

I can't find a way to directly input an Excel function into a cell for this problem. I wrote a simple VBA sub that partially works, but just isn't cutting it.

Any assistance would be greatly appreciated.

 

[mathisp64]

Thanks for both replies. Changing the iteration to 1 allowed the Excel function to work. The VBA code will probably work also, but I failed to mention that I am using worksheet four which I name N2GOV. Since I'm new to VBA it is excellent opportunity for me to go back and and figure out the code changes needed to make it work. Thanks again.

 

[HalfAce]

Since I'm new to VBA it is excellent opportunity for me to go back and and figure out the code changes needed to make it work.

Excellent. I like seeing this. Welcome to the board.