Control program flow

[Dave Unger]

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

 

[Harald Staff]

Hi Dave

You have abstracted this too much to make sense. But as an overall design,
try leaving the flow in one place , the decisions in another, and the
actions in yet another.

Also, you may look for the Select Case method. See if this makes sense to
you:

' top of module
Option Explicit
Option Compare Text

Sub Main()
Dim MyWeather As String
Dim MyAction As String

MyWeather = "Clouded"
MyAction = WhatToDo(MyWeather)
MsgBox "It is " & MyWeather & vbNewLine & _
"Code says " & MyAction

MyWeather = "Snowing"
MyAction = WhatToDo(MyWeather)
MsgBox "It is " & MyWeather & vbNewLine & _
"Code says " & MyAction

End Sub

Private Function WhatToDo(ByVal Weather As String)
Select Case Weather
Case "Hot"
WhatToDo = "Beach trip"
Case "Sunny"
WhatToDo = "Barbecue"
Case "Clouded"
WhatToDo = "Wash your car"
Case "Rainy"
WhatToDo = "See a show"
Case "Thunderstorm"
WhatToDo = "Stay inside"
Case Else
WhatToDo = "Ask your wife"
End Select
End Function



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

 

[Dave Unger]

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

 

[Harald Staff]

You did not understand my response. Try leaving the flow in one place , the
decisions in another, and the
actions in yet another. You have "If Param1" in both procedures and "Hilite"
in the same two.



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

 

[Dave Unger]

Hello Harald,

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

 

[Martin Brown]

Hello Harald,



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.


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

 

[Dave Unger]

Hello Martin,

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.

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

 

[Martin Brown]

Hello Martin,



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

 

[Dave Unger]

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