VBA阵列变体- SubScript超出范围

Question

这是我第一次用VBA编程的第三天。我以前曾教过C编程和Java编程，以供参考。从零开始制作自定义Excel宏。与这一错误作斗争。花了好几个小时在上面..。

应用程序的目的是获取数据，并在工作表之间移动数据。这只是代码的一部分。

错误发生在IF-ELSE中。在“否则”中先发生，因此程序尚未尝试运行if部分。

注意，array1是全局声明的。例如，它甚至不允许我将第一个元素设置为5。但是，如果我试图更改TempArray120中的值(它已经存储了数据)，它可以正常工作。

^认为这是一个声明/实例化问题。

array1(i, 1) = ((TempArray120(i, 1) + TempArray277(i, 1)) / 2) 'getting the avg

^这是我遇到麻烦的台词。

array1(1, 1) = 5

^此行也不工作。

Dim array1() As Variant 'declare a array. The lower array determined by current
Dim array2() As Variant 'delcare a array. The upper array determined by current
Sub main()
    Call DataFetch("Test", False)
    Call DataFetch("Test1", True)
End Sub
Sub DataFetch(sheet As String, LowOrUpper As Boolean)
 'Instance Variable Declaration
    Dim TempArray120() As Variant 'create and array that will hold 10 values and the current for the 120volts
    Dim TempArray277() As Variant 'create and array that will hold 10 values and the current for the 277 volts

    TempArray120 = Worksheets(sheet).Range("F12:F2").Value    'read in the InPower from Dim lvl of 0Volts to 10volts @120volts
    TempArray120(11, 1) = Worksheets(sheet).Range("K2").Value  'read in the OutCurrent at the 10Volt Dim lvl @120volts
    TempArray277 = Worksheets(sheet).Range("F23:F13").Value    'read in the InPower from Dim lvl of 0Volts to 10volts @277volts
    TempArray277(11, 1) = Worksheets(sheet).Range("K13").Value  'read in the OutCurrent at the 10Volt Dim lvl @277volts
    'i belive the .value is correct for array use
    '-------------------------------------------------------------------------------------------------------
    'need to average this data and return to a global array. Needs to be the right array. Will check for that.
    'LowOrUpper is flase for lower current and true for higher current

    If LowOrUpper Then '-if the higher current data
        For i = 1 To 11 Step 1
            Set array2(i, 1) = ((TempArray120(i, 1).Value + TempArray277(i, 1).Value) / 2)   'set avg value to the global array. Note that this is for the lower array
        Next 'end of for loop
    Else '-was false and must be the lower current data
        For i = 1 To 11 Step 1
            array1(i, 1) = ((TempArray120(i, 1) + TempArray277(i, 1)) / 2)   'set avg value to the global array. Note that this is for the lower array
            'array1(i, 1) = TempArray120(i, 1)
                'this does not work. same error
            'array1(1, 1) = 5
                'this does not work. same error
            'TempArray120(1,1)=5
                '^this
        Next 'end of for loop
    End If
    '-------------------------------------------------------------------------------------------------------
    Call DataHandler
End Sub
'**********************************
Sub DataHandler()
 'Instance Variable Declaration
'-------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------
'paste data into lower and upper curve. The data will the be generated. This is the First generation
Worksheets("Step 1 - Coarse Curve").Range("C7:C18").Value = array1  'setting the data values for Lower Curve.Data is in Array1. This should work 5/18/2017 spent a lot of time on this line
Worksheets("Step 1 - Coarse Curve").Range("K7:K17").Value = array2 'setting the data values for Upper Curve.Data is in Array2

Worksheets("Step 1 - Coarse Curve").Range("B5").Value = array1(11, 1).Value 'setting the current cell for lower
Worksheets("Step 1 - Coarse Curve").Range("J5").Value = array2(11, 1).Value 'setting the current cell for upper
Worksheets("Step 1 - Coarse Curve").Range("F5").Value = Worksheets("Main").Range("B5") 'sets the generated data current to user spec
'-------------------------------------------------------------------------------------------------------

'-------------------------------------------------------------------------------------------------------
'handle the data that was just generated => Transfer to the Fine curve
Worksheets("Step 2 - Fine Curve").Range("E3:E13").Value = Worksheets("Step 1 - Coarse Curve").Range("H7:H17").Value 'this is correct
Worksheets("Step 2 - Fine Curve").Range("A102").Value = ID 'insert the ID at the end of data!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!MUST EDIT
Dim fineData As Range 'this will be sent to the CSV file
Set fineData = Worksheets("Step 2 - Fine Curve").Range("B2:B102").Value 'do not believe this needs a .value??????
'-------------------------------------------------------------------------------------------------------

'-------------------------------------------------------------------------------------------------------
'Open new file. Make it visiable.
Dim myFile As String 'will hold path name
myFile = Application.DefaultFilePath & "\" & ID & ".csv" 'determine the pathname for new CSV file
Open myFile For Output As #1 'allows the file to be written to. Can now be refered to as #1 as well
'^if the file already exist it will be deleted and a new file will be created with same name
'now write in the array data
Write #1, fineData.Value
Close #1 'gotta close the file
'note the csv file is saved to the root directory of project
'-------------------------------------------------------------------------------------------------------

End Sub

Answer 1

你太离经叛道了。

隐式大小数组是基于0的*，除非Option Base 1另有规定。

考虑到你的例子：

array1(1，1) =5‘也不起作用

唯一的解释是，假设数组是基于1的，而它们是基于0的。

*从Range中获得的数组将基于1。

由于在同一个模块中混合了基于0的隐式大小数组和基于1的Range数组，所以考虑指定Option Base 1来统一数组边界，并在模块中的任何地方使用基于1的数组，否则需要将(-1/+1)数组偏移到单元坐标。