Visual Basic for Applications


Visual Basic for Applications ( VBA ) is Microsoft’s implementation Visual Basic which is integrated in all applications Microsoft Office , some other Microsoft applications such as Visio and at least partially in some other applications such as AutoCAD , WordPerfect , MicroStation , SolidWorks or ArcGIS . It replaces and extends the capabilities of macro languages ​​specific to older applications such as the WordBasic language built into an older version of the Word software, and can be used to control virtually the entire application language,

As the name implies, VBA is very much related to Visual Basic (the syntaxes and concepts of the two languages ​​resemble each other), but normally can only execute code in a Microsoft Office host application (not a standalone application , It therefore requires a license from the Microsoft office suite). However, it can be used to control one application from another (for example, automatically create a Word document from Excel data ). The code thus executed is stored in document instances, it is also called macro.

VBA is functionally rich and extremely flexible, but it has significant limitations, such as its limited support for callback functions ( callbacks ), and an archaic management errors, use of handlers error instead of a Mechanism of exceptions .

Even if these limitations make this language very little used by IT developers who want to use tools that are first and foremost effective, its simplicity and ease of access have attracted certain professions, notably in finance.

Obsolescence

Since 1 st July 2007, Microsoft no longer distributes licenses of VBA to new clients 1 as they try to replace them with Visual Studio Tools for Applications  [ archive ] (VSTA), application customization toolkit based on the platform Framework .NET . Article by D r eX 2 :

” Software vendors will now use VSTA as a replacement for Visual Studio for Applications (VSA), to provide customization capabilities in their applications. […] VSA was a light client , a VBA alternative run by a server, based on Visual Studio. The server approach has been seen by publishers as less useful than a rich client model , so based on this feedback, Microsoft started developing VSTA. Some publishers still use VSA today, but we find its limited interest. Some of the technologies developed for VSA are included in VSTA. Customizing applications using VSTA takes better advantage of rich client functionality than VSA, Offering a highly optimized environment for customizing both client and server-side applications. “

” ( In ) VSTA now replaces VSA [Visual Studio for Applications] have the technology ISVs will use to Provide customization capabilities in their Applications. […] VSA was a thin-client , server-driven alternative to VBA built on Visual Studio. The server approach was viewed by ISVs as a Microsoft VSTA. There are ISVs successfully using VSA today, but its application was limited. Some of the technology developed for VSA is incorporated within VSTA. VSTA application customization takes better advantage of rich client functionality than VSA, offering a highly optimized environment for application customization on both the client and the server.  “

Office 2007 continues to use the old VBA engine; However, Visual Studio Tools for Office  (en) (VSTO) is available. VBA support in Microsoft Office for Mac was discontinued with the release of version 12 in 2008 3 and then reintroduced in version 2011 4 . See also VB.NET.
The first version of VSTA was provided in April 2006 and has been integrated into various ISVs, including InfoPath 2007 and ABB Robotics. The next version of VSTA (based on Visual Studio 2008 known as “Orcas”) will be distributed around February 2008. The second version of VSTA is very different from the first,

However, due to the dependence of some companies on VBA, VBA is still available in Office 2007, 2010, 2013 and 2016 5 , 6 . The latest version 7.1 is included in Office 2013. The Excel 4 macro language, earlier than VBA, is also supported, even with the latest versions of Excel 7 .

History

The incredible popularity of Visual Basic after its launch led Microsoft to include a simplified version in its desktop applications to replace the different macro languages. However, as shown by the chronology, this change was made over a long period 8 .

  • 1993 – Launch of VBA with Microsoft Excel (version 5)
  • 1994 – Inclusion of VBA with Microsoft Project
  • 1995 – Inclusion of VBA with Microsoft Access, replacing Access Basic
  • 1996 – Inclusion of VBA in Microsoft Word, replacing Word Basic
  • 1997 – Launch of VBA 5.0, covering the Microsoft Office 97 suite
  • 1997 – Microsoft allows the use of VBA for other applications
  • 1998 – Launch of VBA 6.0
  • 2001 – Launch of VBA 6.1 visible in MS Office 2007
  • 2010 – Launch of VBA 7.0 (to ensure compatibility of 32-bit and 64-bit versions of Microsoft Office)
  • 2013 – Launch of VBA 7.1 covering the office suite 2013 and 2016

Interoperability

Minimum VBA support is also available in OpenOffice.org , starting with version 3.0 9 .

Applications

In Excel

The Macro Recorder in Microsoft Excel (Developer Tab / Save Macro) makes it easy to generate VBA code in a procedure.

The entire sequence of actions performed between the beginning and the end of the recording is recorded in a VBA procedure, which can be re-executed identically. It is possible to modify this code or to program directly in the VBE (Developer / Visual Basic tab).
This is the best way to learn how to use VBA for novice programmers.
First, a sequence is recorded by pressing the record button, and then it can be executed step by step (F8 key) in the VBA tool in order to know the actions performed by each line of code.

The main Excel spreadsheet object collections are WorkBook (workbook), Sheets ( spreadsheet ) and Range ( cells ). They can be used in a descending hierarchy:

  • Workbooks (“Year 2012”) Sheets (“Mars”) Range (“B2”) designates cell B2 of the March sheet of the binder Year 2012;
  • Sheets (“March”). Range (“B2”) of the active workbook (open and displayed);
  • Range (“B2”) of the active sheet.

Once a designated object:

  • A method can be applied to it (ObjectName.MethodName): Range (“B2”) Selects cell B2 …
  • Its properties (ObjectName.Property) can be accessed or modified (ObjectNameObject.Property = value); Common properties are Visible (to be used with True and False to show or hide), Value (value of a cell), Count (number of cells in a page, worksheets, etc.).

The reference cells may be to:

  • Absolute : Range (“B2”) or Cells (2,2) designate the cell B2 of the active sheet;
  • Relative to the original cell: ActiveCell.Offset (2, -1) designates the cell located 2 lines lower (- for higher) and a column to the left (+ for right) of the active cell.

Microsoft Excel allows you to create custom functions programmed in VBA code and placed in a module. They are then accessible in the function library (Insert / Function / Custom):

Function FunctionName ( Argument1 , Argument2 As DataType , Optional Argument3 = DefaultValue )
... ( programmed actions ) FunctionName = Return Value End Function

These functions can receive input arguments, the type of which can be specified (Argument2 above) or which can be optional, with a default value (Argument3 above).

In Word

As for Excel, the macro recorder in Microsoft Word (Development Tab / New Macro) makes it easy to generate VBA code in a procedure.

The entire sequence of actions performed between the beginning and the end of the recording is recorded in a VBA procedure, which can be re-executed identically. It is possible to modify this code or to program directly in the VBE (Development Tab / Visual Basic Editor). The macro recorder is the best method for VBA learning for novice programmers. First a sequence is recorded by pressing the record button and then it can be executed step by step (F8 key) in the VBA tool to check the actions performed by each line of code.

The main collections of Word processing objects are Documents (documents), BookMarks ( bookmarks ), Range (the contents of bookmarks). They can be used in a descending hierarchy:

  • Documents (“myDocument”) the selected document from the set of open documents
  • BookMarks (“mySignet”) a bookmark of the current document
  • BookMarks (“mySignet”) .Range.Text the text attached to the bookmark of the current document

Once a designated object:

  • A method can be applied to it (ObjectName.Metername): ActiveDocument.SaveAs “myfile.docx” saves the WORD file under WINDOWS under the name myfile.docx …
  • Its properties (NameObject.Property) can be viewed or modified (affection ObjectName.Property = value); Of the current properties are Visible (to be used with True and False to show or hide), Text (paragraph, bookmark or selection content), Count (number of objects in a collection).

Syntax

Condition

If i = 5 Then
 MsgBox "Hello World!"
End If
If i = 5 then
 Show "Hello World!"
End Si

Loops

For

For i = 1 To 5 Step 1
 j = 2 * d + 1
Next
For i ranging from 1 to 5 with a step of 1
 Multiply j by 2 and it add 1
End for

Do Until

Do Until i = 5
 i = i + 1
Loop
Until such time as i = 5
 Increase i of 1
End up 'that

Do While

Do While i < 5
 j = 2 * j + 1
Loop
As long as i is less to 5
 Multiply j by 2 and it add 1
End so that

Colors (on Excel)

Range ( "A1" ). Interior . Color = RGB ( 255 , 0 , 0 )
Color the background of the cell A1 in red
Range ( "A1" ). Font . Color = RGB ( 255 , 0 , 0 )
Color the text of the cell A1 in red

References

  1. ↑ “http://msdn.microsoft.com/vba/default.asp” ( Archive • Wikiwix • Archive.is • Google • What to do? ) .
  2. ↑ ( en ) ” Howdy! What’s up with VSA?  ”  [ Archive ] , Dr. eX’s Blog , October 3, 2005.
  3. ↑ ( en ) ” WWDC: Microsoft updates Universal status of Mac apps [ archive ] , Macworld  [ archive ] , (Accessed May 25, 2007 ) .
  4. ↑ Pierre Dandumont, “Office 2011: October 26, with the VBA and Outlook”  [ archive ] , Tom’s Hardware EN, September 29, 2010.
  5. ↑ ” Office Dev Center – VBA improvements in Office 2016 [ archive ] , on dev.office.com (accessed September 12, 2016 )
  6. ↑ Michel Dumais, “Antidote, Office 2008, the” SP1 “and the return of” VBA ” ‘ ( Archive • Wikiwix • Archive.is • Google • What to do? ) ,, On mac.branchez-vous.com .
  7. ↑ Working with Excel 4.0 macros  [ archive ]
  8. ↑ Paul Lomax, VB & VBA in a Nutshell , O’Reilly & Associates Inc. , 1998, p.  635 ( ISBN 1-56592-358-8 ), p.  6 .
  9. ↑ “OpenOffice.org 3.0 available in Beta”  [ archive ] , generation-nt.com, May 17, 2008.