使用函数的结果("vbArray"数据类型8204)进行进一步计算 [英] Using result of function ("vbArray" data type 8204) for further calculations

问题描述

请参考对此问题的答案的评论 . WorksheetFunction.LinEst的结果是ArrayVar(8204)数据类型(称为

Refer comments on answer to this question. Result of WorksheetFunction.LinEst is ArrayVar(8204) data type (referred docs.microsoft.com). When I extract an element of the array to worksheet using Application.Index, it works fine. But, any further calculations on the same element results in type mismatch error.

以下是所使用的VBA过程.

Following is the VBA procedure used.

Sub LinEst()

xVal = Application.Transpose(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9))
yVal = Application.Transpose(Array(3, 2, 5, 7, 4, 2, 1, -2, -5, -1))

rSQr = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

Debug.Print VarType(rSQr); TypeName(rSQr) 'Result is : 8204 Variant()

Range("B3") = rSQr 'Works fine; result is 0.818598661125351

'Debug.Print rSQr 'Run-time error '13': Type Mismatch
'rSQr = Round(rSQr, 6) 'Run-time error '13': Type Mismatch


End Sub

请指导如何在VBA中使用rSQr进行进一步的计算/比较.

Please guide on how can we use rSQr for further calculations/ comparisons within VBA.

谢谢.

推荐答案

请尝试以下方法. WorksheetFunction.LinEst返回一个数组(描述一行):

Try the next way, please. WorksheetFunction.LinEst returns an array (describing a line):

Sub testLinEst()
 Dim xVal As Variant, yVal As Variant, rSQr As Variant, el As Variant
 xVal = Application.Transpose(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9))
 yVal = Application.Transpose(Array(3, 2, 5, 7, 4, 2, 1, -2, -5, -1))

 rSQr = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

 Debug.Print VarType(rSQr), rSQr(1), rSQr(2)

 Range("B3:C3") = rSQr 'Works fine
End Sub

这篇关于使用函数的结果("vbArray"数据类型8204)进行进一步计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！