Excel VBA Array help

Posted on 2011-10-10
Last Modified: 2012-05-12
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: 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.
Question by:ckelsoe
    LVL 3

    Accepted Solution

    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
    LVL 41

    Assisted Solution

    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.


    LVL 41

    Assisted Solution

    >>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!



    Author Closing Comment

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now