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

From Training Material
Jump to: navigation, search


Contents

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

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

The default properties and methods

Error handling in the classroom

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

Opening and closing text files

Reading and writing text and binary data

Processing of records in the CSV file

Efficient processing of text files

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)