Call a function within Excel VBA --- need help can't figure out syntax correctly

W

wkwells

I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub
 
G

GS

(e-mail address removed) laid this down on his screen :
I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub
Click to expand...

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...
 
W

wkwells

(e-mail address removed) laid this down on his screen :

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...
Click to expand...


The SortWorksheetsByNameArray is declared as a Public Function

It is in a different workbook module however.

The error I get is type mismatch.
 
G

GS

The SortWorksheetsByNameArray is declared as a Public Function

It is in a different workbook module however.

The error I get is type mismatch.
Click to expand...

You may need to ref the VBA project the function is in. It would be
easier if you copy it into a standard module in your project.

Also, post the function's declaration so we can see what it needs for
Type of args being passed in.
 
W

wkwells

You may need to ref the VBA project the function is in. It would be
easier if you copy it into a standard module in your project.

Also, post the function's declaration so we can see what it needs for
Type of args being passed in.
Click to expand...

I moved this to a module in the workbook it would be used.

I have a vertical list on a worksheet which indicates the order I want
the worksheets ( tabs ) sorted in the workbook.

Thats why I have NameArray = Range("mysheets").Value, this part
works, as I can scroll through the text of the list with
'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

The problem is I can't seem to call the function (code below) from
within VBA to sort the Sheets (tabs)

Appreciate all the help.

Public Function SortWorksheetsByNameArray(NameArray() As Variant, _
ByRef ErrorText As String, Optional WhatWorkbook As Workbook) As
Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WorksheetSortByArray
' This procedure sorts the worksheets named in NameArray to the order
in' which they appear in NameArray. The adjacent elements in NameArray
need
' not be adjacent sheets, but the collection of all sheets named in
' NameArray must form a set of adjacent sheets. If successful, returns
' True and ErrorText is vbNullString. If failure, returns False and
' ErrorText contains reason for failure. WhatWorkbook specifies the
' workbook containing the sheets to sort. If omitted, the
ActiveWorkbook
' is used.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Arr() As Long
Dim N As Long
Dim M As Long
Dim L As Long
Dim WB As Workbook

If WhatWorkbook Is Nothing Then
Set WB = ActiveWorkbook
Else
Set WB = WhatWorkbook
End If

ErrorText = vbNullString

'''''''''''''''''''''''''''''''''''''''''''''''
' The NameArray need not contain all of the
' worksheets in the workbook, but the sheets
' that it does name together must form a group of
' adjacent sheets. Sheets named in NameArray
' need not be adjacent in the NameArray, only
' that when all sheet taken together, they form an
' adjacent group of sheets
'''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(LBound(NameArray) To UBound(NameArray))
'On Error Resume Next
For N = LBound(NameArray) To UBound(NameArray)
'''''''''''''''''''''''''''''''''''''''
' Ensure all sheets in name array exist
'''''''''''''''''''''''''''''''''''''''
Err.Clear
M = Len(WB.Worksheets(NameArray(N)).Name) '<<<
If Err.Number <> 0 Then
ErrorText = "Worksheet does not exist."
SortWorksheetsByNameArray = False
Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Put the index value of the sheet into Arr. Ensure there
' are no duplicates. If Arr(N) is not zero, we've already
' loaded that element of Arr and thus have duplicate sheet
' names.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Arr(N) > 0 Then
ErrorText = "Duplicate worksheet name in NameArray."
SortWorksheetsByNameArray = False
Exit Function
End If

Arr(N) = Worksheets(NameArray(N)).Index
Next N

'''''''''''''''''''''''''''''''''''''''
' Sort the sheet indexes. We don't use
' these for the sorting order, but we
' do use them to ensure that the group
' of sheets passed in NameArray are
' together contiguous.
'''''''''''''''''''''''''''''''''''''''
For M = LBound(Arr) To UBound(Arr)
For N = M To UBound(Arr)
If Arr(N) < Arr(M) Then
L = Arr(N)
Arr(N) = Arr(M)
Arr(M) = L
End If
Next N
Next M
''''''''''''''''''''''''''''''''''''''''
' Now that Arr is sorted ascending, ensure
' that the elements are in order differing
' by exactly 1. Otherwise, sheet are not
' adjacent.
'''''''''''''''''''''''''''''''''''''''''
If ArrayElementsInOrder(Arr:=Arr, Descending:=False, Diff:=1) = False
Then
ErrorText = "Specified sheets are not adjacent."
SortWorksheetsByNameArray = False
Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Now, do the actual move of the sheets.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo 0
WB.Worksheets(NameArray(LBound(NameArray))).Move
before:=WB.Worksheets(Arr(1))
For N = LBound(NameArray) + 1 To UBound(NameArray) - 1
WB.Worksheets(NameArray(N)).Move before:=WB.Worksheets(NameArray(N
+ 1))
Next N

SortWorksheetsByNameArray = True


End Function
 
G

GS

Thanks for posting back!
There's good reasons for an error being raised...

Firstly, your call is missing args needed by the function. It takes 3
args, the first 2 of those are compulsory while the 3rd is optional.

Secondly, the array being passed to the function needs to be 1D. You
are trying to pass a 2D array. You need to put the list into a temp
array BEFORE passing it to the function.
--

As for the function structure itself, IMO it seems a bit
'long-in-tooth' for the task at hand. I'd certainly want to revise this
to something more efficient for the task. If all you're doing is
reordering specific sheets to be adjacent to each other as per the same
order they appear in NameArray, only one loop is necessary to
accomplish this (even if the first sheet in the list stays put and the
remaining sheets stack up beside it). Do you mind telling me where you
got this function? (It's 'style' looks vaguely familiar)

I'll try to come up with something that will work better for you in
your scenario, and post back...
 
W

wkwells

Thats exactly what I am trying to do

I have a Workbook with maybe 40 sheets, the first sheet in the
Workbook has a list of the worksheets, I have named this Range
MySheets. I want to sort the SHEET TABS in the same order as this
Range.

Sub TestArray()
NameArray = Range("MySheets").Value
'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next
End Sub

I know this works to create the Array

My Problem is transferring this Array Information to the
SortWorksheetsByNameArray Function

Appreciate the help
 
G

GS

So then, all the sheetnames in the list are to follow the first sheet
(which contains the list), and any sheets not in the list remain in
their present order AFTER the reordered sheets?

Also, I assume the named range for the list has global scope (workbook
level as apposed to sheet level)?
 
W

wkwells

You are correct, the Cover Sheet will always be at the front of the
Workbook and never move. The List (Range) does not reference itself.

However, as a caviat, all Names in the Range may not actually be a
Sheet. Thus, will need a test as to if the Sheet(s) exist or not.

Not sure if it matters, but the Workbook does contain hidden sheets,
to the left of the Cover Sheet.
 
G

GS

You need to put the name of the sheet containing the list in the 1st
position so it can be used by the macro for where to position the sheet
names that follow. (If you don't want the list sheet's name visible
then just hide its row, but make sure it's the 1st name in the defined
range)

I tried to make the following code self-explanatory so it would be easy
to understand.

'Function bSheetExists' is a self-contained reusable function you can
call at anytime from any VBA procedure whenever you need to check for
this.

Sub Test_ReorderSheetTabs shows the two different ways that you can use
'Function ReorderSheetTabs'.


The following code should be placed in a standard module...

Option Explicit

Public Function ReorderSheetTabs(ByVal WksNames As String, _
Optional IsRange As Boolean = True) As Boolean
' Reorders the sheet tabs as per the order listed in WksNames.
' Tabs are ordered left to right after the 1st sheet in WksNames.
' ********************************************
' * The 1st sheet name in the list should be *
' * positioned BEFORE running this procedure *
' ********************************************
'
' Arguments: WksNames: String; Required
' A range address (by default) where the names are
' stored in the ActiveWorkbook.
' This could also be a comma delimited list.
'
' IsRange: Boolean; Optional
' Assumes WksNames is a range address by default.
' Set to FALSE if WksNames is a delimited string.
'
' Returns: TRUE on success (if no errors). FALSE otherwise
'
Dim vNames As Variant, i As Long, sOrder As String

On Error GoTo errexit
If IsRange Then
vNames = Range(WksNames)
For i = 1 To UBound(vNames)
If bSheetExists(vNames(i, 1)) Then _
sOrder = sOrder & "," & vNames(i, 1)
Next 'i
sOrder = Mid$(sOrder, 2)
Else '//it's a delimited list
sOrder = WksNames
End If 'IsRange

vNames = Split(sOrder, ",")
For i = 1 To UBound(vNames)
If bSheetExists(vNames(i)) Then _
Sheets(vNames(i)).Move After:=Sheets(vNames(i - 1))
Next 'i
errexit:
ReorderSheetTabs = (Err = 0)
End Function

Function bSheetExists(ByVal WksName As String) As Boolean
' Checks if a specified worksheet exists in the ActiveWorkbook.
' Arguments: WksName The name of the worksheet
' Returns: TRUE if the sheet exists
Dim X As Worksheet
On Error Resume Next: Set X = Sheets(WksName)
bSheetExists = (Err = 0)
End Function

Sub Test_ReorderSheetTabs()
Dim sMsg As String

'If storing this sheetnames in a range, pass its address
If ReorderSheetTabs(Range("MySheets").Address) Then _
sMsg = "Success!" Else sMsg = "Failure!!"

'If storing the sheetnames in a delimited list...
Dim vList As Variant, i As Long, c As Range
For Each c In Range("MySheets")
vList = vList & "," & c.Text
Next 'c
If ReorderSheetTabs(Mid$(vList, 2), False) Then _
sMsg = "Success!" Else sMsg = "Failure!!"

MsgBox sMsg
End Sub

**Enhanced function to test if a sheet exists in a specified
workbook...

Function WorksheetExists(WSName As String, _
Optional wb As Excel.Workbook = Nothing) As Boolean
' adapted from an example by Chip Pearson
On Error Resume Next
WorksheetExists = (Len(IIf(wb Is Nothing, ThisWorkbook,
wb).Worksheets(WSName).name) > 0)
End Function
 
W

wkwells

I must be doing something wrong, I had to change you code slightly
under Sub Test_ReorderSheet Tabs()
" If ReorderSheetTabs(Range("MySheets").Address) Then _" to replace
"MySheets" with "WksNames" in order to get the code to run without an
error.

However, althought the code ran, I received a failure message and none
of the sheets actually moved within my workbook.

I did place the sheet with the WksNames as the first sheet in the
workbook. When I ran the Test Reorder code, I could see each code
flipping through all the sheets, it just didn't move them.

Again, I do appreciate the help.
 
W

wkwells

Just wondering if I am not calling on the function properly from
within the VBA code?
 
G

GS

The Test_ sub show exactly how to call the code when passing a range
address OR a delimited string. Use only one method. Once the 1st method
has run the sheets should be reordered as per your list, thus the 2nd
method wouldn't make any changes to that reorder unless you manually
reposition the list sheet and run either method again.

You told me the name of the list range was "MySheets" and so is what I
used in my tests. All worked exactly as expected for me and so YES, you
must be doing something wrong.
 
W

wkwells

Thank you your comment made perfect sense now, I seperated the code
out and it worked

Thank you so much, this is awesome.

I really appreciate your time and effort.

William Wells
 
G

GS

I'm glad to see you got it working! Thanks for letting me know. Always
glad to be of help...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to SQL Stored Procedure function 0
Arrays and scattered cell values or another way? 16
Can't figure out why Macro wont work???? 0
Which Excel event to call when opening a .txt file 1
excel function help 4
How can I output VBA function results (array's for example) to spreadsheet 2
How to pass a dynamic array FROM a function TO a sub 0
help! subscript out of range error when trying to return multiple values from function (as variant a 2

Top