ExcelマクロVBAサンプル集 | ナンバーリンクを解くVBAのパフォーマンス改善3 | Excelマクロの実用サンプル、エクセルVBA集と解説



最終更新日:2014-05-26

ナンバーリンクを解くVBAのパフォーマンス改善3


以下が完成したVBAコードの全てになります。


Option Explicit

Private SuAry() As String
Private SuAry2() As String
Private DispRange As Range
Private rMax As Integer
Private cMax As Integer
Private SuAry9(30) As Integer

Sub main()
  Debug.Print Now()
  Dim iR As Integer
  Dim iC As Integer
  On Error Resume Next
  Set DispRange = Application.InputBox(prompt:="問題範囲をドラッグで選択してください。", Title:="問題範囲を選択", Type:=8)
  If DispRange Is Nothing Then
    Exit Sub
  End If
  On Error GoTo 0
  rMax = DispRange.Rows.Count
  cMax = DispRange.Columns.Count
  ReDim SuAry(1 To rMax, 1 To cMax)
  ReDim SuAry2(1 To rMax, 1 To cMax)
  DispRange.NumberFormat = "@"
  For iR = 1 To rMax
    For iC = 1 To cMax
      With DispRange(iR, iC)
        If .Value = "" Or Not IsNumeric(.Value) Then
          .Value = ""
          .Font.Size = 20
          .Font.Bold = False
          SuAry(iR, iC) = ""
        Else
          SuAry(iR, iC) = .Value
        End If
      End With
    Next
  Next
  iR = 1
  iC = 0
  Call getStart(iR, iC)
  If getAdvance(iR, iC, iR, iC, 0) <> "完了" Then
    MsgBox "レレレのレ"
  End If
  Call dispCell(True)
  Debug.Print Now()
End Sub

Private Function getAdvance(ByVal iR As Integer, ByVal iC As Integer, _
              ByVal iR2 As Integer, ByVal iC2 As Integer, _
              ByVal tryCnt As Integer) As String
  Dim iR3 As Integer
  Dim iC3 As Integer
  Dim iR4 As Integer
  Dim iC4 As Integer
  Dim i As Integer
  Dim strMust As String
  Dim rtn As String
  
  Call getEnd(iR4, iC4, SuAry(iR, iC))
  
  Select Case True
    Case iR4 = iR2 - 1 And iC4 = iC2, _
       iR4 = iR2 And iC4 = iC2 + 1, _
       iR4 = iR2 + 1 And iC4 = iC2, _
       iR4 = iR2 And iC4 = iC2 - 1
      Call dispCell(True)
      tryCnt = 0
      If getStart(iR, iC) = False Then
        getAdvance = "完了"
        Exit Function
      End If
      rtn = getAdvance(iR, iC, iR, iC, tryCnt)
      Select Case rtn
        Case "完了"
          getAdvance = "完了"
          Exit Function
        Case "終点"
          getAdvance = "終点"
          tryCnt = 0
          If getStart(iR, iC) = False Then
            getAdvance = "完了"
            Exit Function
          End If
        Case "破綻"
          getAdvance = "破綻"
          Exit Function
      End Select
  End Select
  
  For i = 1 To 4
    Select Case True
      Case iR4 > iR2 And iC4 > iC2
        Select Case i
          Case 1
            iR3 = iR2 + 1
            iC3 = iC2
          Case 2
            iR3 = iR2
            iC3 = iC2 + 1
          Case 3
            iR3 = iR2 - 1
            iC3 = iC2
          Case 4
            iR3 = iR2
            iC3 = iC2 - 1
        End Select
      Case iR4 > iR2 And iC4 <= iC2
        Select Case i
          Case 1
            iR3 = iR2 + 1
            iC3 = iC2
          Case 2
            iR3 = iR2
            iC3 = iC2 - 1
          Case 3
            iR3 = iR2
            iC3 = iC2 + 1
          Case 4
            iR3 = iR2 - 1
            iC3 = iC2
        End Select
      Case iR4 <= iR2 And iC4 > iC2
        Select Case i
          Case 1
            iR3 = iR2
            iC3 = iC2 + 1
          Case 2
            iR3 = iR2 - 1
            iC3 = iC2
          Case 3
            iR3 = iR2 + 1
            iC3 = iC2
          Case 4
            iR3 = iR2
            iC3 = iC2 - 1
        End Select
      Case iR4 <= iR2 And iC4 <= iC2
        Select Case i
          Case 1
            iR3 = iR2 - 1
            iC3 = iC2
          Case 2
            iR3 = iR2
            iC3 = iC2 - 1
          Case 3
            iR3 = iR2 + 1
            iC3 = iC2
          Case 4
            iR3 = iR2
            iC3 = iC2 + 1
        End Select
    End Select
    
    If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
      If SuAry(iR3, iC3) = "" Then
        If chkAdvance(iR3, iC3, SuAry(iR, iC)) = True Then
          SuAry(iR3, iC3) = SuAry(iR, iC) & "-" & tryCnt
          If chkClose(iR3, iC3, SuAry(iR, iC), iR, iC) = True Then
            If chkRout(iR, iC) = True Then
              tryCnt = tryCnt + 1
              Call dispCell(True, iR3, iC3, SuAry(iR, iC) & "-" & tryCnt)
              rtn = getAdvance(iR, iC, iR3, iC3, tryCnt)
              Select Case rtn
                Case "完了"
                  getAdvance = "完了"
                  Exit Function
              End Select
              If Not IsNumeric(SuAry(iR3, iC3)) Then
                tryCnt = tryCnt - 1
                Call dispCell(False, iR3, iC3, "")
              End If
            Else
              SuAry(iR3, iC3) = ""
            End If
          Else
            SuAry(iR3, iC3) = ""
          End If
        End If
      End If
    End If
  Next
  If IsNumeric(SuAry(iR2, iC2)) Then
    getAdvance = "破綻"
  Else
    tryCnt = tryCnt - 1
    Call dispCell(False, iR2, iC2, "")
    getAdvance = "別ルート探索"
  End If
End Function

Private Function chkAdvance(ByVal iR As Integer, ByVal iC As Integer, ByVal i As Integer) As Boolean
  Dim cnt As Integer
  cnt = 0
  If iR <> 1 And iC <> 1 Then
    If InStr(SuAry(iR - 1, iC - 1), i & "-") > 0 Or SuAry(iR - 1, iC - 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR - 1, iC), i & "-") > 0 Or SuAry(iR - 1, iC) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR, iC - 1), i & "-") > 0 Or SuAry(iR, iC - 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If cnt >= 3 Then
      chkAdvance = False
      Exit Function
    End If
  End If
  
  cnt = 0
  If iR <> rMax And iC <> 1 Then
    If InStr(SuAry(iR + 1, iC - 1), i & "-") > 0 Or SuAry(iR + 1, iC - 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR + 1, iC), i & "-") > 0 Or SuAry(iR + 1, iC) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR, iC - 1), i & "-") > 0 Or SuAry(iR, iC - 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If cnt >= 3 Then
      chkAdvance = False
      Exit Function
    End If
  End If
  
  cnt = 0
  If iR <> 1 And iC <> cMax Then
    If InStr(SuAry(iR - 1, iC + 1), i & "-") > 0 Or SuAry(iR - 1, iC + 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR - 1, iC), i & "-") > 0 Or SuAry(iR - 1, iC) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR, iC + 1), i & "-") > 0 Or SuAry(iR, iC + 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If cnt >= 3 Then
      chkAdvance = False
      Exit Function
    End If
  End If
  
  cnt = 0
  If iR <> rMax And iC <> cMax Then
    If InStr(SuAry(iR + 1, iC + 1), i & "-") > 0 Or SuAry(iR + 1, iC + 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR + 1, iC), i & "-") > 0 Or SuAry(iR + 1, iC) = CStr(i) Then
      cnt = cnt + 1
    End If
    If InStr(SuAry(iR, iC + 1), i & "-") > 0 Or SuAry(iR, iC + 1) = CStr(i) Then
      cnt = cnt + 1
    End If
    If cnt >= 3 Then
      chkAdvance = False
      Exit Function
    End If
  End If
  chkAdvance = True
End Function

Private Sub dispCell(ByVal blnDisp As Boolean, _
           Optional ByVal iR As Integer = 0, Optional ByVal iC As Integer = 0, _
           Optional ByVal dispStr As String = "")
  Dim i As Integer
  Dim j As Integer
  Dim bR As Integer
  Dim bC As Integer
  Dim aR As Integer
  Dim aC As Integer
  Dim suAry3() As String
  ReDim suAry3(1 To rMax, 1 To cMax)

  If iR <> 0 And iC <> 0 Then
    SuAry(iR, iC) = dispStr
  End If
  If blnDisp = False Then
    Exit Sub
  End If
  For i = 1 To rMax
    For j = 1 To cMax
      If IsNumeric(SuAry(i, j)) Then
        suAry3(i, j) = SuAry(i, j)
        If chkEnd(i, j) = True Then
          SuAry2(i, j) = SuAry(i, j) & "-99"
        Else
          SuAry2(i, j) = SuAry(i, j) & "-0"
        End If
      Else
        SuAry2(i, j) = SuAry(i, j)
      End If
    Next
  Next
  For i = 1 To rMax
    For j = 1 To cMax
      Call getBeforeCell(i, j, bR, bC)
      Call getAfterCell(i, j, aR, aC)
      If bR <> 0 And bC <> 0 Then
        If aR = 0 And aC = 0 Then
          suAry3(i, j) = "◆"
        Else
          If (aR > i And i > bR) Or _
            (aR < i And i < bR) Then
            suAry3(i, j) = "┃"
          End If
          If (aC > j And j > bC) Or _
            (aC < j And j < bC) Then
            suAry3(i, j) = "━"
          End If
          If (aC > j And i < bR) Or _
            (aR > i And j < bC) Then
            suAry3(i, j) = "┏"
          End If
          If (aC < j And i < bR) Or _
            (aR > i And j > bC) Then
            suAry3(i, j) = "┓"
          End If
          If (aC > j And i > bR) Or _
            (aR < i And j < bC) Then
            suAry3(i, j) = "┗"
          End If
          If (aC < j And i > bR) Or _
            (aR < i And j > bC) Then
            suAry3(i, j) = "┛"
          End If
        End If
      End If
    Next
  Next
  DispRange = suAry3
  DoEvents
End Sub

Private Sub getBeforeCell(ByVal iR As Integer, ByVal iC As Integer, ByRef iR2 As Integer, ByRef iC2 As Integer)
  Dim i As Integer
  Dim j As Integer
  Dim n1 As Integer
  Dim n2 As Integer
  Dim strSu As String
  iR2 = 0
  iC2 = 2
  If SuAry2(iR, iC) = "" Then
    Exit Sub
  End If
  n1 = Split(SuAry2(iR, iC), "-")(0)
  n2 = Split(SuAry2(iR, iC), "-")(1)
  If n2 = "0" Or n2 = "99" Then
    Exit Sub
  End If
  n2 = n2 - 1
  strSu = n1 & "-" & n2
  For i = 1 To rMax
    For j = 1 To cMax
      If SuAry2(i, j) = strSu Then
        iR2 = i
        iC2 = j
        Exit Sub
      End If
    Next
  Next
End Sub

Private Sub getAfterCell(ByVal iR As Integer, ByVal iC As Integer, ByRef iR2 As Integer, ByRef iC2 As Integer)
  Dim i As Integer
  Dim j As Integer
  Dim n1 As Integer
  Dim n2 As Integer
  Dim strSu As String
  iR2 = 0
  iC2 = 2
  If SuAry2(iR, iC) = "" Then
    Exit Sub
  End If
  n1 = Split(SuAry2(iR, iC), "-")(0)
  n2 = Split(SuAry2(iR, iC), "-")(1)
  If n2 = "0" Or n2 = "99" Then
    Exit Sub
  End If
  n2 = n2 + 1
  strSu = n1 & "-" & n2
  For i = 1 To rMax
    For j = 1 To cMax
      If SuAry(i, j) = strSu Then
        iR2 = i
        iC2 = j
        Exit Sub
      End If
    Next
  Next
  strSu = n1 & "-99"
  For i = 1 To rMax
    For j = 1 To cMax
      If SuAry2(i, j) = strSu Then
        If (iR = i - 1 And iC = j) Or _
          (iR = i And iC = j + 1) Or _
          (iR = i + 1 And iC = j) Or _
          (iR = i And iC = j - 1) Then
          iR2 = i
          iC2 = j
        Else
          iR2 = 0
          iC2 = 0
        End If
        Exit Sub
      End If
    Next
  Next
End Sub

Private Function getStart(ByRef iR As Integer, ByRef iC As Integer) As Boolean
  iC = iC + 1
  If RowColAjust(iR, iC) = False Then
    getStart = False
    Exit Function
  End If
  Do
    If IsNumeric(SuAry(iR, iC)) Then
      If chkEnd(iR, iC) <> True Then
        getStart = True
        Exit Function
      End If
    End If
    iC = iC + 1
    If RowColAjust(iR, iC) = False Then
      getStart = False
      Exit Function
    End If
  Loop
End Function

Private Function RowColAjust(ByRef iR As Integer, ByRef iC As Integer) As Boolean
  If iC > cMax Then
    iR = iR + 1
    iC = 1
  End If
  If iR > rMax Then
    RowColAjust = False
    Exit Function
  End If
  RowColAjust = True
End Function

Private Function chkEnd(ByVal iR As Integer, ByVal iC As Integer) As Boolean
  Dim iR2 As Integer
  Dim iC2 As Integer
  Call getEnd(iR2, iC2, SuAry(iR, iC))
  If iR2 = iR And iC2 = iC Then
    chkEnd = True
  Else
    chkEnd = False
  End If
End Function

Private Sub getEnd(ByRef iR2 As Integer, ByRef iC2 As Integer, ByVal i As String)
  iR2 = rMax
  iC2 = cMax
  Do
    If SuAry(iR2, iC2) = i Then
      Exit Sub
    End If
    iC2 = iC2 - 1
    If iC2 < 1 Then
      iR2 = iR2 - 1
      iC2 = cMax
    End If
    If iR2 < 1 Then
      Exit Sub
    End If
  Loop
End Sub

Private Function chkRout(ByVal iR1 As Integer, ByVal iC1 As Integer) As Boolean
  Dim iR2 As Integer
  Dim iC2 As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  Dim i As Integer
  Dim j As Integer
  chkRout = True
  For i = 1 To rMax
    For j = 1 To cMax
      SuAry2(i, j) = SuAry(i, j)
    Next
  Next
  If getStart(iR1, iC1) = False Then
    chkRout = True
    Exit Function
  End If
  Call getEnd(iR2, iC2, SuAry(iR1, iC1))
  
  If chkRout2(iR1, iC1, iR2, iC2) = False Then
    chkRout = False
  End If
  If chkRout(iR1, iC1) = False Then
    chkRout = False
  End If
End Function

Private Function chkRout2(ByVal iR1 As Integer, ByVal iC1 As Integer, ByVal iR2 As Integer, ByVal iC2 As Integer) As Boolean
  Dim i As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  Select Case True
    Case iR1 = iR2 - 1 And iC1 = iC2, _
       iR1 = iR2 And iC1 = iC2 + 1, _
       iR1 = iR2 + 1 And iC1 = iC2, _
       iR1 = iR2 And iC1 = iC2 - 1
      chkRout2 = True
      Exit Function
  End Select
  For i = 1 To 4
    Select Case i
      Case 1
        iR3 = iR2 + 1
        iC3 = iC2
      Case 2
        iR3 = iR2
        iC3 = iC2 + 1
      Case 3
        iR3 = iR2 - 1
        iC3 = iC2
      Case 4
        iR3 = iR2
        iC3 = iC2 - 1
    End Select
    If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
      If SuAry2(iR3, iC3) = "" Then
        SuAry2(iR3, iC3) = "●"
        If chkRout2(iR1, iC1, iR3, iC3) = True Then
          chkRout2 = True
          Exit Function
        End If
      End If
    End If
  Next
  chkRout2 = False
End Function

Private Function chkClose(ByVal iR1 As Integer, ByVal iC1 As Integer, ByVal iNo As Integer, ByVal iR As Integer, ByVal iC As Integer) As Boolean
  Dim iR2 As Integer
  Dim iC2 As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  Dim nCnt As Integer
  Dim sCnt As Integer
  Dim ix As Integer
  Dim ix2 As Integer
  Dim i As Integer
  Dim j As Integer
  chkClose = True
  
  For ix = 1 To 4
    Select Case ix
      Case 1
        iR2 = iR1 - 1
        iC2 = iC1
      Case 2
        iR2 = iR1 + 1
        iC2 = iC1
      Case 3
        iR2 = iR1
        iC2 = iC1 - 1
      Case 4
        iR2 = iR1
        iC2 = iC1 + 1
    End Select
    If iC2 >= 1 And iC2 <= cMax And iR2 >= 1 And iR2 <= rMax Then
      For i = 1 To rMax
        For j = 1 To cMax
          SuAry2(i, j) = SuAry(i, j)
        Next
      Next
      If SuAry2(iR2, iC2) = "" Then
        If chkClose5(iR2, iC2, iNo) = False Then
          chkClose = False
          Exit Function
        End If
        nCnt = 0
        sCnt = 0
        If chkClose3(iR2, iC2, iNo) = False Then
          chkClose = False
          Exit Function
        End If
        Call chkClose2(iR2, iC2, nCnt, sCnt, iNo)
        If nCnt <= 1 Or (nCnt < 100 And nCnt > sCnt) Then
          If nCnt < 100 And nCnt > sCnt Then
            DoEvents
          End If
          chkClose = False
          Exit Function
        End If
        
        Erase SuAry9
        For i = 1 To rMax
          For j = 1 To cMax
            SuAry2(i, j) = SuAry(i, j)
          Next
        Next
        Call chkClose4(iR2, iC2, iNo)
        If chkAry92 = False Then
          chkClose = False
          Exit Function
        End If
        
        For ix2 = 1 To 4
          Select Case ix2
            Case 1
              iR3 = iR2 - 1
              iC3 = iC2
            Case 2
              iR3 = iR2 + 1
              iC3 = iC2
            Case 3
              iR3 = iR2
              iC3 = iC2 - 1
            Case 4
              iR3 = iR2
              iC3 = iC2 + 1
          End Select
          If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
            For i = 1 To rMax
              For j = 1 To cMax
                SuAry2(i, j) = SuAry(i, j)
              Next
            Next
            SuAry2(iR2, iC2) = "●"
            If SuAry2(iR3, iC3) = "" Then
              nCnt = 0
              sCnt = 0
              Call chkClose2(iR3, iC3, nCnt, sCnt, iNo)
              If nCnt <= 0 Then
                chkClose = False
                Exit Function
              End If
            End If
          End If
        Next
      End If
    End If
  Next
  
  Erase SuAry9
  Select Case True
    Case iC1 = 2
      If SuAry(iR1, 1) = "" Then
        For i = 1 To rMax
          For j = 1 To cMax
            SuAry2(i, j) = SuAry(i, j)
          Next
        Next
        SuAry2(iR1, 1) = "●"
        Call chkClose4(iR1 - 1, 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
        Call chkClose4(iR1 + 1, 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
      End If
    Case iC1 = cMax - 1
      If SuAry(iR1, cMax) = "" Then
        For i = 1 To rMax
          For j = 1 To cMax
            SuAry2(i, j) = SuAry(i, j)
          Next
        Next
        SuAry2(iR1, cMax) = "●"
        Call chkClose4(iR1 - 1, cMax, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
        Call chkClose4(iR1 + 1, cMax, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
      End If
    Case iR1 = 2
      If SuAry(1, iC1) = "" Then
        For i = 1 To rMax
          For j = 1 To cMax
            SuAry2(i, j) = SuAry(i, j)
          Next
        Next
        SuAry2(1, iC1) = "●"
        Call chkClose4(1, iC1 - 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
        Call chkClose4(1, iC1 + 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
      End If
    Case iR1 = rMax - 1
      If SuAry(rMax, iC1) = "" Then
        For i = 1 To rMax
          For j = 1 To cMax
            SuAry2(i, j) = SuAry(i, j)
          Next
        Next
        SuAry2(rMax, iC1) = "●"
        Call chkClose4(rMax, iC1 - 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
        Call chkClose4(rMax, iC1 + 1, iNo)
        If chkAry91 = False Then
          chkClose = False
          Exit Function
        End If
      End If
  End Select
End Function

Private Sub chkClose2(ByVal iR1 As Integer, ByVal iC1 As Integer, ByRef nCnt As Integer, ByRef sCnt As Integer, ByVal iNo As Integer)
  Dim i As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  If nCnt >= 100 Then
    Exit Sub
  End If
  For i = 0 To 4
    Select Case i
      Case 0
        iR3 = iR1
        iC3 = iC1
      Case 1
        iR3 = iR1 + 1
        iC3 = iC1
      Case 2
        iR3 = iR1
        iC3 = iC1 + 1
      Case 3
        iR3 = iR1 - 1
        iC3 = iC1
      Case 4
        iR3 = iR1
        iC3 = iC1 - 1
    End Select
    If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
      If SuAry2(iR3, iC3) = "" Then
        SuAry2(iR3, iC3) = "●"
        sCnt = sCnt + 1
        Call chkClose2(iR3, iC3, nCnt, sCnt, iNo)
      Else
        If SuAry2(iR3, iC3) = CStr(iNo) Then
          If chkEnd(iR3, iC3) = True Then
            nCnt = 100
            Exit Sub
          End If
        ElseIf IsNumeric(SuAry2(iR3, iC3)) Then
          If chkUsed(iR3, iC3) = True Then
            SuAry2(iR3, iC3) = "●"
          Else
            SuAry2(iR3, iC3) = "●"
            nCnt = nCnt + 1
            If nCnt >= 4 Then
              Exit Sub
            End If
          End If
        End If
      End If
    End If
    If nCnt >= 100 Then
      Exit Sub
    End If
  Next
End Sub

Private Function chkClose3(ByVal iR1 As Integer, ByVal iC1 As Integer, ByVal iNo As Integer) As Boolean
  Dim cnt As Integer
  Dim i As Integer
  Dim j As Integer
  chkClose3 = True
  If (iR1 = 1 Or iR1 = rMax) And (iC1 = 1 Or iC1 = cMax) Then
    chkClose3 = True
    Exit Function
  End If
  cnt = 0
  If iR1 > 1 Then
    If SuAry2(iR1 - 1, iC1) = "" Then
      cnt = cnt + 1
    End If
    If IsNumeric(SuAry2(iR1 - 1, iC1)) Then
      chkClose3 = True
      Exit Function
    End If
    If SuAry2(iR1 - 1, iC1) <> "●" And InStr(SuAry2(iR1 - 1, iC1), "-") > 0 Then
      If Split(SuAry2(iR1 - 1, iC1), "-")(0) <> CStr(iNo) Then
        chkClose3 = True
        Exit Function
      End If
    End If
  End If
  
  If iR1 < rMax Then
    If SuAry2(iR1 + 1, iC1) = "" Then
      cnt = cnt + 1
    End If
    If IsNumeric(SuAry2(iR1 + 1, iC1)) Then
      chkClose3 = True
      Exit Function
    End If
    If SuAry2(iR1 + 1, iC1) <> "●" And InStr(SuAry2(iR1 + 1, iC1), "-") > 0 Then
      If Split(SuAry2(iR1 + 1, iC1), "-")(0) <> CStr(iNo) Then
        chkClose3 = True
        Exit Function
      End If
    End If
  End If
  
  If iC1 > 1 Then
    If SuAry2(iR1, iC1 - 1) = "" Then
      cnt = cnt + 1
    End If
    If IsNumeric(SuAry2(iR1, iC1 - 1)) Then
      chkClose3 = True
      Exit Function
    End If
    If SuAry2(iR1, iC1 - 1) <> "●" And InStr(SuAry2(iR1, iC1 - 1), "-") > 0 Then
      If Split(SuAry2(iR1, iC1 - 1), "-")(0) <> CStr(iNo) Then
        chkClose3 = True
        Exit Function
      End If
    End If
  End If
  
  If iC1 < cMax Then
    If SuAry2(iR1, iC1 + 1) = "" Then
      cnt = cnt + 1
    End If
    If IsNumeric(SuAry2(iR1, iC1 + 1)) Then
      chkClose3 = True
      Exit Function
    End If
    If SuAry2(iR1, iC1 + 1) <> "●" And InStr(SuAry2(iR1, iC1 + 1), "-") > 0 Then
      If Split(SuAry2(iR1, iC1 + 1), "-")(0) <> CStr(iNo) Then
        chkClose3 = True
        Exit Function
      End If
    End If
  End If
  If cnt <= 1 Then
    chkClose3 = False
  End If
End Function

Private Sub chkClose4(ByVal iR1 As Integer, ByVal iC1 As Integer, ByVal iNo As Integer)
  Dim i As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  For i = 1 To 4
    Select Case i
      Case 1
        iR3 = iR1 + 1
        iC3 = iC1
      Case 2
        iR3 = iR1
        iC3 = iC1 + 1
      Case 3
        iR3 = iR1 - 1
        iC3 = iC1
      Case 4
        iR3 = iR1
        iC3 = iC1 - 1
    End Select
    If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
      If SuAry2(iR3, iC3) = "" Then
        SuAry2(iR3, iC3) = "●"
        Call chkClose4(iR3, iC3, iNo)
      Else
        If IsNumeric(SuAry2(iR3, iC3)) Then
          If SuAry2(iR3, iC3) = CStr(iNo) Then
            SuAry9(SuAry2(iR3, iC3)) = -1
          Else
            If chkUsed(iR3, iC3) = False Then
              SuAry9(SuAry2(iR3, iC3)) = SuAry9(SuAry2(iR3, iC3)) + 1
              SuAry2(iR3, iC3) = "●"
            Else
              SuAry2(iR3, iC3) = "●"
            End If
          End If
        End If
      End If
    End If
  Next
End Sub

Private Function chkClose5(ByVal iR1 As Integer, ByVal iC1 As Integer, ByVal iNo As Integer) As Boolean
  chkClose5 = True
  If iR1 <= 1 Or iR1 >= rMax Or iC1 <= 1 Or iC1 >= cMax Then
    Exit Function
  End If
  If SuAry2(iR1 - 1, iC1) = "" Or _
    SuAry2(iR1 + 1, iC1) = "" Or _
    SuAry2(iR1, iC1 - 1) = "" Or _
    SuAry2(iR1, iC1 + 1) = "" Then
    Exit Function
  End If
  If SuAry2(iR1 - 1, iC1) = CStr(iNo) Or _
    SuAry2(iR1 + 1, iC1) = CStr(iNo) Or _
    SuAry2(iR1, iC1 - 1) = CStr(iNo) Or _
    SuAry2(iR1, iC1 + 1) = CStr(iNo) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1 - 1, iC1)) And _
    SuAry2(iR1 + 1, iC1) = SuAry2(iR1 - 1, iC1) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1 - 1, iC1)) And _
    SuAry2(iR1, iC1 - 1) = SuAry2(iR1 - 1, iC1) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1 - 1, iC1)) And _
    SuAry2(iR1, iC1 + 1) = SuAry2(iR1 - 1, iC1) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1 + 1, iC1)) And _
    SuAry2(iR1, iC1 - 1) = SuAry2(iR1 + 1, iC1) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1 - 1, iC1)) And _
    SuAry2(iR1, iC1 + 1) = SuAry2(iR1 - 1, iC1) Then
    Exit Function
  End If
  If IsNumeric(SuAry2(iR1, iC1 - 1)) And _
    SuAry2(iR1, iC1 + 1) = SuAry2(iR1, iC1 - 1) Then
    Exit Function
  End If
  chkClose5 = False
End Function

Private Function chkUsed(ByVal iR1 As Integer, ByVal iC1 As Integer) As Boolean
  Dim i As Integer
  Dim iR3 As Integer
  Dim iC3 As Integer
  For i = 1 To 4
    Select Case i
      Case 1
        iR3 = iR1 + 1
        iC3 = iC1
      Case 2
        iR3 = iR1
        iC3 = iC1 + 1
      Case 3
        iR3 = iR1 - 1
        iC3 = iC1
      Case 4
        iR3 = iR1
        iC3 = iC1 - 1
    End Select
    If iC3 >= 1 And iC3 <= cMax And iR3 >= 1 And iR3 <= rMax Then
      If InStr(SuAry2(iR3, iC3), SuAry2(iR1, iC1) & "-") > 0 Then
        chkUsed = True
        Exit Function
      End If
    End If
  Next
  chkUsed = False
End Function

Private Function chkAry91() As Boolean
  Dim i As Integer
  Dim cnt As Integer
  cnt = 0
  For i = LBound(SuAry9) To UBound(SuAry9)
    If SuAry9(i) = 1 Then
      cnt = cnt + 1
    End If
  Next
  If cnt > 1 Then
    chkAry91 = False
  Else
    chkAry91 = True
  End If
End Function

Private Function chkAry92() As Boolean
  Dim i As Integer
  Dim cnt As Integer
  Dim sFlg As Boolean
  sFlg = True
  cnt = 0
  For i = LBound(SuAry9) To UBound(SuAry9)
    If SuAry9(i) = 2 Then
      cnt = cnt + 1
    End If
    If SuAry9(i) < 0 Then
      chkAry92 = True
      Exit Function
    End If
    If SuAry9(i) > 0 Then
      sFlg = False
    End If
  Next
  If cnt = 0 And sFlg = False Then
    chkAry92 = False
  Else
    chkAry92 = True
  End If
End Function


10×18で、18分

12×12で、16分

という結果となりました。

もちろん、時間はたまたまテストした問題における時間です。

解けるまでの時間は問題に大きく左右されますので、あくまで目安です。

ちなみに、

ナンバーリンク(パズル)を解くVBAに挑戦」で使用した問題ですと、

10秒程度で解けています。

本当は、10×18や12×12で、1分を切れないものかと思い挑戦したのですが、

そこには至りませんでした。

もっと、厳密な判定をしていくことと、

そもそも、どっちの方向に進むことが効率的なのか等々

まだまだ、組み込むべきロジックが残されています。

今回の修正にかけた時間と同じくらいの時間を費やせば、

なんとかなりそうに思ってはいるので、

今後、時間が取れたら、また挑戦したいと思います。

それでも、まあなんとか実用に耐えうる速度にはなったのではないかと思います。

このパズルはお遊びですが、

実際のシステム開発でもパフォーマンスは重要な要素になります。

ただし、費用対効果を考える必要がありますので、

許容範囲に収まったときは、それ以上むやみに続けるのは単なる自己満足にしかなりません。

ただ、技術者って、結構自己満足の仕事を良くしているものなんですけどね。(笑)


ナンバーリンクを解くVBAのパフォーマンス改善 : 1 2 3




同じテーマ「ExcelマクロVBAサンプル集」の記事

アメブロの記事本文をVBAでバックアップする6
数独(ナンプレ)を解くVBAに挑戦5
数独(ナンプレ)を解くアルゴリズムの要点とパフォーマンスの検証4
ナンバーリンク(パズル)を解くVBAに挑戦8

新着記事 ・・・新着記事一覧を見る

データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • ナンバーリンクを解くVBAのパフォーマンス改善3

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP