Please Help - Conditional If Then code in VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kastnna - 04 Nov 2005 23:56 GMT

Need help again guys. My ignorance has resurfaced and I am on a
deadline to complete this new checklist. So far I have written the
code so that when a new doc is opened based on the checklist template,
a macro is immediately started. The macro ask a series of questions
using input boxes and the input is inserted into bookmarked TextFields.
I need a list to form and then input that list into the doc based on
the answers to the questions.

For example, the first input box ask which state the product is being
sold in. The next asks which product is being sold. The last input box
is a YesNo button box that ask if the product is a replacement. All
that information is then used to give a listing of the necessary forms
I would need to sell that product in that state. Different forms are
needed for differnet products and different states. The same product
will require diffenent forms for two different states. Finally, if the
product is a replacement, an additional form is required that also
varies by state.

Essentially I have a "IF state=a AND product=b AND replacement=c THEN
list=x; but IF state=a AND product=b AND replacement=d THEN list=w;
but............"

Please help and Thanks in advance!

Reply to this Message

Jezebel - 05 Nov 2005 01:00 GMT

There are several coding approaches, depending on how comples your logic is
going to get. Simplest is structure you're almost using ---

IF state=a AND product=b AND replacement=c THEN
list=x

elseif state=a AND product=b AND replacement=d THEN
list=w

elseif .... then
...

else
[default case]
end if

This kind of structure is murder to maintain if you have lots of options.
Other options are Case statements, and nesting ---

Select Case State
Case a

If Product = b then
If Replacement = c then
List = x
elseif Replacement = d then
List = y
else
...
end if
elseif Product = q then
...
else
...
end if

Case b
...

Case else
End select

Separately, if you have many questions that the user has to answer, a
UserForm would be *much* better than a series of InputBoxes. (Currently,
what do you do if you realise you made a mistake on a previous box?)

> Need help again guys. My ignorance has resurfaced and I am on a
> deadline to complete this new checklist. So far I have written the
[quoted text clipped - 19 lines]
>
> Please help and Thanks in advance!

Reply to this Message

kastnna - 05 Nov 2005 01:16 GMT

Thanks, for the good stuff. I don' know much about VB, a UserForm may
be better for me. I've never really messed with UserForms. Right now if
I make a mistake filling in the information, I can't go back. It hasn't
been a big deal because there are only three input questions and the
form was made to access and complete quickly.

Like you said, a case may be best for me. There are eight different
possible states, ten possible products, and two options for the
replacement (obviously yes or no). Changing just one of the options
completely alters the output list. That results in alot of possible
lists being generated.

let me know what you guys think.

Reply to this Message

Jezebel - 05 Nov 2005 06:01 GMT

With 160 possible combinations, I suggest you stay away from trying to do it
all in one big logical structure -- it's just too hard to set up and
maintain that much detail.

If your lists are predefined for each possible combination, then some kind
of look-up might be better: populate a collection whose members are the
possible lists, and whose keys are constructed from the state, product, and
replacement values. Then you might have something along the lines of

pKey = cstr$(state) & cstr$(product) & IIF(Replacement, "Y", "N")
list = ListCollection(pKey)

If your lists are constructed on the fly, inferred from the various values
(eg, some elements are a function of the state, some of the product, etc)
then set up a function that returns the list given the state, product, and
replacement as arguments ---

Private Function GetList(State as string, Product as string, Replacement as
boolean) as array
....
end function

Called using

list = Getlist(state, product, replacement)

> Thanks, for the good stuff. I don' know much about VB, a UserForm may
> be better for me. I've never really messed with UserForms. Right now if
[quoted text clipped - 9 lines]
>
> let me know what you guys think.

Reply to this Message