可以在 VBA 的 LinEst 函数中使用数组吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21267540/
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
Can an array be used within the LinEst function in VBA?
提问by user3111019
Basically, rather that selecting a range from cells I have stored values in an array through the use of a loop. What I would ideally like to do is use these arrays as the known x's and y's in the LinEst function.
基本上，我通过使用循环将值存储在数组中，而不是从单元格中选择一个范围。理想情况下，我想做的是在 LinEst 函数中使用这些数组作为已知的 x 和 y。
The purpose of this is unimportant as what I am trying to do is only part of the code I have already written. However, the Do loops (well at least the 2nd) do need to be there as the code I am attempting to apply this to requires them in order to function.
这样做的目的并不重要，因为我想要做的只是我已经编写的代码的一部分。但是，Do 循环（至少是第二个）确实需要存在，因为我试图将其应用到的代码需要它们才能运行。
Below is a simple example of code I am trying to write.
下面是我正在尝试编写的代码的简单示例。
Sub Test()
Dim Counter As Long
Dim Counter_1 As Long
Dim x As Single
Dim y As Single
Dim i As Single
Dim m As Single
Dim myArray_1() As Single
Dim myArray_2() As Single
ReDim myArray_1(i)
ReDim myArray_2(i)
Counter = 2
Counter_1 = 2
i = 0
Cells(1, 4) = "m"
x = Cells(Counter, 1)
y = Cells(Counter, 2)
Do
Do Until x = 0
myArray_1(i) = x
myArray_2(i) = y
Cells(Counter, 6) = myArray_1(i)
Cells(Counter, 7) = myArray_2(i)
i = i + 1
Counter = Counter + 1
x = Cells(Counter, 1)
y = Cells(Counter, 2)
ReDim Preserve myArray_1(i)
ReDim Preserve myArray_2(i)
Loop
m = WorksheetFunction.LinEst(myArray_2, myArray_1)
Cells(Counter_1, 4) = m
Loop
End Sub
So basically I want the LinEst function to use each array as known y's and known x's. Depending on what I change I get different errors, such as "type mismatch", or "unable to get the LinEst property of the worksheetfunction class". Either way I have so far had no luck in getting this to work and it always errors. From the LinEst function all I want is the gradient 'm'.
所以基本上我希望 LinEst 函数将每个数组用作已知的 y 和已知的 x。根据我所做的更改，我会收到不同的错误，例如“类型不匹配”或“无法获取工作表函数类的 LinEst 属性”。无论哪种方式，到目前为止，我都没有运气使其正常工作，并且总是出错。从 LinEst 函数中，我想要的只是渐变“m”。
The only reason things have been put into cells is to make sure that the code is doing what I ask of it.
将东西放入单元格的唯一原因是确保代码按照我的要求执行。
From what I can tell looking around the internet it is possible to use an array within the LinEst function, however the examples are usually drastically different to what I am trying to do.
从我在互联网上看到的情况来看，可以在 LinEst 函数中使用数组，但是这些示例通常与我想要做的完全不同。
If anyone can help at all I would be most greatful. Thank you in advance. Any questions feel free to ask.
如果有人可以提供帮助，我将是最伟大的。先感谢您。有任何问题请随时询问我（们）。
回答by rskar
Yes, it can be done. The code snippet below should help get you started:
是的，这是可以做到的。下面的代码片段应该可以帮助您入门：
Dim x() As Variant
ReDim x(1 To 3)
x(1) = 1
x(2) = 2
x(3) = 3
Dim y() As Variant
ReDim y(1 To 3)
y(1) = 4
y(2) = 5
y(3) = 6
Dim z() As Variant
z = WorksheetFunction.LinEst(x, y)
The function returns a Variantwhich "boxes" an array of Variant(which will be either one- or two-dimensional). The other two parameters (not shown above) are either True or False. The function is otherwise detailed in the Excel Help.
该函数返回一个Variant“装箱”一个数组Variant（将是一维或二维的）。其他两个参数（上面未显示）是 True 或 False。该函数在 Excel 帮助中另有详细说明。
回答by Nielsou Akbrg
I achieved this with the below code. Hope it helps.
我用下面的代码实现了这一点。希望能帮助到你。
Sub RunLinEst()
Dim vectorX() As Double
Dim vectorY() As Double
Dim theLeastSquareCoef
'you need to define matrix otherwise it doesn't work
ReDim vectorX(0 To 4, 0 To 0)
ReDim vectorY(0 To 4, 0 To 0)
vectorX(0, 0) = 0
vectorX(1, 0) = 1
vectorX(2, 0) = 2
vectorX(3, 0) = 3
vectorX(4, 0) = 4
vectorY(0, 0) = 0
vectorY(1, 0) = 1
vectorY(2, 0) = 4
vectorY(3, 0) = 9
vectorY(4, 0) = 16
theLeastSquareCoef = Application.LinEst(vectorY, Application.Power(vectorX, Array(1, 2)))
Range("F4").Value = Application.Index(theLeastSquareCoef, 1)
Range("F5").Value = Application.Index(theLeastSquareCoef, 2)
Range("F6").Value = Application.Index(theLeastSquareCoef, 3)
End Sub
