Cannot VBA write data to cells in Excel 2007/2010 within a function
Solution 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
Solution 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.
Solution 3
If you want to modify two cells with one formula, you may want to consider returning an array from your function. Here's an example:
Function abb()
Dim arr As Variant
ReDim arr(1 To 2)
arr(1) = "aardvark"
arr(2) = "bee"
abb = arr
End Function
Select cells A2 to B2. Type =abb()
and press ShiftCtrlEnter to specify that it is an array formula. This formula then modifies both cells (A2 and B2) at the same time.
Perhaps you can customise this to do what you want.
Comments
-
Davuz almost 2 years
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?
-
Davuz about 12 yearswhen i debug, i got stop at statement "Sheets("Sheet1").Range("A1").Value2 = ". I don't know why?
-
Anantha Sharma about 12 yearsWhat is the name of the sheet, if its not
Sheet1
then the issue will come.. try this insteadSheets(1).Range("A1").Value
, the index will help in finding the correct sheet, once this works you can replace the index with the actual sheet name. -
shahkalpesh about 12 years@Davuz: What do you mean by "got stop"? Does it throw any error? Is the workbook opened readonly?
-
Davuz about 12 years@shahkalpesh when i debug, it was run to that statement and throw exception (maybe...) and in excel output "#VALUE"
-
Anantha Sharma about 12 yearssay
MsgBox someValue
this will say what the msgbox contains.. if the excel cell is getting updated then the statementSheets("Sheet1").Range("A1").Value2=someValue
is working... -
Davuz about 12 years@AnanthaSharma oh, i put
Sheets("Sheet1").Range("A1").Value2=someValue
in Sub and calling by press F8, it's work. but if call this Sub in another function, it's not work. why... -
Anantha Sharma about 12 yearsit should work.. check the value of someValue.. if you still continue to face issues. you can try and upload the excel (or a simpler version of it) with your question and I'll take a closer look.
-
Davuz about 12 years@AnanthaSharma Yes it works for macro. But doesn't work for function. I have updated my question above. Please help
-
brettdj about 12 yearsIt can actually be done, but it is complex. see stackoverflow.com/q/8520732/641067
-
Tony Dallimore about 12 years@brettdj. I missed this question and your answer which is very interesting. However, this is not a technique I would wish to recommend to a newbie. I think this is a case of the questioner telling us how he thinks his problem can be solved rather than what his problem is. I want to know why he want a function in B2 to change A1?
-
Davuz about 12 years@TonyDallimore i want cache value. Assume i calculate sum of very many cell
Range(B1:B1000)
, i want cache this sum value to A1 (or any cell), so i reopen excel file, excel doesn't need recalculate -
Davuz about 12 years@brettdj i have read (stackoverflow.com/q/8520732/641067) , but i don't know how to call
AfterUDFRoutine2
with arguments? -
brettdj about 12 years@Jean-FrançoisCorbett I agree. I was staggered that someone was able conceive of then execute this. Although it does show the danger of "it can't be done".
-
Tony Dallimore about 12 years@Davuz. You want to cache the result of a complex calculation so the complex calculation is not repeated. You will need some system of (1) knowing the complex calculation is required, (2) performing it and saving the result and (3) stopping it from happening again. You also need this system to be better than Excel's system for controlling recalculation. Having you tried timing the recalculation? How much time do you want to save and how frequently?
-
Davuz about 12 years@TonyDallimore Thank you! i think i have the answer from
brettdj
-
Admin almost 11 yearsmy gosh +1 but i'd rather stick to the rules that you cannot do it
-
Paras Parmar about 3 yearsAlthough this looks like a simple answer, it's a wrong answer to the above question.The original post creator asks if its possible for a user formula to write beyond the boundary of its own cell .The answer is A user defined formula cannot write values outside its own cell without significant effort.