|
Call a function within Excel VBA --- need help can't figure out syntax correctly
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 |
Call a function within Excel VBA --- need help can't figure out syntax correctly
On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote:
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 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... The SortWorksheetsByNameArray is declared as a Public Function It is in a different workbook module however. The error I get is type mismatch. |
Call a function within Excel VBA --- need help can't figure out syntax correctly
on 2/1/2012, supposed :
On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote: 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 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... The SortWorksheetsByNameArray is declared as a Public Function It is in a different workbook module however. The error I get is type mismatch. 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Call a function within Excel VBA --- need help can't figure out syntax correctly
On Wed, 01 Feb 2012 10:56:17 -0500, GS wrote:
on 2/1/2012, supposed : On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote: 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 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... The SortWorksheetsByNameArray is declared as a Public Function It is in a different workbook module however. The error I get is type mismatch. 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. 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 befo=WB.Worksheets(Arr(1)) For N = LBound(NameArray) + 1 To UBound(NameArray) - 1 WB.Worksheets(NameArray(N)).Move befo=WB.Worksheets(NameArray(N + 1)) Next N SortWorksheetsByNameArray = True End Function |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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 On Wed, 01 Feb 2012 13:41:05 -0500, GS wrote: 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... |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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)? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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. On Wed, 01 Feb 2012 14:56:48 -0500, GS wrote: 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)? |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Call a function within Excel VBA --- need help can't figure out syntax correctly
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 On Thu, 02 Feb 2012 09:20:39 -0500, GS wrote: 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. |
Call a function within Excel VBA --- need help can't figure out syntax correctly
I'm glad to see you got it working! Thanks for letting me know. Always
glad to be of help... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| All times are GMT +1. The time now is 08:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com