Some vba functions to automate some tasks in Excel

Some examples of simple VBA code, to help you manage your Excel worsheets. With VBA you can make a code of a few lines helping sorting the worksheets of a big Excel workbook in alphabetical order. Or it can also help by preparing an index of the worksheets of this workbook. Can be useful to prepare a report for example. A must have for your VBA toolbox.

' ***************************************************
' Procedure : wsh_Sort
' Description : Automaticly sort sheets from a given Excel workbook
' Parameters 1 
'   wb : the name of the  Excelworkbook
'  
'  version:0.01
' ***************************************************
Sub wsh_Sort(wb)
On Error GoTo emng_wsh_Sort
    Workbooks(wb).Activate
    Dim wsC As Worksheet
    Dim bCont As Boolean
    Dim sPrev
    bCont = True
    While bCont = True
        sPrev = ""
        bCont = False
        For Each wsC In ActiveWorkbook.Sheets
            If sPrev = "" Then
                sPrev = wsC.Name
            Else
                If sPrev > wsC.Name Then
                    Worksheets(wsC.Name).Move Before:=Worksheets(sPrev)
                    bCont = True
                Else
                    sPrev = wsC.Name
                End If
            End If
        Next wsC
    Wend
Exit Sub
emng_wsh_Sort:
    MsgBox "Error: " + Err.Description, vbOKOnly + vbExclamation, "Error wsh_Sort function"
End Sub
*********************END***************************

Example given : the sheets of the workbook appears in the following order :

After the applying the wsh_sort VBA macro on this workbook, here is the result :

' ***************************************************
' Procedure : wsh_Name2Array
' Description : Automaticly store the names of all the sheets in an array.
' Parameters 1 
'   wb : the name of the  Excelworkbook
'  
'  version:0.01
' ***************************************************
 Function wsh_Name2Array(wb)
On Error GoTo emng_wsh_Name2Array
    Dim tabl As Variant
    Dim wsC As Worksheet
    Dim iEnum As Integer
    Workbooks(wb).Activate
    ReDim tabl(Workbooks(wb).Sheets.Count)
    iEnum = 0
        For Each wsC In ActiveWorkbook.Sheets
          tabl(iEnum) = wsC.Name
          iEnum = iEnum + 1
          Next wsC
    wsh_Name2Array = tabl
    Exit Function
emng_wsh_Name2Array:
    MsgBox "Error: " + Err.Description, vbOKOnly + vbExclamation, "Error wsh_Name2Array function"
End Function

  To help us show you how this function works, we'll use a custom realce.net function designed to parse an Excel Array into a string array_show, see VBA library for array management.

Applying this function to a workbook nammed WB1.xls with the following sheets :

with the following code :

   MsgBox array_show(wsh_Name2Array("WB1.xls"))

The execution of this code will display a dialog message box showing the following text :

Array(A, B, C, D, E, F, G,H)