Here's a way to use that UDF for a spreadsheet function.
Put this in a normal module
[VBA]Function SUBVALS(inRange As Range) As String
Application.Volatile
On Error Resume Next
With Application.Caller.Validation
SUBVALS = .ErrorMessage
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="=TRUE"
On Error GoTo 0
.InputMessage = inRange.Range("a1").Address(, , , True)
.ShowInput = False
.ShowError = False
.ErrorMessage = SUBVALS
End With
End Function[/VBA]
And this in the sheet's code module
Private Sub Worksheet_Calculate()
Dim oneCell As Range
On Error Resume Next
If Me.Cells.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub
For Each oneCell In Me.Cells.SpecialCells(xlCellTypeAllValidation)
If oneCell.Formula Like "*SUBVALS(*" Then
With oneCell.Validation
.ErrorMessage = ValuesInsteadOfPrecedents(Range(.InputMessage))
End With
End If
Next oneCell
On Error GoTo 0
Application.EnableEvents = False
Calculate
Application.EnableEvents = True
End Sub
Then, when put in a cell, =SUBVALS(A1) will return the formula in A1 with cell references replaced by values.
(note: SUBVALS will error if called from a VB routine)