我不希望我的 Excel 加载项返回一个数组(相反，我需要一个 UDF 来更改其他单元格) [英] I don't want my Excel Add-In to return an array (instead I need a UDF to change other cells)

问题描述

我已经创建了一个 Excel 插件，以及这个插件的一个功能，比如说 New_Years 目前需要 2 年，并在这 2 年之间的每个元旦输出Excel 中的数组.所以 New_Years(2000,2002) 将在最后一个单元格中返回 Jan 1st 2000、Jan 1st 2001 和 Jan 1st 2002.

I've created an Excel Add-In, and one of the functions of this Add-In, lets say New_Years currently takes in 2 years and outputs every New Years day between those 2 years as an array in Excel. So New_Years(2000,2002) would return Jan 1st 2000, Jan 1st 2001, and Jan 1st 2002 in the last cell.

问题是我必须知道那个时间会有 3 个日期，选择 3 个单元格，在顶部单元格中输入我的公式，然后按 Ctrl + Shift + Enter填写数组.

The problem is that I have to know there are going to be 3 dates in that time, select 3 cells, enter my formula in the top cell, and then hit Ctrl + Shift + Enter to fill out the array.

我使用 XLW 版本 5 将我的 C++ 代码转换为 .xll 文件.如果有某种方法我可以用我的公式填充一个正方形，我真的很喜欢它，Excel 会根据需要用适当的日期填充下面的正方形.有谁知道这是否可能?还是不可能?

I use XLW version 5 to convert my C++ code to an .xll file. I would really like it if there was some way I could just fill in one square with my formula, and Excel would fill in the squares below as needed with the appropriate dates. Anyone know if this is possible? Or impossible?

非常感谢！

推荐答案

这实际上是可能的，尽管很复杂.我从 Kevin Jones aka Zorvek 因为它位于 在 EE 付费墙后面(如果有人可以访问，请附上链接)

It is actually possible albeit complex. 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)

虽然 Excel 严格禁止 UDF 更改任何单元格、工作表、或工作簿属性，有一种方法可以在使用 Windows 计时器和 Application.OnTime 计时器调用 UDF序列.必须在 UDF 中使用 Windows 计时器，因为Excel 会忽略 UDF 内的任何 Application.OnTime 调用.但是因为Windows 计时器有限制(如果出现以下情况，Excel 将立即退出)如果正在编辑单元格或对话框已打开)，它仅用于安排 Application.OnTime计时器，一个安全计时器，Excel 只允许在单元格被触发时触发没有被编辑，也没有打开任何对话框.

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.

下面的示例代码说明了如何从在 UDF 中，如何使用该计时器例程来启动Application.OnTime 计时器，以及如何传递只有自己知道的信息UDF 到随后的定时器执行例程.下面的代码必须是放置在常规模块中.

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.

Private 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 AddTwoNumbers( _
      ByVal Value1 As Double, _
      ByVal Value2 As Double _
   ) As Double

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

   AddTwoNumbers = Value1 + Value2

   ' 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
      Cell.Offset(0, 1).Value = Cell.Value
   Loop
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   End Sub

这篇关于我不希望我的 Excel 加载项返回一个数组(相反，我需要一个 UDF 来更改其他单元格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！