Function to return a previous cell value

[Guest]

I want to write a VBA function to do the following:

Function resides in cell B1 and references the value of A1 (A1 references
other parts of the workbook).

Value of A1 is 5, so value of B1 is also 5.

User changes some input data and A1 changes to 17, so B1 is now 17.

User makes additional changes resulting in A1 changing to *KEY_ERR.

VBA function in B1 tests for this condition and returns the last good value
in A1 (17).

Any suggesstions?

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
If .Value <> "*KEY_ERR" Then
.Offset(0, 1).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

Here's one approach that works reliably in the limited testing I've
done on it.

Option Explicit

Function NewNonErrorValue(x As Range)
If x.Cells.Count > 1 Then
NewNonErrorValue = _
"This version accepts only single cell ranges"
Exit Function
End If
Static OldValues As Object
If OldValues Is Nothing Then
Set OldValues = CreateObject("scripting.dictionary")
'could also use a collection -- I think
End If
If Not IsError(x.Value) Then
OldValues.Item(x.Address) = _
x.Value * 2 'whatever calculation is really required
End If
On Error Resume Next
NewNonErrorValue = OldValues.Item(x.Address)
On Error GoTo 0
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Well, you could use the same concept I demonstrated in my post. If the
returned value is a legitimate one, store it in a collection (with the
caller.address as the index) and return it. If the return value is an
error value, return the value in the collection and discard the error
value.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions