building an array

[rero360]

Good morning,

I'm trying to build an array for a school project, scenario is this:

You have a city that is 30 miles (y) by 20 miles (x) with roads every mile, have to write code that gives the best location for placement of a distribution center based on the business locations and cost to delivery goods to each.

I have one bit of code that records the number of client businesses, an array that records the locations of the businesses, and an array that stores volume of product each client buys.

Where I'm stuck at, is I think I should build an array that is 0 to 30, 0 to 20 (the size of the city) but I have to evaluate the cost based on user defined precision (.25, .5, 1, and 2 miles) so I should have the array be able to store the values from the calculations for 120 by 80 cells.

Not sure if that makes sense, here is the requirements:
Specifically, your program should be able to complete the following tasks:
 Read the customer data, and user input for analysis resolution. Your program must be designed to
accommodate changes in the number of customers, their locations, and their product delivery volumes.
User options for analysis resolution will be: 0.25 miles, 0.5 miles, 1 mile, and 2 miles.
 Validate that user input is numeric and valid.
 Analyze the costs at all possible distribution center locations (which may be collocated with customer
locations), and determine the optimums.
 Display the X and Y values of the optimum distribution center locations and the corresponding minimum
weekly cost. There may be multiple locations with the same minimum cost.
 Display the costs at all locations adjacent to the optimums, in order to show the sensitivity of the result.


the formulas to use are:

Distance to customer (Di)=abs|x-xi|+|y+yi|
cost for customer (Ci)=1/2*Di*Vi (volume of customer product)* Ft

Ft= 0.03162*y+0.04213*x+0.4462

cost=sum Ci from 1 to number of clients

below is my code so far, I started it to have it basically build the array and display it on a second sheet, so I can visualize it but I can't have that in the final product. In debugging it, it gets to the line of code di= and gives me a subscript out of range.


Option Explicit
Option Base 1


Sub load()
Dim Cust!, x#, y#, volume#(), n!, loc#(), i%, vol#, xi#, ci#, di#, j#
Dim choices#, val#(), nptsx!, nptsy!, Ft#, lowx!, lowy!, low!, M!
Dim costmatrix#(), npts!, cost!()

'find number of customers
Cust = 0
Do While Cells(8 + Cust, 1).Value <> ""
Cust = Cust + 1
Loop

If Cust < 2 Then
MsgBox "number of customers must be greater than 1."
Exit Sub
End If

'establist array of customer locations
ReDim loc#(1, Cust)
For j = 1 To Cust
x = Cells(7 + j, 2)
y = Cells(7 + j, 3)
Next j

ReDim volume#(Cust, 1)
For i = 1 To Cust
vol = Cells(7 + i, 4)
Next i

choices = Cells(3, 7).Value
nptsx = 30 / choices + 1
nptsy = 20 / choices + 1

'30x20 grid
ReDim costmatrix(x To nptsx, y To nptsy)
For x = 0 To nptsx - 1
Sheet3.Cells(1, x + 2) = x * choices
Next x
For y = 0 To nptsy - 1
Sheet3.Cells(2 + y, 1) = y * choices
Next y

For j = 1 To Cust
For x = 0 To nptsx - 1
For y = 0 To nptsy - 1
di = Abs(Sheet3.Cells(1, x + 1) - loc(j, 1)) + Abs(Sheet3.Cells(1, y + 1) - loc(j, 1))
Ft = 0.03162 * loc(j, 2) + 0.4213 * loc(j, 1) + 0.4462
ci = 1 / 2 * di * vol * Ft
Sheet3.Cells(x + 2, 2 + y) = ci
Next y
Next x
Next j

lowx = x
lowy = y

Range("I9:J:3").Clear

Range("I9") = "optimum"
Range("J9") = lowx * choices
Range("K9") = lowy * choices
Range("L9") = low

i = 9
If lowy < npts - 1 Then
i = i + 1
Cells(1, "I") = "Increment North"
Cells(1, "L") = cost(lowx, lowy + 1)
End If
If lowy > 0 Then
i = i + 1
Cells(1, "I") = "Increment South"
Cells(1, "L") = cost(lowx, lowy - 1)
End If
If lowx < npts - 1 Then
i = i + 1
Cells(1, "I") = "Increment East"
Cells(1, "L") = cost(lowx, lowy + 1)
End If
If lowx > 0 Then
i = i + 1
Cells(1, "I") = "Increment West"
Cells(1, "L") = cost(lowx, lowy - 1)
End If

End Sub


So in closing, what I need help with is how do I build an array for points 0 to 30miles and 0 to 20miles, recording info for every .25 mile?

I'll write an if then line that will go on the analysis resolution number picked to search the array for the lowest number.

Thank you in advance for any help.

[SamT]

The requirement to use .25 miles implies that the Client addresses are realistic. That also implies that their street numbers start at 1 and run by the 100's for each block and include a N,S,E,W indicator (1325N East3rd Street). If the Zero Reference Crossway is the intersection of NorthMain. EastMain, SouthMain and WestMain, then that client is at Point +3, +13.25. Similarly, a client at 1435W South4th St, would be at location -14.35, -4.

One of our best coders once said, and I must paraphrase, "Process Follows Structure" and he meant that you must first properly structure your data before you can begin to code. May I suggest that you give your data (Street names, Client info, and Distribution Center Info,) some thought.

Consider an alphabetically sorted list of Street names with two additional columns, distance from the Zero Reference North or South (+ or -) and the same for the Y Axis, (distance East or West.) This should the only hard coded or manual calculations as Streets are the only Objects set in Cement, or at least asphalt :)

A list of Dist Centers with a Street name column and a Street number Column
A Similar list of Customers with an additional column for weekly volume.

You can write a Function to translate Street names and Numbers into XY point values from those three lists. This will be a fairly complex function as you should incorporate( or plan for it) the ability to strip extraneous info from the Street number (Suite designators etc.) I would use native Double Type accuracy at this point and until the final output of your code.

In the interest of pure speed of response, once initialized, I would create a User Defined Type, "typeCustomer" for example.

Public Type typeCustomer
Name As String
Street As String
NorthSouth as Double
EastWest As Double
Volume As Long
End Type

Then load a Dictionary, "Customers" with the Customer Name as the Key and the typeCustomer Var as the Item. (Requires a Reference to Microsoft Scripting Runtime)

Dim Customer As typeCustomer
For i = 1 to CustomerCount
CustomStreetTranslatorFunctionAbove(ByRef Customer, Cells(i))
Customers.Add Customer.Name, Customer
Next i
Repeat the UDT and Dictionary for Distribution Centers and you have two Arrays, addressable by name, that return pinpoint XY locations. As a bonus, the Customers Dictionary also returns the Customer Volume.

IMO, you should keep the Main Procedure as clean as possible by passing most of the repeatable work to sub Procedures and Functions. Each sub Procedure and function should only do one thing.

At this point you are ready to use some of Excel's advanced functions, such as Solver, but I must defer to an Excel Expert to point you in the right direction on that consideration.