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

[[Category:VBA]]

== Excel Object Model ==

=== The protection sheet from VBA ===


<syntaxhighlight lang="vb">
 Sub protection()
    Cells.Locked = True
    Cells.FormulaHidden = True
    ActiveSheet.Protect Password:="asdf"
 End Sub
</syntaxhighlight>

=== 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.

<syntaxhighlight lang="vb">
 Workbooks.Close
</syntaxhighlight>
		

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.

<syntaxhighlight lang="vb">
 Workbooks.Add
</syntaxhighlight>
 		
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.

<syntaxhighlight lang="vb">
 Workbooks.Open FileName:="Array.xls", ReadOnly:=True
</syntaxhighlight>

=== 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 ===

<syntaxhighlight lang="vb">
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
</syntaxhighlight>

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

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

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

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

<syntaxhighlight lang="vb">
Sub Initialize()
    Dim appEventHandler As New AppEventHandler
    appEventHandler.InitializeAppEvents
End Sub
</syntaxhighlight>

#  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). [https://en.wikipedia.org/wiki/Class_(computer_programming)]

=== 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:

<syntaxhighlight lang="vb">
 Public carName As String
 Public carColor As String
 Public carSpeed As Integer
 Public carNumberOfDoors As Integer
</syntaxhighlight>

'''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

<syntaxhighlight lang="vb">
 Sub Accelerate(ByVal accelerateBy As Integer)
    carSpeed = carSpeed + accelerateBy
 End Sub
</syntaxhighlight>

=== Creating and Destroying Objects ===

=== Create methods ===

=== Create property ===

=== Validation data using property ===

'Property Let'

'Property Get'

<syntaxhighlight lang="vb">
Option Explicit

 Public carName As String
 Public carColor As String
 Public carSpeed As Integer
 Private carNumberOfDoors As Integer
</syntaxhighlight>


<syntaxhighlight lang="vb">
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
</syntaxhighlight>

=== The default properties and methods ===

=== Error handling in the Class ===

<syntaxhighlight lang="vb">
 Sub testErr()
     Debug.Print 2 / 0
 End Sub
</syntaxhighlight>

 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).


{| class="wikitable"
|-
! 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 ===


<syntaxhighlight lang="vb"> 
    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
</syntaxhighlight>

=== Extracting The Text From A File ===

<syntaxhighlight lang="vb"> 
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

</syntaxhighlight>

== 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) ==