Hello,

I’ve been presented with a client designed workbook. Simply stated,
the workbook contains a 2 column table, column 1 is the parameter
name, column 2 is the parameter value. The parameter values control
the program flow – the problem is the same parameter is used at
different places in the code. The following is a dummied down sample
to illustrate.

Sub main()
Call Sub1
If Param1 = "B" Then do something, else do nothing
End Sub

Sub Sub1()
For x = 1 To 5
If Param1 = "A" Then do something, else do nothing
Next x
End Sub

Of course, the simple example above can easily be handled with an
If>Then statement. The real application is very complex, contains 40+
parameters in the table and each parameter has up to 4 different
values. Sprinkling appropriate If/Then statements throughout the code
would make any modification/maintenance a nightmare.

I’m sure there is a more elegant solution, and I’m hoping someone can
point me in the right direction. Any advice will be most
appreciated. Thanks in advance.

Regards,

Dave U

Hello Harald,

Thanks for your reply. Your example using the Select Case method
(with which I’m very familiar) will work quite well in the simple
scenario you provided, where the query resides in 1 procedure, but
will not work in the more complicated situation I’m dealing with.

I re-worked my samples, hopefully will better illustrate what I’m
referring to.

Sub Main()
----Code here to retrieve parameters from table (Param1, Param2,
etc)
Call AnswerQuestion
If Param1 = "B" Then Hilite all incorrect answers in red ’provide
feedback to user after completion
End Sub

Sub AnswerQuestion ()
For x = 1 To 5
----Code here to display question(x)---
----Code here to evaluate answer to question(x)---
If Param1 = "A" AND answer(x) is incorrect then Hilite
answer(x) in red ’provide immediate feedback to user
Next x
End Sub

As I already said, no problem using If/Then in the above sample, but
when dealing with 40+ parameters, maintenance can become difficult
(but maybe there is no alternative). I was thinking along the lines
of programmatically building a class object at the start of the
application, it’s structure would be defined by the parameters. Then
the If/Then statements would no longer be required.

Regards,

Dave U

Hello Harald,

On Jul 18, 12:18*pm, "Harald Staff" <nos...@not.invalid> wrote:
> You did not understand my response.

Yes, I did. I do understand what you’re saying about flow, decisions,
action, and I’m accepting that as good advice. I think we’re talking
at cross purposes here.

> You have "If Param1" in both procedures and "Hilite"
> in the same two.

In this simple example it almost has to be that way. Sub
AnswerQuestion is checking each answer as it is entered - if incorrect
AND Param1 = “A”, it hilites it in red. Later, when all the questions
are answered, sub Main also checks Param1, if = “B”, then hilites all
incorrect answers in red. So, dependant on Param1 value, either the
incorrect answers are hilited in real time, or, hilited all at once,
after completion.

As we’re using Param1 at 2 different times, to do 2 different things,
it’s almost impossible to keep all the flow, decisions, and action
together. And yes, this example could be constructed more sensibly,
but in the “real” application these 2 procedures could be miles apart.

Maybe part of the problem is, there should be 2 separate parameters,
each with a value =true/false, not 1 parameter with multiple values
(which is what the client is wanting). I’ll have to go back to the
drawing board with some of this.

Thanks very much for your expertise and replies, much appreciated.

Regards,

Dave U

On 18/07/2010 22:07, Dave Unger wrote:
> Hello Harald,
>
> On Jul 18, 12:18 pm, "Harald Staff"<nos...@not.invalid> wrote:
>> You did not understand my response.
>
> Yes, I did. I do understand what you’re saying about flow, decisions,
> action, and I’m accepting that as good advice. I think we’re talking
> at cross purposes here.
>
>> You have "If Param1" in both procedures and "Hilite"
>> in the same two.
>
> In this simple example it almost has to be that way. Sub
> AnswerQuestion is checking each answer as it is entered - if incorrect
> AND Param1 = “A”, it hilites it in red. Later, when all the questions
> are answered, sub Main also checks Param1, if = “B”, then hilites all
> incorrect answers in red. So, dependant on Param1 value, either the
> incorrect answers are hilited in real time, or, hilited all at once,
> after completion.

See how much of the validity checking you can do using lists of valid
parameters and then separate out the residual checking of the worksheet
entries for validity entirely from whatever the processing action code
for the sheet does. Having spaghetti with no clear logic overview is a
recipe for trouble.

It seems like you need to split the code into CheckAnswersValid perhaps
with a parameter to tell it which parameter changed most recently for
the realtime check and which can then be reused in the Main action code.
>
> As we’re using Param1 at 2 different times, to do 2 different things,
> it’s almost impossible to keep all the flow, decisions, and action
> together. And yes, this example could be constructed more sensibly,
> but in the “real” application these 2 procedures could be miles apart.

You should only ever have one routine for checking the inputs are valid!
>
> Maybe part of the problem is, there should be 2 separate parameters,
> each with a value =true/false, not 1 parameter with multiple values
> (which is what the client is wanting). I’ll have to go back to the
> drawing board with some of this.

That doesn't alter things much. If anything it is better to have the
input parameters that reflect how the *user* thinks of things.

Box is small/medium/large/extralarge with a 4 way case statement makes
a lot more sense to user and programmer and is easier to maintain than
nested if statements.
>
> Thanks very much for your expertise and replies, much appreciated.

If there is a McCabes CCI routine available for VBA then you might want
to give it a try to find out where the maintenance traps are residing in
this codebase.

Regards,
Martin Brown

Hello Martin,

On Jul 19, 1:15*am, Martin Brown <|||newspam...@nezumi.demon.co.uk>
wrote:

> > As we’re using Param1 at 2 different times, to do 2 different things,
> > it’s almost impossible to keep all the flow, decisions, and action
> > together. *And yes, this example could be constructed more sensibly,
> > but in the “real” application these 2 procedures could be miles apart.
>
> You should only ever have one routine for checking the inputs are valid!

There is only 1 routine for validating the input. It's the display
routine(s) that are in question. The option is to display the
feedback (each correct/incorrect response with a green/red hilite) as
each question is answered, or, wait until all the questions have been
answered and then hilite. As these 2 events are happening at
different times, I can't see how they can possibly be combined into a
common structure.

The idea here is that the administrator has the option of turning off
the immediate feedback so the person taking the test is unaware of
right/wrong answers until the end.

> > Maybe part of the problem is, there should be 2 separate parameters,
> > each with a value =true/false, not 1 parameter with multiple values
> > (which is what the client is wanting). *I’ll have to go back to the
> > drawing board with some of this.
>
> That doesn't alter things much. If anything it is better to have the
> input parameters that reflect how the *user* thinks of things.

I'm in total agreement with you on this, my statement above was an
erroneous thought that unfortunately got recorded.

> If there is a McCabes CCI routine available for VBA then you might want to give it a try to find out where the maintenance traps are residing in this codebase.

I haven't heard about this before, I'll certainly look into it.


In hindsight, I think a lot of the difficulty with this post is that I
must not have been explaining myself properly. I've been working on
this very complex project for some time, and sometimes forget to "fill
in the blanks" when discussing this with others.

Thanks very much for your reply,

regards,

Dave

On 19/07/2010 17:09, Dave Unger wrote:
> Hello Martin,
>
> On Jul 19, 1:15 am, Martin Brown<|||newspam...@nezumi.demon.co.uk>
> wrote:
>
>>> As we’re using Param1 at 2 different times, to do 2 different things,
>>> it’s almost impossible to keep all the flow, decisions, and action
>>> together. And yes, this example could be constructed more sensibly,
>>> but in the “real” application these 2 procedures could be miles apart.
>>
>> You should only ever have one routine for checking the inputs are valid!
>
> There is only 1 routine for validating the input. It's the display
> routine(s) that are in question. The option is to display the
> feedback (each correct/incorrect response with a green/red hilite) as
> each question is answered, or, wait until all the questions have been
> answered and then hilite. As these 2 events are happening at
> different times, I can't see how they can possibly be combined into a
> common structure.

One way would be

question = GetQuestionNumber()
CheckAnswer(question, question)
IF realtime THEN
UpdateDisplay(question, question)
ELSIF AllDone THEN
CheckAnswer(1,N) -- needed in case questions right answers interact
UpdateDisplay(1,N)
END

>
> The idea here is that the administrator has the option of turning off
> the immediate feedback so the person taking the test is unaware of
> right/wrong answers until the end.

Regards,
Martin Brown

Hello Martin,

> One way would be
>
> question = GetQuestionNumber()
> CheckAnswer(question, question)
> IF realtime THEN
> * * UpdateDisplay(question, question)
> ELSIF AllDone THEN
> * * CheckAnswer(1,N) *-- needed in case questions right answers interact
> * * UpdateDisplay(1,N)
> END
>

A very elegant solution, this is exactly the type of thing I was
looking for. I must have got myself in a mental rut, this approach
had not occurred to me.

Thanks so much,

regards

Dave U