VBA: Problems w/ StrComp and If...Then
Posted by Christian on January 31, 2002 9:14 AM
I would appreciate any help someone could give me. I'm writing my first VBA program in Excel.
Element = Worksheets("Calculations").Range("F1")
The value for this is "AY"
ACSComp = StrComp(Element, "ACS", 1)
AYComp = StrComp(Element, "AY", 1)
TubeComp = StrComp(Element, "Tube", 1)
SteelComp = StrComp(Element, "Steel", 1)
These return 1,0,-1,-1 respectively
#If ACSComp = 0 Then
Call DrawACS
Exit Sub
#End If
#If AYComp = 0 Then
Call DrawAY
Exit Sub
#End If
The result here is that it calls DrawACS, not DrawAY. If I enter anything other that 0 for #If ASCComp =, then it drops down and runs DrawAY.
Does anyone have any idea what's going on? Only AYComp is returning a 0, so why does the If think they all are??? Below is the entire code. Thank you!
---Entire Code---
Dim Element As String 'Element identifier
Dim X As Integer, Y As Integer
Dim Xbase As Integer, Ybase As Integer 'Base coordinates
Dim Dibase As Integer 'Base diameter
Dim Di As Integer
Dim Name As String 'Name of new Element
Dim SH As String
Dim ACSComp As Integer, AYComp As Integer, TubeComp As Integer, SteelComp As Integer
Private Sub DrawCable()
'Variables
Dibase = 26.5 'Base Diameter
Xbase = "78" 'CM X position
Ybase = "126" 'CM Y Position
'CM
Element = Worksheets("Calculations").Range("F1")
X = Xbase
Y = Ybase
Di = Dibase
Name = "CM"
Call Drawit
Exit Sub
End Sub
Private Sub Drawit()
Call ChooseType
Exit Sub
End Sub
Private Sub DrawACS()
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X, Y, Di, Di).Name = "ACSOut"
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X + 3, Y + 3, Di - 6, Di - 6).Name = "ACSIn"
Worksheets("Data Sheets").Shapes("ACSIn").Select
Selection.ShapeRange.Fill.Patterned msoPattern50Percent
Worksheets("Data Sheets").Shapes.Range(Array("ACSOut", "ACSIn")).Select
Selection.ShapeRange.Group.Name = Name
Exit Sub
End Sub
Private Sub DrawAY()
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X, Y, Di, Di).Name = Name
Worksheets("Data Sheets").Shapes(Name).Select
Selection.ShapeRange.Fill.Patterned msoPattern20Percent
Exit Sub
End Sub
Private Sub DrawTube()
Dim DiT As Integer
DiT = Di * 0.86 'Tube Diameter
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X, Y, Di, Di).Name = "TubeFrame"
Worksheets("Data Sheets").Shapes("TubeFrame").Select
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X, Y, DiT, DiT).Name = "TubeSelf"
Worksheets("Data Sheets").Shapes("Tubeself").Select
Selection.ShapeRange.Line.Weight = 2#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Worksheets("Data Sheets").Shapes.Range(Array("TubeFrame", "TubeSelf")).Select
Selection.ShapeRange.Align msoAlignCenters, False
Selection.ShapeRange.Align msoAlignMiddles, False
Selection.ShapeRange.Group.Name = Name
Exit Sub
End Sub
Private Sub DrawSteel()
Worksheets("Data Sheets").Shapes.AddShape(msoShapeOval, X, Y, Di, Di).Name = Name
Worksheets("Data Sheets").Shapes(Name).Select
Selection.ShapeRange.Fill.Patterned msoPattern60Percent
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 55
Exit Sub
End Sub
Private Sub ChooseType() 'Identifies correct Wire Drawing
ACSComp = StrComp(Element, "ACS", 1) 'Compares Element to element types (0 means a match)
AYComp = StrComp(Element, "AY", 1)
TubeComp = StrComp(Element, "Tube", 1)
SteelComp = StrComp(Element, "Steel", 1)
Range("B6").Select
ActiveCell.FormulaR1C1 = ACSComp
Range("B7").Select
ActiveCell.FormulaR1C1 = AYComp
Range("B8").Select
ActiveCell.FormulaR1C1 = TubeComp
Range("B9").Select
ActiveCell.FormulaR1C1 = SteelComp
Range("D6").Select
ActiveCell.FormulaR1C1 = Element
#If ACSComp = 0 Then
Call DrawACS
Exit Sub
#End If
#If AYComp = 0 Then
Call DrawAY
Exit Sub
#End If
#If TubeComp = 0 Then
Call DrawTube
Exit Sub
#End If
#If SteelComp = 0 Then
Call DrawSteel
Exit Sub
#End If
Response = MsgBox("Wiretype not found!", 16, Error)
End Sub
Private Sub scrap()
ACSComp = StrComp(Element, "ACS", 1) 'Compares Element to element types (0 means a match)
AYComp = StrComp(Element, "AY", 1)
TubeComp = StrComp(Element, "Tube", 1)
SteelComp = StrComp(Element, "Steel", 1)
Range("A1").Select
ActiveCell.FormulaR1C1 = ACSComp
Range("A2").Select
ActiveCell.FormulaR1C1 = AYComp
Range("A3").Select
ActiveCell.FormulaR1C1 = TubeComp
Range("A4").Select
ActiveCell.FormulaR1C1 = SteelComp
Range("C1").Select
ActiveCell.FormulaR1C1 = Element
End Sub