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



Posted by Mark O'Brien on January 31, 2002 11:32 AM

Try changing the first line in your snippet to:

Element = Worksheets("Calculations").Range("F1").value

This should work.

Why are you using "#If" statements and not regular "If" statements? "#If" is usually only used to tell the compiler to ignore code dependent on the OS platform. This may also go some way to solving the problem, e.g.

If ACSComp = 0 Then
Call DrawACS
Exit Sub
End If