What Makes Excel VBA Special?

This is a very specifically worded question.  I’m not asking “For data analysis and visualization, why should we choose Excel VBA over other tools?”   (The shortest answer to that question might be that tool choice is mandated externally.)  I’m not asking “Which are the unique or superior characteristics of Excel VBA?”  I am asking “In order to use Excel VBA effectively, which are the specific features, methods, and practices that we must master, above and beyond the usual language features and syntax?

VBA is essentially a loosely object-oriented language with structured programming roots.  It is clearly trying to subscribe to the “batteries included” philosophy.  There is a wealth of material available elsewhere about the language and its features.  Here, I am going to attempt to answer my question above, documenting my understanding of key special features, methods, and practices that are important to using Excel VBA.

This very long post contains my notes on both the big picture and a few coder’s tips.  In my opinion, here are the specific Excel VBA features, methods, and practices that we must master above and beyond object-oriented and structured programming language features.  I have described my evolving understanding of these features below.

  • The Spreadsheet Interface
  • Reading and Writing Spreadsheet Data
  • VBA versus Excel Functions
  • The User Interface
  • Event Handling
  • The Development Environment

The Spreadsheet Interface

To use Python, R, Tableau, or other tools with spreadsheet data, our application must be able to read and write the spreadsheet-formatted data.  We must master the tools and methods for reading and writing such data.  Obviously, the most unique aspect of Excel VBA is that the code is already “embedded” in the spreadsheet.  We must master the VBA way of addressing the data.

Out, Out, Damned Dots!

VBA is dot-happy.  VBA uses dots to express object hierarchy, but also to express both methods and properties.  The format is:

OBJECT.Identifier[.sub_Identifier]

Here is the devilish part:  Either the Identifier or the sub_Identifier can be a:

  • Method
  • Property
  • Event

The really devilish part is that properties and methods can be mixed, such as in this example that deletes a chart:

ActiveWorkbook.Charts.Delete

Frequently the documentation is a little inconsistent about when something is a method, property, or event (such as Activate.)

Object References in VBA Object Hierarchy

An example fully qualified object reference:

Application.Workbooks("Wb1.xlsm").Worksheets("Ws1").Range("A1").Value = 100

Partially qualified object references assume ActiveWorkbook and ActiveSheet.

Names can be simplified with:

With ThisWorkbook.Worksheets(1)
    .Range("A1").Value = "Hello"
    .Range("A2").Value = Time
End With

A complete list of the numerous named VBA objects is available here.

Syntax

The following are typical syntactic expressions:

Object.Property = Value
Object.Method
Object.Method Parameter:=Value
Object.Method Argument1, Argument2
Object.Property.Method

Workbooks and Workbook

Workbooks is the collection containing each individual workbook.  Each workbook (single) is an object identified by file name.  Here is the tricky bit:  the plural word “Workbooks” can actually be a reference to a specific Workbook object.  This means that the plural word can actually be subject to the object methods and properties instead of the collection methods and properties.  This difference may be controlled by whether the parameter is provided as a value to the collection or to the method.  For example:

Workbooks.Open("BOOK4.XLS")

Workbooks("BOOK4.XLS").Activate

The Workbooks collection has a small number of methods and properties, with no events.  Meanwhile the Workbook object has a large collection of methods, properties, and events.

Workbooks

The key Workbooks methods include:

  • Add
  • Close      (Closes all open workbooks)
  • Open      (There are a number of related methods for opening XML, TXT, etc.)

The key Workbooks properties include:

  • Count
  • Item

Workbook

A Workbook object can be referred to directly or can be referred to as a specific member of the Workbooks collection.  For example:

Workbooks("BOOK4.XLS").Activate
Workbooks("BOOK4.XLS").Save

Other important workbook objects (technically an application property) include:

  • ActiveWorkbook
  • ThisWorkbook

Generally, it is best practice to use ThisWorkbook as the VBA code then references the workbook in which it is running.  This prevents risk of failure due to some other workbook being active at the moment.

The key Workbook methods include:

  • Activate
  • Open
  • Close
  • Save
  • SaveAs     (There is a related method for saving as XML)

The key Workbook properties include:

  • ActiveChart
  • ActiveSheet
  • Charts
  • Path
  • PivotTables
  • Sheets

Workbook Contents

In the Excel object hierarchy, a workbook can contain several types of objects.  The two most important user-focused objects are the Worksheet and the Chart.  Other objects include Names, Windows, and VBProjects.

Worksheets and Worksheet

Excel’s use of the noun “worksheet” is even more devilish than its use of the noun “workbook.”  As before, Worksheets is the collection containing each individual worksheet.  Each worksheet (single) is an object identified by a unique name or a sequence number.  As before, there is a tricky bit where the plural word “Worksheets” can actually be a reference to a specific Worksheet object.  This means that the plural word can actually be subject to the object methods and properties instead of the collection methods and properties.  Just to make things confusing, Worksheets is also a property of Workbooks, and returns a collection of Sheets.

The Worksheets collection has a small number of methods and properties, with no events.  Meanwhile the Worksheet object has a large collection of methods, properties, and events.

The Sheets collection contains both the inventory of worksheet objects and chart objects, and this is a super-set of the Worksheets and Charts collection.  For this reason, programming practices are likely to lead to the selection and use of the Worksheets and Charts methods for better specificity.

Consider these examples.  If the objective is to add a new blank worksheet at the very “end” of a typical workbook containing a mix of worksheets and charts, the following examples will result in different sheet sequences.  The last one will even generate an error!

Worksheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add After:=Sheets(Sheets.Count)
Sheets.Add After:=Worksheets(Sheets.Count)

Worksheets

Other important worksheet objects (technically an application property) include:

  • ActiveWorksheet

The key Worksheets methods include:

  • Add
  • Copy
  • Delete
  • Move
  • PrintOut
  • Select

The key Worksheets properties include:

  • Count
  • Item
  • Visible

Worksheet

A Worksheet object can be referred to directly or can be referred to as a specific member of the Worksheets collection.  For example:

Worksheets("DataSheet").Activate
Worksheets(2).Activate
Sheets.Add

The key Worksheet methods include:

  • Activate
  • Calculate
  • Copy
  • Delete
  • Move
  • Paste
  • PrintOut
  • Close
  • SaveAs
  • Select

The key Worksheet properties include:

  • Cells
  • Columns
  • Range
  • Rows
  • Visible

Rows and Columns

Row and column addresses can be detected, stored in variables, and manipulated.  Note that columns can be managed as column numbers.

Dim dblRowNo As Double, dblColNo As Double
Dim intRowCounter As Integer, intColCounter As Integer

dblRowNo = ActiveCell.Row
dblColNo = ActiveCell.Column

Range and Cells

Directly reading and writing spreadsheet data is clearly the most important ability of VBA as a programming language.  VBA allows addressing cells either by the user visible “Column & Row” string, or by numeric counters.

Addressing Cells and Ranges

Where cell position is provided using the traditional string, data can be written directly using the Range.Value property.  Note that address is column before row:

strAddress = "D6"
Range(strAddress).Value = "Commission"

Where row and column positions need to be calculated, data can be written directly using the Cell.Value property.  Note that the address is row before column:

Cells(dRow + iRC, dColumn + iCC).Value = j * k

You must use a numeric value for Row, but you may use the numeric value or string value for Column. Both of the following lines refer to cell C5:

Cells(5,"C")
Cells(5,3)

Cells properties can be used as parameters in the Range property. The following refers to the range A1:E5:

Range(Cells(1,1),Cells(5,5))

The range can be referenced absolutely or relatively.  (Best practice is to use absolute references where possible.)

ThisWorkbook.Worksheets("Sheet1").Range("A1") = 67
ThisWorkbook.Worksheets("Sheet1").Range("A2") = "John Smith"
ThisWorkbook.Worksheets("Sheet1").Range("A3") = #11/21/2017#

The relative range can use the code name of the worksheet.  You can see the code name of the sheet in the VBAProject window. It is the name outside the parenthesis.

cnSheet1.Range("A1") = 67

Entire rows or columns may be addressed (primarily for property changes.)

cnSheet1.Columns(2).Font.Size = 9
cnSheet1.Columns("D:F").ColumnWidth = 4
cnSheet1.Rows(5).Font.Size = 18

The entire worksheet contents can be addressed simply (primarily for clear and reset.)

Cells.ClearContents
Cells.ClearFormats

Bounding Ranges

The bounds of a range can be determined using the Range.End method and the xlDown, xlUp, xlToRight and xlToLeft parameters.

Selecting, Activating, and Addressing Cells

When humans use spreadsheets, they activate worksheets and cells, but can also select worksheets, cells, and ranges.  More than one worksheet can be selected, but only one active (or activated.)  More than one cell can be selected, but only one active.

Warning:  Different ranges on different worksheets can be selected, which can change what is active when changes are made in which worksheets are selected.  The way this works may defy initial logical expectations.

VBA code can address cells directly without disturbing the human user’s selection.  VBA code can also address cells by changing what cells are activated and what range selected.  The VBA coder must decide when to address cells for manipulation directly, and when to force a change in the user’s selection.

Most of the code examples in this posting use direct addressing, such as:

With Worksheets("Sheet1") 
        .Range("A1") = "Name" 
        .Range("B1") = "Address" 
        .Range("A1:B1").Font.Bold = True 
End With

99% of the time, direct addressing is preferable.

Addressing via selection and activation uses syntax resembling the following:

Sheets("Sheet1").Select 
Range("A1").Select 
ActiveCell.FormulaR1C1 = "Name" 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "Address" 
Range("A1:B1").Select 
Selection.Font.Bold = True

Forcing a change in the selection may be necessary when VBA is performing a search within cells, clearing cells, or when VBA wants the user to provide input in specific cells.  VBA in macros may need to be designed to only change cells the user has selected.

When using the selection and activation method, the Offset property is commonly used to address cells in proximity.  The Offset property is generally used with Range objects (including Range object variables) and the application ActiveCell object.  Typical Offset examples include:

ActiveCell.Offset(0, 1).Select

Set RangeVar1 = RangeVar1.Offset(-1,0)
 ActiveCell.Offset(1, 0).Range("A1").Select strMyValue = ActiveCell.Offset(0,2).Value ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
 



Reading and Writing Spreadsheet Data

Reading Spreadsheet Data

Single cell values can be read into VBA variables using the Range object.  Be sure that you have the right variable type defined to match the cell contents!

val = cnSheet1.Range("A1")
Dim sText As String
sText = cnSheet1.Range("B1")

Multiple cell values can be read into VBA arrays.

Dim StudentMarks() As Variant
' Read 26 values into array from the first row
StudentMarks = Range("A1:Z1").Value

Writing Spreadsheet Data

Cell contents can be set using either the Range object or the Cells object.

The Range object allows referencing a cell by its user visible address via a text string.  Note that this is the user visible Column & Row format.

strAddress = "D6"
Range(strAddress).Value = "Heading"

The Cell object allows referencing a cell numerically.  Note that this is in Row & Column format.

Cells(dRow + iRC, dColumn + iCC).Value = j * k

Multiple cell contents can be written (identically) from a single value or variable.

' Write to a range of cells
cnSheet1.Range("A1:A10") = 67

' Write text to multiple ranges of cells
cnSheet1.Range("B2:B5,B7:B9") = "John Smith"

Multiple cell contents can be written from a VBA array.

' Write the 26 values to the third row.  Note that StudentMarks is array!
Range("A3:Z3").Value = StudentMarks

Writing Spreadsheet Metadata

Spreadsheet metadata could include both the cell properties and also the formulaic contents of cells that perform manipulations.

Setting cell properties is simple, but a strong grasp of object properties and parameters is required.

Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
With Worksheets("Sheet1").Cells.Font 
    .Name = "Arial" 
    .Size = 8 
End With

Where cells contain formulas instead of alphanumeric data, the property must be used.

Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)"

VBA Versus Excel Functions

Following the “batteries included” philosophy, VBA includes a wide range of date, string, numeric, and other data manipulation functions.  These are similar to, but not identical to the date, string, and numeric functions available within Excel spreadsheet cells.  There is not complete overlap between the two sets of functions.  Excel functions are available in VBA code via the Application.WorksheetFunction method.  Example:

Dim myRange As Range 
Set myRange = Worksheets("Sheet1").Range("A1:C10") 
answer = Application.WorksheetFunction.Min(myRange)

A list of the available functions is available here.


The User Interface

VBA provides communications directly with the user via panels, forms, and Active-X controls, outside of simply manipulating the contents of Excel spreadsheet cells.

Excel VBA offers a message box panel for simple user communications focused on output.  The message box provides 6 formats of user response after the user has read the message.  These include the use of the traditional “OK,” “Yes,” “No,” “Abort,” “Retry,” and “Cancel” buttons.  The format and the response are coded with integers.  The best practice is to use some of the established VBA constants for that control (in case Microsoft chooses to change the integer coding in the future.)

Excel VBA offers an input panel for simple user communications focused on input.  The input box provides a message prompt and then accepts one field for direct input.  The “OK and “Cancel” buttons are offered.”  This input panel is offered as either a VBA function or as an Application method.  The only apparent difference is that the latter offers the ability to control the type of data accepted.  The latter approach will use Excel error message panels to flag incorrect input type, so it may be necessary to keep the warning flag/control on.

On its own, Excel will generate dialog boxes when the user (or the VBA program) attempts certain operations (such as deleting a worksheet.)  VBA enables turning off these kind of automatically generate dialogs while the code is running.  Standard practice is:

Application.DisplayAlerts = False
Worksheets("Sheet6").Delete 
Application.DisplayAlerts = True

(Image courtesy of the3cats at Pixabay.)

Advertisements