Really tricky excel problem

  • Thread starter Thread starter Nigel Bigelsby
  • Start date Start date
N

Nigel Bigelsby

Can someone please help me put a solution in place for this problem.

We are working on a new internal web-based database to help track our
customer's activities, billing, etc. We need to write scenarios for the
company that has been hired to develop it. This seems to be more of a legal
issue than a technical, but we want to make sure we cover ourselves. Here is
the situation:

We want to have the hired company test multiple fields to make sure we get
the desired end results. If we don't present a scenario, there is a
potential the company we hired can say that we didn't request such a
requirement from the application.

The variables are items, for example,such as what would happen if I entered
a space in 1 of the 15 fields. Would the application spend a mutliple amount
of time searching for a million records, or would it come back immediately
with an error? If we don't mention a scenario such as this, then we might
have to pay for a customization since we did not request it: again a legal
issue.

We could use Excel to help automate the mutliple scenarios that could result
and then enter what we anticipate should be the end result. This would be a
matter of taking various single scenarios (each one a cell) and merging them
together.

I am thinking about entering the following info in the cells:
Sheet 1: space, 1, and multiple number
Sheet 2: alpha, and numeric
Sheet 3: First name, last name, street address, city, state, zipcode, (etc)
Sheet 4: a combination of Sheet 1, sheet 2, and sheet 3 and their variations
Sheet 5 (possibly) a way of running a macro that would eliminate duplicated
scenarios in Sheet 4
Sheet 6 merging the cells in Sheet 5 to be our end product of what criteria
we need tested.

Any help or direction would be appreciated
Thanks
 
I can't really tell about your options, but if you want to generate
combinations (replace the array elements with your options)

Dim opt1, opt2, opt3, opt4
Dim i as long, j as long, k as long, l as long
Dim lngRow as long
opt1 = Array(1,2,3)
opt2 = Array("A","B","C","D")
opt3 = Array("S","T")
opt4 = Array("House","Cat","Dog")

lngRow = 0
for i = lbound(opt1) to ubound(opt1)
for j = lbound(opt2) to ubound(opt2)
for k = lbound(opt3) to ubound(opt3)
for l = lbound(opt4) to ubound(opt4)
lngRow = lngRow + 1
cells(lngRow,1).Value = opt1(i)
cells(lngRow,2).Value = opt2(j)
cells(lngRow,3).Value = opt3(k)
cells(lngrow,4).Value = opt4(l)
next l
next k
next j
next i
 
Here are the specifics that we need to test:

We have a form with 17 different fields.

Rep
Product
Rev From
Rev To
Incentive
Delinquent Customer
Credit Class
Listed Name
Area Code
Tel Prefix
Tel Suffix
Appointment From
Appointment From
Keyword
Street Name
Zip Code

We want to have a scenario such as:

Tester enters a space into the Rep Field.
Expected result would be an error message.

Tester enters a number into the Rep Field
Expected result would be an error message.

Tester enters an alpha character into the Rep Field
Expected result would be list of all reps whose name
starts with that letter.

And so on. Then we would want multiple scenarios
such as

Tester enters multiple alpha characters in the rep Field
and a space in the area code.
Expected result would be an error message.

As you can see, there are literally thousands of different
combinations. What would be the best way to do that?
 
Back
Top