从Access VBA调用Excel函数 [英] Calling Excel function from Access VBA
问题描述
我正在尝试调用Excel函数
linest($ D $ 4:$ D $ 13,$ C $ 4:$ C $ 13 ^ {1,2,3来自Access中的
我的代码包括:
ReDim Y( n)
ReDim X(n)
Dim objExcel作为Excel.Application
设置objExcel = CreateObject(" Excel.Application")
然后我填充Y()和X()。
然后调用
结果= objExcel。 Application.LinEst(X,Y ^ {1,2,3},true,false)
产生"无效字符"错误"{"。
如何(a)我输入此Excel表达式和(b)结果应该是什么对象类型?
Peter Martin
请您解释一下表达方式
Y ^ {1,2,3}
是否会被计算出来,结果应该是什么?这不是VBA中的合法语法,您需要在使用它之前对其进行翻译。
我很难找到 这个表达式的含义;它似乎是某种取幂^带有数组{1,2,3}。
另外:我猜你想要运行3阶多项式的regerssion然后一个工作的例子是
Dim Y(2,4)As Double
Dim X(4)As Double
Dim Result As Variant
Dim objExcel As Object'Excel.Application
Dim i As Long
Set objExcel = CreateObject(" Excel.Application")
'填写数据
随机化
对于i = 0到4
X(i)= i + 0.5 - Rnd
Y(0,i)= i + 0.5 - Rnd
Y(1,i)= Y(0,i)^ 2
Y(2,i)= Y(0,i)^ 3
Debug.Print X(i ),Y(0,i),Y(1,i),Y(2,i)
下一个
'运行回归
结果= objExcel.Application.LinEst (X,Y,True,False)
'打印系数
Debug.Print
For i = 1 To UBound(Result)
Debug.Print Result( i)
下一个
设置objExcel = Nothing不e通常表示带X的自变量和带Y的因变量。反之亦然,这并不重要,但这可能是混淆和混淆的根源。
另外我不知道的是Excel是否需要基于0或基于1的数组。我已经为X和Y使用了基于0的数组,但结果是从1开始的。您必须使用Excel中的内容来检查此计算。
MatthiasKläy,Kläy Computing AG
I'm trying to call the Excel function
linest($D$4:$D$13,$C$4:$C$13^{1,2,3},true,false)
from within Access.
My code includes:
ReDim Y(n)
ReDim X(n)
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
I then populated Y() and X().
I then called
Result = objExcel.Application.LinEst(X,Y^{1,2,3},true,false)
giving rise to an "Invalid Character" error on "{".
How do (a) I enter this Excel expression and (b) what object type should Result be?
Peter Martin
Could you please explain how the expression
Y^{1,2,3}
is calculated and what the result should be? This is not legal syntax in VBA and you need to translate it before you can use it.
I have a hard time to find out the meaning of this expression; it seems to be some kind of exponentiation ^ with an array {1,2,3}.
Addition: I'm going to guess that you want to run a polynomial regerssion of degree 3. Then a working example would be
Dim Y(2, 4) As Double Dim X(4) As Double Dim Result As Variant Dim objExcel As Object ' Excel.Application Dim i As Long Set objExcel = CreateObject("Excel.Application") ' Fill in the data Randomize For i = 0 To 4 X(i) = i + 0.5 - Rnd Y(0, i) = i + 0.5 - Rnd Y(1, i) = Y(0, i) ^ 2 Y(2, i) = Y(0, i) ^ 3 Debug.Print X(i), Y(0, i), Y(1, i), Y(2, i) Next ' run the regression Result = objExcel.Application.LinEst(X, Y, True, False) ' print the coefficients Debug.Print For i = 1 To UBound(Result) Debug.Print Result(i) Next Set objExcel = NothingNote that one usually denotes the independent variable with X and the dependent variable with Y. It doesn't really matter that you do it vice versa, but this could be a source of confusion and mix-up.
Also what I don't know is if Excel expects 0-based or 1-based arrays. I have used 0-based arrays for X and Y, but the Result is 1-based. You will have to check this calculation with what you get in Excel.
Matthias Kläy, Kläy Computing AG
这篇关于从Access VBA调用Excel函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!