Hi Folks,
I have been racking my brain for a few days now on this problem. Here's what I have encountered:
I have a sheet with rows of input data. The first time I activate the routine, I pick up a line of input values from this sheet and do some calculations on them. When I am done, I create an array of variables (Each variable an output value from the calculations above the array call out) and put them into a row in an output spreadsheet.
Then, I grab the next line of input data, and then I also need to pull values for certain variables for this next set of calculations. These come from the last line of data in the output spreadsheet (the data that was placed by the array of variables) and I pull the values I need into variables. Here is the problem:
When I add a watch with a break when the value changes and I play the script; the variables fill with the right numbers. BUT, when it comes time for the calculations to be performed the variables change to strings with the variable names that I used to put them into the output spreadsheet. I get a type mismatch error when I start to perform calculations because by the time the calculations begin the variables have transformed from double to string.... Does anybody have a clue what may be going on? Thank you very much for your time!
-Chris
Here's the problem area:
Here is the portion of code I am having trouble with:
I have been racking my brain for a few days now on this problem. Here's what I have encountered:
I have a sheet with rows of input data. The first time I activate the routine, I pick up a line of input values from this sheet and do some calculations on them. When I am done, I create an array of variables (Each variable an output value from the calculations above the array call out) and put them into a row in an output spreadsheet.
Then, I grab the next line of input data, and then I also need to pull values for certain variables for this next set of calculations. These come from the last line of data in the output spreadsheet (the data that was placed by the array of variables) and I pull the values I need into variables. Here is the problem:
When I add a watch with a break when the value changes and I play the script; the variables fill with the right numbers. BUT, when it comes time for the calculations to be performed the variables change to strings with the variable names that I used to put them into the output spreadsheet. I get a type mismatch error when I start to perform calculations because by the time the calculations begin the variables have transformed from double to string.... Does anybody have a clue what may be going on? Thank you very much for your time!
-Chris
Here's the problem area:
Code:
'**************************************************************************************************
'***********************************PROBLEM AREA **************************************************
'**************************************************************************************************
With Sheets(PLANFORM_OUTPUT_SHEET)
X_LEI = .Range("A" & COUNT1).Value
Y_I = .Range("B" & COUNT1).Value
X_TEI = .Range("D" & COUNT1).Value
CI = .Range("M" & COUNT1).Value
End With
'AND NOW FOR SIMILAR CALCULATIONS PERFORMED ABOVE:
'Y-LOCATION OF OUTER CHORD AND CHANGE IN Y-LOCATION:
Y_O = (SPAN / 2) * PANEL_ARRAY(1, 5) 'OUTER
'THIS IS WHERE THE VALUES CALLED OUT JUST ABOVE START CHANGING FROM THE ACTUAL VALUES TO THE STRING VARIABLES
'AND THE CODE SEEMS TO JUMP BACK UP TO THE STATEMENTS ABOVE WHEN IT DOES THIS.... ANY IDEAS??
DY = Y_O - Y_I 'DIFFERENCE
'**************************************************************************************************
'***********************************PROBLEM AREA **************************************************
'**************************************************************************************************Here is the portion of code I am having trouble with:
Code:
Private Sub CALC_PLANFORM_Click()
'THE USER INPUTS A VALID PFIN NAME THAT THE PROGRAM:
PLANFORM_INPUT_WS = InputBox(PROMPT:="ENTER A VALID PFIN WORKSHEET TO CALCULATE YOUR PLANFORM GEOMETRY. INPUT NAME OF PLANFORM INPUT SHEET WITHOUT PFIN.", _
Title:="2D PLANFORM WORKSHEET SPECIFICATION", Default:="")
PLANFORM_NEW_NAME = PLANFORM_INPUT_WS & " PFIN"
'DETERMINE THE NUMBER OF WING PANELS TO ANALYZE AND COMPUTE THE PLANFORM FROM
'EACH ROW IN THE PFIN WORKSHEET IS CONSIDERED A WING PANEL, THUS:
NUM_PANELS = Cells(Rows.Count, 1).End(xlUp).Row - 5
'THE - 5 IS TO SUBTRACT THE HEADER INFORMATION FROM THE LAST ROW NUMBER GIVEN. THIS GIVES THE NUMBER OF PANELS
'TO COMPUTE THE 2D PLANFORM WITH
'SELECT THE DESIRED PLANFORM INPUT SHEET:
Sheets(PLANFORM_NEW_NAME).Select
'**************************************************************************************************
'************************CHECKING A FEW PANEL ATTRIBUTES*******************************************
'**************************************************************************************************
'EACH PANEL HAS A COUPLE OF ATTRIBUTES WE WANT TO CHECK:
'1. FOR EACH PANEL THE INNER CHORD MUST BE EQUAL TO THE OUTER CHORD OF THE LAST PANEL. THE EXCEPTIONS
'ARE THE FIRST AND LAST PANEL INNER AND OUTER CHORDS RESPECTIVELY. THIS BIT OF CODE CHECKS THIS:
For I = 1 To (NUM_PANELS - 1)
If Cells(I + 6, 1).Value <> Cells(I + 5, 2) Then
MsgBox ("YOU DO NOT HAVE A CONSISTENT SET OF PANELS. YOU MAY WANT TO GO BACK AND CHECK THAT ALL INTERNAL PANELS HAVE THE SAME INNER CHORD AS THE OUTER CHORD OF THE LAST PANEL.")
End If
Next I
'2. FOR EACH PANEL THE INNER Y/2B MUST BE EQUAL TO THE Y/2B OF THE OUTER CHORD OF THE PREVIOUS PANEL. THE EXCEPTIONS
'ARE THE FIRST AND LAST PANEL INNER AND OUTER Y/2B'S RESPECTIVELY. THE FOLLOWING CODE CHECKS THIS:
For I = 1 To (NUM_PANELS - 1)
If Cells(I + 6, 4).Value <> Cells(I + 5, 5) Then
MsgBox ("YOU DO NOT HAVE A CONSISTENT SET OF PANELS. YOU MAY WANT TO GO BACK AND CHECK THAT ALL INTERNAL PANELS HAVE THE SAME Y/2B AS THE OUTER Y/2B OF THE LAST PANEL. OTHERWISE, GAPS MAY BE PRESENT IN THE PANELS.")
End If
Next I
'**************************************************************************************************
'********************END CHECKING A FEW PANEL ATTRIBUTES*******************************************
'**************************************************************************************************
'**************************************************************************************************
'***************CREATING AND FORMATTING OUTPUT SPREADSHEET AND HEADERS*****************************
'**************************************************************************************************
'NOW WE WILL CREATE A WORKSHEET THAT IS THE OUTPUT WORKSHEET. THIS IS WHERE ALL THE 2D CALCULATIONS WILL TAKE PLACE
PLANFORM_OUTPUT_SHEET = PLANFORM_INPUT_WS & " PFOUT"
Worksheets.Add(After:=Worksheets(PLANFORM_NEW_NAME)).Name = PLANFORM_OUTPUT_SHEET
'COLORING THE INPUT WORKSHEET CELLS ALL WHITE:
Cells.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'X LOCATION OF LEADING EDGE
Range("A1").Select
ActiveCell.FormulaR1C1 = "X_LE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" X LOCATION OF LEADING EDGE FROM GLOBAL X = 0"
'Y LOCATION OF LEADING EDGE
Range("B1").Select
ActiveCell.FormulaR1C1 = "Y_LE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" X LOCATION OF LEADING EDGE FROM GLOBAL Y = 0"
'Z LOCATION OF LEADING EDGE
Range("C1").Select
ActiveCell.FormulaR1C1 = "Z_LE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Z LOCATION OF LEADING EDGE FROM GLOBAL Z = 0. NOTE: NO TWIST OR DIHEDRAL/ANHEDRAL HAS BEEN ADDED AT THIS POINT"
'X LOCATION OF TRAILING EDGE
Range("D1").Select
ActiveCell.FormulaR1C1 = "X_TE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" X LOCATION OF TRAILING EDGE FROM GLOBAL X = 0"
'Y LOCATION OF TRAILING EDGE
Range("E1").Select
ActiveCell.FormulaR1C1 = "Y_TE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Y LOCATION OF TRAILING EDGE FROM GLOBAL Y = 0"
'Z LOCATION OF TRAILING EDGE
Range("F1").Select
ActiveCell.FormulaR1C1 = "Z_TE (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Z LOCATION OF TRAILING EDGE FROM GLOBAL Z = 0. NOTE: NO TWIST OR DIHEDRAL/ANHEDRAL HAS BEEN ADDED AT THIS POINT"
'X LOCATION OF FORWARD SPAR
Range("G1").Select
ActiveCell.FormulaR1C1 = "X_FS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" X LOCATION OF FORWARD SPAR FROM GLOBAL X = 0"
'Y LOCATION OF FORWARD SPAR
Range("H1").Select
ActiveCell.FormulaR1C1 = "Y_FS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Y LOCATION OF FORWARD SPAR FROM GLOBAL Y = 0"
'Z LOCATION OF FORWARD SPAR
Range("I1").Select
ActiveCell.FormulaR1C1 = "Z_FS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Z LOCATION OF FORWARD SPAR FROM GLOBAL Z = 0. NOTE: NO TWIST OR DIHEDRAL/ANHEDRAL HAS BEEN ADDED AT THIS POINT"
'X LOCATION OF REAR SPAR
Range("J1").Select
ActiveCell.FormulaR1C1 = "X_RS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" X LOCATION OF REAR SPAR FROM GLOBAL X = 0"
'Y LOCATION OF REAR SPAR
Range("K1").Select
ActiveCell.FormulaR1C1 = "Y_RS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Y LOCATION OF REAR SPAR FROM GLOBAL Y = 0"
'Z LOCATION OF REAR SPAR
Range("L1").Select
ActiveCell.FormulaR1C1 = "Z_RS (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" Z LOCATION OF REAR SPAR FROM GLOBAL Z = 0. NOTE: NO TWIST OR DIHEDRAL/ANHEDRAL HAS BEEN ADDED AT THIS POINT"
'AIRFOIL CHORD
Range("M1").Select
ActiveCell.FormulaR1C1 = "CHORD (IN)"
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=" CHORD OF CURRENT AIRFOIL SECTION IN 2D"
'AUTO-FIT THE CELLS TO THE NAMES
Cells.EntireColumn.AutoFit
'NOTE: LATER ON WE WILL DEVELOP THE AREA CALCULATIONS
'**************************************************************************************************
'***********END CREATING AND FORMATTING OUTPUT SPREADSHEET AND HEADERS*****************************
'**************************************************************************************************
'**************************************************************************************************
'*****************************CALCULATING THE PLANFORM GEOMETRY************************************
'**************************************************************************************************
'SELECT THE DESIRED PLANFORM INPUT SHEET:
Sheets(PLANFORM_NEW_NAME).Select
'LOAD THE X-LOCATION OF THE FIRST PANEL INNER CHORD LEADING EDGE:
X_LE_0 = Range("B1").Value
'LOAD THE SPAN FOR THE SEMI-SPAN % CALCULATIONS:
SPAN = Range("B2").Value
'LOAD THE Z-LOCATION OF THE PLANFORM. THIS WILL FORM THE LOCATION OF THE CENTERLINE AXIS FROM GLOBAL Z = 0:
Z_0 = Range("B3").Value
'CREATE AN ARRAY FOR THE 1'ST PANEL:
PANEL_ARRAY_1 = Range("A6:I6").Value
'ARRAY NOMENCLATURE:
'PANEL_ARRAY_1(1,1) = INNER CHORD (IN)
'PANEL_ARRAY_1(1,2) = OUTER CHORD (IN)
'PANEL_ARRAY_1(1,3) = QUARTER CHORD ANGLE (DEGREES FROM Y-AXIS (CW +))
'PANEL_ARRAY_1(1,4) = SEMI-SPAN LOCATION OF INNER CHORD ON PANEL (%)
'PANEL_ARRAY_1(1,5) = SEMI-SPAN LOCATION OF OUTER CHORD ON PANEL (%)
'PANEL_ARRAY_1(1,6) = INNER CHORD LOCATION OF FRONT SPAR (%)
'PANEL_ARRAY_1(1,7) = OUTER CHORD LOCATION OF FRONT SPAR (%)
'PANEL_ARRAY_1(1,8) = INNER CHORD LOCATION OF REAR SPAR (%)
'PANEL_ARRAY_1(1,9) = OUTER CHORD LOCATION OF REAR SPAR (%)
'THIS IF STATEMENT ALLOWS US TO GENERATE THE INNER MOST CHORD COORDINATES AND THEN
'SUBSEQUENTLY GENERATE THE OTHER COORDINATES BASED ON THESE INITIAL COORDINATES:
'Y-LOCATIONS OF INNER AND OUTER CHORD:
Y_I = (SPAN / 2) * PANEL_ARRAY_1(1, 4) 'INNER
Y_O = (SPAN / 2) * PANEL_ARRAY_1(1, 5) 'OUTER
'CHANGE IN Y-LOCATION BETWEEN INNER AND OUTER CHORDS WITHIN THE PANEL:
DY = Y_O - Y_I
'LOCATING LEADING EDGE OF INNER MOST CHORD:
X_LEI = X_LE_0
'LOCATING TRAILING EDGE OF INNER MOST CHORD:
X_TEI = X_LEI + PANEL_ARRAY_1(1, 1)
'LOCATING LEADING EDGE OF THE OUTER PANEL:
If PANEL_ARRAY_1(1, 3) = 90 Then 'THIS HELPS SET THE CHANGE IN CHORD DUE TO 90 DEGREE SWEEP TO ZERO
TANGENT = 0
Else
TANGENT = Tan(WorksheetFunction.Radians(PANEL_ARRAY_1(1, 3)))
End If
'CHANGE IN LEADING EDGE X-VALUE FROM INNER CHORD LOCATION
DX_LEO = 0.25 * PANEL_ARRAY_1(1, 1) + DY * TANGENT - 0.25 * PANEL_ARRAY_1(1, 2)
'X-LOCATION OF OUTER CHORD LEADING EDGE
X_LEO = X_LEI + DX_LEO
'X-LOCATION OF OUTER CHORD TRAILING EDGE
X_TEO = X_LEO + PANEL_ARRAY_1(1, 2)
'X-LOCATION OF INNER CHORD FORWARD SPAR
X_SP_IFWD = X_LEI + PANEL_ARRAY_1(1, 6) * PANEL_ARRAY_1(1, 1)
'X-LOCATION OF OUTER CHORD FORWARD SPAR
X_SP_OFWD = X_LEO + PANEL_ARRAY_1(1, 7) * PANEL_ARRAY_1(1, 2)
'X-LOCATION OF INNER CHORD REAR SPAR
X_SP_IAFT = X_TEI - PANEL_ARRAY_1(1, 8) * PANEL_ARRAY_1(1, 1)
'X-LOCATION OF OUTER CHORD REAR SPAR
X_SP_OAFT = X_TEO - PANEL_ARRAY_1(1, 9) * PANEL_ARRAY_1(1, 2)
'CALCULATING THE AREA OF THE I'TH PANEL:
AREA = DY * PANEL_ARRAY_1(1, 1) - 0.5 * DY * (X_LEO - X_LEI) - 0.5 * DY * (PANEL_ARRAY_1(1, 1) - X_TEO)
'PARTITION VALUES INTO ARRAY THAT WILL GO INTO THE OUTPUT SPREADSHEET AND BECOME THE BEGINNING CALCULATIONS FOR THE NEXT
'PANEL CALCULATION. THE INNER CHORD VALUES WILL BE RECORDED, WHILE THE OUTER CHORD WILL BECOME THE INNER CHORD
'CREATE AN ARRAY OF VALUES FOR THE INNER PANEL THAT WILL BE PASTED INTO THE OUTPUT SPREADSHEET THIS ITERATION:
INNER_CHORD_ARRAY = Array(X_LEI, Y_I, Z_0, X_TEI, Y_I, Z_0, X_SP_IFWD, Y_I, Z_0, X_SP_IAFT, Y_I, Z_0, PANEL_ARRAY_1(1, 1))
OUTER_CHORD_ARRAY = Array(X_LEO, Y_O, Z_0, X_TEO, Y_O, Z_0, X_SP_OFWD, Y_O, Z_0, X_SP_OAFT, Y_O, Z_0, PANEL_ARRAY_1(1, 2))
'GO TO THE OUTPUT SPREADSHEET AND PASTE IN THE VALUES OF THE ARRAYS ABOVE:
Sheets(PLANFORM_OUTPUT_SHEET).Select
Range("A2:M2").Value = INNER_CHORD_ARRAY
Range("A3:M3").Value = OUTER_CHORD_ARRAY
'NOW WE USE A FOR LOOP TO LOOP THROUGH EACH PANEL AND PERFORM THE CALCULATIONS TO LOCATE EACH LEAD EDGE, TRAILING EDGE
'AND SPAR.
For Count = 2 To NUM_PANELS
COUNT5 = Count + 5
'THE ABOVE CALCULATIONS ARE FOR THE FIRST PANEL ONLY. NOW WE WILL USE THE VALUES FOR THE OUTER CHORD OF THE FIRST
'PANEL TO DO CALCULATIONS ON THE NEXT PANEL AND SO ON:
'RE-SELECT INPUT WORKSHEET AND MAKE ARRAY OF CURRENT PANEL:
With Sheets(PLANFORM_NEW_NAME)
PANEL_ARRAY = .Range("A" & COUNT5 & ":I" & COUNT5).Value
End With
'ARRAY NOMENCLATURE:
'PANEL_ARRAY(1) = INNER CHORD (IN)
'PANEL_ARRAY(2) = OUTER CHORD (IN)
'PANEL_ARRAY(3) = QUARTER CHORD ANGLE (DEGREES FROM Y-AXIS (CW +))
'PANEL_ARRAY(4) = SEMI-SPAN LOCATION OF INNER CHORD ON PANEL (%)
'PANEL_ARRAY(5) = SEMI-SPAN LOCATION OF OUTER CHORD ON PANEL (%)
'PANEL_ARRAY(6) = INNER CHORD LOCATION OF FRONT SPAR (%)
'PANEL_ARRAY(7) = OUTER CHORD LOCATION OF FRONT SPAR (%)
'PANEL_ARRAY(8) = INNER CHORD LOCATION OF REAR SPAR (%)
'PANEL_ARRAY(9) = OUTER CHORD LOCATION OF REAR SPAR (%)
'SELECT OUTPUT SPREADSHEET AND CREATE AN ARRAY OF VALUES THAT CAN BE USED AS THE DEFINITION OF THE INNER CHORD OF
'THE CURRENT I'TH PANEL:
COUNT1 = Count + 1
'**************************************************************************************************
'***********************************PROBLEM AREA **************************************************
'**************************************************************************************************
With Sheets(PLANFORM_OUTPUT_SHEET)
X_LEI = .Range("A" & COUNT1).Value
Y_I = .Range("B" & COUNT1).Value
X_TEI = .Range("D" & COUNT1).Value
CI = .Range("M" & COUNT1).Value
End With
'AND NOW FOR SIMILAR CALCULATIONS PERFORMED ABOVE:
'Y-LOCATION OF OUTER CHORD AND CHANGE IN Y-LOCATION:
Y_O = (SPAN / 2) * PANEL_ARRAY(1, 5) 'OUTER
'THIS IS WHERE THE VALUES CALLED OUT JUST ABOVE START CHANGING FROM THE ACTUAL VALUES TO THE STRING VARIABLES
'AND THE CODE SEEMS TO JUMP BACK UP TO THE STATEMENTS ABOVE WHEN IT DOES THIS.... ANY IDEAS??
DY = Y_O - Y_I 'DIFFERENCE
'**************************************************************************************************
'***********************************PROBLEM AREA **************************************************
'**************************************************************************************************
'LOCATING LEADING EDGE OF THE OUTER PANEL:
If PANEL_ARRAY(1, 3) = 90 Then 'THIS HELPS SET THE CHANGE IN CHORD DUE TO 90 DEGREE SWEEP TO ZERO
TANGENT = 0
Else
TANGENT = Tan(WorksheetFunction.Radians(PANEL_ARRAY(1, 3)))
End If
'CHANGE IN LEADING EDGE X-VALUE FROM INNER CHORD LOCATION
DX_LEO = 0.25 * CI + DY * TANGENT - 0.25 * PANEL_ARRAY(1, 2)
'X-LOCATION OF OUTER CHORD LEADING EDGE
X_LEO = X_LEI + DX_LEO
'X-LOCATION OF OUTER CHORD TRAILING EDGE
X_TEO = X_LEO + PANEL_ARRAY(1, 2)
'X-LOCATION OF OUTER CHORD FORWARD SPAR
X_SP_OFWD = X_LEO + PANEL_ARRAY(1, 7) * PANEL_ARRAY(1, 2)
'X-LOCATION OF OUTER CHORD REAR SPAR
X_SP_OAFT = X_TEO - PANEL_ARRAY(1, 9) * PANEL_ARRAY(1, 2)
'CALCULATING THE AREA OF THE I'TH PANEL:
AREA = DY * CI - 0.5 * DY * (X_LEO - X_LEI) - 0.5 * DY * (CI - X_TEO)
'CREATING ARRAY THAT IS THE NEW OUTER CHORD OF THE I'TH PANEL
OUTBOARD_CHORD_ARRAY = Array("X_LEO", "Y_O", "Z_0", "X_TEO", "Y_O", "Z_0", "X_SP_OFWD", "Y_O", "Z_0", "X_SP_OAFT", "Y_O", "Z_0", "PANEL_ARRAY(2)")
'COPYING THE DATA TO THE OUTPUT SPREADSHEET:
Sheets(PLANFORM_OUTPUT_SHEET).Select
COUNT2 = Count + 2
Range("A" & COUNT2 & ":M" & COUNT2).Value = OUTBOARD_CHORD_ARRAY
Next Count
End Sub
