excel - 使用VBA代码在excel中查找循环链接

Question

我有一张如下表。这表示Name列中的值之间的链接（或边）。

+-------+-------------------+
| Name  | from              |
+-------+-------------------+
| G     | X; Y; HG; WP      |
| X     | U                 |
| Y     |                   |
| U     | V                 |
| V     |                   |
| K     | M; N              |
| M     |                   |
| N     |                   |
| G1    | G                 |
| G2    | G1                |
| G3    | G2                |
| G4a   | G3                |
| J     | G4a               |
| G4b   | G3                |
| G5b   | G4b               |
| H     | G5b               |
| R     | H; J              |
| R1    | R                 |
| R2    | R1                |
| O     | R2                |
| O1    | O                 |
| O2    | O1                |
| O3    | O2                |
| F     | H; K; TR          |
| P     | G; Z              |
| Z     |                   |
| HG    | VB; NH            |
| WP    |                   |
| TR    | Z                 |
| VB    | ICH; OL; NZ; LO   |
| NH    |                   |
| ICH   | NZ                |
| NZ    |                   |
| LO    |                   |
| OL    | TZ; HG            |
| TZ    |                   |
| BN    | WD; PO            |
| WD    | RZ; UX            |
| PO    | QA; IU; BV; MM; BN|
| RZ    |                   |
| UX    |                   |
| IU    |                   |
| QA    |                   |
| BV    |                   |
| MM    |                   |
+-------+-------------------+

Name如果我们按顺序查找列中的值，我想看看列中的值是否作为前辈存在from。换句话说，我想看看是否有任何循环链接。

如何用VBAexcel中的代码做到这一点？

在此示例HG, VB, OL, BN, PO中具有循环链接。我只想突出显示“名称”列中的那些单元格。

一种方法是在单独的列中查找每一行的所有前辈，如下所示。

+-------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| GName | from               | predecessors                                                                                                                                        |
+-------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| G     | X; Y; HG; WP       | X; U; V; Y; HG; VB; NH; ICH; NZ; LO; OL; TZ; WP                                                                                                     |
| X     | U                  | U; V                                                                                                                                                |
| Y     |                    |                                                                                                                                                     |
| U     | V                  | V                                                                                                                                                   |
| V     |                    |                                                                                                                                                     |
| K     | M; N               | M; N                                                                                                                                                |
| M     |                    |                                                                                                                                                     |
| N     |                    |                                                                                                                                                     |
| G1    | G                  | G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                                  |
| G2    | G1                 | G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                              |
| G3    | G2                 | G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                          |
| G4a   | G3                 | G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                      |
| J     | G4a                | G4a; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                 |
| G4b   | G3                 | G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                      |
| G5b   | G4b                | G4b; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                                 |
| H     | G5b                | G5b; G4b; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                                            |
| R     | H; J               | J; G4a; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ; H; G5b; G4b; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ |
| R1    | R                  | R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                              |
| R2    | R1                 | R1; R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                          |
| O     | R2                 | R2; R1; R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                      |
| O1    | O                  | O; R2; R1; R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                                   |
| O2    | O1                 | O1; O; R2; R1; R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                               |
| O3    | O2                 | O2; O1; O; R2; R1; R; J; H; G4a; G5b; G3; G4b; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ                                           |
| F     | H; KTR             | K; M; N; H; G5b; G4b; G3; G2; G1; G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ; TR; Z                                                         |
| P     | G; Z               | G; X; Y; HG; WP; U; VB; NH; V; ICH; NZ; LO; OL; TZ; Z                                                                                               |
| Z     |                    |                                                                                                                                                     |
| HG    | VB; NH             | VB; ICH; NZ; LO; OL; HG; TZ; NH; NH                                                                                                                 |
| WP    |                    |                                                                                                                                                     |
| TR    | Z                  | Z                                                                                                                                                   |
| VB    | ICH; OL; NZ; LO    | ICH; NZ; NZ; LO; OL; HG; TZ; VB; NH; ICH; NZ; LO                                                                                                    |
| NH    |                    |                                                                                                                                                     |
| ICH   | NZ                 | NZ                                                                                                                                                  |
| NZ    |                    |                                                                                                                                                     |
| LO    |                    |                                                                                                                                                     |
| OL    | TZ; HG             | HG; VB; NH; ICH; NZ; LO; OL; TZ; TZ                                                                                                                 |
| TZ    |                    |                                                                                                                                                     |
| BN    | WD; PO             | WD; RZ; UX; PO; BN; IU; QA; BV; MM; WD; RZ; UX                                                                                                      |
| WD    | RZ; UX             | RZ; UX                                                                                                                                              |
| PO    | QA; IU; BV; MM; BN | BN; WD; PO; RZ; UX; IU; QA; BV; MM; IU; QA; BV; MM                                                                                                  |
| RZ    |                    |                                                                                                                                                     |
| UX    |                    |                                                                                                                                                     |
| IU    |                    |                                                                                                                                                     |
| QA    |                    |                                                                                                                                                     |
| BV    |                    |                                                                                                                                                     |
| MM    |                    |                                                                                                                                                     |
+-------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+

Name现在在列中的相应单元格中搜索列中的值predecessors将给出所需的结果。

如何使用VBAexcel中的代码实现这一点？

编辑据我所知，这是一个网络问题。为了更清楚，这里是网络图。

EDIT2这是我在@GSerg建议的方向上的尝试（这里是VBA noob） 。卡在获取前辈的位置。得到Argument not optional错误。

Public Function NetworkCyclicityCheck(node As String, col As Range) As String

Dim dicP As Object: Set dicP = CreateObject("Scripting.Dictionary")

If Trim(node) <> "" Then
        For Each x In Split(cl.Value2, ";")
            Key = Trim(x)
            With Range
            pcell = .Find(What:=Key, LookAt:=xlWhole, MatchCase:=False)
            dicP(pcell) = dicP(pcell) & "," & cl.Address(0, 0)
        Next x
End If

Set NetworkCyclicityCheck = dicP
Set dicP = Nothing

End Function

标签： excelvbagraph

Answer 1

扩展我的评论，

Option Explicit

Public Sub GenerateFormulas()
  Dim NodeNames As Range
  Set NodeNames = Range("A2:A56")

  ' Assumed that the "from" column is immediately to the right,
  ' and that the column after it is the one where the formulas will appear

  Dim dict As Object
  Set dict = CreateObject("Scripting.Dictionary")

  Dim c As Range
  For Each c In NodeNames.Cells
    dict(c.value) = c.Offset(0, 2).Address(False, False, xlA1)
  Next

  For Each c In NodeNames.Cells
    c.Offset(0, 2).Formula = ListToFormula(dict, c.Offset(0, 1).value)
  Next
End Sub

Private Function ListToFormula(ByVal dict As Object, ByVal list As String) As String
  Dim nodes() As String
  nodes = Split(list, ";")

  ListToFormula = "="""""

  Dim i As Long
  For i = LBound(nodes) To UBound(nodes)
    ListToFormula = ListToFormula & " & " & dict(Trim$(nodes(i)))
  Next
End Function

现在您可以查看Worksheet.CircularReference，它也会很好地显示在工作表上：