我使用此解决方案从父和id关系生成路径
如何在excel中建立父子数据表？
在我的情况下，排序应该基于现有字段。(seq_num)，自定义排序级别1，而不是自定义排序级别2。。。。。。自定义分拣级别最大
输入
id Parent_id seq_num
29938 29937 901
29939 29938 0
29940 29938 5
29941 29938 6
29942 29938 8
29943 29938 14
29944 29938 13
29945 29938 9
29946 29938 12
29947 29938 1
29948 29938 10
29949 29938 3
29950 29944 512
29951 29944 513
29952 29943 512
29953 29943 513
输出
订单id路径
1 29938 29937.29938
2 29939 29937.29938.29939
3 29947 29937.29938.29947
4 29949 29937.29938.29949
5 29940 29937.29938.29940
6 29941 29937.29938.29941
7 29942 29937.29938.29942
8 29945 29937.29938.29945
9 29948 29937.29938.29948
10 29946 29937.29938.29946
11 29944 29937.29938.29944
12 29950 29937.29938.29944.29950
13 29951 29937.29938.29944.29951
14 29943 29937.29938.29943
15 29952 29937.29938.29943.29952
16 29953 29937.29938.29943.29953
我使用的是excel(ado with jet)，输入表是csv，jet不支持递归自联接，级别数量一直在变化。
答案简介
随着你的最新更新，我相信我对你寻求的排序顺序有了更好的理解，尽管我仍然有问题。然而，你的需求可能是你独有的，所以如果我完全理解你的需求并提供了你所需的确切代码，这个答案对其他人来说用处不大。
我相信下面的程序符合您的要求，但也对其他具有定制排序顺序的人有用。
你可以跳到我解释如何安装它的代码上方。然后你可以尝试宏Test，它提供了四个如何使用宏QuickSort创建不同序列的例子。正如我稍后所解释的，我相信示例3将是满足您需求的一个有用的起点。
本文的大部分内容解释了我是如何使用一些鲜为人知的VBA功能来创建一个灵活的排序例程的，该例程可以在许多情况下使用，我相信包括您的情况。
需求范围
在我之前的回答中包含的代码中，我有：
Call SimpleSort(StrArray)
如果你在互联网上搜索VBA排序例程，你会发现很多都有类似的参数。StrArray是一个字符串数组。例程将在元素之间交换值，以便返回时值按升序排列。
一种常见的变体是：
Call SimpleSort(StrArray, InxLow, InxHigh)
这允许您指定只对数组的一部分进行排序。
这提供了一点灵活性，但例程仍然将字符串数组按升序排序。此例程不会对long数组或double数组进行排序，也不会将字符串数组按降序进行排序，或将任何内容按自定义序列进行排序。
我可以使StrArray成为一个变量数组，这样它就可以包含字符串、long或double。然而，其他例程可能需要正确键入数组，因此这并不总是一个方便的解决方案。
我可以添加一个布尔参数，True表示按升序排序值，False表示按降序排序值。我必须找到排序例程中的位置，它决定是否需要交换，并引入if then else End if。
但如果我想要其他序列，这将没有帮助。您想要升序，而不是数组元素值的升序。你想让这类人看看其他地方，看看元素X是否在元素Y之前。试图用定制代码替换现有的do-I-swap代码是很棘手的，只能解决今天的问题。为每个不同的序列设置不同的排序例程可能会变得难以管理。
一个可能与您相关的常见要求是，数组的顺序不能更改。也许数组包含非常长的字符串；在元素之间交换长字符串是缓慢的。也许它是VBA调用用户类型的数组，但大多数语言调用结构；结构之间的值交换通常必须逐字段执行。也许不可能将数组重新排序到原始序列中，这样它就不会受到干扰。
当您不希望对目标数组进行排序时，标准技术是使用索引数组。假设我有一个目标数组，我不想排序，但我想按字母顺序访问：
Element No 1 2 3 4 5 6 7 8 9
Target D C E A I G F H B
我创建数组索引并初始化它，这样：
Element No 1 2 3 4 5 6 7 8 9
Target D C E A I G F H B
Index 1 2 3 4 5 6 7 8 9
然后，我根据Target:中的值对Index数组进行排序
Element No 1 2 3 4 5 6 7 8 9
Target D C E A I G F H B
Index 4 9 2 1 3 7 6 8 5
这给出：
Index(1) = 4和Target(Index(1)) = "A"Index(2) = 9和Target(Index(2)) = "B"Index(3) = 2和Target(Index(3)) = "C"
也就是说，Index允许在不改变Target的情况下访问所需序列中的Target的元素。
在上面，我试图描述您将需要或可能需要的功能。如果你想把字符串数组按升序排序，你可以很容易地在互联网上找到合适的例程。如果你想要一个其他类型的例程，你必须调整其中一个例程或编写自己的代码。如果你要调整/编写自己的代码，你还可以包括你期望需要的所有功能，因为额外的功能通常更容易调试排序例程。如果可以避免的话，你不想有一个以上的排序例程。
我希望在我的一个排序例程中看到的功能：
- 能够对任何类型的数组进行排序
- 创建已排序的索引数组，而不是已排序的目标数组
- 能够按任意顺序排序
有用的技术1-ParamArray
要求2——创建一个排序的索引数组，而不是一个排序目标数组——这有点难理解，但不需要VBA的专门知识。
如果您了解ParamArrays，那么需求1(能够对任何类型的数组进行排序)就变得很容易了。我经常感到惊讶的是，ParamArrays并没有被更好地了解和充分使用，因为它们非常有用。
在大多数VBA教程的早期，都教导您声明子例程。例如：
Sub MySub(ByRef Param1() As String, ByVal Param2 As Long)
: : : : :
End Sub
我可以指定Param2是可选的，这样我就可以用一个或两个参数调用MySub，但这是这种声明风格灵活性的极限。另一种样式是：
Sub MySub(ByRef Param1() As String, ByVal Param2 As Long , _
ParamArray Extra() As Variant)
: : : : :
End Sub
ParamArray只能是最后一个参数。在这里，我指定了两个固定参数，它们必须出现在每个调用中，然后是零个或多个其他参数。例如，我可能有：
Call MySub(StrArrayA, 5, 26.1, "abcdef", StrArrayB)
MySub:
- 对
Param1的引用访问StrArrayA。因此Param1(5) = "a"将StrArrayA(5)设置为="a"> - 参数2=5
- 额外(0)=26.1
- Extra(1)="abcdef">
- 引用
Extra(2)访问StrArrayB
ParamArray的下限将始终为零。编译器对这些额外参数的性质一无所知。如果MySub要求第一个额外参数为双精度，程序员必须使用函数VarType来检查它是否为双精度。
我不打算描述你可以用ParamArrays做的所有美妙的事情，因为这远远超出了这个答案的范围。
你只需要知道我提供的排序例程的最后一个参数是Target数组，它是一个ParamArray，所以它可以是字符串、doubles、long或任何其他基本类型的数组。
我想我可以说Target数组可以是任何类型，但我发现这是不真实的。这超出了您当前需求的范围。为了完整起见，我在最后讨论这个问题。
有用的技术2-运行
可以按名称运行宏。例如：
Result = Application.Run(QSequenceName, Param1, Param2, Param3, …)
您可以使用Run做比我需要的或此处指定的更多的事情。QSequenceName是用于排序例程的参数。您必须编写一个布尔函数，该函数将告诉排序例程所需的两个值的序列。排序例程不知道Target数组，也不知道您想要的序列。名为"QSequenceName"的布尔函数知道目标数组的类型和所需的序列。是的，你必须对这个布尔函数进行编码，但你不必修改排序例程来获得你需要的任何排序序列。
考虑：
Function StrDescend(Target() As String, Index() As Long, Inx1 As Long, Inx2 As Long) As Boolean
StrDescend = IIf(Target(Index(Inx1)) >= Target(Index(Inx2)), True, False)
End Function
这是我编写的一个布尔函数，用于演示排序例程。参数必须为：
Target：需要排序索引的数组Index：排序结束时定义Target序列的数组Inx1：第一个元素对Index的索引。请注意宏是如何使用Target(Index(Inx1)来访问第一个元素的值的- CCD_ 34：第二个元素对CCD_ 35的索引
如果两个值相等，或者元素1在最终序列中位于元素2之前，则函数必须返回True。如果元素1要在最终序列中的元素2之后，则函数必须返回False。
CCD_ 38是这种函数的一个简单示例，并且使用具有CCD_ 39的元素的值。下面的代码包括一些例子，这些例子表明了测序的复杂性
使用此函数的排序例程的调用是：
Call QuickSort(True, Index, LBound(KeyStr), UBound(KeyStr), "StrDescend", KeyStr)
- 对您来说，第一个参数将始终是
True - 第二个参数是一个Longs数组，其中将返回排序后的索引
- CCD_ 41和CCD_。如果只对目标数组的一部分进行排序，则可以调整这些值
"StrDescend"是用于指定"快速排序"排序序列的布尔函数的名称KeyStr是目标阵列
QuickSort的参数在宏的顶部指定。你说过你觉得递归很难。QuickSort是递归的，所以我建议您忽略它的工作方式；只要想想如何使用它来实现您需要的序列。
在运行宏Test并了解它是如何实现前三种排序之前，您应该忽略此部分。最后的顺序要复杂得多，我相信这远远超出了你目前的要求。我想展示QuickSort可以实现什么。忽略最后一个排序和这一部分，直到(除非？)你对前三个排序完全满意为止。
我想展示QuickSort如何对用户类型数组进行排序。我定义了用户类型Person和用户类型Child，其中Person包括Child类型的数组。然后，我尝试对Person类型的数组进行排序。我发现我无法在ParamArray中传递用户类型的数组。我不知道我以前怎么没有达到这个限制。
我将这两种用户类型转换为一个粗糙的类。我不想提供关于类或集合的教程。我要说的是，这是一个如何不构造类的例子。然而，这足以证明QuickSort将创建一个Persons数组的索引，该数组给出的顺序是：没有孩子的人，然后是按最小孩子降序排列的人。我不知道为什么有人想要这个序列，但我认为这是一个很容易理解的例子。
创建一个新工作簿。
创建一个新的Class模块，并将其命名为CPersonData。(使用F4访问类模块的属性。Name是第一个属性。)将此代码复制到类模块：
Public NameFamily As String
Public NameGiven As String
' * Example values for ChildNameGiven:
' George
' George:Jane
' George:Jane:Mary
' * Example values for ChildNameAge:
' 5
' 5:4
' 5:4:10
' * ChildNameGiven and ChildAge must have the same number of colons with the
' parts separated by colons matched by position. In the third examples above:
' George is 5, Jane is 4 and Mary is 10.
Public ChildNameGiven As String
Public ChildAge As String
创建一个普通模块并将此代码复制到它：
Option Explicit
Dim AlphabetSequence() As Variant
Sub Test()
Dim ChildNamePart() As String
Dim ChildAgePart() As String
Dim Index() As Long
Dim InxChildCrnt As Long
Dim InxCrnt As Long
Dim InxPerson As Long
Dim KeyStr() As String
Dim NumChildren As Long
Dim Person() As CPersonData
' Create array of strings and output to immediate window
' ======================================================
ReDim KeyStr(1 To 12)
KeyStr(1) = "B": KeyStr(2) = "F": KeyStr(3) = "C"
KeyStr(4) = "E": KeyStr(5) = "A": KeyStr(6) = "D"
KeyStr(7) = "I": KeyStr(8) = "H": KeyStr(9) = "G"
KeyStr(10) = "I": KeyStr(11) = "E": KeyStr(12) = "A"
Debug.Print " Array seq ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & InxCrnt, 3) & " ";
Next
Debug.Print
Debug.Print " Key ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & KeyStr(InxCrnt), 3) & " ";
Next
Debug.Print
Debug.Print
' Sort KeyStr into ascending sequence and output to immediate window
' ==================================================================
Call QuickSort(True, Index, LBound(KeyStr), UBound(KeyStr), "StrAscend", KeyStr)
Debug.Print " Ascending ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & InxCrnt, 3) & " ";
Next
Debug.Print
Debug.Print " Index ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & Index(InxCrnt), 3) & " ";
Next
Debug.Print
Debug.Print " Key ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & KeyStr(Index(InxCrnt)), 3) & " ";
Next
Debug.Print
Debug.Print
' Sort KeyStr into descending sequence and output to immediate window
' ===================================================================
Call QuickSort(True, Index, LBound(KeyStr), UBound(KeyStr), "StrDescend", KeyStr)
Debug.Print "Descending ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & InxCrnt, 3) & " ";
Next
Debug.Print
Debug.Print " Index ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & Index(InxCrnt), 3) & " ";
Next
Debug.Print
Debug.Print " Key ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & KeyStr(Index(InxCrnt)), 3) & " ";
Next
Debug.Print
Debug.Print
AlphabetSequence = Array("A", "E", "I", "O", "U", "B", "C", "D", "F", "G", "H", "J", "K", _
"L", "M", "N", "P", "Q", "R", "S", "T", "V", "W", "X", "Y", "Z")
' Sort KeyStr into vowels first then consonants and output to immediate window.
' The sequence vowels the consinants is defined by the array AlphabetSequence.
' ============================================================================
Call QuickSort(True, Index, LBound(KeyStr), UBound(KeyStr), "VowelFirst", KeyStr)
Debug.Print " Vowel 1st ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & InxCrnt, 3) & " ";
Next
Debug.Print
Debug.Print " Index ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & Index(InxCrnt), 3) & " ";
Next
Debug.Print
Debug.Print " Key ";
For InxCrnt = LBound(KeyStr) To UBound(KeyStr)
Debug.Print Right(" " & KeyStr(Index(InxCrnt)), 3) & " ";
Next
Debug.Print
' Create array of persons
' =======================
ReDim Person(0 To 5)
Set Person(0) = New CPersonData
Person(0).NameFamily = "Brown"
Person(0).NameGiven = "Adrian"
Person(0).ChildNameGiven = "George"
Person(0).ChildAge = "5"
Set Person(1) = New CPersonData
Person(1).NameFamily = "Green"
Person(1).NameGiven = "Barbara"
Person(1).ChildNameGiven = ""
Person(1).ChildAge = ""
Set Person(2) = New CPersonData
Person(2).NameFamily = "Smith"
Person(2).NameGiven = "Charles"
Person(2).ChildNameGiven = "Harriet:Ian:Jane"
Person(2).ChildAge = "4:7:11"
Set Person(3) = New CPersonData
Person(3).NameFamily = "Farmer"
Person(3).NameGiven = "Diana"
Person(3).ChildNameGiven = ""
Person(3).ChildAge = ""
Set Person(4) = New CPersonData
Person(4).NameFamily = "Roe"
Person(4).NameGiven = "Eric"
Person(4).ChildNameGiven = "Kenneth:Laura"
Person(4).ChildAge = "10:1"
Set Person(5) = New CPersonData
Person(5).NameFamily = "Walker"
Person(5).NameGiven = "Fawn"
Person(5).ChildNameGiven = ""
Person(5).ChildAge = ""
' Output Person array to Immediate window
' =======================================
Debug.Print
Debug.Print "Sequence within Person array"
For InxPerson = LBound(Person) To UBound(Person)
Debug.Print Person(InxPerson).NameGiven & " " & Person(InxPerson).NameFamily
ChildNamePart = Split(Person(InxPerson).ChildNameGiven, ":")
ChildAgePart = Split(Person(InxPerson).ChildAge, ":")
For InxChildCrnt = 0 To UBound(ChildNamePart)
If ChildNamePart(InxChildCrnt) <> "" Then
Debug.Print " " & ChildNamePart(InxChildCrnt) & " (" & _
ChildAgePart(InxChildCrnt) & ")"
End If
Next
Next
' Sort Person array into sequence defined by function "AscendAgeYoungestChild"
' ============================================================================
Call QuickSort(True, Index, LBound(Person), UBound(Person), "AscendAgeYoungestChild", Person)
' Output Person array in sequence specified by Index
' ==================================================
Debug.Print
Debug.Print "Persons without children first then descending order of youngest child"
For InxCrnt = LBound(Index) To UBound(Index)
InxPerson = Index(InxCrnt)
Debug.Print Person(InxPerson).NameGiven & " " & Person(InxPerson).NameFamily
ChildNamePart = Split(Person(InxPerson).ChildNameGiven, ":")
ChildAgePart = Split(Person(InxPerson).ChildAge, ":")
For InxChildCrnt = 0 To UBound(ChildNamePart)
If ChildNamePart(InxChildCrnt) <> "" Then
Debug.Print " " & ChildNamePart(InxChildCrnt) & " (" & _
ChildAgePart(InxChildCrnt) & ")"
End If
Next
Next
End Sub
Sub QuickSort(ByVal TopLevel As Boolean, ByRef Index() As Long, _
ByVal InxLow As Long, ByVal InxHigh As Long, _
QSequenceName As String, ParamArray Target() As Variant)
' * Original algorithm developed by C A R Hoare in 1960.
' * This implementation based on Pascal procedure published in second edition
' of Algorithms by Robert Sedgewick.
' * Converted to VBA by Tony Dallimore and amended to:
' * Sort Index array rather than Key array
' * Use function passed as parameter to determine if two elements are in
' the correct sequence to avoid hard-coding the required sequence into
' this routine.
' * Parameters:
' * TopLevel True for the outer call; False for inner calls
' Index is only initialised for an outer call
' * Index At start of outer call dimensioned to (InxLow To InxHigh)
' and initialised to InxLow, InxLow+1, InxLow+2, ... .
' On final exit, elements InxLow to InxHigh will define the
' sequence of elements InxLow to InxHigh of the Key array.
' * InxLow Identifies the first element of the Key array to be sorted
' * InxHigh Identifies the last element of the Key array to be sorted
' InxLow must be less than or equal to InxHigh
' If InxLow and InxHigh are set to the lower and upper bounds
' of the key array, the entire array will be sorted.
' * QSequenceName The name of the boolean function that determines if two
' elements of the Target array are in the required sequence. The
' function must return False if the elements are not in the
' correct sequence.
' Parameters are:
' * Target array
' * Index array
' * Index of first element within Index
' * Index of second element within Index
' * Target Target is a ParamArray. It will contain every parameter after
' QSwapName. There should only be one such parameter which is
' the Target array. Target is a zero-based array so Target(0)
' is the array whose required sequence is to be returned in Index.
' This routine does not know the nature of the Target array or
' the nature of the desired sequence.
' * The unmodified algorithm is recursive. It first partitions the elements of
' the Key array such that:
' * The element Key(X) is in its final place for some X.
' * All elements Key(InxLow) to Key(X-1) will come before Key(X) in the fully
' sorted array.
' * All elements Key(X+1) to Key(InxHigh) will come after Key(X) in the fully
' sorted array.
' It then calls itself twice: once for elements Key(InxLow) to Key(X-1) and once
' for elements Key(X+1) to Key(InxHigh).
' * In this implementation the Key array is replaced by a Target array. This routine
' does not know the type of the Target or what information within an element
' determines the desired sequence. On exit, Index will have been sorted so, for
' all X in the range InxLow to InxHight, Index(X) specifies the position of
' Target(Index(X))in the sequence specified by function QSequenceName.
Dim InxCrnt As Long
Dim InxHighTemp As Long
Dim InxLowTemp As Long
Dim InxPartition As Long
Dim InxTemp As Long
Dim CorrectSequence As Boolean
If TopLevel Then
' Only initialise Index for the outer call
' Size Index array to match InxLow to InxHigh
ReDim Index(InxLow To InxHigh)
' Initialise Index array
For InxCrnt = InxLow To InxHigh
Index(InxCrnt) = InxCrnt
Next
End If
' Initialise indices for partitioning
InxLowTemp = InxLow - 1
InxHighTemp = InxHigh
' My understanding is that the algorithm does not depend on which element of the
' Target array is the partitioning element. In this implementation it is element
' InxHigh.
InxPartition = InxHigh
Do While InxHighTemp > InxLowTemp
' Step InxLowTemp until Target element InxLowTemp is not to come before
' Target element InxPartition in the final sequence
Do While InxLowTemp < InxHigh
InxLowTemp = InxLowTemp + 1
If InxLowTemp = InxPartition Then Exit Do
CorrectSequence = Application.Run(QSequenceName, Target(0), Index, InxLowTemp, InxPartition)
If Not CorrectSequence Then Exit Do
Loop
' Reduce InxHighTemp until Target element InxHighTemp is not to come after
' Target element InxPartition in the final sequence
Do While InxHighTemp > InxLow
InxHighTemp = InxHighTemp - 1
'Debug.Assert InxHighTemp <> 0
CorrectSequence = Application.Run(QSequenceName, Target(0), Index, InxPartition, InxHighTemp)
If Not CorrectSequence Then Exit Do
Loop
' Swap position of InxLowTemp and InxHighTemp
InxTemp = Index(InxLowTemp)
Index(InxLowTemp) = Index(InxHighTemp)
Index(InxHighTemp) = InxTemp
Loop
' Final swap.
Index(InxHighTemp) = Index(InxLowTemp)
Index(InxLowTemp) = Index(InxPartition)
Index(InxHigh) = InxTemp
' Sort the two halves of the array unless they are less than two elements wide
If InxLowTemp > InxLow Then
Call QuickSort(False, Index, InxLow, InxLowTemp - 1, QSequenceName, Target(0))
End If
If InxLowTemp < InxHigh Then
Call QuickSort(False, Index, InxLowTemp + 1, InxHigh, QSequenceName, Target(0))
End If
End Sub
Function AscendAgeYoungestChild(Person() As CPersonData, Index() As Long, _
Inx1 As Long, Inx2 As Long) As Boolean
' * If Person(Index(Inx1)) has no children, return True
' * If Person(Index(Inx1)) has children but Person(Index(Inx2))
' does not, return False
' * If both Person(Index(Inx1)) and Person(Index(Inx2)) have children,
' return True if Person(Index(Inx1))'a youngest child is older than
' Person(Index(Inx2))'s youngest child
Dim ChildAgePart() As String
Dim InxChild As Long
Dim InxInx1 As Long
Dim InxInx2 As Long
Dim NumChildren1 As Long
Dim NumChildren2 As Long
Dim AgeYoungest1 As Long
Dim AgeYoungest2 As Long
InxInx1 = Index(Inx1)
InxInx2 = Index(Inx2)
If Person(InxInx1).ChildAge = "" Then
' Person(Index(Inx1)) has no children. If Person(Index(Inx2)) has children,
' Person(Index(Inx2)) is to come first in the required sequence. If
' Person(Index(Inx2)) has no children, Person(Index(Inx1)) and
' Person(Index(Inx2)) are "equal" and the current sequence is OK.
' Either way, return True
AscendAgeYoungestChild = True
Exit Function
End If
If Person(InxInx2).ChildAge = "" Then
' Person(Index(Inx1)) has children but Person(Index(Inx2)) doe not
AscendAgeYoungestChild = False
Exit Function
End If
' Both persons have children
' Find age of youngest child of Person(Index(Inx1))
ChildAgePart = Split(Person(InxInx1).ChildAge, ":")
AgeYoungest1 = Val(ChildAgePart(0))
For InxChild = 1 To UBound(ChildAgePart)
If AgeYoungest1 > Val(ChildAgePart(InxChild)) Then
AgeYoungest1 = Val(ChildAgePart(InxChild))
End If
Next
' Find age of youngest child of Person(Index(Inx2))
ChildAgePart = Split(Person(InxInx2).ChildAge, ":")
AgeYoungest2 = Val(ChildAgePart(0))
For InxChild = 1 To UBound(ChildAgePart)
If AgeYoungest2 > Val(ChildAgePart(InxChild)) Then
AgeYoungest2 = Val(ChildAgePart(InxChild))
End If
Next
If AgeYoungest1 > AgeYoungest2 Then
AscendAgeYoungestChild = True
Else
AscendAgeYoungestChild = False
End If
End Function
Function StrAscend(Target() As String, Index() As Long, Inx1 As Long, Inx2 As Long) As Boolean
' Return True if element Index(Inx1) is to come before element Index(Inx2)
' in the final sequence which is ascending alphanumeric
StrAscend = IIf(Target(Index(Inx1)) <= Target(Index(Inx2)), True, False)
End Function
Function StrDescend(Target() As String, Index() As Long, Inx1 As Long, Inx2 As Long) As Boolean
' Return True if element Index(Inx1) is to come before element Index(Inx2)
' in the final sequence which is descending alphanumeric
StrDescend = IIf(Target(Index(Inx1)) >= Target(Index(Inx2)), True, False)
End Function
Function VowelFirst(Target() As String, Index() As Long, Inx1 As Long, Inx2 As Long) As Boolean
' Return True if element Index(Inx1) is to come before element Index(Inx2)
' in the final sequence which is the sequence defined by AlphabetSequence
' which is vowels then consonants
Dim FirstLetter As String
Dim InxAlpha1 As Long
Dim InxAlpha2 As Long
' First first letter of Target(Inx1) in AlphabetSequence
FirstLetter = Left(Target(Index(Inx1)), 1)
For InxAlpha1 = LBound(AlphabetSequence) To UBound(AlphabetSequence)
If AlphabetSequence(InxAlpha1) = FirstLetter Then
Exit For
End If
Next
FirstLetter = Left(Target(Index(Inx2)), 1)
For InxAlpha2 = LBound(AlphabetSequence) To UBound(AlphabetSequence)
If AlphabetSequence(InxAlpha2) = FirstLetter Then
Exit For
End If
Next
If InxAlpha1 <= InxAlpha2 Then
VowelFirst = True
Else
VowelFirst = False
End If
End Function
运行宏Test并研究前三个排序序列的输出。我认为例子3最符合你的要求。您可以将seq_num列加载到数组中，并使用函数VowelFirst作为起点。
如果有必要，可以提出问题，但你从自己的学习中了解得越多，你就会发展得越快。