Re: if statements and nesting

A major disadvantage with this particular type of UDF being, of course,
that it's "inflexible and error prone", and therefore, per your
definition, of "bad design".

Since it's non-volatile and doesn't include the input cells as
arguments, the user can't have any confidence that the result is correct
unless a manual calculate all has just been performed.

If a row or column were inserted in the range of inputs, the UDF fails
without any indication, while the formula self-adjusts.

Note also that Harlan's formula doesn't depend on the input cell
configuration (i.e., change C3 to Z47 and it works fine), or on any
pattern to the values a, b, c, etc. (which were not assumed, by me at
least, to be characters: it works if a=5, b="hello", c=FALSE, etc.), or
on any pattern to the results (which could be any types of values, not
just a declining numeric series).

Those problems could be solved using arguments, named ranges and arrays
of values, along with extensive error checking, of course, but at that
point, the complexity of the UDF, not to mention the skill necessary to
maintain it, probably exceeds its utility.

And of course, it requires that macros be enabled...


In article <dvraks$tia$1@xxxxxxxxxxxx>,
"Rommert J. Casimir" <casimir@xxxxxx> wrote:

In ordinary programming, five or more successive if statements usually are a
sign of bad design, as they are inflexible and error-prone. For the problem
stated, I would prefer a VBA function, such as:

Function Getvalue()
Dim Found As Boolean, i As Integer, c As Byte
Getvalue = ""
Found = False: i = 2: c = Asc("a")
While Not Found And i <> 10
If Cells(i, i) = Chr(c) Then
Found = True: Getvalue = i - 1
Else
i = i + 1: c = c + 1
End If
Wend
End Function

My general advice is:

If you reach the limits of your tool, try to simplify your problem or look
for a better tool
.