Pass Key Components Defined

Clearly, an important support capability is the translation between the industry-specific m/nnn codes and the address of the associated Excel cell. An Excel cell is identified by two values, the row and the column. For that, you need a User Defined Type, or UDT. Next, because you plan to start with cell A1 containing 1/101, you can write the two translation functions and put them in the standard module used for the initializeSystem code in the previous section:

Public Type ExcelCoords Row As Long Col As Long End Type

Function XLtoWiringCoords(aRow As Long, aCol As Long)

XLtoWiringCoords = CStr((aCol - 1) \ 10 + 1) & "/" _

& CStr(100 + (aRow - 1) * 10 + (aCol - 1) Mod 10 + 1) End Function

Function WiringToExcelCoords(WiringCode As String) As ExcelCoords Dim FirstPart As Long, SecondPart As Long, SlashLoc As Long SlashLoc = InStr(1, WiringCode, "/") If SlashLoc = 0 Then Exit Function On Error Resume Next

FirstPart = CLng(Left(WiringCode, SlashLoc - 1))

SecondPart = CLng(Right(WiringCode, Len(WiringCode) - SlashLoc))

On Error GoTo 0

If FirstPart < 1 Or FirstPart > 10 Then Exit Function '<<<<< If SecondPart < 101 Or SecondPart > 700 Then Exit Function '<<<<< WiringToExcelCoords.Row = (SecondPart - 101) \ 10 + 1

WiringToExcelCoords.Col = (FirstPart - 1) * 10 + _ (SecondPart - 101) Mod 10 + 1 End Function

Finally, you need some global constants. The color choices of light gray (unused wiring contact) and blue (in-use contact) correspond to the index values 15 and 5, respectively, in Excel's default color palette. And the way to tag a worksheet as containing a wiring diagram is to add a specific constant to a specific cell in the worksheet. These constants are declared Public because they will be used in multiple modules. The following code goes at the top of the same standard module used for the previous example: Public Const _

cUnusedColorldx As Integer = 15, cInUseColorldx As Integer = 5, _ cWSWiringTagID As String = "TM Wiring", _ cWSWiringTagAddr As String = "A61"

This concludes the first pass at building the code for the system. The code not only compiles but will even execute. It doesn't do much, but some of the safety checks in the event procedure can be tested.

0 0

Post a comment