EXCEL中VB编程，下标越界错误

Question

Option Explicit

Public i As Integer, J As Integer, k As Integer, m As Integer, l As String
Public p As Integer

'定义暂存数组
Public N_Sum(23, 2) As String
Public M_Sum(23, 2) As String
Public Mid_Sum(2) As String

'定义各类型产品的不良项目数
Public Const N_SLD = 30

'定义取代产品型号的字母
Public Const H = "SLD"

Sub Command_SLD()
Sheets(H).Select
p = 0

Do While p < 33
'判断第一周的不良项目及其情况，并筛选出不良项目不为零的项目,将单元格内容暂存入数组中
J = 0
For i = 1 To N_SLD
If Sheets(H).Cells(91 + i, 15 + p) <> "0" Then
J = J + 1
N_Sum(J, 1) = Sheets(H).Cells(91 + i, 3)
N_Sum(J, 2) = Sheets(H).Cells(91 + i, 15 + p)
End If
Next i

'将数组中的内容按不良数的大小做从大到小的排序

For m = 1 To J
k = 0
For i = 1 To J
If Val(N_Sum(i, 2)) > k Then
Mid_Sum(1) = k
Mid_Sum(2) = l
k = CInt(N_Sum(i, 2))
l = N_Sum(i, 1)
N_Sum(i, 2) = Mid_Sum(1)
N_Sum(i, 1) = Mid_Sum(2)

End If
Next i

M_Sum(m, 1) = l
M_Sum(m, 2) = k
Next m

'清空统计表格
For m = 1 To N_SLD
Sheets(H).Cells(125 + m, 10 + p) = ""
Sheets(H).Cells(125 + m, 11 + p) = ""
Next m

For m = 1 To J
Sheets(H).Cells(125 + m, 10 + p) = M_Sum(m, 1)
Sheets(H).Cells(125 + m, 11 + p) = M_Sum(m, 2)
Next m
p = p + 8
i = 0
J = 0
k = 0
l = ""
m = 0

Loop

'*************统计月报表。***************
J = 0
For i = 1 To N_SLD
If Sheets(H).Cells(91 + i, 48) <> "0" Then
J = J + 1
N_Sum(J, 1) = Sheets(H).Cells(91 + i, 3)
N_Sum(J, 2) = Sheets(H).Cells(91 + i, 48)
End If
Next i

'将数组中的内容按不良数的大小做从大到小的排序

For m = 1 To J
k = 0
For i = 1 To J
If Val(N_Sum(i, 2)) > k Then
Mid_Sum(1) = k
Mid_Sum(2) = l
k = CInt(N_Sum(i, 2))
l = N_Sum(i, 1)
N_Sum(i, 2) = Mid_Sum(1)
N_Sum(i, 1) = Mid_Sum(2)

End If
Next i

M_Sum(m, 1) = l
M_Sum(m, 2) = k
Next m

'清空统计表格
For m = 1 To N_SLD
Sheets(H).Cells(125 + m, 50) = ""
Sheets(H).Cells(125 + m, 51) = ""
Next m

For m = 1 To J
Sheets(H).Cells(125 + m, 50) = M_Sum(m, 1)
Sheets(H).Cells(125 + m, 51) = M_Sum(m, 2)
Next m
p = p + 8
i = 0
J = 0
k = 0
l = ""
m = 0

End Sub
我是照着一个程序改的，只改了一些单元格的位置数据。其他的没动。怎么回事？帮忙指出。

监视 Sheets(H).Select ：下标越界

Answer 1

Public N_Sum(23, 2) As String
Public M_Sum(23, 2) As String
Public Mid_Sum(2) As String
'定义各类型产品的不良项目数
Public Const N_SLD = 30
问题可能就在这里！N_Sum(23, 2)、M_Sum(23, 2)的下标是23，2，但是你在循环：
For i = 1 To N_SLD
If Sheets(H).Cells(91 + i, 48) <> "0" Then
J = J + 1
N_Sum(J, 1) = Sheets(H).Cells(91 + i, 3)
N_Sum(J, 2) = Sheets(H).Cells(91 + i, 48)
End If
Next i
中是到N_SLD，而N_SLD的值是30，况且还有J=J+1，这样J的值是2、4、7、11、16、22变化，因此循环到i=7下标就越界了。

Answer 2

这是你没有事先创建空记事薄的缘故！
使用Workbooks.Add方法就可以了。

这样的，工作表的选取有两种办法：用名称，用索引。

这里可能是因为打的工作表改过名字。所以建议用索引。

改为 Workbooks(ViceName).Sheets(1).Activate