Using Excel for More Than Number Crunching

In this case study, Excel serves as the platform to document the customized configuration of an electrical control system that consists of up to 6,000 contact points. Each contact point can be connected to one other point by a wire. Although it is unlikely that all 6,000 points will be used at any given time, the solution must cater to the possibility.

Further, the method used to identify these contact points is industry specific. The points are labeled by a combination of two numbers separated by a slash, as in m/nnn, where m goes from 1 to 10 horizontally, and nnn goes from 101 to 700 vertically.

Each of the 6,000 contact points is represented by one Excel cell. When one contact point is connected to another, the respective cells contain the identifier of the other contact.

Of course, if the intended connection uses a point that is already in use, the connection cannot be allowed. Finally, in addition to making connections, there is also a need to undo connections and to change them. Clearly, to maintain this system by hand, whether in Excel or in some other format, would be a laborious and extremely error-prone task.

To create this you will have to

■ Map application (that is, client) terminology to/from Excel terminology.

■ Create the initial visual effects.

■ Design the functional procedures needed for the desired application capability.

■ Develop VBA event procedures to implement the functional procedures.

The immediate question that comes to mind is whether it is possible to automate this system, and if so, how. Would it be possible to simply write in the cell corresponding to some contact point A the code corresponding to contact point B, and have Excel automatically update the cell corresponding to contact point B with the location of contact point A? And, of course, the system should disallow the connection if contact point B is already in use.

To undo an existing connection, the user would simply delete the entry in one of the cells associated with that connection. For example, to remove the link established between 1/101 and 10/700, the user would simply delete the contents of the cell corresponding to one of the contacts, 1/101 or 10/700. The system would restore the original "unconnected" values and formats to both those cells.

Changing a connection would require a two-step process. In the first, the system must delete the existing connection and in the second step, establish a new one.

0 0

Post a comment