vba 将单元格作为其他电子表格中公式的输入参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24298603/
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
Passing cells as input parameters of formula in other spreadsheet
提问by Bamqf
My workbook looks like this:
我的工作簿看起来像这样：
Sheet1:
A B C D
1 VarName Multiplier1 Multiplier2 Multiplier3
2 Var1 0.3 0.8 0.4
3 Var2 0.4 0.9 0.1
...
100 Var100 0.2 0.2 0.7
101 Parameter1 Parameter2 Index
102 Sheet2!A2 Sheet2!B2 Complicated formulas like =SUMPRODUCT(B2:B100, C2:C100, D2:D100)+C102/B102
Sheet2:
A B C
1 Parameter1 Parameter2 FinalResult
2 100 25 Complicated formulas like =IF(Sheet1!D102<1, 1, Sheet1!D102)
Sheet3:
A B C
1 Parameter1 Parameter2 FinalResult
2 110 30 ?
3 140 40 ?
...(about 200,000 rows)
More informaton:In Both Sheet1 and Sheet2, the actual formulas are much more complicated than the ones shown here. In Sheet1 we have 154 intermediate variables calculated from 7 basic parameters from Sheet2, the sumproduct is just one intermediate function. In Sheet2 there are 15 different cases depending on parameters and index calculated from Sheet1. So basically I cannot hard code them together and the only entrance is in Sheet2. Another reason I wouldn't change Sheet1 and Sheet2 is because this is a model bought from other company and the way we are told to use it is to input parameters in Sheet2.
更多信息：在 Sheet1 和 Sheet2 中，实际公式比此处显示的要复杂得多。在 Sheet1 中，我们从 Sheet2 的 7 个基本参数计算出 154 个中间变量，sumproduct 只是一个中间函数。在 Sheet2 中有 15 种不同的情况，具体取决于从 Sheet1 计算出的参数和索引。所以基本上我不能将它们硬编码在一起，唯一的入口是在 Sheet2 中。我不会更改 Sheet1 和 Sheet2 的另一个原因是，这是从其他公司购买的模型，我们被告知使用它的方式是在 Sheet2 中输入参数。
How can I write formula in Column C of Sheet3 so that I can calculate the result for every pair of Parameter1 and Parameter2? I tried recording macro to copy and paste like the following:
如何在 Sheet3 的 C 列中编写公式，以便可以计算每对 Parameter1 和 Parameter2 的结果？我尝试录制宏来复制和粘贴，如下所示：
Sub ToEnd()
Dim i As Long
Application.ScreenUpdating = False
i = 1
While i < 200000
Worksheets("Sheet3").Range("A1:B1").Offset(i, 0).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Range("C2").Select
Selection.Copy
Sheets("Sheet3").Select
Range("C1").Offset(i, 0).Select
Selection.PasteSpecial Paste:=xlValues
i = i + 1
Wend
End Sub
But in 6 seconds I only get 200 results,I estimate the time complexity for one case to be 10^4 at most, so it is much slower than what I expected. I wonder if there's something can be written in C2 of Sheet3 looks like
但是在6秒内我只得到200个结果，我估计一个case的时间复杂度最多为10^4，所以它比我预期的要慢得多。我想知道是否有什么东西可以写在 Sheet3 的 C2 中看起来像
=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A:$B, Sheet2!$C)
that I can autofill with inner loop hence faster? Or any other better way to do my task would be welcome.
我可以用内循环自动填充从而更快？或者任何其他更好的方式来完成我的任务都会受到欢迎。
回答by cint
Updated Answer:
更新答案：
I need a function f looks like f(x) {put x in right place of Sheet2; wait till Sheet2 finish calculation; get the result from another place of Sheet2 and return}
=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A$2:$B$2, Sheet2!$C$2)
我需要一个函数 f 看起来像 f(x) {put x 在 Sheet2 的正确位置；等待Sheet2完成计算；从Sheet2的另一个地方获取结果并返回}
=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A$2:$B$2, Sheet2!$C$2)
Your desired custom function (UDF) requires changing the values of other cells which is not possible because of the Excel environmental. The way you are doing ("cells copy & paste") is run through VBA Subprocedure. But you may also noticed that this is not good idea, as cells copy & paste are performed on front-end with screen updating, which is sightly slow. Find more details in this post: Set a cell value from a function.
您想要的自定义函数 ( UDF) 需要更改其他单元格的值，这是不可能的，因为 Excel 环境。您正在执行的操作（“单元格复制和粘贴”）是通过 VBASub程序运行的。但是你可能也注意到这不是一个好主意，因为单元格复制和粘贴是在前端执行的，屏幕更新速度有点慢。在这篇文章中找到更多详细信息：从函数设置单元格值。
As I said before, no matter how many variables/formulas are involved in sheet 1, your sheet 2 is redundant. It separates your calculation and increases your workflow. Sheet 2 input parameters obviously is dummy from sheet 3. And you can move sheet 2 formulas to sheet 1 for consistence.
正如我之前所说，无论工作表 1 中涉及多少变量/公式，您的工作表 2 都是多余的。它分离您的计算并增加您的工作流程。表 2 输入参数显然是表 3 中的虚拟参数。您可以将表 2 公式移动到表 1 以保持一致性。
If you really need a custom function, the only way you can do is modify you worksheet structure by following below steps I posted earlier. Or you can also write a Sub which requires highly tailored coding by go through every calculation workflow details. Again, the code/formulas can be varied differently depended on the actual case of your scenario. I cannot hard code here for you unless I get the whole workbook.
如果您确实需要自定义函数，那么您唯一可以做的就是按照我之前发布的以下步骤修改工作表结构。或者，您也可以编写一个 Sub，它需要通过每个计算工作流程细节进行高度定制的编码。同样，代码/公式可以根据场景的实际情况而有所不同。除非我得到整个工作簿，否则我无法在这里为您硬编码。
I won't suggest you use VBA approach to complete your task (either coding a Sub or UDF). As far as I understand, your task can be solely completed by normal worksheet functions. I hope you can get some hints from below worksheet picture.
我不会建议您使用 VBA 方法来完成您的任务（编码 Sub 或 UDF）。据我了解，您的任务只能通过正常的工作表功能来完成。我希望你能从下面的工作表图片中得到一些提示。
Earlier Answer:
之前的回答：
Actually, you don't need VBA and Sheet 2. You can get your result through one single formula:
实际上，您不需要 VBA 和 Sheet 2。您可以通过一个公式获得结果：
Basic Worksheet Formula Function Approach
基本工作表公式函数方法
I can see that the purpose of your sheet2 is only for single calculation (formula Sheet2!D102).
Another formula is stored in Sheet1 (formula Sheet1!D102).
我可以看到您的 sheet2 的目的仅用于单一计算（公式Sheet2!D102）。另一个公式存储在 Sheet1（公式Sheet1!D102）中。
In fact, you can combine your two formulas in Sheet1!D102& Sheet2!D102into one single formula.
事实上，您可以将Sheet1!D102& 中的两个公式合并Sheet2!D102为一个公式。
For example, in Sheet3 cell C2, you can use this formula:
例如，在 Sheet3 单元格 C2 中，您可以使用以下公式：
=IF((SUMPRODUCT(Sheet1!$B:$B,Sheet1!$C:$C,Sheet1!$D:$D)+(B2/A2))<1,1,(SUMPRODUCT(Sheet1!$B:$B,Sheet1!$C:$C,Sheet1!$D:$D)+(B2/A2)))
Above formula Logic can be simplified as:
上式逻辑可以简化为：
=IF( (SumProductResult+(Parameter2/Parameter1))<1 , 1 , (SumProductResult+(Parameter2/Parameter1)) )
Logic & algorithm (not formula / code):
逻辑和算法（不是公式/代码）：
If (SumProductResult+(Parameter2/Parameter1)) < 1
Then show/Return 1
Else show/Return (SumProductResult+(Parameter2/Parameter1))
End If
VBA Approach
VBA方法
If you really want VBA to do that, then you use VBA to create custom function (still using above algorithm).
如果您真的希望 VBA 这样做，那么您可以使用 VBA 创建自定义函数（仍然使用上述算法）。
Function FinalResultReturn(SumProductResultCell As Double, Parameter1 As Double, Parameter2 As Double) As Double
ComputedValued = SumProductResultCell + (Parameter2 / Parameter1)
If ComputedValued < 1 Then
FinalResultReturn = 1
Else
FinalResultReturn = ComputedValued
End If
End Function
How to use this VBA:
如何使用此 VBA：
- Create a VBA module and paste above code for creating new custom function.
- Now you can use your custom function in worksheet.
- 创建一个 VBA 模块并粘贴上面的代码以创建新的自定义函数。
- 现在您可以在工作表中使用您的自定义函数。
Sytnax:
语法：
=FinalResultReturn( SumProductResultCell , Parameter1 , Parameter2 )
Example:
In Sheet3 cell C2 input this:
示例：
在 Sheet3 单元格 C2 中输入：
=FinalResultReturn(Sheet1!$D9,A3,B3)
where Sheet1!$D$999is the SumProductResultCell, i.e. =SUMPRODUCT(B2:B100, C2:C100, D2:D100).
(PS: I create this SumProductResultCell for minimizing the input parameter of custom function.)
其中Sheet1!$D$999是SumProductResultCell，即=SUMPRODUCT(B2:B100, C2:C100, D2:D100）。
（PS：我创建这个 SumProductResultCell 是为了最小化自定义函数的输入参数。）
I hope I didn't misinterpret your worksheet calculation workflow. If yes, please let me know and make modify.
我希望我没有误解您的工作表计算工作流程。如果是，请告诉我并进行修改。
