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?

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)


"Mitch Powell" <Mitch wrote in message
...
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?

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

In article , =?Utf-
8?B?TWl0Y2ggUG93ZWxs?= <Mitch says...
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?

Thanks - That works great given my example -:) but upon trying to implement I
realized the example is not quite realistic. I'll be more specific:

Cell A1 has a reference to an external database that a user of the workbook
may not have a reference to. The user that saved the workbook had a valid
reference and therefore cell A1 has a good value. When the user without the
reference to the DB opens the workbook and calcs, the reference will return
an error. What I need to be able to do is have a "wrapper" function around
the external DB function that will capture the value of the cell before a
recalc reteives an error:

=WapperFunction(DBRef)

I've tried a function trying to capture the value of the calling cell before
a recalc using Application.Caller.Value but this creates a circulare
reference and doesn't work.

Function WrapperFunction(val)

Dim x

x = Application.Caller.Value

If IsError(val) Then
WrapperFunction = val
Else
WrapperFunction = val
End If

End Function


----------------- I'm stumped.


"Tushar Mehta" wrote:

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

In article , =?Utf-
8?B?TWl0Y2ggUG93ZWxs?= <Mitch says...
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?

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

In article ,
says...
Thanks - That works great given my example -:) but upon trying to implement I
realized the example is not quite realistic. I'll be more specific:

Cell A1 has a reference to an external database that a user of the workbook
may not have a reference to. The user that saved the workbook had a valid
reference and therefore cell A1 has a good value. When the user without the
reference to the DB opens the workbook and calcs, the reference will return
an error. What I need to be able to do is have a "wrapper" function around
the external DB function that will capture the value of the cell before a
recalc reteives an error:

=WapperFunction(DBRef)

I've tried a function trying to capture the value of the calling cell before
a recalc using Application.Caller.Value but this creates a circulare
reference and doesn't work.

Function WrapperFunction(val)

Dim x

x = Application.Caller.Value

If IsError(val) Then
WrapperFunction = val
Else
WrapperFunction = val
End If

End Function


----------------- I'm stumped.


"Tushar Mehta" wrote:

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

In article , =?Utf-
8?B?TWl0Y2ggUG93ZWxs?= <Mitch says...
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?