Cannot VBA write data to cells in Excel 2007/2010 within a function

Question

I want to set value for cells by VBA. I have googled, and see some resolution:

Sheets("SheetName").Range("A1").value = someValue
Sheets("SheetName").Cells(1,1).value = someValue

With this kind of code, I can just read data from cell A1 but I cannot set a new value to it.

Update

The code to set cell A1 value is put within a Function as below.

Function abb()
    Sheets("SheetName").Range("A1").value = 122333
    abb = 'any thing'
End Function

In cell B2, I set =abb() and hit enter. I get #VALUE but nothing happen at A1.

Putting this code in a macro, it works.

My question is, how to make A1 have values within a function?

Answer 1

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

Answer 2

You cannot change cell A1 with a function in B2.

Visit: Description of limitations of custom functions in Excel . The text includes:

"A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

• Insert, delete, or format cells on the spreadsheet.
• Change another cell's value. [My highlighting]
• Move, rename, delete, or add sheets to a workbook.
• Change any of the environment options, such as calculation mode or screen views.
• Add names to a workbook.
• Set properties or execute most methods."

Why do you want to change cell A1 in this way? Explain your objective and perhaps someone can help.