VBA excel function

Question

I need to make an Excel macro that takes triangle side length and makes sure it exists and it if does it needs to output what type of triangle it is.

Whenever I type values a = 2, b = 2 and c = 2, it should be an equilateral triangle but it outputs it as an a isosceles triangle.

Any help appreciated!

Public Function triangle(a, b, c) As String
Select Case True
Case a + b <= c Or a + c <= b Or c + b <= a
triangle = "Doesnt exist"
Case a = b Or c = b Or a = c
triangle = "Isosceles"
Case (a + b) ^ 2 = c ^ 2 Or (b + c) ^ 2 = b ^ 2 Or (c + a) ^ 2 = a ^ 2
triangle = "right triangle"
Case a = b = c
triangle = "Equilateral"
Case Else
triangle = "Regular triangle"

End Select

MsgBox triangle

End Function

It meets the criteria for Isosceles before it gets to equilateral. — Warcupine
– Warcupine, Commented Sep 9, 2019 at 17:46
Also i don't believe a = b = c will work in VBA, you need and statements. — Warcupine
– Warcupine, Commented Sep 9, 2019 at 17:48
@Warcupine so.. you're posting an answer or I submit my draft? ;-) — Mathieu Guindon
– Mathieu Guindon, Commented Sep 9, 2019 at 17:49
@MathieuGuindon Have at it, yours will be better explained than mine. — Warcupine
– Warcupine, Commented Sep 9, 2019 at 17:50

Mathieu Guindon · Accepted Answer · 2019-09-09 21:21:39Z

2

Case blocks evaluate top-to-bottom, and whichever branch first matches the condition (i.e. whichever is the first to be True) will be the winning condition.

Thus, you should evaluate the most constrained rules first, and the less constrained ones last - meaning you need to check for an equilateral triangle before you check for an isosceles one, since an equilateral triangle is also isosceles, as Warcupine already noted.

But there's a problem with the equilateral condition:

Case a = b = c

VBA will evaluate a = b as a Boolean expression, and then take the result of that and compare it to c - so if c is any non-zero value, a = b = c will be True regardless of the value of c (assuming non-zero) if both a and b are equal.

If the function is intended to be used as a UDF, consider avoiding MsgBox calls, and returning a Variant so that you can yield an actual Excel cell error value given invalid inputs - note that what you call a "regular triangle" probably falls under the definition of a scalene triangle:

Public Function TriangleType(ByVal a As Double, ByVal b As Double, ByVal c As Double) As Variant
    If a <= 0 Or b <= 0 Or c <= 0 Then
        TriangleType = CVErr(xlErrValue)
        Exit Function
    End If

    Select Case True
        Case a + b <= c Or a + c <= b Or c + b <= a
            TriangleType = CVErr(xlErrValue)

        Case a = b And b = c And c = a
            TriangleType = "Equilateral"
        Case a = b Or c = b Or a = c
            TriangleType = "Isosceles"
        'Case (a + b) ^ 2 = c ^ 2 Or (b + c) ^ 2 = b ^ 2 Or (c + a) ^ 2 = a ^ 2
        Case a ^ 2 + b ^ 2 = c ^ 2 Or b ^ 2 + c ^ 2 = a ^ 2 Or c ^ 2 + a ^ 2 = b ^ 2
            TriangleType = "Right Triangle"

        Case Else
            TriangleType = "Scalene Triangle"
    End Select

End Function

edited Sep 9, 2019 at 21:21

answered Sep 9, 2019 at 17:53

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Warcupine Over a year ago

That a=b=c explanation was what I couldn't explain, just knew it wasn't valid, +1 for knowledge.

chris neilsen Over a year ago

FYI, there's also a problem with the OP's Right Triangle condition

chris neilsen Over a year ago

@Mathieu not quite. Should be a^2 +b^2 =c^2 etc

Mathieu Guindon Over a year ago

@chrisneilsen lol, I math hates me (actually fixed now) - thanks!

chris neilsen Over a year ago

Thinking about this some more, this shows a weakness in the OPs classification logic: a triangle can be both Isosceles and Right. Should the function report both? And the floating point precision issue definitely needs to be addressed. As is, some of these tests will fail on all but a few special cases

|

simple-solution · Accepted Answer · 2019-09-09 18:00:24Z

2

You might want to think about numerical considerations! At what point of accuracy you want to consider a triangle a "right triangle"?

3 + 4 + 5 => right triangle

3 + 4 + 5,000001 => regular (scalene) triangle (right now!!)

Option Explicit

Public Function triangle(a, b, c) As String

If a + b <= c Or a + c <= b Or c + b <= a Then
    triangle = "Doesnt exist"
ElseIf a = b And a = c And b = c Then
    triangle = "equilateral"
ElseIf a = b Or c = b Or a = c Then
    triangle = "isosceles"
ElseIf (a ^ 2 + b ^ 2) = c ^ 2 Or (b ^ 2 + c ^ 2) = b ^ 2 Or (c ^ 2 + a ^ 2) = a ^ 2 Then
    triangle = "right triangle"
Else
    triangle = "regular (scalene) triangle"
End If

Debug.Print a, b, c, " --> ", triangle

End Function

answered Sep 9, 2019 at 18:00

simple-solution

1,1491 gold badge6 silver badges13 bronze badges

1 Comment

Mathieu Guindon Over a year ago

Good point. This is true for anything involving float-point values!

Collectives™ on Stack Overflow

VBA excel function

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related