From your comment above you wanted to try this approach
If you enter
=abb()
into any cell
Then cell A1 of that sheet wil be set to 12333
This is the line to update to pick the cell to update, and to place a value in it
Range("A1").Value = 122333
From I don’t want my Excel Add-In to return an array (instead I need a UDF to change other cells)
I am reposting this piece of magic from Kevin Jones aka Zorvek as it sits behind the EE Paywall (link attached if anyone has access)
While Excel strictly forbids a UDF from changing any cell, worksheet,
or workbook properties, there is a way to effect such changes when a
UDF is called using a Windows timer and an Application.OnTime timer in
sequence. The Windows timer has to be used within the UDF because
Excel ignores any Application.OnTime calls inside a UDF. But, because
the Windows timer has limitations (Excel will instantly quit if a
Windows timer tries to run VBA code if a cell is being edited or a
dialog is open), it is used only to schedule an Application.OnTime
timer, a safe timer which Excel only allows to be fired if a cell is
not being edited and no dialogs are open.The example code below illustrates how to start a Windows timer from
inside a UDF, how to use that timer routine to start an
Application.OnTime timer, and how to pass information known only to
the UDF to subsequent timer-executed routines. The code below must be
placed in a regular module.
Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long _
) As Long
Private Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long _
) As Long
Private mCalculatedCells As Collection
Private mWindowsTimerID As Long
Private mApplicationTimerTime As Date
Public Function abb()
' This is a UDF that returns the sum of two numbers and starts a windows timer
' that starts a second Appliction.OnTime timer that performs activities not
' allowed in a UDF. Do not make this UDF volatile, pass any volatile functions
' to it, or pass any cells containing volatile formulas/functions or
' uncontrolled looping will start.
abb = "Whatever you want"
' Cache the caller's reference so it can be dealt with in a non-UDF routine
If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection
On Error Resume Next
mCalculatedCells.Add Application.Caller, Application.Caller.Address
On Error GoTo 0
' Setting/resetting the timer should be the last action taken in the UDF
If mWindowsTimerID <> 0 Then KillTimer 0&, mWindowsTimerID
mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1)
End Function
Public Sub AfterUDFRoutine1()
' This is the first of two timer routines. This one is called by the Windows
' timer. Since a Windows timer cannot run code if a cell is being edited or a
' dialog is open this routine schedules a second safe timer using
' Application.OnTime which is ignored in a UDF.
' Stop the Windows timer
On Error Resume Next
KillTimer 0&, mWindowsTimerID
On Error GoTo 0
mWindowsTimerID = 0
' Cancel any previous OnTime timers
If mApplicationTimerTime <> 0 Then
On Error Resume Next
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2", , False
On Error GoTo 0
End If
' Schedule timer
mApplicationTimerTime = Now
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2"
End Sub
Public Sub AfterUDFRoutine2()
' This is the second of two timer routines. Because this timer routine is
' triggered by Application.OnTime it is safe, i.e., Excel will not allow the
' timer to fire unless the environment is safe (no open model dialogs or cell
' being edited).
Dim Cell As Range
' Do tasks not allowed in a UDF...
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Do While mCalculatedCells.Count > 0
Set Cell = mCalculatedCells(1)
mCalculatedCells.Remove 1
Range("A1").Value = 122333
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub