传递数组作为参数传递给一个VBA函数 [英] Passing Arrays as a parameter to a VBA function
问题描述
我有一个用户自定义,从X的排列和Y值数组在定义的X1值,它工作正常进行内插的Excell工作表函数(线性)。我曾尝试(在本例中code以下NPL)另一个函数中使用此设置来它一个VBA模块内的私有静态函数,然后调用使用函数中创建的数据阵列的功能。
当我用这个在s preadsheet我得到一个#VALUE错误。
任何想法我做错了吗?
举例code:
功能NPL(长,梁)
A =阵列(1,2,3,4)
B =阵列(2,4,6,8)
C =线性(A,B,1.5)
NPL = C
结束功能
私有静态函数线性(X,Y,X1)N = 0
I = 1
做
N =
I = I + 1
循环直到X(I)LT; X(I - 1)或N = X.CountA = 0
I = 0
做
I = I + 1
循环直到X(I)GT; X1或I> N - 1如果X1< X(n)和X 1> X(1)。然后
线性= Y(I - 1)+(X 1 - X(I - 1))*(Y(I) - Y(I - 1))/(X(1) - X(I - 1))
elseif的X1> X(N)或X 1 = X(N)然后
线性= Y(N)
其他
线性= Y(1)
万一结束功能
替换您的
待办事项
N =
I = I + 1
循环直到X(I)LT; X(I - 1)或N = X.Count
与
待办事项
N =
I = I + 1
循环直到X(I)LT; X(I - 1)或N = UBound函数(X) - LBOUND(X)+ 1
这应该适用于任何一维数组。
I have a user defined Excell worksheet function (Linear) that interpolates from an array of X and an array of Y values at a defined X1 value, which works fine. I have tried to use this within another function (NPL in the example code below) be setting it a a Private Static function within a VBA module and then calling the function using arrays of data created within the function. When I use this in the spreadsheet I get a #VALUE error. Any ideas what I am doing wrong?
Example code:
Function NPL(Length, Beam)
A = Array(1, 2, 3, 4)
B = Array(2, 4, 6, 8)
C = Linear(A, B, 1.5)
NPL = C
End Function
Private Static Function Linear(X, Y, X1)
N = 0
I = 1
Do
N = I
I = I + 1
Loop Until X(I) < X(I - 1) Or N = X.Count
A = 0
I = 0
Do
I = I + 1
Loop Until X(I) > X1 Or I > N - 1
If X1 < X(N) And X1 > X(1) Then
Linear = Y(I - 1) + (X1 - X(I - 1)) * (Y(I) - Y(I - 1)) / (X(I) - X(I - 1))
ElseIf X1 > X(N) Or X1 = X(N) Then
Linear = Y(N)
Else
Linear = Y(1)
End If
End Function
Replace your
Do
N = I
I = I + 1
Loop Until X(I) < X(I - 1) Or N = X.Count
with
Do
N = I
I = I + 1
Loop Until X(I) < X(I - 1) Or N = UBound(X) - LBound(X) + 1
This should work for any 1D array.
这篇关于传递数组作为参数传递给一个VBA函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!