一个函数中的VBA无法将数据写入Excel 2007/2010中的单元格 [英] Cannot VBA write data to cells in Excel 2007/2010 within a function
问题描述
Sheets(SheetName)。Range(A1)。value = someValue
表单(SheetName)。单元格(1,1).value = someValue
使用这种代码,我只能从单元格A1读取数据,但是我无法设置一个新的值。
更新
设置单元格A1值的代码放在函数
如下。
函数abb()
表格SheetName)。Range(A1)。value = 122333
abb ='任何东西'
结束功能
在单元格B2中,我设置 = abb()
并按Enter键。我得到#VALUE,但没有发生在A1。
将此代码放在宏中,它可以工作。
我的问题是,如何使A1在一个函数内有值?
从你上面的评论你想尝试这种方法
如果您输入任何内容,请输入
= abb()
单元格
然后该表格的单元格A1将被设置为12333
这是要更新的行要更新的单元格并在其中放置一个值
Range(A1)。Value = 122333
从我不希望我的Excel加载项返回数组(而是需要UDF来更改其他单元格)
我正在从 Kevin Jones aka Zorvek ,因为它坐在 EE Paywall后面(如果有人有访问权限,链接)
虽然Excel严格禁止UDF更改任何单元格,工作表,
或工作簿属性,但是当调用
UDF时,有一种方法可以实现这些更改在
序列中使用Windows定时器和Application.OnTime定时器。 Windows定时器必须在UDF中使用,因为
Excel会忽略UDF中的任何Application.OnTime调用。但是,因为
Windows定时器有限制(Excel将立即退出,如果一个
Windows计时器尝试运行VBA代码,如果一个单元格被编辑或一个
对话框打开),它被使用只有安排一个Application.OnTime
定时器,一个安全的计时器,Excel只允许触发一个单元格
未被编辑,没有对话框打开。
下面的示例代码说明了如何从UDF内的
启动Windows定时器,如何使用该定时器例程启动
Application.OnTime定时器,以及如何传递只知道的信息到
UDF到后续的定时器执行例程。下面的代码必须是
放在常规模块中。
声明函数SetTimer Libuser32(_
ByVal HWnd As Long,_
ByVal nIDEvent As Long,_
ByVal uElapse As Long,_
ByVal lpTimerFunc As Long _
) As Long
私有声明函数KillTimer Libuser32(_
ByVal HWnd As Long,_
ByVal nIDEvent As Long _
)As Long
私人mCalculatedCells作为集合
私有mWindowsTimerID为长
私人mApplicationTimerTime作为日期
公共功能abb()
'这是一个UDF它返回两个数字的和,并启动一个Windows定时器
',启动一个第二个Appliction.OnTime定时器,执行UDF允许的活动不是
'。不要让这个UDF变化,传递任何易失性函数
'或传递任何包含volatile公式/函数的单元格或
'不受控制的循环将开始。
abb =无论你想要什么
'缓存调用者的引用,以便可以在非UDF例程中处理
如果mCalculatedCells是Nothing然后设置mCalculatedCells =新集合
错误恢复下一步
mCalculatedCells.Add Application.Caller,Application.Caller.Address
错误转到0
'设置/重置定时器应该是在UDF
中执行的最后一个操作如果mWindowsTimerID<> 0然后KillTimer 0& mWindowsTimerID
mWindowsTimerID = SetTimer(0& 0& 1,AddressOf AfterUDFRoutine1)
结束函数
公共Sub AfterUDFRoutine1()
'这是两个定时器程序中的第一个。这个由Windows
'计时器调用。由于Windows定时器无法运行代码,如果正在编辑单元格或
'对话框打开,此例程使用
'Application.OnTime安排第二个安全计时器,这在UDF中被忽略。
'停止Windows定时器
On Error Resume Next
KillTimer 0& mWindowsTimerID
错误GoTo 0
mWindowsTimerID = 0
'取消任何以前的OnTime计时器
如果mApplicationTimerTime<> 0然后
On Error Resume Next
Application.OnTime mApplicationTimerTimeAfterUDFRoutine2,False
错误GoTo 0
结束如果
'计划计时器
mApplicationTimerTime = Now
Application.OnTime mApplicationTimerTimeAfterUDFRoutine2
End Sub
Public Sub AfterUDFRoutine2()
这是两个定时器程序中的第二个。因为这个定时器例程是由Application.OnTime触发的
',它是安全的,即Excel不会允许
'定时器触发,除非环境是安全的(没有打开的模型对话框或单元格
被编辑)。
Dim Cell As Range
'执行任务不允许在UDF ...
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
尽管mCalculatedCells.Count> 0
设置单元格= mCalculatedCells(1)
mCalculatedCells.Remove 1
范围(A1)。值= 122333
循环
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
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?
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
这篇关于一个函数中的VBA无法将数据写入Excel 2007/2010中的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!