|
-
Jun 28th, 2011, 09:54 AM
#1
Thread Starter
Junior Member
Multivariable Exception
This program is being used to analyze an excel document with coordinates for a robot. The entire program works...My boss indicated he wanted me to throw an exception in whenever a coordinate that wasn't being given a tolerance wasn't supposed to move, he wanted the program to give an error message. Which I figured out and outputs a message box saying "Result Compare Fail!"
Now here is my dilemma..whenever I encounter a section in the excel sheet analyzing multiple variables, I still get the error message, because the program doesn't think of it as multiple variable coordinates changing. Is there an easy way to fix this? I'm stumped!
This is one of the cases, but they are all very similar.
Code:
'\\\START DECLARATIONS OF VARIABLES////////////////////////////////////////////////
'Declarations for excel application control
Dim xlApp As Excel.Application 'Set xlApp as an excel application
Dim xlBook As Excel.Workbook 'Set xlBook as an excel workbook
Dim xlSheet1 As Excel.Worksheet 'Set xlSheet1 as an excel worksheet
'Declarations for excel cell search and load manipulation
Dim currentRow As Integer 'For statement iterator (used below)
Dim position As Integer 'Used for Array position in testArray
Dim cposition As Integer 'Used for Array position in result1Array/result2Array
Dim started As Boolean 'Have we searched through the Excel spreadsheet to the starting section yet?
'Declarations for columns of the Excel document (used for indexing and manipulating data)
Dim sectionColumn As Excel.Range 'Set sectionColumn as a range of values (values declared below)
Dim moveColumn As Excel.Range 'Excel column we search for a "move" in.
Dim resultColumn As Excel.Range 'Excel column we use for result comparisons
'Declarations for data arrays
Dim testArray() As String 'Test data array of strings
Dim result1Array() As String 'Top result data array of strings
Dim result2Array() As String 'Bottom result data array of strings
'Declarations for variables in the VB interface that we need to use
Dim srcStart As String 'Excel spreadsheet's starting test section
srcStart = section1Text.Text 'Set textbox to variable
Dim srcEnd As String 'Excel spreadsheet's ending test section
srcEnd = section2Text.Text 'Set textbox to variable
'Declarations for test data statistics
Dim pfColumn As Excel.Range 'Pass/Fail column
Dim passes As Integer 'Amount of passes
passes = 0 'Init passes to 0
Dim fails As Integer 'Amount of fails
fails = 0 'Init fails to 0
Dim rfails As Integer 'Amount or result fails
rfails = 0 'Init rfails to 0
'\\\STARTUP CODE: Opens Excel and sets up the columns//////////////////////////////
xlApp = CreateObject("Excel.Application") 'Use xlApp as Excel Application...
If showCheck.Checked Then
xlApp.Visible = True 'Make spreadsheet visible?, user input checkbox
End If
xlBook = xlApp.Workbooks.Open(fileText.Text) 'Open the filepath of XLS doc selected, user defined by fileText
xlSheet1 = xlBook.Worksheets(sheetText.Text) 'Open the Test Cases worksheet, user defined by sheetText
sectionColumn = xlSheet1.Range(rangeText.Text) 'Set the search range of entire spreadheet
moveColumn = xlSheet1.Range(rangeText.Text).Offset(0, 3) 'moveColumn is set to the column we will search for a move command
resultColumn = xlSheet1.Range(rangeText.Text).Offset(0, 6) 'resultColumn is set to the column we will compare our 2 results from
pfColumn = xlSheet1.Range(rangeText.Text).Offset(0, 5) 'pfColumn is set to the Pass/Fail column, so we can write in a P/F, accordingly
'\\\START SEARCH AND COMPARE ALGORITHM/////////////////////////////////////////////
'Start seaching for the section the user wants to test
For currentRow = 1 To sectionColumn.Count
'Find cell with starting section number and output a message box
If sectionColumn.Cells(currentRow).Value = srcStart Then
If (debugCheck.Checked = True) Or (failCheck.Checked = True) Then
MsgBox("STARTING AT: " + sectionColumn.Cells(currentRow).Text() + " " + sectionColumn.Cells(currentRow).offset(0, 2).Text() + " Row: " + currentRow.ToString)
End If
started = True
End If
'Had to use a goto to exit multiple for statements
SearchMove:
'If we have navigated to the section we want to test...
If started = True Then
'Search moveColumn cells for (default) "move", made user input now
If moveColumn.Cells(currentRow).Text().Contains(searchText.Text) Then
'This is where it gets REALLY REALLY FUN
'split command string into array of strings, store it into testArray
testArray = Split(moveColumn.Cells(currentRow).Text(), " ")
'split each result cell into array of strings, store into result1Array/result2Array
result1Array = Split(resultColumn.Cells(currentRow - 1).Text(), " ")
result2Array = Split(resultColumn.Cells(currentRow + 1).Text(), " ")
'Start comparison algorithm by using first column for index
For position = 1 To (testArray.Length - 1)
'For special cases section 20, 21, and 22
If (currentRow >= 609) Then
'Find number in array for abs moves, use the number as index
If IsNumber(testArray, position) And (testArray(position - 1) = "abs") Then
'Iterate array from array position 0 to max
For cposition = 1 To (result1Array.Length - 1)
If ((testArray(position - 2).ToLower = "x") Or (testArray(position - 2).ToLower = "y")) Then
testArray(position) *= -1
End If
'Since each command is in format variable|abs/rel|value, see if test data variable matches result variable
If testArray(position - 2).ToLower = result1Array(cposition).ToLower Then
'Format the rz if the angle is >180
If (testArray(position - 2).ToLower = "rz") Then
testArray(position) = ConvertAngle(testArray(position)).ToString
End If
'Calculate to see if the test passes/fails
If (testArray(position - 1) = "abs") AndAlso (AbsComparison(toDoub(result2Array, cposition + 1), toDoub(testArray, position), Convert.ToDouble(tolText.Text))) Then
If debugCheck.Checked = True Then
MsgBox("Abs PASS!" & vbNewLine & "Line: " + currentRow.ToString & vbNewLine & "T1: " + testArray(position - 2) + " " + testArray(position - 1) + " " + testArray(position) & vbNewLine & " R1: " + result1Array(cposition) + " " + result1Array(cposition + 1) & vbNewLine & " R2: " + result2Array(cposition) + " " + result2Array(cposition + 1))
End If
pfColumn.Cells(currentRow).Value = "P"
passes += 1
cposition += 2
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|