根据您上面的评论,您想尝试这种方法
如果您输入
=abb()
进入任何细胞
然后该工作表的单元格 A1 将设置为 12333
这是要更新的行,用于选择要更新的单元格,并在其中放置一个值
Range("A1").Value = 122333
From 我不希望我的 Excel 加载项返回数组(相反,我需要 UDF 来更改其他单元格) https://stackoverflow.com/q/8520732/641067
我正在重新发布这个魔法凯文·琼斯又名佐维克 https://mvp.support.microsoft.com/profile=7E428211-E8AB-4F68-AEB1-FDD5C3DB5433因为它坐在那里EE 付费墙背后 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22095686.html(如果有人可以访问,请附上链接)
虽然 Excel 严格禁止 UDF 更改任何单元格、工作表,
或工作簿属性,有一种方法可以在以下情况下实现此类更改:
使用 Windows 计时器和 Application.OnTime 计时器调用 UDF
顺序。 Windows 计时器必须在 UDF 中使用,因为
Excel 会忽略 UDF 内的任何 Application.OnTime 调用。但是因为
Windows 计时器有限制(Excel 将立即退出,如果
如果正在编辑单元格或正在编辑单元格,Windows 计时器会尝试运行 VBA 代码
对话框打开),它仅用于调度 Application.OnTime
计时器,一个安全的计时器,Excel 仅允许在单元格处于活动状态时触发该计时器
没有被编辑,也没有打开任何对话框。
下面的示例代码说明了如何启动 Windows 计时器
在 UDF 中,如何使用该计时器例程来启动
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