# VBA

Budget $1500-5000 USD

Project: Using Visual Basic for Applications (VBA), provide an Access

2000 report which will draw a color coded line graph based on a

distribution of percentages.

Background: A number of facilities are reporting their performance by

providing simple numerator/denominator information on a variety of

indicators (or target areas).

Specifications:

- There are 7 variables to account for: target area, time period,

facility ID, percentage, high percentage limit, low percentage limit and

median.

- The user provides 4 of these variables on an Access form which opens

the report. These variables are target area, time period, high limit and

low limit.

- Each report/graph should only display one target area and time period

at a time. The graph itself displays the facility IDs across the X-axis

and each facility's percentage (0 to 100) along the Y-axis.

- The high limit is a horizontal line drawn across the graph at the

high percentage given by the user. The low limit is a horizontal line

drawn at the low percentage given. The median is a horizontal line draw

across the graph by the median calculated.

- Each data point on the graph must be color coded. If the percentage

is greater than or equal to the high percentage limit it must colored

red. If the percentage is less than or equal the low percentage limit it

must be colored green. If the percentage falls between these high and

low percentage limits it must be colored black.

What already exists:

- A VBA function which will create the table needed for these graphs.

The functions parameters are target area and time period. This returns

an Access table which contains the facility IDs and their respective

percentage.

- A VBA function which determines the percentage at the high percentage

limit or low percentage limit. When the user enters the high and low

percentage limits, they are actually entering a percentile value. The

VBA function simply takes this value and a boolean value to indicate

high or low and returns back a real number which is the percentage limit

value.

- A VBA function to calculate the median value in the distribution.