Pass Top Down Code Completed

You are almost finished. All that's left to do is fill in the gaps in the procedures associated with the creation of the initial system, and complete the two procedures addAConnection and deleteAConnection.

Each of the remaining four procedures associated with initializing the system is pretty straightforward:

■ resetFormatting simply clears all the cells in the worksheet, sets the number format to General, and sets the color index to light gray (strictly speaking, to the constant used to indicate an unused cell).

Sub resetFormatting(aWS As Worksheet) With aWS.Cells

.ClearContents .NumberFormat = "General" .Font.Colorlndex = cUnusedColorldx End With End Sub

■ createOneDataBlock uses the equivalent of the GUI command Edit, File, Series to create one block 60 rows by 10 columns filled with nnn values from 101 to 700. The first data series creates a single row containing the values 101 to 110. The second fills 60 rows with the appropriate numbers.

Sub createOneDataBlock(aWS As Worksheet) With aWS.Range("A1")

.FormulaR1C1 = "101"

.DataSeries Rowcol:=xlRows, Type:=xlLinear, _ Date:=xlDay, _

Step:=1, Stop:=110, Trend:=False .Resize(1, 10).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=10, Stop:=700, Trend:=False

End With End Sub

■ cloneDataBlocks identifies the starting locations of each of the nine remaining blocks and then copies the first block in one step. The result consists of 10 blocks of the nnn numbers in a single range 60 rows by 100 columns.

Sub cloneDataBlocks(aWS As Worksheet) Dim Dest As Range, i As Integer

With aWS.Range("A1")

.CurrentRegion.Copy Dest End With End Sub

■ Finally, addMSlash uses a single Excel formula entered into a range 60 rows by 100 columns located below the existing data. The formula maps the nnn values already present to the corresponding m/nnn results. Then the procedure replaces the existing nnn values with the result in this new range. Finally, it deletes the range with the now unneeded formulas.

Sub addMSlash(aWS As Worksheet)

With aWS.Range("A1").Offset(60, 0).Resize(60, 100) .FormulaR1C1 = _

"=TRUNC((COLUMN()-1)/10,0)+1 &""/""&R[-60]C" aWS.Cells.NumberFormat = .Copy aWS.Range("a1").PasteSpecial xlPasteValues .EntireRow.Delete End With End Sub

The last two procedures are addAConnection and deleteAConnection. addAConnection first checks that the other end of the connection is acceptable and then ensures that that contact is available. If no error is found, both ends of the connection are updated with the other's ID. Note that in the case of an error, the cell contents are returned to the previous value through the resetValue procedure. This makes it easy to reset a cell to its unused state without respect to where it happens.

Function addAConnection(Sh As Worksheet, Target As Range) Dim Conn2 As ExcelCoords Conn2 = WiringToExcelCoords(Target.Value) If Conn2.Row = 0 Then

MsgBox Target.Value & " is not an acceptable value"

addAConnection = True resetValue Target

End If

With Sh.Cells(Conn2.Row, Conn2.Col) If .Font.Colorlndex <> cUnusedColorldx Then

MsgBox Target.Value & " is already in use!" & vbNewLine _

& "It is connected to " & Cells(Conn2.Row, Conn2.Col).Value resetValue Target addAConnection = True Exit Function '<<<<< End If

Application.EnableEvents = False On Error GoTo ErrHandler

.Value = XLtoWiringCoords(Target.Row, Target.Column) .Font.Colorlndex = cInUseColorldx End With

0 0

Post a comment