VBA 数组变体 - 下标超出范围

Question

这是我第一次在 VBA 中编程的第三天。过去我曾学习过 C 编程和 Java 编程，以供引用。从头开始制作自定义 Excel 宏。与此错误作斗争。花了好几个小时...

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

IF-ELSE 发生错误。在 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

你一个接一个。

除非 Option Base 1 另有说明，否则隐式大小的数组是从 0 开始的* .

鉴于你的例子说:

array1(1, 1) = 5 'doesn't work either

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

*从 Range 获得的数组将以 1 为基础。

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