Sort Sheets Procedure
 

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:

Sort Sheets Procedure
 

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" wrote in message
...

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:

Sort Sheets Procedure
 

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:
"Bob Phillips" wrote in message
...
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" wrote in message
...

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:

Sort Sheets Procedure
 

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


"Sandman" wrote in message
...
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:
"Bob Phillips" wrote in
message
...
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" wrote in message
...

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:

Sort Sheets Procedure
 

Hi Sandman,

Try Changing: Sub BubbleSort(List())

To: Sub BubbleSort(List)

---
Regards,
Norman

"Sandman" wrote in message
...
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:
"Bob Phillips" wrote in message
...
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" wrote in message
...

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:

Sort Sheets Procedure
 

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:
"Norman Jones" wrote in message
...
Hi Sandman,

Try Changing: Sub BubbleSort(List())

To: Sub BubbleSort(List)

---
Regards,
Norman

"Sandman" wrote in message
...
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:
"Bob Phillips" wrote in message
...
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" wrote in message
...

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: