当我在VBA-Excel中把1个子分成2个子时，“下标超出范围”错误

I wrote a VBA-SQL code in a "Module" which reads a table from a sheet, then it sends the table to server via SQL-code. I am saving each column as an array result1(), result2(), result3(), and Col1, Col2 are just column names in the server. it is index for the arrays (same index for all arrays) The VBA-SQL code is something like this. And this Sub works perfectly:

我在“模块”中编写了一个VBA-SQL代码，该模块从表中读取表，然后通过sql代码将表发送到服务器。我将每个列保存为一个数组result1()、result2()、result3()和Col1, Col2只是服务器中的列名。它是数组的索引(所有数组的索引都是相同的)。这个子程序运行得很好:

Sub Datasend_Click()

Dim result1() As Variant, result2() As Variant, result3() As Variant
Dim Col1 As String, Col2 As String, Col3 As String
Dim it As Integer

    Set ValidSheet = Worksheets("Sheet2")
    Set DataRange = ValidSheet.Range("C22:C81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result1(it)
            result1(it) = dataa.Value
            it = it + 1
    Next


     Set DataRange = ValidSheet.Range("D22:D81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result2(it)
            result2(it) = dataa.Value
            it = it + 1
    Next

 For it = 1 To 60

    SQL = "INSERT INTO PaymentPattern (" & Col1 & ", " & Col2 & ", " & Col3 & ") "
    SQL = SQL & "VALUES (" & Str(result1(it)) & ", " & Str(result2(it)) & ", " & Str(result3(it)) & ")"

    dbclass.ExecuteSQL SQL

  Next it

End Sub

However, I want to separate this Sub into 2 Sub's instead. The first Sub will read and save the arrays from the sheet (then I can use the arrays for other Sub's), and the second Sub will call the first Suband run the SQL-code to send the arrays to the server. I wrote 2 Sub's like below, but I get an error like "Subscript out of range" for the line SQL = SQL & "VALUES (" & Str(result1(it)) & ", " & Str(result2(it)) & ", " & Str(result3(it)) & ")". When I debug it, it shows that it=1 as normal, but it shows "Subcript out of range" for the result1(it). Can you say please what is wrong?

但是，我想把这个下标分成两个下标。第一个子代将从表中读取并保存数组(然后我可以使用其他子代的数组)，第二个子代将调用第一个子代并运行sql代码将数组发送到服务器。我写了两个Sub，如下所示，但是我得到了一个错误，比如“下标超出范围”，用于行SQL = SQL &“值(”& Str(result1(it))和“& Str(result2(it))和”、“& Str(result3(it)和”)”。当我调试它时，它显示它=1正常，但是它显示了结果t1的“Subcript out of range”(它)。你能告诉我怎么了吗?

Sub arrayread()
  Dim result1() As Variant, result2() As Variant, result3() As Variant
  Dim it As Integer

    Set ValidSheet = Worksheets("Sheet2")
    Set DataRange = ValidSheet.Range("C22:C81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result1(it)
            result1(it) = dataa.Value
            it = it + 1
    Next


     Set DataRange = ValidSheet.Range("D22:D81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result2(it)
            result2(it) = dataa.Value
            it = it + 1
    Next


End Sub

Sub Datasend_Click()
  Dim result1() As Variant, result2() As Variant, result3() As Variant
  Dim it As Integer
  Dim Col1 As String, Col2 As String, Col3 As String

  arrayread

  For it = 1 To 60

    SQL = "INSERT INTO PaymentPattern (" & Col1 & ", " & Col2 & ", " & Col3 & ") "
    SQL = SQL & "VALUES (" & Str(result1(it)) & ", " & Str(result2(it)) & ", " & Str(result3(it)) & ")"

    dbclass.ExecuteSQL SQL

  Next it

End Sub

1 个解决方案

#1

It comes down to the scope of the arrays (where they are declared). You are declaring them separately in each sub, so result1 in Datasend_Click knows nothing about result1 from arrayread. Therefore, result1 and result2 will be empty in the second sub which causes your Subscript out of range error, given you have no elements yet you are trying to access it.

它归结到数组的范围(声明它们的位置)。您将在每个sub中分别声明它们，因此Datasend_Click中的result1与arrayread没有任何关系。因此，在第二个sub中，result1和result2是空的，这会导致下标超出范围错误，因为您没有元素，但是您正在尝试访问它。

Here is a Microsoft article outlining VBA variable scope: https://support.microsoft.com/en-us/kb/141693. Chip Pearson also has a good article on this subject: http://www.cpearson.com/excel/scope.aspx

下面是一篇概述VBA变量作用域的Microsoft文章:https://support.microsoft.com/en-us/kb/141693。Chip Pearson在这方面也有一篇不错的文章:http://www.cpearson.com/excel/scope.aspx

You should move the declaration of result1 and result2 to the top of your Module (above the Subs) so they they have Class Level scope (available to all methods in the Module).

您应该将result1和result2的声明移动到模块的顶部(在Subs之上)，以便它们具有类级范围(对模块中的所有方法都可用)。

Dim result1() As Variant, result2() As Variant, result3() As Variant


Sub arrayread()
  Dim it As Integer

    Set ValidSheet = Worksheets("Sheet2")
    Set DataRange = ValidSheet.Range("C22:C81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result1(it)
            result1(it) = dataa.Value
            it = it + 1
    Next


     Set DataRange = ValidSheet.Range("D22:D81")
        it = 1
    For Each dataa In DataRange
        ReDim Preserve result2(it)
            result2(it) = dataa.Value
            it = it + 1
    Next


End Sub

Sub Datasend_Click()

  Dim it As Integer
  Dim Col1 As String, Col2 As String, Col3 As String

  arrayread

  For it = 1 To 60

    SQL = "INSERT INTO PaymentPattern (" & Col1 & ", " & Col2 & ", " & Col3 & ") "
    SQL = SQL & "VALUES (" & Str(result1(it)) & ", " & Str(result2(it)) & ", " & Str(result3(it)) & ")"

    dbclass.ExecuteSQL SQL

  Next it

End Sub

My preferred solution would be to be passing the results in and out of the methods rather than having module-level scope, as this can cause spaghetti code issues down the track.

我的首选解决方案是将结果传入和退出方法，而不是使用模块级的范围，因为这可能导致在轨道上的意大利面代码问题。

#1