在EXCEL中用vba编程调用函数时弹出错误:缺少数组!请问这是怎么回事,是不是因为函数的形式和数组一样而使得编译时误以为函数为数组了?程序大致如下:Sub main()
Dim D As Integer,PMP As Integer,TK As Integer
…………
…………
PMP=123.45
TK=456.78
D=DDETAIL(PMP,TK) 《=========就停在这里:缺少数组
…………
…………
End SubFunction DDETAIL(P,T)
……
……
DDETAIL=……
End Function
Dim D As Integer,PMP As Integer,TK As Integer
…………
…………
PMP=123.45
TK=456.78
D=DDETAIL(PMP,TK) 《=========就停在这里:缺少数组
…………
…………
End SubFunction DDETAIL(P,T)
……
……
DDETAIL=……
End Function
Option ExplicitSub main()
Dim D As Long, PMP As Integer, TK As Integer
'…………
'…………
PMP = 123.45
TK = 456.78
D = DDETAIL(PMP, TK) ' 《=========就停在这里:缺少数组
'…………
'…………
End SubFunction DDETAIL(P, T)
'……
'……
DDETAIL = P * T
End Function
听了你的换了2003,结果还是一样啊
我打开 Excel 2003,打开 VBA编辑器,添加了一个标准模块,把你的代码复制过去,该改的改了一下。
运行,没有任何问题。如果你那儿不能运行,我也想不明白。
要不你在一个新工作簿中,把我的代码复制过去,试一下有没有问题。
.....
Function DDETAIL(P, T)函数定义的名字DDETAIL与调用的名字不一致,仔细检查一下~~~
应各位大哥要求把详细的发上来了,跪求大哥指点
Sub Calculation()Dim I As Integer, CIDH(21) As Integer, NCCH As Integer, IH As Integer, CID(21) As Integer, NCC As Integer, ICOMP As Integer, ILEVEL As Integer, ITABLE As Integer
Dim D As Double, Z As Double, ZB As Double, DB As Double, FPV As Double, TK As Double, _
PMP As Double, DDETAIL As Double, ZDETAIL As Double, UNITS As DoubleDim TDATA(21) As Double
Dim PDATA(21) As Double
Dim XK(21, 2) As Double
Dim XI(21) As Double, XIH(21) As Double, XIC(21) As DoubleDim J As Double, M As Double, N As Double, M1 As Double, M2 As Double, M3 As Double, M4 As Double'--------------------------initialization
Worksheets("SheetData").Activate
Worksheets("SheetData").Range("B1").Activate
For I = 1 To 21
TDATA(I) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next I Worksheets("SheetData").Activate
Worksheets("SheetData").Range("D1").Activate
For J = 1 To 21
PDATA(J) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next J
Worksheets("SheetData").Activate
Worksheets("SheetData").Range("F1").Activate
For M = 1 To 21
For N = 1 To 2
XK(M, N) = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
Next N
ActiveCell.Offset(1, -2).Activate
Next M
ICOMP = 1
ITABLE = 20Worksheets("SheetCalculation").Activate
Worksheets("SheetCalculation").Range("A1").ActivateFor IH = 1 To ITABLE
If IH = 10 Then
ICOMP = 2
End If
NCC = 0
ILEVEL = 0 For M1 = 1 To 21
If XK(M1, ICOMP) <> 0 Then
NCC = NCC + 1
CID(NCC) = M1
XI(NCC) = XK(M1, ICOMP)
XIC(NCC) = XK(M1, ICOMP)
End If
Next M1
If IH = 1 Then GoTo cal_400 If NCCH <> NCC Then GoTo cal_400
For M2 = 1 To NCC
If CIDH(M2) <> CID(M2) Then GoTo cal_400
Next M2
For M3 = 1 To NCC
If XIH(M3) <> XI(M3) Then GoTo cal_300
Next M3
If TDATA(IH) <> TDATA(IH - 1) Then GoTo cal_200
If PDATA(IH) <> PDATA(IH - 1) Then GoTo cal_100
GoTo cal_10cal_400: Call su_PARAMDL(NCC, CID)
ILEVEL = ILEVEL + 1
cal_300: Call su_CHARDL(NCC, XI, ZB, DB)
ILEVEL = ILEVEL + 1
cal_200: TK = TDATA(IH)
Call su_TEMP(TK)
ILEVEL = ILEVEL + 1
cal_100: PMP = PDATA(IH)
ILEVEL = ILEVEL + 1
D = DDETAIL(PMP, TK) <=========停在这里,缺少数组
Z = ZDETAIL(D, TK)
FPV = Sqr(ZB / Z)
D = Dcal_10: ActiveCell.Value = IH
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = TDATA(IH)
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = PDATA(IH)
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = D
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = Z
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = FPV
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = ILEVEL
ActiveCell.Offset(1, -6).Activate For M4 = 1 To NCC
CIDH(M4) = CID(M4)
XIH(M4) = XIC(M4)
Next M4
NCCH = NCC
Next IHEnd Sub
Function DDETAIL(P, T) Dim IMAX As Integer, I As Integer, CODE As Integer
Dim EPSP As Double, EPSR As Double, EPSMIN As Double, RHO As Double, RHOL As Double, RHOH As Double, _
PRHOL As Double, PRHOH As Double '''''''''''''' T As Double, P As Double
Dim DDETAIL As Integer, PDETAIL As Integer, X1 As Integer, X2 As Integer, _
X3 As Integer, Y1 As Integer, Y2 As Integer, Y3 As Integer
Dim DELX As Double, DELPRV As Double, DELMIN As Double, DELBIS As Double, XNUMER As Double, XDENOM As Double, SGNDEL As Double
Dim Y2MY3 As Double, Y3MY1 As Double, Y1MY2 As Double, BOUNDN As DoubleIMAX = 150
EPSP = 0.000001
EPSR = 0.000001
EPSMIN = 0.0000001
CODE = 0'--------------------call braket()
Call su_BRAKET
If CODE = 1 Or CODE = 3 Then
DDETAIL = RHO
End IfX1 = RHOL
X2 = RHOH
Y1 = PRHOL - P
Y2 = PRHOH - P
DELX = X1 - X2
DELPRV = DELXX3 = X1
Y3 = Y1For I = 1 To IMAX
If Y2 * Y3 > 0 Then
X3 = X1
Y3 = Y1
DELX = X1 - X2
DELPRV = DELX
End If
If Abs(Y3) < Abs(Y2) Then
X1 = X2
X2 = X3
X3 = X1
Y1 = Y2
Y2 = Y3
Y3 = Y1
End If
DELMIN = EPSMIN * Abs(X2)
DELBIS = 0.5 * (X3 - X2)
If Abs(DELPRV) < DELMIN Or Abs(Y1) < Abs(Y2) Then
DELX = DELBIS
DELPRV = DELBIS
Else
If X3 <> X1 Then
Y2MY3 = Y2 - Y3
Y3MY1 = Y3 - Y1
Y1MY2 = Y1 - Y2
XDENOM = -(Y1MY2) * (Y2MY3) * (Y3MY1)
XNUMER = X1 * Y2 * Y3 * (Y2MY3) + X2 * Y3 * Y1 * (Y3MY1) + X3 * Y1 * Y2 * (Y1MY2) - X2 * XDENOM
Else
XNUMER = (X2 - X1) * Y2
XDENOM = Y1 - Y2
End If
If 2 * Abs(XNUMER) < Abs(DELPRV * XDENOM) Then
DELPRV = DELX
DELX = XNUMER / XDENOM
Else
DELX = DELBIS
DELPRV = DELBIS
End If
End If
If (Abs(Y2) < EPSP * P) And (Abs(DELX) < EPSR * Abs(X2)) Then
DDETAIL = X2 + DELX
End If
If Abs(DELX) < DELMIN Then
SGNDEL = DELBIS / Abs(DELBIS)
DELX = 1.0000009 * SGNDEL * DELMIN
DELPRV = DELX
End If
BOUNDN = DELX * (X2 + DELX - X3)
If BOUNDN > 0 Then
DELX = DELBIS
DELPRV = DELBIS
End If
X1 = X2
Y1 = Y2
X2 = X2 + DELX
Y2 = PDETAIL(X2, T) - P '----------------------------call PDETAIL
Next I
MsgBox ("DDETAIL:MAXIMUM NUMBER OF ITERATIONS EXCEEDED")
DDETAIL = X2
End Function
Dim D As Double, Z As Double, ZB As Double, DB As Double, FPV As Double, TK As Double, _
PMP As Double, DDETAIL As Double, ZDETAIL As Double, UNITS As Double Dim TDATA(21) As Double
Dim PDATA(21) As Double
Dim XK(21, 2) As Double
Dim XI(21) As Double, XIH(21) As Double, XIC(21) As Double Dim J As Double, M As Double, N As Double, M1 As Double, M2 As Double, M3 As Double, M4 As Double '--------------------------initialization
Worksheets("SheetData").Activate
Worksheets("SheetData").Range("B1").Activate
For I = 1 To 21
TDATA(I) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next I Worksheets("SheetData").Activate
Worksheets("SheetData").Range("D1").Activate
For J = 1 To 21
PDATA(J) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Next J
Worksheets("SheetData").Activate
Worksheets("SheetData").Range("F1").Activate
For M = 1 To 21
For N = 1 To 2
XK(M, N) = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
Next N
ActiveCell.Offset(1, -2).Activate
Next M
ICOMP = 1
ITABLE = 20 Worksheets("SheetCalculation").Activate
Worksheets("SheetCalculation").Range("A1").Activate For IH = 1 To ITABLE
If IH = 10 Then
ICOMP = 2
End If
NCC = 0
ILEVEL = 0 For M1 = 1 To 21
If XK(M1, ICOMP) <> 0 Then
NCC = NCC + 1
CID(NCC) = M1
XI(NCC) = XK(M1, ICOMP)
XIC(NCC) = XK(M1, ICOMP)
End If
Next M1
If IH = 1 Then GoTo cal_400 If NCCH <> NCC Then GoTo cal_400
For M2 = 1 To NCC
If CIDH(M2) <> CID(M2) Then GoTo cal_400
Next M2
For M3 = 1 To NCC
If XIH(M3) <> XI(M3) Then GoTo cal_300
Next M3
If TDATA(IH) <> TDATA(IH - 1) Then GoTo cal_200
If PDATA(IH) <> PDATA(IH - 1) Then GoTo cal_100
GoTo cal_10 cal_400: Call su_PARAMDL(NCC, CID)
ILEVEL = ILEVEL + 1
cal_300: Call su_CHARDL(NCC, XI, ZB, DB)
ILEVEL = ILEVEL + 1
cal_200: TK = TDATA(IH)
Call su_TEMP(TK)
ILEVEL = ILEVEL + 1
cal_100: PMP = PDATA(IH)
ILEVEL = ILEVEL + 1
D = DDETAIL(PMP, TK) '<=========停在这里,缺少数组
Z = ZDETAIL(D, TK)
FPV = Sqr(ZB / Z)
D = D cal_10: ActiveCell.Value = IH
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = TDATA(IH)
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = PDATA(IH)
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = D
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = Z
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = FPV
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = ILEVEL
ActiveCell.Offset(1, -6).Activate For M4 = 1 To NCC
CIDH(M4) = CID(M4)
XIH(M4) = XIC(M4)
Next M4
NCCH = NCC
Next IH End Sub
Function DDETAIL(P, T) Dim IMAX As Integer, I As Integer, CODE As Integer
Dim EPSP As Double, EPSR As Double, EPSMIN As Double, RHO As Double, RHOL As Double, RHOH As Double, _
PRHOL As Double, PRHOH As Double '''''''''''''' T As Double, P As Double
Dim DDETAIL As Integer, PDETAIL As Integer, X1 As Integer, X2 As Integer, _
X3 As Integer, Y1 As Integer, Y2 As Integer, Y3 As Integer
Dim DELX As Double, DELPRV As Double, DELMIN As Double, DELBIS As Double, XNUMER As Double, XDENOM As Double, SGNDEL As Double
Dim Y2MY3 As Double, Y3MY1 As Double, Y1MY2 As Double, BOUNDN As Double IMAX = 150
EPSP = 0.000001
EPSR = 0.000001
EPSMIN = 0.0000001
CODE = 0 '--------------------call braket()
Call su_BRAKET
If CODE = 1 Or CODE = 3 Then
DDETAIL = RHO
End If X1 = RHOL
X2 = RHOH
Y1 = PRHOL - P
Y2 = PRHOH - P
DELX = X1 - X2
DELPRV = DELX X3 = X1
Y3 = Y1 For I = 1 To IMAX
If Y2 * Y3 > 0 Then
X3 = X1
Y3 = Y1
DELX = X1 - X2
DELPRV = DELX
End If
If Abs(Y3) < Abs(Y2) Then
X1 = X2
X2 = X3
X3 = X1
Y1 = Y2
Y2 = Y3
Y3 = Y1
End If
DELMIN = EPSMIN * Abs(X2)
DELBIS = 0.5 * (X3 - X2)
If Abs(DELPRV) < DELMIN Or Abs(Y1) < Abs(Y2) Then
DELX = DELBIS
DELPRV = DELBIS
Else
If X3 <> X1 Then
Y2MY3 = Y2 - Y3
Y3MY1 = Y3 - Y1
Y1MY2 = Y1 - Y2
XDENOM = -(Y1MY2) * (Y2MY3) * (Y3MY1)
XNUMER = X1 * Y2 * Y3 * (Y2MY3) + X2 * Y3 * Y1 * (Y3MY1) + X3 * Y1 * Y2 * (Y1MY2) - X2 * XDENOM
Else
XNUMER = (X2 - X1) * Y2
XDENOM = Y1 - Y2
End If
If 2 * Abs(XNUMER) < Abs(DELPRV * XDENOM) Then
DELPRV = DELX
DELX = XNUMER / XDENOM
Else
DELX = DELBIS
DELPRV = DELBIS
End If
End If
If (Abs(Y2) < EPSP * P) And (Abs(DELX) < EPSR * Abs(X2)) Then
DDETAIL = X2 + DELX
End If
If Abs(DELX) < DELMIN Then
SGNDEL = DELBIS / Abs(DELBIS)
DELX = 1.0000009 * SGNDEL * DELMIN
DELPRV = DELX
End If
BOUNDN = DELX * (X2 + DELX - X3)
If BOUNDN > 0 Then
DELX = DELBIS
DELPRV = DELBIS
End If
X1 = X2
Y1 = Y2
X2 = X2 + DELX
Y2 = PDETAIL(X2, T) - P '----------------------------call PDETAIL
Next I
MsgBox ("DDETAIL:MAXIMUM NUMBER OF ITERATIONS EXCEEDED")
DDETAIL = X2
End Function
特别是在函数内声明了一个与函数同名的变量:
Function DDETAIL(P, T) Dim IMAX As Integer, I As Integer, CODE As Integer
Dim EPSP As Double, EPSR As Double, EPSMIN As Double, RHO As Double, RHOL As Double, RHOH As Double, _
PRHOL As Double, PRHOH As Double '''''''''''''' T As Double, P As Double
Dim DDETAIL As Integer, PDETAIL As Integer, X1 As Integer, X2 As Integer, _
...............
..............'--------------------call braket()
Call su_BRAKET
If CODE = 1 Or CODE = 3 Then
DDETAIL = RHO
End If
............
.............
If (Abs(Y2) < EPSP * P) And (Abs(DELX) < EPSR * Abs(X2)) Then
DDETAIL = X2 + DELX
End If
....................
........... MsgBox ("DDETAIL:MAXIMUM NUMBER OF ITERATIONS EXCEEDED")
DDETAIL = X2
End Function
鬼才知道你是指 那个局部变量 还是 函数 本身啊!
楼主似乎觉得多用 DDETAIL 很 NB!
标识符几乎有无穷多种组合,楼主怎么就那么钟爱 DDETAIL 呢?
Function DDETAIL(P, T) as integer
相当于
Function DDETAIL(P, T) as variant
Option ExplicitSub main() MsgBox max(3, 5)
End SubFunction max(m As Integer, n As Integer) As Integer If (m > n) Then
max = m
Else
max = n
End IfEnd Function
Dim D As Double, Z As Double, ZB As Double, DB As Double, FPV As Double, TK As Double, PMP As Double, DDETAIL As Double, ZDETAIL As Double, UNITS As Double因为 DDETAIL As Double'声明了一个局部变量 DDETAIL
所以 D = DDETAIL(PMP,TK) 的时候,会提示需要数组,是提示你应该把
DDETAIL声明称数组。而你的目的是调用函数,所以删除 DDETAIL As Double 即可。