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
array1()orarray2()Doublevalue shouldn't be assigned with theSetkeyword, which is used to assign object references.array1declared and populated?Dimmakes it local to the module, not anywhere near global.