Sort Sheets Procedure

[Sandman]

I am very new to VBA programing and have purchased John Walkenbach's "Power
Programming With VBA" Book I am following along with a program to sort
wooksheets. I followed the instructions in the book but when I run it I get
a compile error, at the following location:

' Sort the array in ascending order
Call BubbleSort(SheetNames)

The error say "Type Mismatch: Array or User Defined type expected"

The (SheetName) is what is highlighted. it is identical to the code in the
book but I can't figure out what is wrong with it.

Any help would be appreciated.


Here is the entire code:

Sub SortSheets()

' This Routine sorts the sheets of the
' active workbook in ascending order.

Dim SheetNames() As String
Dim i As Integer
Dim SheetCount As Integer
Dim VisibleWins As Integer
Dim Item As Object
On Error Resume Next
SheetCount = ActiveWorkbook.Sheets.Count
If Err <> 0 Then Exit Sub ' No active workbook

' Check For protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " Is Protected.", _
vbCritical, "Cannot Sort Sheets."
Exit Sub
End If

' Disable Ctrl+Break
Application.EnableCancelKey = xlDisabled

' Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count

' Redimension the array
ReDim SheetNames(1 To SheetCount)

' Store a referance to the active sheet
Set OldActive = ActiveSheet

' Fill array with sheet names and hidden status
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i

' Sort the array in ascending order
Call BubbleSort(SheetNames)

' Turn off screen updating
Application.ScreenUpdating = False

' Move the sheets
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
ActiveWorkbook.Sheets(i)
Next i

' Reactivate the orignal active sheet
OldActive.Activate
End Sub
--





Darrin Sand
Project Manager
MEC Services Inc
Phone: 701-337-5404
Cell: 701-240-4000
email: (e-mail address removed)

 

[Bob Phillips]

What does your BubbleSort code look like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sandman]

Bob,

Here is the bubble code:

Sub BubbleSort(List())
' Sorts the list array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub

--





Darrin Sand
Project Manager
MEC Services Inc
Phone: 701-337-5404
Cell: 701-240-4000
email: (e-mail address removed)

 

[Chip Pearson]

Sandman,

Your BubbleSort is expecting an array of Variants, not an array
of Strings. Change either

Sub BubbleSort(List())
' to
Sub BubbleSort(List() As String)

Or

Dim SheetNames() As String
' to
Dim SheetNames() As Variant


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Norman Jones]

Hi Sandman,

Try Changing: Sub BubbleSort(List())

To: Sub BubbleSort(List)

 

[Sandman]

Thanks Guys I tried each of them and both will work

--





Darrin Sand
Project Manager
MEC Services Inc
Phone: 701-337-5404
Cell: 701-240-4000
email: (e-mail address removed)