VBA 无法在函数内将数据写入 Excel 2007/2010 中的单元格

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

提问by Davuz

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

我想通过 VBA 设置单元格的值。我用谷歌搜索，并看到了一些解决方案：

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.

使用这种代码，我只能从单元格 A1 读取数据，但无法为其设置新值。

Update

更新

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

设置单元格 A1 值的代码放在 a 中Function，如下所示。

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.

在单元格 B2 中，我设置=abb()并按 Enter。我得到了 #VALUE，但 A1 没有任何反应。

Putting this code in a macro, it works.

将此代码放入宏中，它可以工作。

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

我的问题是，如何使 A1 在函数中具有值？

回答by brettdj

From your comment above you wanted to try this approach

根据您上面的评论，您想尝试这种方法

If you enter
=abb()
into any cell

如果您进入
=abb()
任何单元格

Then cell A1 of that sheet wil be set to 12333

然后该工作表的单元格 A1 将设置为 12333

This is the line to update to pick the cell to update, and to place a value in it
Range("A1").Value = 122333

这是要更新的行以选择要更新的单元格，并在其中放置一个值
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)

从我不希望我的 Excel 加载项返回一个数组（相反，我需要一个 UDF 来更改其他单元格）

I am reposting this piece of magic from Kevin Jones aka Zorvekas 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.

我正在重新发布Kevin Jones aka Zorvek 的这块魔法，因为它位于EE Paywall 后面（如果有人可以访问，请附上链接）

尽管 Excel 严格禁止 UDF 更改任何单元格、工作表或工作簿属性，但当使用 Windows 计时器和 Application.OnTime 计时器依次调用 UDF 时，有一种方法可以实现此类更改。Windows 计时器必须在 UDF 中使用，因为 Excel 会忽略 UDF 中的任何 Application.OnTime 调用。但是，由于 Windows 计时器有限制（如果 Windows 计时器在编辑单元格或打开对话框时尝试运行 VBA 代码，Excel 将立即退出），因此它仅用于安排 Application.OnTime 计时器，这是一个安全计时器只有在未编辑单元格且未打开任何对话框时，Excel 才允许触发。

下面的示例代码说明了如何从 UDF 内部启动 Windows 计时器，如何使用该计时器例程来启动 Application.OnTime 计时器，以及如何将只有 UDF 知道的信息传递给后续的计时器执行例程。下面的代码必须放在常规模块中。

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

回答by Tony Dallimore

You cannot change cell A1 with a function in B2.

您不能使用 B2 中的函数更改单元格 A1。

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

访问：Excel 中自定义函数的限制说明 。正文包括：

"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:

“由工作表单元格中的公式调用的用户定义函数无法更改 Microsoft Excel 的环境。这意味着此类函数不能执行以下任何操作：

• 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.

为什么要以这种方式更改单元格A1？解释您的目标，也许有人可以提供帮助。

回答by Jean-Fran?ois Corbett

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 ShiftCtrlEnterto specify that it is an array formula. This formula then modifies both cells (A2 and B2) at the same time.

选择单元格 A2 到 B2。键入=abb()并按下ShiftCtrlEnter以指定它是一个数组公式。然后，此公式同时修改两个单元格（A2 和 B2）。

Perhaps you can customise this to do what you want.

也许你可以自定义它来做你想做的事。

回答by Anantha Sharma

it should work - try this

它应该工作 - 试试这个

1. Open a new excel sheet
2. Create a new macro
3. Add this Sheets("Sheet1").Range("A1").Value2 = "value"

1. 打开一个新的 Excel 工作表
2. 创建一个新的宏
3. 添加这个 Sheets("Sheet1").Range("A1").Value2 = "value"

you can use both .Valueand .Value2, make sure that the sheet name is correct.

您可以同时使用.Valueand .Value2，请确保工作表名称正确。