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
IfThen 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

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



"Dave Unger" wrote in message
...
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
IfThen 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

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.



"Dave Unger" wrote in message
...
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" 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 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
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.

On 19/07/2010 17:09, Dave Unger wrote:
Hello Martin,

On Jul 19, 1:15 am, Martin
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