vba 在 excel 用户定义函数中使用工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4640336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using a sheet in an excel user defined function
提问by gideon
The VBA I'm trying to write is fairly simple but Ive never written VBA and coming from the visual studio & C# world, this truly is hell!!
我正在尝试编写的 VBA 相当简单，但我从未编写过 VBA 并且来自 Visual Studio 和 C# 世界，这真的是地狱！！
So I'll really be grateful for any help/pointers/hints here
所以我真的很感激这里的任何帮助/指示/提示
So I have two important sheets. The Range sheet has 2 values per date. It needs a result, The Calc sheet takes two values and gets me a result.
所以我有两个重要的表。范围表每个日期有 2 个值。它需要一个结果，Calc 表需要两个值，然后给我一个结果。
I want to Put the Current, and OneYear values for each dateinto the Calc sheet and get the result into the result column.
我想将每个日期的 Current 和 OneYear 值放入 Calc 表中，并将结果放入结果列中。
So I tried defining a UDF but I learned later I cannot modify sheet contents in a UDF.
所以我尝试定义一个 UDF，但后来我了解到我无法修改 UDF 中的工作表内容。
I tried recording a macro and then hoped to go from there, but I got something like this:
我尝试录制一个宏，然后希望从那里开始，但我得到了这样的东西：
Range("A2").Select
Sheets("24Feb05-24FEB10").Select
Range("K9").Select
ActiveCell.FormulaR1C1 = "=Calc!R[-8]C[-10]"
Range("K9").Select
Im assuming R1C1 is Row and Column but why on earth does it say -8 and -10!??
我假设 R1C1 是行和列，但为什么它说 -8 和 -10！？？
Can someone get me in the right direction?
有人可以让我朝着正确的方向前进吗？
Note: There are about 2000+ dates.
注意：大约有 2000 多个日期。
Hope my question is clear!
希望我的问题很清楚！
UpdateI'll clear up a few more things:
更新我将澄清一些更多的事情：
Im grosslysimplifying the Calc sheet, in reality, it needs 5 values (there are columns upto SixYears) And then that sheet calls about 4 other sheets and does some finance calculations that some non-tech-savvy guy wrote.
我大大简化了 Calc 表，实际上，它需要 5 个值（有列到 SixYears）然后该表调用了大约 4 个其他表，并进行了一些非技术专家编写的财务计算。
I tried to get the finance guy to express to me in algebra what he's doing, so I could just write up a C# function.
我试图让财务人员用代数向我表达他在做什么，所以我可以编写一个 C# 函数。
I also tried writing a UDF, but like I said it needs to putin the values and get the result, which means the UDF will modify the sheet.
我也试着写一个UDF，但就像我说的，它需要投入的价值和得到的结果，这意味着UDF将修改表。
Function Func1(txt As Variant) As String
//Returns the nth element from a delimited text string
Dim txt As String
txt = text
Sheet4.Cells(1, 1) = txt
Func1 = Sheet4.Cells(2, 1)//get the result value'
End Function
Basically Im having a head ache learning/understand VBA syntax and the API.
基本上，我在学习/理解 VBA 语法和 API 时头疼。
Im looking for maybe a small exampleof how I would take values from one sheet into another and then get the some other values back? Maybe in a loop!?
我正在寻找一个小例子，说明我如何将值从一张纸转换到另一张纸，然后取回其他一些值？也许在一个循环中！？
采纳答案by jtolle
You might be making this more complicated than you need to. As a programmer, I'm sure you appreciate that when learning a new system, a little time with the help, Google, "Dummies" books, etc. is time well spent. Excel emphasizes a very different programming style than, say, C#.
您可能使这比您需要的更复杂。作为一名程序员，我相信你会意识到在学习一个新系统时，花点时间在帮助、谷歌、“傻瓜”书籍等方面是值得的。Excel 强调一种与 C# 非常不同的编程风格。
So, here are some options, starting with the simplest and most Excel-idiomatic:
所以，这里有一些选择，从最简单和最符合 Excel 习惯的开始：
1) Use a formulain your result column. If the calculation you want to do is simple enough, this is the way to go. As a trivial example, say your calculation is just adding the two numbers. You'd put the formula
1)在结果列中使用公式。如果你想要做的计算足够简单，这就是要走的路。作为一个简单的例子，假设您的计算只是将两个数字相加。你把公式
=B2+C2
into cell E2, and then drag it down the E column. Excel will know what to do and make forumlas for E3=B3+C3 and so on.
进入单元格 E2，然后将其向下拖动到 E 列。Excel 会知道该怎么做，并为 E3=B3+C3 等制作论坛。
You can do quite a lot with just formulas. Excel has many built-in worksheet functions, and you can find tons of material on the web and in the bookstore.
你可以用公式做很多事情。Excel 具有许多内置的工作表功能，您可以在网络和书店中找到大量资料。
2) Write a UDF in VBAand call it from the result column. A UDF is a function that doesn't modify anything else in the worksheet. It just calculates a result based on its input parameters. You would write a UDF when you have something too complex to do in a formula, or when you want to use the formula in multiple places. (Note that dragging a forumla across a Range is idiomatic Excel and counts as "one place" for DRY purposes).
2)在 VBA 中编写一个 UDF并从结果列中调用它。UDF 是一个不会修改工作表中其他任何内容的函数。它只是根据其输入参数计算结果。当您有一些太复杂的事情无法在公式中执行时，或者您想在多个地方使用该公式时，您将编写 UDF。（请注意，在范围内拖动论坛是惯用的 Excel，并且出于 DRY 目的计为“一个地方”）。
So, to write a UDF (let's use the addition example, but you'd write more complex logic), you'd put a function like this into a VBA code module:
因此，要编写 UDF（让我们使用加法示例，但您将编写更复杂的逻辑），您需要将这样的函数放入 VBA 代码模块中：
Public Function myUDF(current, oneYear)
myUDF = current + oneYear
End Function
You'd then put the formula
然后你把公式
=myUDF(B2, C2)
into cell E2 and drag that down the E column.
进入单元格 E2 并将其向下拖动到 E 列。
Note that I've neglected stuff like error handling in this example. This is one reason why formulas are more "Excel" than UDFs. Now you have to start writing code that departs from Excel's declarative functional style, and it's a different mental model.
请注意，在此示例中，我忽略了诸如错误处理之类的内容。这是公式比 UDF 更“Excel”的原因之一。现在您必须开始编写与 Excel 声明式函数风格不同的代码，这是一种不同的思维模型。
EDIT: 2.5) Use an Excel Data Table- I'm embarrased that I forgot this before, but if you have exactly one or two cells that are your independent parameters, and exactly one cell that is your result, then Excel has a built-in "what-if" analysis tool that you can use. The name "data table" is regrettable since both of those words are so heavily overloaded, but that's what it's called. You tell Excel what your input(s) and output are, and it will do what you want. See:
编辑：2.5)使用 Excel 数据表- 很抱歉我之前忘记了这一点，但是如果您恰好有一两个单元格是您的独立参数，并且只有一个单元格是您的结果，那么 Excel 有一个内置的-在您可以使用的“假设”分析工具中。“数据表”这个名字令人遗憾，因为这两个词都超载了，但这就是它的名字。你告诉 Excel 你的输入和输出是什么，它会做你想做的。看：
Here is the little intro from that link:
这是该链接的小介绍：
A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.
数据表是一系列单元格，显示如何更改公式中的一个或两个变量（公式：单元格中的一系列值、单元格引用、名称、函数或运算符，它们一起产生新值。公式总是开始带有等号 (=).) 将影响这些公式的结果。数据表提供了在一个操作中计算多个结果的快捷方式，以及一种在工作表上查看和比较所有不同变体结果的方法。
3) Write a macrothat uses your calc sheet like it was a function. You might do this if you've got really complex logic on that sheet, and it's too hard to convert to VBA because it changes all the time or because it's being developed by an Excel user who doesn't know VBA and isn't familiar with imperative programming. I'm not going to supply code, but the pseudocode might look like this:
3）编写一个宏，像函数一样使用你的计算表。如果您在该工作表上有非常复杂的逻辑，您可能会这样做，并且转换为 VBA 太难了，因为它一直在变化，或者因为它是由不了解 VBA 且不熟悉的 Excel 用户开发的使用命令式编程。我不打算提供代码，但伪代码可能如下所示：
Public Sub doCalc()
'for each cell in the result column
'copy the corresponding current and oneYear cells to the calc sheet
'Excel will recalc the calc sheet
'copy the result cell on the calc sheet back to the Range sheet in the right place
'end
End Sub
This is now departing pretty far from the "Excel way", since you have to set up your data yourself, and then run the macro. If you go back and change some of the "inputs", you need to re-run the macro. (This can be automated to some extent, using Worksheet events.)
这与“Excel 方式”相去甚远，因为您必须自己设置数据，然后运行宏。如果您返回并更改某些“输入”，则需要重新运行宏。（这可以在某种程度上自动化，使用工作表事件。）
Lot's of programmers who are new to Excel jump right to this step, relegating Excel to a giant grid control and neglecting the reason it exists in the first place - automatic recalculation.
许多不熟悉 Excel 的程序员直接跳到这一步，将 Excel 降级为一个巨大的网格控件，而忽略了它首先存在的原因 - 自动重新计算。
EDIT IN RESPONSE TO ADDITIONAL INFO IN QUESTION:
针对问题中的其他信息进行编辑：
O.K., it looks like case (3) is appropriate because you have a complex sheet not easily manually translated into VBA or C# code. Here is some (oversimplified, hardcoded, lacking EH, etc.) code to give you more of an idea of how to do this in VBA:
好的，看起来情况 (3) 是合适的，因为您有一个复杂的工作表，不容易手动转换为 VBA 或 C# 代码。以下是一些（过度简化、硬编码、缺少 EH 等）代码，可让您更多地了解如何在 VBA 中执行此操作：
First, there is the VBA macro that pretends it's a UDF. Note that it is a Sub, not a function, and you don'tcall it from a formula. You call subroutines in response to the user doing something like choosing a menu option or pressing a command button:
首先，有一个 VBA 宏假装它是一个 UDF。请注意，它是一个 Sub，而不是一个函数，并且您不会从公式中调用它。您调用子例程以响应用户执行诸如选择菜单选项或按下命令按钮之类的操作：
Public Sub fakeFunct(input1, input2, outputRng As Range)
CalcSheet.[b1] = input1
CalcSheet.[b2] = input2
'Excel recalcs what's in cell B3 if you have it set to automatic recalcualation
outputRng = CalcSheet.[b3]
End Sub
In this example, 'CalcSheet' is the "code name" of your worksheet module. (For more about this, see Chip Pearson's excellent website: http://www.cpearson.com/excel/codemods.htm)
在此示例中，“CalcSheet”是工作表模块的“代码名称”。（关于这方面的更多信息，请参见 Chip Pearson 的优秀网站：http: //www.cpearson.com/excel/codemods.htm）
You'd actually call the above macro from another one that loops through your "inputs":
你实际上会从另一个循环你的“输入”的宏调用上面的宏：
Public Sub loopFakeFunct()
Dim i As Long
For i = 2 To 2000
Call fakeFunct(RangeSheet.Cells(i, 2), RangeSheet.Cells(i, 3), RangeSheet.Cells(i, 5))
Next i
End Sub
This is the thing you call in response to user action, or from the Worksheet_Change event, etc. It will be slow and clunky, and I'm leaving a lot of practical VBA stuff out, but it should be enough to get you started. (I used 'RangeSheet' instead of 'Range' as a sheet code name to avoid conflicts with the Range class and the Application.Range method defined by Excel.)
这是您在响应用户操作或从 Worksheet_Change 事件等中调用的东西。它会很慢而且很笨重，而且我会遗漏很多实用的 VBA 内容，但它应该足以让您入门。（我使用 'RangeSheet' 而不是 'Range' 作为工作表代码名称，以避免与 Range 类和 Excel 定义的 Application.Range 方法发生冲突。）
4) Use some third-party toolthat converts your worksheet into a UDF that you can then call as in (2). There are commerical tools out there that will take your sheet, let you specifiy which cells are inputs and outputs, and then generate C++ code or whatever that can be compiled into an XLL add-in.
4)使用一些第三方工具将您的工作表转换为 UDF，然后您可以在 (2) 中调用该 UDF。有一些商业工具可以获取您的工作表，让您指定哪些单元格是输入和输出，然后生成 C++ 代码或任何可以编译成 XLL 加载项的代码。
5) Wait for Microsoftto do this:
5）等待微软这样做：
http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel.htm
http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel.htm
6) Use Resolver One:
6）使用解析器一：
http://www.resolversystems.com/products/resolver-one/
http://www.resolversystems.com/products/resolver-one/
It has a feature that allows you to use a whole workbook as a function in another one, or I think in any Python code.
它具有一项功能，允许您将整个工作簿用作另一个工作簿中的函数，或者我认为在任何 Python 代码中。
回答by Patrick Honorez
You recorded your macro in "relative" mode. R[-8]C[-10] means: the cell 8 rows above and 10 columns to the left of current cell.
您以“相对”模式录制了宏。R[-8]C[-10] 表示：当前单元格上8行左10列的单元格。
回答by Charles Williams
Its usually best in cases like this to work within the Excel paradigm rather than convert to the C# way: Not sure why you need a UDF rather than the Excel formulae which your Finance guy has coded: why can't you/finance guy just code some fomulae to feed the inputs to the finance guy sheets, then write some more formulae to feed the results back? If you really need to do this with VBA then J Tolle has given you the basic architecture for a repetitive loop but I would recommend looking at doing it with formulae first.
在这种情况下，通常最好在 Excel 范式中工作而不是转换为 C# 方式：不确定为什么需要 UDF 而不是财务人员编码的 Excel 公式：为什么你/财务人员不能只编码一些公式将输入提供给财务人员表，然后再写一些公式来反馈结果？如果你真的需要用 VBA 来做这件事，那么 J Tolle 已经为你提供了重复循环的基本架构，但我建议先用公式来做。
