Visual Basic for Applications (VBA) in Excel - Advanced Training Course

Excel Object Model

The protection sheet from VBA

 Sub protection()
    Cells.Locked = True
    Cells.FormulaHidden = True
    ActiveSheet.Protect Password:="asdf"
 End Sub

Object Workbook, Workbooks collection

Workbook

Represents an open Excel workbook

Properties and methods

• Activate - activating a workbook
• Close - closing a workbook
• FileFormat - file format or type of workbook
• Name - The name of the workbook
• Path - Path to the workbook file
• FullName - the name of the workbook file with path
• HasPassword - Is the workbook protected by password?
• HasVBAProject - Does the workbook contain the VBA project
• PrintOut - Print the workbook
• PrintPreview - Call print preview window
• Protect - Protect your workbook from modification (with the ability to set a password)
• ReadOnly - Can the contents of the workbook be modified?
• RefreshAll - Refresh all external data ranges and PivotTables
• Save - Saves changes to the current workbook
• Saved - Did you make changes to the file?
• SaveAs - Save the workbook to a file with a different name
• SaveCopyAs - Save a copy of a workbook to a file with a different name

Child objects

• Names - Collection of names used in the workbook
• Sheets - Collection of worksheets and charts
• Styles - Collection of formatting options sets for a range

Workbooks Collection

The example below closes all open workbooks.

 Workbooks.Close

With the Add method, we create a new empty workbook and add it to the collection. The following example adds a new empty workbook in Excel.

 Workbooks.Add

Metody Open używamy do otwarcie pliku. Open tworzy nowy skoroszyt w ramach otwartego pliku. Poniższy przykład otwiera Array.xls jako skoroszyt tylko do odczytu.

The Open methods are used to open the file. Open creates a new workbook within an open file. The following example opens Array.xls as a read-only workbook.

 Workbooks.Open FileName:="Array.xls", ReadOnly:=True

Events

Application-level events

Option Explicit

' Declare variables for the event handlers
Private WithEvents AppEvents As Application

' This is the event handler for when a workbook is opened
Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "The workbook " & Wb.Name & " has been opened."
End Sub

' This is the event handler for when a workbook is closed
Private Sub AppEvents_WorkbookClose(ByVal Wb As Workbook)
    MsgBox "The workbook " & Wb.Name & " has been closed."
End Sub

Sub InitializeAppEvents()
    ' Set the AppEvents object to the current application object
    Set AppEvents = Application
End Sub

To use these event handlers, you need to add them to a class module in your VBA project. Here's how:

1. In the VBA Editor, go to Insert -> Class Module.
2. In the new class module, paste the code above.
3. Rename the class module to AppEventHandler (or any name you prefer).
4. Save the class module.

To activate the event handlers, you need to call the InitializeAppEvents sub from a regular module:

1. In the VBA Editor, go to Insert -> Module.
2. In the new module, paste the following code:

Sub Initialize()
    Dim appEventHandler As New AppEventHandler
    appEventHandler.InitializeAppEvents
End Sub

1. 3 Save the module.

Now, every time you open or close a workbook in Excel, you will see a message box with the name of the workbook that was opened or closed.

Object-Oriented Programming

Classes and Objects

By creating programs in VBA we have access to objects representing the actual elements of the application, for example for Excel, these will be Range, Worksheet or Workbook. But sometimes situations arise that make a particular task more efficient when we define our own object. You can create your own objects and use them in the same way as built-in Excel objects. We create these objects using class modules. Unlike modules that may contain loosely coupled code, the class is a hermetic entity closely related to each other. This requires a more abstract thinking in programming, but the reward is functionalities inaccessible in the modular approach.

In object-oriented programming, a class is an extensible program-code-template for creating objects, providing initial values for state (member variables) and implementations of behavior (member functions or methods). [1]

Creating classes

When we want to build our own object, the class module becomes a template for the properties and methods of the object

Example:

We create a Car object in which we will write its name, color, door number and speed with which it moves. We start by inserting the module class (Insert -> Class Module). The ClassCar object has four properties:

 Public carName As String
 Public carColor As String
 Public carSpeed As Integer
 Public carNumberOfDoors As Integer

Properties, that are variables declared with the use of Private or Public keywords. Our variables are declared using the Public keyword because we want them to be accessible from the standard module.

Methods are 'actions' that will be executed by objects. In classes, these operations are sub-procedures or functions. Below is the code that creates the Accelerate method, which will change the speed of the car

 Sub Accelerate(ByVal accelerateBy As Integer)
    carSpeed = carSpeed + accelerateBy
 End Sub

Validation data using property

'Property Let'

'Property Get'

Option Explicit

 Public carName As String
 Public carColor As String
 Public carSpeed As Integer
 Private carNumberOfDoors As Integer

Public Property Get NumberOfDoors() As Integer
    NumberOfDoors = carNumberOfDoors
End Property

Public Property Let NumberOfDoors(ByVal vNewValue As Integer)

    If vNewValue >= 2 And vNewValue <= 5 Then
            carNumberOfDoors = vNewValue
    End If

End Property

Error handling in the Class

 Sub testErr()
     Debug.Print 2 / 0
 End Sub

Sub carInfo()
Set newAuto = New ClassCar
 newAuto.testErr

End Sub

</syntaxhighlight>

File Operations

To open or create a new file, use the Open statement

Open pathname For mode  As [#] filenumber 

pathname The file name which can include folder and even drive

filenumber The number of the file (Integer).

Mode	Description
Output	Sequential Access that allows read and write. If file does not exist it will be created, while all data from existing file will be deleted.
Append	Sequential Access that allows read and write to the end of the file. Opens an existing file and adds new data to it. If file does not exist it is created
Input	Sequential Access that allows read only. If the file does not exist, an error message will appear

• CLOSE statement to close a text file.
• EOF function to indicate when the end of a file has been reached.
• LOF use LOF function to determine the size of an open file
• FileLen Use the FileLen function to obtain the length of a file that is not open.
• FREEFILE function to return the next free file number.
• LINE INPUT statement to read a single line.
• PRINT statement to write display formatted data to a sequential file.
• WRITE statement to write data to a sequential file.

Creating A Text File

 
    Sub CreateTextFile()
    Dim TxtFile As Integer
    Dim FilePath As String
    
      FilePath = "C:\Users\NobleProg\Desktop\MyFile.txt"
    
    'To determine the next available file number use FreeFile function
      TxtFile = FreeFile
    
    'Open the text file
      Open FilePath For Output As TextFile
    
    'Write some text
      Print #TxtFile, "Hi All!"
      Print #TxtFile, "Greg was here."
      Print #TxtFile, "bye"
      
    'Save & Close Text File
      Close TxtFile
    
    End Sub

Extracting The Text From A File

 
Sub GetDataFromTxtFile()

Dim TxtFile As Integer, FilePath As String, FileContent As String

  FilePath = "C:\Users\NobleProg\Desktop\MyFile.txt"

 'To determine the next available file number use FreeFile function
  TxtFile = FreeFile

'Open the text file
  Open FilePath For Input As TxtFile

'Get file content to a variable
  FileContent = Input(LOF(TxtFile), TxtFile)

'Show file content on the MsgBox function
  MsgBox FileContent

'Close Text File
  Close TxtFile

End Sub