可以在VBA的LinEst函数中使用数组吗? [英] Can an array be used within the LinEst function in VBA?

问题描述

基本上，不是从单元格中选择范围，而是通过使用循环将值存储在数组中.我理想地要做的是在LinEst函数中使用这些数组作为已知的x和y.

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.

此操作的目的并不重要，因为我试图做的只是我已经编写的代码的一部分.但是，Do循环(至少是第2个循环)确实需要存在，因为我试图将其应用到的代码要求它们才能起作用.

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.

下面是我尝试编写的简单代码示例.

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

所以基本上我希望LinEst函数使用每个数组作为已知的y和已知的x.根据我所做的更改，我会收到不同的错误，例如类型不匹配"或无法获取worksheetfunction类的LinEst属性".到目前为止，无论哪种方式，我都没有运气让它正常工作，而且总是出错.从LinEst函数中，我想要的只是梯度'm'.

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

将事情放进单元格的唯一原因是确保代码正在按照我的要求进行操作.

The only reason things have been put into cells is to make sure that the code is doing what I ask of it.

据我所知，可以在LinEst函数中使用数组，但是这些示例通常与我尝试执行的操作大不相同.

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.

如果有人能提供全部帮助，我将非常感激.先感谢您.任何问题都可以问.

If anyone can help at all I would be most greatful. Thank you in advance. Any questions feel free to ask.

推荐答案

是的，可以做到.下面的代码段将帮助您入门:

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)

该函数返回一个Variant，将一个Variant数组(将是一维或二维)装箱".其他两个参数(上面未显示)为True或False.否则，该功能在Excel帮助中进行了详细说明.

The function returns a Variant which "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.

这篇关于可以在VBA的LinEst函数中使用数组吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！