In the last article, Understanding Excel Macros and VBA Code 1 – Automatic Index Sheet, we looked at adding a macro to a worksheet. In this Excel VBA code, we are going to add the macro to the workbook.   The macro we are going to look at will sort all of the worksheets in the current workbook. It can sort in both ascending and descending order. The macro will sort by number and or also alphabetically. This is ideal if you work with a large number of tabs or worksheets in excel documents and you need to put them in order.

But before we look at the next macro, let’s quickly introduce DIM. In the code we looked at last week you may remember seeing the following code:

Dim wSheet As Worksheet

Dim l As Long

l = 1

The DIM statement declares many different types of variables. These include whole numbers (byte, integer or long), floating-point numbers (single or double), string (alphanumeric), currency, date, Boolean and object. In the example above, Dim wSheet As Worksheet defines the worksheet an object, we have also defined I as a long interger, starting with 1.

Looking at the next code, the first think you need to do is copy the code from below. Then press Alt + F11 to open the VBA screen. On the left side where the vertical pane is located, find your Excel file; it will be called VBAProject (YOUR FILE’S NAME HERE) and click this. Open the Excel objects file and at the bottom of this you should see ‘ThisWorkbook. Select this and then paste the code. You can now save and close your VB screen. To run the macro, go to the developer’s ribbon, select macro and select run.

In this code, we have also set up a question in a message box. Dependent on the answer given, the macro, with the use of IF statements, will then sort by ascending order or descending order.

Sub Sort_Worksheets()

‘The Excel Club com macro example2

Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult

   iAnswer = MsgBox(“Sort Sheets in Ascending Order?” & Chr(10) _
     & “Clicking No will sort in Descending Order”, _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, “Sort Worksheets”)
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count – 1

         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If

         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)

            End If
         End If

      Next j
   Next i

End Sub

This code may look difficult for the novice, however I mentioned before that the best way to learn code is to explore examples. The next time you see this message box, it will be so much more familiar to you. And the aim is to give some codes that will be useful as well as a learning tool.