How to print the sorted array in word using VBA?

Question

I have a two Input data of Year and Name in separate two arrays. I need to sort both the array values first i need to sort it chronologically(Year) and then if year information repeats it will sort the Array Alphabetically.

As for as I complete the sorting for both year and then name. Using Wordbasic.sortarray command

Input: (Before sorting)

SDF 1997
ELS 1986
PJK 1983
WKL 1995
EFD 1986

Output: (After sorting)

PJK 1983
EFD 1986
ELS 1986
WKL 1995
SDF 1997

If I print it in word it printed like this:

PJK 1983, ELS 1986, EFD 1986, WKL 1995, SDF 1997.

Here is my code for Printing the data. Would anyone please look into this and guide me where did i made mistake?

WordBasic.sortarray SortyearArray()

Code:

Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim N As Integer
Dim Counter As Integer
COUNTER1 = 1

i1 = 1
J1 = 5

For I = 0 To UBound(SortyearArray())
    Counter = 1
    For J = I + 1 To UBound(SortyearArray())
        If SortyearArray(I) = SortyearArray(J) Then
            Counter = Counter + 1
            MsgBox (Counter)
        End If
        COUNTER1 = Counter + COUNTER1
    Next J
    If Counter = 1 Then
        For N = i1 To J1
            If SortyearArray(I) = Year(N) Then
                Selection.TypeText Text:="(" & AuthorName(N) & Year(N) & ")"
            End If
        Next N
    End If
Next I

Will your Array which contains "SDF", "ELS" have duplicates like the 2nd array which has duplicate years? — Siddharth Rout
– Siddharth Rout, Commented Jun 13, 2012 at 9:32
ELS and EFD have the same year. since EFD is the duplicate value of ELS. I need to Print it like this PJK 1983, EFD 1986, ELS 1986, WKL 1995, SDF 1997. — DeeGo
– DeeGo, Commented Jun 13, 2012 at 9:40
I understand that. What I mean is will the 1st array have duplicates? i.e will there be two "SDF" in the 1st array? — Siddharth Rout
– Siddharth Rout, Commented Jun 13, 2012 at 9:41

Cylian · Accepted Answer · 2012-06-13 14:50:58Z

2

The input

SDF 1997
ELS 1986
PJK 1983
WKL 1995
EFD 1986

Core functions:

    Public Function QuickSort(ByRef array2check() As String, min As Long, max As Long) As Boolean
Dim lo As Long, hi As Long
Dim lo0 As Long, hi0 As Long
Dim midPos As String

    lo = min: hi = max
    lo0 = lo: hi0 = hi
    midPos = array2check((lo0 + hi0) / 2)
    DoEvents
    While (lo <= hi)
        While ((lo < hi0) And (array2check(lo) < midPos))
            lo = lo + 1
        Wend
        While ((hi > lo0) And (array2check(hi) > midPos))
            hi = hi - 1
        Wend
        If lo <= hi Then
            Call swap(array2check, lo, hi)
            lo = lo + 1
            hi = hi - 1
        End If
    DoEvents
    Wend

    If lo0 < hi Then Call QuickSort(array2check, lo0, hi)
    If lo < hi0 Then Call QuickSort(array2check, lo, hi0)

    QuickSort = True

End Function

Private Sub swap(arr() As String, idx1 As Long, idx2 As Long)
Dim tmp As String
    tmp = arr(idx1)
    arr(idx1) = arr(idx2)
    arr(idx2) = tmp
End Sub

The Sample tester

Public Sub sample_test()
    Dim test_arr() As String
    test_arr = Split("SDF 1997" & vbCrLf & "ELS 1986" & vbCrLf & "PJK 1983" & vbCrLf & "WKL 1995" & vbCrLf & "EFD 1986", vbCrLf)
    If QuickSort(test_arr, LBound(test_arr), UBound(test_arr)) = True Then
        'Debug.Print Join(test_arr, vbCrLf)
        MsgBox Join(test_arr, vbCrLf)
    End If
End Sub

The Result

EFD 1986
ELS 1986
PJK 1983
SDF 1997
WKL 1995

Hope this helps.

edited Jun 13, 2012 at 14:50

answered Jun 13, 2012 at 10:00

Cylian

11.2k4 gold badges46 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cylian Over a year ago

@Jean-FrançoisCorbett:If array is quite big then this would never hang the user machine.

Jean-François Corbett Over a year ago

Sure, but I've never seen such a liberal use of DoEvents at every turn... Especially considering that DoEvents itself costs time to run.

Cylian Over a year ago

It might be a big problem during debugging in presence with an Timer control present. I think you're right. And I've updated my answer.

Collectives™ on Stack Overflow

How to print the sorted array in word using VBA?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related