1

This is my third day programming in VBA for the first time. I have been taught C programming and Java programming in the past for reference. Making a custom Excel Macro from scratch. Struggling with this error. Have spent hours on it...

Purpose of application is to take data, and move it around between worksheets. This is only part of the code.

Error occurs in the IF-ELSE. Occurs in the else first, so the program has never tried to run the if portion yet.

Note that array1 is declared globally. It wont even let me set the first element to 5 for example. But if I try to change the value in the TempArray120 (which already has data stored) it works fine.

^thinking this is a declaration/instantiation issue

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

^this is the line I am having trouble with

array1(1, 1) = 5

^this line also does not work

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
12
  • 1
    You never set the size of array1() or array2() Commented May 18, 2017 at 19:19
  • Array subscripts holding a Double value shouldn't be assigned with the Set keyword, which is used to assign object references. Commented May 18, 2017 at 19:24
  • How is array1 declared and populated? Commented May 18, 2017 at 19:26
  • 1
    Array1 is declared at the very top. First line @Mat'sMug Commented May 18, 2017 at 19:28
  • and populated. FWIW Dim makes it local to the module, not anywhere near global. Commented May 18, 2017 at 19:28

1 Answer 1

3

You're off-by-one.

Implicitly sized arrays are 0-based*, unless specified otherwise by Option Base 1.

Given your example saying:

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

The only explanation is that you're assuming your arrays are 1-based, when they're 0-based.

*An array obtained from a Range will be 1-based.

Since you have a mix of 0-based implicitly-sized arrays and 1-based Range arrays in the same module, consider specifying Option Base 1 to unify the array bounds and work with 1-based arrays everywhere in the module, or you'll need to offset (-1/+1) array to cell coordinates.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.