Excel VBA Advanced Training Courses

Ranges That Do More

The Excel Object Model

The Excel Object Model is a hierarchical structure of objects that you can use to interact with Excel. At the top is the Application object, representing the entire Excel application. Below it, you have objects like Workbooks, Worksheets, Ranges, and many others.

Exploring the Range Object in Detail

The Range object is one of the most commonly used objects in Excel VBA. It represents a cell, a row, a column, or a selection of cells containing one or more contiguous blocks of cells. Here are some key properties and methods:

Properties:

.Value: Gets or sets the value of the range.

.Address: Returns the address of the range.

.Rows and .Columns: Return the rows and columns within the range.

Methods:

.Select: Selects the range.

.Copy and .PasteSpecial: Copy and paste the range.

.Clear: Clears the contents of the range.

Example:

Sub ExampleRange()

   Dim rng As Range

   Set rng = ThisWorkbook.Sheets('Sheet1').Range('A1:B2')

   rng.Value = 'Hello, World!'

End Sub

Events That Fire Automatically

Triggers and Events in Excel VBA

Events in VBA are actions that occur in Excel, which can trigger the execution of a macro. For example, opening a workbook, changing a cell, or clicking a button are all events. You can write VBA code to respond to these events.

Running Macros Automatically

To run macros automatically, you can use workbook or worksheet events. Here are a few examples:

Workbook_Open Event: This event triggers when the workbook is opened.

Private Sub Workbook_Open()

   MsgBox 'Welcome to the workbook!'

End Sub

Place this code in the ThisWorkbook module. When the workbook opens, a message box will appear.

Worksheet_Change Event: This event triggers when a cell value changes.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = '$A$1' Then

       MsgBox 'Cell A1 has changed!'

   End If

End Sub

Place this code in the worksheet module. When the value in cell A1 changes, a message box will appear.

PivotTables in Code

Manipulating PivotTables through VBA

Creating a PivotTable:

Here’s a simple example of how to create a PivotTable from a data range in your active worksheet:

Sub CreatePivotTable()

   Dim sht As Worksheet

   Dim pvtCache As PivotCache

   Dim pvt As PivotTable

   Dim SrcData As String

   Dim StartPvt As String

   ' Define the data range

   SrcData = ActiveSheet.Name & '!A1:D100'

   ' Create a new worksheet

   Set sht = Sheets.Add

   ' Define where the PivotTable will start

   StartPvt = sht.Name & '!A3'

   ' Create Pivot Cache from Source Data

   Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

   ' Create PivotTable from Pivot Cache

   Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:='PivotTable1')

End Sub

Adding Fields to a PivotTable:

Once you have created a PivotTable, you can add fields to it:

Sub AddPivotFields()

   Dim pvt As PivotTable

   Set pvt = ActiveSheet.PivotTables('PivotTable1')

   ' Add fields to the PivotTable

   With pvt

       .PivotFields('Year').Orientation = xlPageField

       .PivotFields('Month').Orientation = xlColumnField

       .PivotFields('Account').Orientation = xlRowField

       .PivotFields('Amount').Orientation = xlDataField

   End With

End Sub

Office Apps Working Together

Working with Other Office Applications

You can use similar methods to work with other Office applications.

For example, you can automate Word to create documents, format text, and more.

Example: Creating a Word Document

Early Binding:

Open the VBA editor (Alt + F11).

Go to Tools -> References.

Check Microsoft Word xx.x Object Library.

Sub CreateWordDocument()

   Dim WordApp As Word.Application

   Dim WordDoc As Word.Document

   ' Create a new instance of Word

   Set WordApp = New Word.Application

   WordApp.Visible = True

   ' Create a new document

   Set WordDoc = WordApp.Documents.Add

   ' Add text

   WordDoc.Content.Text = 'Hello from Excel VBA!'

   ' Clean up

   Set WordDoc = Nothing

   Set WordApp = Nothing

End Sub