Selected Answer
DJones
This illustrates a different approach to using a UDF.
In the attached revised file, column A identifies some items (inputs and results) and column B holds a numerical value. Cells B9 and B10 hold a simple formula using the 5 inputs but it could be anything.
Click the green "Vary Input(s)" button (shape) and it launches a simple UserForm via this macro (in Module 2):
Sub RectangleRoundedCorners1_Click()
UserForm1.Show
End Sub
The bold line initialises the UserForm via the code below. You'll see it displays whatever the input names are (which you can change but not live) and shows current values in grey boxes. Change an value and press enter and that box goes white (to show which ones you have) and the new calculation values are shown.
At the top I declare some variables (whose value will be held) to simplify writing the other macros.
Dim FirstInput As Range, FirstOutput As Range, UF_EnableEvents As Boolean
Private Sub UserForm_Initialize()
'prevent other triggers
UF_EnableEvents = False
' say where the inputs and outputs start
Set FirstInput = Sheet1.Range("A1")
Set FirstOutput = Sheet1.Range("A9")
' add input labels and values
With FirstInput
For n = 1 To 5
Me.Controls("Label" & n).Caption = .Offset(n - 1, 0).Value
Me.Controls("TextBox" & n).Value = .Offset(n - 1, 1).Value
'link to input spreadsheet cell
Me.Controls("TextBox" & n).ControlSource = FirstInput.Parent.Name & "!" & FirstInput.Offset(n - 1, 1).Address
Next n
End With
' add result labels and values
With FirstOutput
For n = 1 To 2
Me.Controls("Label" & 29 + n).Caption = .Offset(n - 1, 0).Value
Me.Controls("TextBox" & 29 + n).Value = .Offset(n - 1, 1).Value
Next n
End With
UF_EnableEvents = True
End Sub
When an input value changes, it triggers codes like this
Private Sub TextBox1_AfterUpdate()
Call VaryInput(1)
End Sub
Private Sub TextBox2_AfterUpdate()
Call VaryInput(2)
End Sub
which just pass a different number to this macro:
Private Sub VaryInput(n As Long)
' don't do this on initialisation
If UF_EnableEvents = False Then Exit Sub
Me.Controls("TextBox" & n).BackColor = vbWhite
' update result values
With FirstOutput
For n = 1 To 2
Me.Controls("TextBox" & 29 + n).Value = .Offset(n - 1, 1).Value
Next n
End With
End Sub
The idea is this one bit of code can be extended or corrected easier than doing 5 or even 20+ sets of code.
If this approach appeals, it could be extended to many more inputs (with different "start" locations). You could take inputs and values from diverse places on a spreadsheet.
Note that I haven't impemented code for the button to retore values (but it wouldn't be hard to do).
MAJOR REVISION: 10 May 2023
Further to extensive discussions, the approach below allows users to write a UDF which writes values to cells in another sheet, returns a simple calcutation. The UDF formula can be editted later too.
The second attached file below (UDFake v0_a.xlsm) implements a UDF called UDFunction in cells A10 to D10 (which can be extended) and uses 3 arguments (but could be many more).
If that UDF and 3 numbers are entered in cell B10 of worksheet UDF sheet, say:
=UDF(5,2,8)
it returns value 2 which is from D6 of "Calculation sheet", whose formula is just:
=A1*B2-C3
whose values have been written as 5, 2 and 8 respectively. Enter B10 again and you can change the arguments- press enter though.
When the UDFunction is entered, it calls this in Module1:
Public Function UDFunction(Arg1 As Variant, Arg2 As Variant, Arg3 As Variant) As String
UDFunction = "#Please Enter#=UDFunction(" & Arg1 & "," & Arg2 & "," & Arg3 & ")"
End Function
and briefly returns the formula string (which is used later) but starting "#Please Enter#".
That triggers this event macro (commented for guidance) with two public variables:
Option Explicit
Public UDFake As String, Prmpt As String
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.CountLarge > 1 Or Intersect(Target, Range("A10:D10")) Is Nothing Then Exit Sub
Dim n As Long, InputAdds As Variant, ResultAdd As String
' state addresses for inputs on calculation sheet...
InputAdds = Array("A1", "B2", "C3")
' ... and where the result is found
ResultAdd = "D6"
Application.EnableEvents = False
Dim Args As Variant
If InStr(Target.Value, Prmpt) = 1 Then
' save the formula
Sheet2.Range(Target.Address) = Target.Value
' strip out function and brackets then put arguments in an array,
Args = Replace(Target.Value, Prmpt & UDFake & "(", "")
Args = Left(Args, InStrRev(Args, ")") - 1)
Args = Split(Args, ",")
' Write arguments to calculation sheet
With Sheet3
For n = 0 To UBound(Args)
.Range(InputAdds(n)) = Args(n)
Next n
' return result value
Target.Value = .Range(ResultAdd).Value
End With
End If
Application.EnableEvents = True
End Sub
You'll see that the formula string gets saved in the corresponding cell in a hidden sheet (which you might want to make xlVeryHidden if you use this approach).
Those public variables get set when a cell in A10:D10 is selected. This event code also retrieves the formula for editting:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Or Intersect(Target, Range("A10:D10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
'set a prompt string
Prmpt = "#Please Enter#"
UDFake = "=UDFunction"
With Sheet2.Range(Target.Address)
If Not IsError(.Value) Then
If InStr(.Value, UDFake) >= 1 Then
' restore saved formula to formula bar without prompt string
Target.Value = Replace(Sheet2.Range(Target.Address).Value, Prmpt, "")
End If
End If
End With
Application.EnableEvents = True
End Sub
Hope this helps. If so, please remember to mark this answer as Selected.