OzGrid

This will return ALL built in CommandBars and ALL custom CommandBars that are within the open Workbook.  There are 85 built in CommandBars in Excel.  and 845 associated level 1 Controls.  How do I know this, I counted them! Not really :o)  Included with this lesson is some code I have written that will list all CommandBars and their associated level 1 Controls.  As you learn more about VBA for Excel and you become comfortable with it, you will no doubt find yourself wanting to do more than just automate common tasks within Excel.  You will possibly end up wanting to create your own custom CommandBar and/or modifying Excels built in CommandBars.  To do this you will need to gain access to them and their Controls and to do this you will need to know their names or captions.  There are two ways to access CommandBars and their Controls,  one is to use the Index number and the other is to use the name or caption.  I strongly recommend using the latter (name or caption) method as you will be able to tell at a glance which CommandBar or Control you are dealing with.

As CommandBars represent a Collection of the CommandBar Object and CommandBars are a member of the Application we must go through the correct order to gain access.  So if we want to hide a particular CommandBar we would use the code:

---

Sub HideACommandBar()
    Application.CommandBars("Formatting").Visible = True
End Sub

---

As you can see this will simply show the Formatting CommandBar.  We have told Excel we want a Object member of the CommandBars Collection and that member is called "Formatting".  We have then set it's Visible Property to True.  the Visible property returns a Boolean ie, either True or False.  So having gained access to the "Formatting" CommandBar we can now also gain access to any one of it's Controls.  To do this we would use:

---

Sub DisableACommandBarsControl()
    Application.CommandBars("Formatting").Controls("&Fill Color").Enabled =False
End Sub

---

This would grey out (disable) the "Fill Color" icon on the "Formatting" Commandbar.  The use of the "&" before the Control names simply represents the underline that is used on Excel Toolbars, eg File would be expressed as ("&File").

Now if we are dealing with the "Worksheet Menu Bar" Excels main CommandBar we not only need to gain access to the first level Control, but also it's second level and third level.  To do this we would use:

---

Sub AccessMenuBarLevel2()
    Application.CommandBars("Worksheet Menu Bar").Controls _
                       ("&Insert").Controls("&Name").Enabled = False
End Sub

---

From here we can also go to the last level, to do this we would use:

---

Sub AccessMenuBarLevel3()
    Application.CommandBars("Worksheet Menu Bar").Controls _
                       ("&Insert").Controls("&Name").  _
                       Controls("&Define...").Enabled = False
End Sub

---

So as you can see, once we know the names of the CommandBar and It's Controls caption we are able to manipulate it in many ways.

Workbook

As with the CommandBar Object, to gain access to a Workbook Object we go through the Workbooks Collection.  some of the most common uses of the Workbook Object are Opening, Saving, Activating and Closing.  Lets look at each of these.

Opening

To open a Workbook we must tell Excel it's name and if we are dealing with more than one directory, it's file path.  So to open a Workbook that is on the same drive in the same folder we would use:

---

Sub OpenAWorkbook()
    Workbooks.Open ("Book1.xls")
End Sub

---

The "Open" Method also takes some arguments all of which are optional.  (An argument is a constant, variable, or expression passed to a procedure).  The most common one is the "UpdateLinks"

UpdateLinks tells Excel how to deal with a file we are opening that contains links.  If the argument is omitted, the user is asked (via a message box)  how links should be updated.

The table below is from the VBE help in Excel

Value     Meaning
0           Doesn't update any references

1           Updates external references but not remote references

2           Updates remote references but not external references

3           Updates both remote and external references

So to open a file and NOT update links we would use:

---

Sub OpenAWorkbookWithLinksWithoutUpdating()
    Workbooks.Open "Book1.xls", UpDatelinks:=0
End Sub

---

Saving

With this Method we can Save a Workbook as it's existing name or as another name and path.  We can trick Excel into thinking a Workbook is already had it's changes Saved.  This may also be a good time to introduce the two methods available to refer to the active Workbook.  The first one is:

---

Sub SaveActiveWorkbook()
    ActiveWorkbook.Save
End Sub

---

The second is:

---

Sub SaveThisWorkbook()
    ThisWorkbook.Save
End Sub

---

Both of these Methods will Save the a Workbook as it's existing name.  There is one small (which could be huge) difference with these two methods and that is:

1. "ActiveWorkbook" always refers to the Workbook that happens to be active at the time of running the code.

2. While "ThisWorkbook" always refers to the Workbook that houses the code, regardless of which workbook happens to be the Active Workbook.

Each Method is good in that they are generic (we don't need to know the name of the Workbook).  But use them in their wrong context and you could end up accessing the wrong Workbook.  I generally use "ThisWorkbook" with the exception of  the Workbook being an Add-in.   An Add-in is a Workbook that has been saved as such ( *.xla).  Once it has been saved it will always open as a Hidden Workbook.

As I motioned above we can trick Excel into thinking any changes to a Workbook have been saved, to do this we would use:

---

Sub TrickExcelToThinkWorkbookIsSaved()
    ThisWorkbook.Saved = True
End Sub

---

Run this code immediately prior to closing a Workbook that has had changes and Excel will think the Workbook has already been saved and close without saving.

Activating

While we can (in theory) have an infinite number of Workbooks open at any one time we can only ever have one of them Active at any one time.   At times we may need to Activate another Workbook so we can do something with it via VBA.  If we try to Access another Workbooks Objects etc while it is NOT active we will encounter a Run time error! We can do this easily if we know it's name like this:

---

Sub ActivateAnotherWorkbookViaName()
    Workbooks("Book2").Activate
End Sub

---

Problem is we may not always know the name of any other open Workbook so we need to either find out it's name or use it's Index number, like this:

---

Sub ActivateAnotherWorkbookViaIndex()
    Workbooks(3).Activate
End Sub

---

An important note here is, the index number is the same as the order in which the workbooks were opened.  Workbooks(1) is the first workbook opened, Workbooks(2) is the second Workbook opened and so on...  Activating a Workbook won't change its index number.  All open workbooks are included in the index count, even if they are hidden.

Closing

As with Open Method of a Workbook the Close Method also takes arguments, which again are all optional.  To close the open Workbook you would use:

---

Sub CloseThisWorkbook()
    ThisWorkbook.Close
End Sub

---

This would Close the Workbook and Prompt the user to save any changes.

---

Sub CloseThisWorkbookAndSave()
    ThisWorkbook.Close SaveChanges:=True
End Sub

---

This would Save the Workbook without prompting and then Close.  Of course using "False" in place of "True" would Close the Workbook and not save any changes.

Worksheet

The Worksheet Object will most likely be an Object that you will encounter often.  You will need to refer to it to gain access to it's Objects, with the exception of the ActiveSheet.  If the Worksheet is not identified in your code Excel will by default assume the Active sheet.  As with the Workbooks collection we can refer to a Worksheet via its Name (tab name) or its Index number.  One thing you should be aware of is we cannot access a Chart Sheet via the Worksheets Collection Object as a Chart sheet is not a Worksheet and as such not a member of the Worksheets Collection.  To access a Chart sheet we would use the Sheets Collection Object.  The Sheets Collection Object represents ALL sheets in the ActiveWorkbook or the specified Workbook (including Chart Sheets).

To use its Name you would use:

---

Sub UsingTabName()
    Worksheets("Sheet2").Activate
End Sub

---

Or

---

Sub UsingTabName2()
    Sheets("Sheet2").Activate
End Sub

---

To use its Index number you would use:

---

Sub UsingIndexNumber()
    Worksheets(2).Activate
End Sub

---

Or

---

Sub UsingIndexNumber2()
    Sheets(2).Activate
End Sub

---

This would activate the second Worksheet in a Workbook.  The Index numbers run from left to right.

Personally I use the Sheets Object most of the time not only is it easier to type but it also allows us to use much better method of accessing a Worksheet and that is via its CodeName.  I wont go into too much detail on the Sheets Codename at this point as we will cover it in more detail in your Debugging code lesson later.  What I will say is it can be found in the Properties Window of the Sheet object and also in the Project Explorer.  It is the

name NOT in the parenthesis.  So for the two examples above you could use:

 

---

Sub UsingCodeName()
    Sheet2.Activate
End Sub

---

I will stress now that using the CodeName Method is one of those habits you should form early.  When we do your Debugging lesson this will become apparent.

Range

Without doubt the Range Objects is by far the most used and important aspects of not only VBA for Excel but for Excel itself.  As Excel is primarily a spreadsheet application its whole concept depends on the Range.  To put it in layman's terms, Excel is a load of little boxes (116777216 per Worksheet).  We can access any one of these Cells (little boxes) by nominating its unique address.  We do this by using a grid pattern and this is very similar to finding a Street on a road map it is that simple! When referring to a Cell or a Range of Cells within Excel we use either the R1C1 method, where R=Row and Column=Column or the A1 method.  The A1 is the preferred Method so that is what we will use.  Now as I have said, Excel has 116777216 Cells on each Worksheet these are represented by 256 Columns and 65536 Rows remember these 2 numbers as they come in very handy especially the Row number.  The amount of Columns and Rows on a worksheet are fixed (and as a consequence so are the cells), we cannot add more and we cannot remove any.  As we have far more Rows than columns we should always attempt to work with this in mind.  Excel is geared up for tables to be set up with Columns as headings and Rows for data storage.

When we are working with the Range Object we should try to avoid ever having to Select it you will find we can achieve this aim 99% of the time.  The two most common ways to refer to a Cell are:

Cells(1, 1).Select

Or

Range("A1").Select

Yes , I know I used the word Select :o)

I much prefer to use the latter as I find it much simpler to use a letter for a Column and number for a row.  I guess most people do, which is why Excel introduced the A1 style reference.  There also another way to refer to a cell and is the way we should use whenever possible, this way is to use its name (if it has one).  If we had a Cell or Range of Cells named "MyRange" we would use:

Range("MyRange").Select

I will stress here again that form the habit early of naming Ranges in Excel.  This is very important in VBA as users are inserting Rows, Cells and Columns or cutting and pasting may have you referring to the wrong cell.  Once a Cell or Range of Cells are named you wont encounter this potential problem as the Name will move with the Range.  Don't misunderstand me here, as I'm not suggesting for a minute you name all Cells within a Workbook but DO name Columns and Rows that you need to remain constant then you can (with reasonable confidence) refer to a particular Cell within the Named Column or Row and access the intended one.  Imagine we have Named Row two "MyRange" and this Row will contain all our Headings we can refer to the second Cell in this Range like this:

 

---

Sub ReferToANamedRangesCellMethod1()
    Range("MyRange").Range("B1").Select
End Sub

---

Or

---

Sub ReferToANamedRangesCellMethod2()
    Range("MyRange").Cells(1, 2).Select
End Sub

---

So if a not so helpful user comes along and inserts a Row above our named Row we wont be effected.

You will notice that we are using "Range("B1")" and "Cells(1,2)" to refer to a Range in Row 2 or maybe even Row 500.   It doesn't matter where our Named Range "MyRange" ends up we would always use the same method to access the second cell within the Range.  This is because using the Range (or Cells) Method after the Range Object makes it Relative.  Perhaps the easiest way to see this is to Record a Macro using the Relative button doing the following:

Selecting the cell immediately to the right of the ActiveCell.

No matter which Cell is the ActiveCell you will always end up with the code:

ActiveCell.Offset(0, 1).Range("A1").Select

Now as with most Recorded code we can edit it, in this case we can use:

ActiveCell.Offset(0, 1).Select

Or

ActiveCell.Range("B1").Select

Or

ActiveCell.Cells(1,2).Select

So when working with the Range Object we have many options open to us.

A common problem faced in VBA for Excel when working with Ranges, is how to find the first and last cell in a range.  This is where the number of Rows in an Excel Worksheet comes in very handy.  Let's assume you want to find the very last used cell in Column A.  In this case we would use:

 

---

Sub LastUsedCellInColumnA()
    Range("A65536").End(xlUp).Select
End Sub

---

To find the first blank cell in Column A:

---

Sub FirstBlankCellInColumnA()
    Range("A1").End(xlDown).Select
End Sub

---

To find the first blank cell in Row 1:

 

---

Sub FirstBlankCellInRow1()
    Range("A1").End(xlToRight).Select
End Sub

---

To find the LastUsedCellInRow1:

 

---

Sub LastUsedCellInRow1()
    Range("IV1").End(xlToLeft).Select
End Sub

---

Once we can find these four points of a range we can apply some VBA to achieve our ultimate aim.

All of the Objects we have discussed above contain many more Object, Properties and Methods than I have shown here.  But once you know the Objects hierarchy and how to refer to them it is possible to access all of them.  As you become more familiar with VBA you will find that there is virtually nothing that cannot be achieved, all it takes is an open mind as some lateral thinking.

I have included a Workbook example with this lesson that includes all the code mentioned here.  It also includes two exercises for you to have a go at.  As I have said before, if you solve the problem look at this as the icing on the cake, the cake itself is in trying to solve it.