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

From Training Material
Revision as of 18:19, 1 March 2023 by Gpruszczynski (talk | contribs) (→‎Application-level events)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search


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

Worksheet Object, Collection Worksheets

Validation sheets

Practical methods of the Range object

Copy, paste, paste special

Property CurrentRegion

Find, replace

Sorting ranges

Charts (Object Chart)

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.

Boards

Dynamic arrays

Table arrays Variant

Optimizing arrays and memory

Multi-dimensional arrays

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

Creating and Destroying Objects

Create methods

Create property

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

The default properties and methods

Error handling in the Class

 Sub testErr()
     Debug.Print 2 / 0
 End Sub
Sub carInfo()
Set newAuto = New ClassCar
 newAuto.testErr

End Sub

</syntaxhighlight>

Create and manage collections

Create a collection

Adding and removing items

References to the components (using a key and an index)

Advanced structures and functions VBA =

Passing parameters by value and reference (ByRef and ByVal)

Procedures with a variable number of parameters

Optional Parameters and Defaults

Procedures of unknown number of parameters (ParamArray)

Enumeration, convenient parameter passing

Type the user (User-defined Type)

Service Null, Nothing, empty string "", Empty, 0

Type conversion (Conversion)

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

Use VBA functions in other applications

Extras

Create your own add-ons

Create a toolbar for addition

Installing your own add-ons and their protection

Using external libraries

Connecting to external databases (ODBC, OLEDB)