|
Last month, in Lesson 1, I started
with an introduction to the basics of writing VBA code in Excel, with a couple
of simple examples. Well, it was all just an elaborate hoax! Sheer nonsense!
A work of fiction! I’m no programmer – I can’t even work my garage door opener!
Just kidding.
We’ll start out this month’s lesson with the solution to last month’s “homework”.
(Download here: Excel
VBA Article 1 Example Solution.xls [in zipped format]) We started with
a subroutine called RemoveDuplicates, which sorted the active worksheet
by column 1, then deleted any row (aka record) which was a duplicate
of a previous row (in terms of its column 1 value). The challenge was
to modify RemoveDuplicates so that it would keep track of the total number
of instances of a given record, and put the total for each record in
column 3.
Well, obviously we’ll need a counter. For creativity’s sake, we’ll call
it “Count”. We will start it out at 1, to represent the record which will not be
deleted, then add 1 to it each time we delete another duplicate. See the
new lines (bolded) in Example 1 below.
Example 1
Sub RemoveDuplicates()
Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count
Count = 1
For Row = totalrows To
2 Step -1
If Cells(Row, 1).Value
= Cells(Row - 1, 1).Value Then
Rows(Row).Delete
Count = Count
+ 1
End If
Next Row
End Sub
If you’re not already familiar to writing code, the line “Count = Count
+ 1” will look odd to you, but don’t think of it as an algebraic equation.
This is just how we add one to the current value of Count.
So now we’re counting! However, we have two more things to do: put the count
for a particular record in the spreadsheet, and reset the counter when we
get to a new record. For this we will add an “else” clause to our if-then
statement. The if statement in our subroutine is checking to see if the value
in a particular row, column 1 is the same as the value in column 1 of the
previous row (Row – 1). If this is true, we have a duplicate, so we delete
it, and add one to our counter. If they are different, however, this means
that we are changing from one record to a different record, which in turn
means we should record our counter value for the “old” record, and start
the counter over again for our new record. See the new lines in Example 2…
Example 2
Sub RemoveDuplicates()
Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count
Count = 1
For Row = totalrows To 2 Step
-1
If Cells(Row, 1).Value
= Cells(Row - 1, 1).Value Then
Rows(Row).Delete
Count = Count + 1
Else
Cells(Row, 3).Value = Count 'save count for old record
Count = 1 ' reset
counter for new record
End If
Next Row
' Don't forget last record (which is actually
first)...
Cells(1, 3).Value
= Count
End
Sub
A couple of notes:
- We
had to add a line at the end to store the counter value for the topmost
record. Since this is the last record we come across in our code,
and the first in the spreadsheet, there is no previous record to compare
it to, so we never get to it within our else clause.
- You
can (and should) put comments in your code, as I’ve done above (in green).
In VB, anything after a single quotation is a comment.
Variables and Data Types
In the example above, I’ve used variables like Row, totalrows, and Count
to store values.
A variable is a named element which can be used in your code to store a
value that can change during execution of the code. Variables are generally
of a specific type (numeric, text, etc) and are declared using the Dim statement.
For example:
Dim iCounter as
integer
Dim sName as string
I did not declare variables in any of the examples so far, and VBA does
not force you to declare your variables. However, I highly recommend you
not only declare your variables, but tell VBA to force you to declare them.
You do this by putting a line at the top of your code (above all of your
subroutines) which says Option Explicit. VBA will give you a “variable
not defined” error message whenever it encounters an undeclared variable.
Without this, you could mistype one of your variable names and not know it.
VBA would just assume it was a new variable and initialize it to zero or “”.
If you’re working with a large piece of code, this could be a big debugging
headache.
Example 3
Option Explicit
Sub Hello()
Dim iCol as integer
Dim lRow as long
Dim sText as string
iCol = 2
lRow = 3
sText = “Hello Excel”
Cells(lRow, iCol).Value =
sText
End Sub
Excel Objects, Properties, and Methods
In addition to the standard data types described above, Excel provides a
number of defined “object” types representing various elements specific to
Excel. Examples of Excel Objects include Application, Workbook, Worksheet,
and Range. Application represents the entire Excel application. Workbook
represents an Excel .xls file. Worksheet represents a single sheet (tab)
within a workbook.(A Range represents a set of one or more cells. These types
of objects are used to control the behavior of Excel from your VBA code.
An object is like a variable in that it can store a value, but is more complex
in that it can hold multiple values (properties) and has its own set of defined
actions (methods) it can perform. In the Excel VBA help file, the help page
for any Excel object type has hyperlinks at the top for the lists of properties
and methods which apply.
Some examples of Excel objects and their properties and methods:
Application Object
Properties: Workbooks, ActiveWorkbook, ActiveSheet, Caption,
Charts, DisplayAlerts, CutCopyMode…
Methods: CheckSpelling, Run, Quit…
Workbook Object
Properties: Worksheets, ActiveSheet, ActiveChart, Name, Path, FullName,
FileFormat…
Methods: Activate, Save, SaveAs, Close, Protect…
Worksheet Object
Properties: Name, Index, Cells, Columns, Rows, Type, Visible…
Methods: Activate,
Select, Calculate, Copy, Delete, Move, PrintOut, Protect, Unprotect…
Range Object
Properties: Count, Columns, Rows, Cells, Value…
Methods: Activate, Select,
Find, Copy…
There are many more Excel objects than the four
I’ve listed, but these are probably the most widely-used. Also, each of the
four objects I’ve listed has many more properties and methods.
To refer to an object’s property or method, simply write the object name
followed by a dot/period followed by the property or method name.
For example, in the line…
Application.ActiveSheet.Cells(2,3).Value = 6
ActiveSheet is a property of the Application object and is an object of
type Worksheet. Cells is, in turn, a property of the Worksheet object which
represents a Range object. Value is a property of the Range object.
What can make this confusing is the fact that property names and object
type names in Excel are often the same. The Workbook object and Worksheet
object each have a property called Application, which represents the Application
object. Range is also both a property and an object type.
Implied Objects
Property names can sometimes be used without “object qualifiers”. In other
words, in some cases you can specify the property name, without specifying
the object you are referring to.
For example, the line we used above…
Application.ActiveSheet.Cells(2,3).Value = 6
Can also be written as…
ActiveSheet.Cells(2,3).Value = 6
Although ActiveSheet is a property of Application, you may use ActiveSheet
without specifiying “Application”, since there is only one Application object.
The same line above may also be written as…
Cells(2,3).Value = 6
In this case, Cells is a property of a Worksheet object, but you can use
it without specifying which worksheet you are referring to. In this case,
Excel will use whichever sheet is active (currently displayed in Excel) as
a default.
Collections
Many types of objects in Excel come in groups. There may be multiple workbooks
open in Excel at a given time. Each workbook may have multiple worksheets.
A worksheet can have multiple graphs. These multiple objects are gathered
together in what’s called a collection. The collection itself is an object,
with its own set of properties and methods.
For example, the Application object has a property called Workbooks, which
represents a Workbook Collection object. It includes a single Workbook object
for each workbook currently open in Excel. Similarly, the Workbook object
has a property called Worksheets which is a Worksheet Collection object,
including one Worksheet object for each worksheet in the workbook.
Referencing Items in A Collection
The simplest way to reference an item in a collection is by number. All
objects in a collection have a numeric index, starting with 1. To reference
an element in a collection by number, simply put the number in parentheses
after the collection object. Try the following example…
Open Excel and create a new workbook. Open the VB Editor (Alt + F11). Double-click
on “ThisWorkbook” to bring up a code window on the right. Paste in the following
lines:
Example 4
Option Explicit
Sub SeeSheetNames()
Dim iSheetCount As Integer
Dim iSheet As Integer
iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet
End Sub
Switch back to Excel and run the subroutine by using Alt + F8 to bring up
the macro list and double-clicking on SeeSheetNames. As you see, the subroutine
activates each sheet in the workbook and displays the name of that sheet
in a message box.
A couple of notes on Example 4:
- I
could have used just “Worksheets.Count” instead of “ActiveWorkbook.Worksheets.Count”.
When the workbook object is not specified, Excel VBA uses the current
active workbook by default.
- I
mentioned above that each type of collection object has its own set
of properties and methods. The one property which is common to all
collections is Count, which gives you the number of items currently
in that collection.
- MsgBox
is a VB function you may use to display text in a popup box.
Another way to reference workbooks or worksheets is by name. Instead of
using a number in the parentheses, put the name (in double quotes) in the
parentheses. This is helpful when you are working with multiple workbooks,
or a spreadsheet with a lot of worksheets with specific purposes.
Examples:
Workbooks(“ClassSample1.xls”).Activate
Worksheets(“TestValues”).Cells(1,1)
= 60
You can also refer to the Workbook in which the code you are running is
contained with the keyword ThisWorkbook. This is useful when you are using
one workbook and its code to modify the data in another workbook.
For example:
ActiveWorkbook.Worksheets(1).Name
= ThisWorkbook.Worksheets(1).Name
This line changes the name of the first worksheet in whichever workbook
is currently active to be the same as the name of the first worksheet in
the workbook which includes this code.
Another Exercise
More homework for you to try if you like... Create a workbook with a “CreateWorkbook” subroutine
which automatically creates a new workbook, then sets the names of each worksheet
in the new workbook according to the names it reads from the first column
of its own first worksheet. In other words, you start with a workbook whose
first worksheet looks like Figure 1. Running the CreateWorkbook subroutine
will automatically create a new workbook which looks like Figure 2.
 
Figure 1 Figure
2.
You may need some hints:
- You
will need to use the Add method of the Workbooks collection to
create the new workbook.
- You
will need to determine how many names are in the worksheet, i.e.
how many worksheets will need to be created in the new workbook. See the
use of UsedRange in example 1 above.
- You
will need to add or delete worksheets in the new workbook to
get the correct number. You will use the Add method of the Worksheets collection
and the Delete method of the Worksheet object.
- You
will be well-served to use a “while” loop when adding or deleting worksheets.
If you are not familiar with a while loop, it looks something like
this:
Dim x As Integer
While x < 5
x = x + 1
Wend
Everything between the While and the Wend repeats
until the While condition is true.
- You
will be dealing with two workbooks. Use ThisWorkbook and ActiveWorkbook
to distinguish between them. You may use the assumption that the new
workbook, when you create it, becomes the active workbook.
Give it a shot and send me an e-mail if you get stuck. Good luck!
- Mark
Need further help getting your Excel VBA code right? Join our free Excel VBA
support group! See this link for details: http://groups.yahoo.com/group/ExcelVBA/ .
|
