• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Excel VBA Array help

I have an array in Excel that I currently have hard coded. I need to make this more dynamic in nature as the array size and value are not constant. This array consist of the names of worksheets in a workbook. The intent of the array is to set the order of the worksheets.

My current code looks something like this:

Dim arSheets() as Long
Dim intWSCount as Integer
dim intLastSheet as Integer ' keep track of which sheet in the array was sorted last.

ReDim arSheets(1 to intWSCount)

arSheets(1) = "The first sheet"       ' This text is the sheet name
arSheets(2) = "The second sheet"

' and so on through the total sheets in the workbook. Note that arSheets(0) is excluded 
' as the first sheet will remain first.

    intLastSheet = 1 ' set at 1 to start with array position 1.
    For I = 0 To UBound(arSheets)
        xlsTargetWorkbook.Sheets(arSheets(intLastSheet)).Move after:=xlsTargetWorkbook.Sheets(intLastSheet)
        intLastSheet = 1 + I
    Next I

Open in new window

The question that I have is how can I assign a sheet quickly to an array position without having to type "arSheets(1) = "The first sheet"  for each item. In my current code I have to renumber the array if I inserted a new sheet between the current arSheets(1) and arSheets(2) - meaning that arSheets(2) would become arSheets(3), and so on. This is tedious if there are a lot of elements in the array (the current worksheet has 38 sheets to be sorted.

This VBA code takes a bunch of CSV files and imports them into one workbook. Ultimately I want to display  a list of the sheets in a dialog box and allow the user to reorder them as they want (thinking of something like this: http://www.dailydoseofexcel.com/archives/2004/06/14/move-upmove-down-in-a-listbox/). Maybe it would be the shortest route to the desired goal to build this functionality first then order the sheets from the list order.

Any thoughts would be appreciated.
  • 2
3 Solutions
Hi ckelsoe,

in your problem array was not required. instead of array i have used listbox to list all the sheets and have provided the user the ability to move the sheet positions.
Now all you have to do is
1. add one more button in userform for OK
2. iterate through list of sheet names
3. then run your code for Move sheets.

i have attached the demofile.

I hope it helps.
Rahul Demo-2.xls Demo-2.xls
Here's your solution.  The app launches a userform loaded with the worksheets, using the UP/DOWN (reference your link) with minor enhancement.  Hit "Order Shts" to order the sheets.  You can keep manipulating till satisfied, at which you can hit CANCEL.  The button to start it off is on Sheet1, however, you can launch the sub "loadShowTabs()" to initiate the process.

Here's the code:

Sub loadShowTabs()
    Load UserForm1
End Sub


Private Sub cbOK_Click()
    Call orderSheets
    Call UserForm_Initialize
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Long

    Set wkb = ThisWorkbook

    For Each wks In wkb.Worksheets
        ListBox1.AddItem wks.Name
    Next wks

End Sub
Private Sub orderSheets()
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Long

    Set wkb = ThisWorkbook

    For i = 0 To ListBox1.ListCount - 1
        Set wks = wkb.Worksheets(ListBox1.List(i))
        wks.Move after:=wkb.Worksheets(wkb.Worksheets.Count)
    Next i
End Sub
Private Sub cbDown_Click()
    MoveItem 1
    ListBox1.ListIndex = ListBox1.ListIndex + 1

End Sub
Private Sub cbUp_Click()
    MoveItem -1
    ListBox1.ListIndex = ListBox1.ListIndex - 1

End Sub
Private Sub MoveItem(lOffset As Long)
    Dim aTemp() As String
    Dim i As Long
    With Me.ListBox1
        If .ListIndex > -1 Then
            ReDim aTemp(0 To .ColumnCount - 1)
            For i = 0 To .ColumnCount - 1
                aTemp(i) = .List(.ListIndex + lOffset, i)
                .List(.ListIndex + lOffset, i) = .List(.ListIndex, i)
                .List(.ListIndex, i) = aTemp(i)
            Next i
        End If
    End With
End Sub

Open in new window

The process for ordering the sheets doesn't require the creation of a special array - the sheets are already ordered in the ListBox1 object.  Hence, all we need to do is start at the top of the listbox and move each item to the end of the worksheet list, with the MOVE command.

See attached worksheet.


>>The question that I have is how can I assign a sheet quickly to an array position without having to type "arSheets(1) = "The first sheet"

My post above stands as the solution from which you can design your ultimate result.  I'd like to also address the specific question you had (noted above in this comment):

A followup item addressing your question.  It may not have been relevant in the solution, but its worth chatting about.

Please find a few examples on using variant objects for "quick loading" of arrays.  The first example is loading from a static string, in VBA.  The Second example just loads a worksheet's Column A with existing sheets in the workbook.  The final example (after having manually moved the rows around for a new sort order) uses variant data type to accept a load from the worksheet to populate the array.  Then, similar logic is used to resort the sheets.

Hopefully, you'll find these examples useful in future development...


You can create a variant object, tmpVar as Variant and assign it to a string comma separated list like this:

Dim tmpVar as Variant

     tmpVar = Split("Sheet1,Sheet2,The Third Sheet,FourthSheet,etc.",",") 'this "splits" the string, with comma separation and plugs the result into tmpVar

As a result:

     tmpVar(0) would equal "Sheet1"

See demo1() subroutine in the attached.

Another way to handle "loading the array" from sheet names in the order you desire might be to first, create a list in a worksheet, then manipulate that list and read it into the array...


Sub demo2_Initialize()
Dim wkb As Workbook
Dim wks As Worksheet
Dim outCursor As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Sheet1")
    Set outCursor = wks.Range("A1")
    For Each wks In wkb.Worksheets
        outCursor.Value = wks.Name
        Set outCursor = outCursor.Offset(1, 0)
    Next wks
End Sub

NOW - manually reorder the list, then

RUN THIS demo3_OrderSheets() subroutine, to load the results into an array, then order the sheets
Sub demo3_OrderSheets()
Dim wkb As Workbook
Dim wks As Worksheet
Dim moveSht As Worksheet
Dim myCell As Range
Dim tmpVar As Variant

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Sheet1")
    tmpVar = Application.Transpose(wks.Range("A1", wks.Range("A" & wks.Rows.Count).End(xlUp)))
    For i = LBound(tmpVar) To UBound(tmpVar)
        Set moveSht = wkb.Worksheets(tmpVar(i))
        moveSht.Move after:=wkb.Worksheets(wkb.Worksheets.Count)
    Next i
End Sub

Open in new window

See attached - the macro buttons and sheet lists are on Sheet1.  Play with it!


ckelsoeAuthor Commented:
Thank you both for two excellent ways to solve the problem that I needed solved. As I wrote the original question the implementation of the listbox object in my existing UI. I had intended on holding off on that due to the time I thought it would take to implement, however, I will probably spend less time and have a better solution by going ahead and implementing it.

Thanks again for the quick and knowledgeable responses.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now