我不希望我的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）将返回2000年1月1日，2001年1月1日和2002年1月1日的最后一个单元格。

问题是，我必须知道将要在这段时间3日期，选择3个细胞，在顶部单元格中输入我的公式，然后按按Ctrl + Shift + Enter键填写阵列。

我用XLW版本5到我的C ++ code转换为.xll文件。我真的很喜欢它，如果有一些方法我可以只填写与我的公式一平方，并根据需要与适当的日期的Excel将在下面的方框填写。任何人都知道这是可能的？或不可能？

非常感谢！

解决方案

它实际上是可能的复杂尽管。我重新张贴这片神奇的凯文 - 琼斯又名Zorvek 因为它位于<一个href=\"http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22095686.html\">behind在EE付费墙（附链接如果任何人有访问权限）

  

在Excel中严格更改的任意单元格，工作表，不允许UDF，
  或工作簿的属性，有实现这样的变化的方式，当一个
  UDF被称为使用Windows定时器和Application.OnTime计时器
  序列。 Windows的计时器已被UDF的，因为内使用
  Excel中忽略任何一个Application.OnTime里面UDF调用。但是因为
  Windows的计时器有其局限性（Excel将立即退出，如果
  窗口计时器尝试运行VBA code，如果正在编辑单元格或
  对话是打开的），它仅用于安排一个Application.OnTime
  计时器，一个安全定时器，它的Excel只允许如果一个细胞被烧成
  不编辑无对话框打开。

  
  下面的

例如code说明了如何开始从Windows计时器
  一个UDF里面，如何使用计时器例程启动
  Application.OnTime定时器，以及如何传递信息，只知道
  UDF的后续计时器执行的程序。下面的code必须是
  放置在常规的模块

 私人声明函数库SetTimer的USER32（_
      BYVAL HWND长，_
      BYVAL nIDEvent长，_
      BYVAL uElapse长，_
      BYVAL lpTimerFunc只要_
   ） 只要私人声明函数库KillTimer函数USER32（_
      BYVAL HWND长，_
      BYVAL nIDEvent只要_
   ） 只要私人mCalculatedCells为集合
私人mWindowsTimerID只要
私人mApplicationTimerTime随着日期公共职能AddTwoNumbers（_
      BYVAL值1为DOUBLE，_
      BYVAL值2为双_
   ）为双这是返回两数之和启动窗口定时器UDF
启动执行活动中没有第二个Appliction.OnTime计时器
允许的UDF。不要让这个UDF挥发，通过任何挥发性功能
'它，或通过含有挥发性公式/功能的任何细胞或
不受控制的循环将启动。   AddTwoNumbers =值1 +值2   '缓存呼叫者的引用，以便它可以与在非UDF常规处理
   如果mCalculatedCells是Nothing然后设置mCalculatedCells =新集合
   在错误恢复下一页
   mCalculatedCells.Add Application.Caller，Application.Caller.Address
   对错误转到0   设置/复位定时器应在UDF采取的最后一个动作
   如果mWindowsTimerID＆LT;＆GT; 0然后KillTimer函数0安培;，mWindowsTimerID
   mWindowsTimerID = SetTimer的（0安培;, 0安培，1，AddressOf AfterUDFRoutine1）结束功能公用Sub AfterUDFRoutine1（）这是前两个定时器例程。这一个是由Windows被称为
定时器。由于在Windows计时器无法运行code，如果正在编辑单元格或
对话框打开第二个安全定时器使用这个程序的时间表
Application.OnTime这在UDF被忽略。   停止Windows计时器
   在错误恢复下一页
   KillTimer函数0安培;，mWindowsTimerID
   对错误转到0
   mWindowsTimerID = 0   取消任何previous准时计时器
   如果mApplicationTimerTime＆LT;＆GT; 0，则
      在错误恢复下一页
      Application.OnTime mApplicationTimerTime，AfterUDFRoutine2，假
      对错误转到0
   万一   日程定时
   mApplicationTimerTime =现在
   Application.OnTime mApplicationTimerTime，AfterUDFRoutine2结束小组公用Sub AfterUDFRoutine2（）这是二计时器例程的第二位。因为这个定时器例程
通过Application.OnTime触发它是安全的，即，Excel将不允许
定时火，除非环境是安全的（没有开放模式对话框或细胞
正在编辑）。   昏暗的小区作为范围   不要在UDF不允许任务...
   Application.ScreenUpdating =假
   Application.Calculation = xlCalculationManual
   做虽然mCalculatedCells.Count＆GT; 0
      集细胞= mCalculatedCells（1）
      mCalculatedCells.Remove 1
      Cell.Offset（0，1）。价值= Cell.Value
   循环
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = TRUE
   结束小组
 

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.

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.

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?

Many thanks!

解决方案

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)

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.

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