補遺5 | 制御系CAD

図5.9

この割付図は、配布したrcpsp51.xlsmのシートwaritsukeとして得られたものです。

A造船所の４つの定盤P01+P02、P03、P05、P06に対する定盤計画問題を別々に解いた結果を、CSVファイルrcpsp51a.csv、rcpsp51b.csv、rcpsp51c.csv、rcpsp51d.csvとして得て、これらを一つにまとめたものがシートrcpsp51です。それと稼働日を表すシートcを準備し、次のVBAプログラムを実行して、上図を得ています。

VBAプログラム


Sub Waritsuketemp()
  Worksheets("waritsuke").Activate
  ActiveSheet.DrawingObjects.Delete
  Dim Sheet1 As Variant
  Sheet1 = "rcpsp51"
  Dim time1 As Integer, time2 As Integer, sp As Integer
  time1 = 45: time2 = 120: sp = 47:
  ReDim p(7) As Variant '定盤位置
  p(1) = 1: p(2) = 62: p(3) = 123: p(4) = 168:
  p(5) = 197: p(6) = 252: p(7) = 307:
  ReDim R(7) As Variant '定盤名称
  R(1) = "P01": R(2) = "P02": R(3) = "P03": R(4) = "P04":
  R(5) = "P05": R(6) = "P06": R(7) = "P07":
  Dim act As Variant, mode As Variant, ID As Integer, no As Integer
  Dim joban As Integer, sx As Integer, sy As Integer, tx As Integer
  Dim ty As Integer, start As Integer, completion As Integer
  For T = time1 To time2
    Cells(3 + sp * (T - time1), 2).Value = T
  Next T
  Dim S As Range, Q As Shape
  For i = 2 To 69
    act = Worksheets(Sheet1).Cells(i, 1) 'i=2のとき、act=S01_B041P_FWFW
    mode = Worksheets(Sheet1).Cells(i, 2) 'mode=mode[02]_[001][047_001][011_012]
    ID = Len(mode) 'MsgBox (ID)
    If ID = 32 Then
      joban = Val(Mid(mode, 11, 3)) 'mode=mode[02]_[001][047_001][011_012]
      sx = Val(Mid(mode, 16, 3)) 'mode=mode[02]_[001][047_001][011_012]
      sy = Val(Mid(mode, 20, 3)) 'mode=mode[02]_[001][047_001][011_012]
      tx = Val(Mid(mode, 25, 3)) 'mode=mode[02]_[001][047_001][011_012]
      ty = Val(Mid(mode, 29, 3)) 'mode=mode[02]_[001][047_001][011_012]
      start = Worksheets(Sheet1).Cells(i, 3) + 1
      completion = Worksheets(Sheet1).Cells(i, 5)
      For j = start - time1 To completion - time1
        Set S = Range(Cells(4 + sp * j + sy, 4 + p(joban) + sx), _
        Cells(3 + sp * j + sy + ty, 3 + p(joban) + sx + tx))
        Set Q = ActiveSheet.Shapes.AddShape(1, S.Left, S.Top, _
        S.Width, S.Height)
        Q.Select
        ic = i Mod 6
        Selection.Font.ColorIndex = 1
        If ic = 0 Then
          Q.Fill.ForeColor.RGB = RGB(255, 0, 0) '赤
        ElseIf ic = 1 Then
          Q.Fill.ForeColor.RGB = RGB(0, 255, 0) '緑
        ElseIf ic = 2 Then
          Q.Fill.ForeColor.RGB = RGB(0, 0, 255) '青
          Selection.Font.ColorIndex = 2
        ElseIf ic = 3 Then
          Q.Fill.ForeColor.RGB = RGB(255, 255, 0) '黄
        ElseIf ic = 4 Then
          Q.Fill.ForeColor.RGB = RGB(0, 255, 255) 'シアン
        ElseIf ic = 5 Then
          Q.Fill.ForeColor.RGB = RGB(255, 0, 255) 'マジェンダ
        End If
        Selection.Text = act
        Selection.Font.Size = 40
      Next j
    End If
  Next i
End Sub