Common Objects
We will look at each of these
in turn and I will show you what I believe to be their most useful Properties
and Methods. One of the best features of VBA for Excel is that by typing an
Object name and then typing a period (full stop) Excel will list all of the
Properties and Methods associated with it. Use this feature to it's fullest and
not only will you save typing but you should eliminate all
typos.
Application
You will
recall the Application is at the top of the Object hierarchy and contains many
Properties and Methods as well as Collections of other Objects. You would use
the Application Object to gain access to Object Collections such
as:
CommandBars
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:
"ActiveWorkbook" always refers to the Workbook that happens to be active at the time of running the code.
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.
If you have any 'real world' problems that you would like to tackle, feel free to say so and we can incorporate them in any one of the lessons. I believe this is a very good way to learn as you will be tackling something you can relate to. As always if you have any questions at all and/or you would like to focus on a specific topic feel free to say so, that's what I'm here for!