一个函数中的VBA无法将数据写入Excel 2007/2010中的单元格 [英] Cannot VBA write data to cells in Excel 2007/2010 within a function

问题描述

我想通过VBA设置单元格的值。我已经google了，看到一些解决方案：

  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屋！