• What is VBA?
• What is the origin of vba?
• What is the difference between the VB and the VBA?
• VBA for Excel, VBA for Access, VBA for Word, etc.
• Macros and procedures
• Macros and Formules
• What is an Add-In?
• Can we open a working file carried out from an add-in?

What is VBA?

VBA is the initials of " Visual BASIC for application ". It is a programming language which makes it possible to the user to conceive and to automate common tasks such as objects, methods, properties, events, instructions and functions in applications like Excel or WinWord for example.

For example you will find below 2 procedures for conversion in small letters of a selected text zone capital letters or vice-versa.

'==================================================== 'Conversion texte en minuscule '==================================================== Sub Lower_Case() On Error Resume Next ActiveSheet.Unprotect If ActiveSheet.ProtectContents = True Then MsgBox "The contents of worksheet are protected." End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range On Error Resume Next 'In case no cells in selection For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = LCase(cell.Value) Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub '====================================================== 'Conversion texte en majuscule '====================================================== Sub Upper_Case() On Error Resume Next ActiveSheet.Unprotect If ActiveSheet.ProtectContents = True Then MsgBox "The contents of worksheet are protected." End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range On Error Resume Next 'In case no cells in selection For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = UCase(cell.Value) Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

Visual BASIC for applications (VBA) is the tool, which you can use to adapt Excel, create your own functions and to automate your repetitive tasks. From Excel 97, all VBA programming is done in English. This has as a principal advantage to be able to employ the procedures without worrying about which version of language on Excel the user will employ. The disadvantage with the use of English as programming language is that in certain cases some problems can appear in the characteristics of the national language such as dates formats and characters numbers of separator of list.

To program in Excel VBA, you must as a preliminary know very well Excel functionalities, if not you will waste your time to reinvent procedures, which did not really take place.

What is the origin of vba?

Visual BASIC for application (VBA) is a descent programming language of BASIC developed in the beginning of the Sixties. BASIC was employed intensively on the generation of the office computers, which did not have the capacity to run to other more sophisticated languages.

The following versions of the BASIC borrowed devices from other languages during years so that the name became less and less suitable. Visual BASIC for Windows (VBW) was presented in 1992 like control records for Windows, providing commands to handle the windows, dialogue boxes, indicators, menus, toolbars and so on.

For the beginning, Excel VBA was used to automate tasks, to control the computer to do what the user did, but in a more effective and rapid ways by macro-commands. Now it can be used for developing applications for Excel.

What is the difference between the VB and the VBA?

VB (visual BASIC) is a language for programs creators whereas VBA (visual BASIC for application) is a language for applications creators (Excel, Access, etc.).

The programs such as for example Excel or Access have already an environment with hundreds of integrated functions. With VBA on Excel for example, the costs of applications design are reduced since there is not has to reinvent the entire environment.

VBA for Excel, VBA for Access, VBA for Word, etc. ?

Although VBA is common structure through applications (Excel, Word, ...), by its nature, the majority of the written procedures for an application do not work for others.

There is no cell except in Excel for example. Thus while this document can cover some general rules, the major part of the detail will apply only to the use in Excel. In the same way, although there are similarities in rules syntax, the functions and instructions are the same, but the objects, properties and methods differ appreciably.

Macros and procedures

The macro is a recording of VBA code which is used to automate a whole series of actions carried out directly from Excel.

The use of macro is generally not very effective. In a procedure one declares the variables, reduced the instructions number and thus more effective to the execution.

VBA-Excel is a programming language which it is possible to develop applications by creating procedures and integrate VBA programs into the interface of the Excel application.

Macros and Formulas

Much users Excel are familiarized with the formulas, which can be inserted into cells to carry out calculations. There is large numbers of integrated functions available immediately in Excel.

VBA can be employed to create personalized functions to be used in the formulas just like the integrated functions. It is important to note, although VBA functions can be employed like the integrated functions of Excel, the integrated functions of Excel cannot be employed in VBA.

For example you will find below the calculation function " Patm " written in VBA giving the atmospheric pressure according to altitude.

'============================================================ ' Pression atmosphérique en Pascals ' - Z = Altitude en m '============================================================ Public Function Patm(Z As Single) As Double Patm = 101325 * ((1 - (2.25577 * 10 ^ -5) * Z)) ^ 5.25588 End Function

According to the formula :

• A (Z) = Altitude in m
• Pb (atm) = Atmospheric pressure at A altitude in Pa
• 101325 = · Atmospheric pressure on the sea level

VBA has its own whole of functions, of which some are similar in the functionality of those used in the formulas, but there are significant differences in the manner of those which are employed and the rules under which they function.

What is an Add-In?

An Add-in is a data-processing program which itself integrates the environment of an applications. There are various types of add-ins, which can be added to Excel or WinWord for example.

A add-in file on Microsoft Excel is a special workbook with the file format of " xla ". A add-in file can contain working sheets, diagram sheets, macros and functions written in VBA

The macros and the functions in a add-in file add optional commands into Microsoft Excel environment and can prevent the user directly to look at or to publish them.

How to install an add-in on Excel?

An add-in file can be installed by opening the file as if you must open a working file "*.xls" in Excel. You can also install the add-in in the library directory.

When you install an addin, the specific functions of the program are at the disposal of the user, and the personalized menus in the addin are also added to the existing menus on Excel.

It is preferable to install the Addins files into Excel library.

Excel is installed in theory by defect in the repertory C Drive:

• For Excel 95 copy Addins in c:\MSOffice\Excel\Library
• For Excel 97 and 2000* copy Addins in c:\Program Files\MSOffice\Excel\Library

Can we open a working file carried out from an add-in?

Yes, if you want to see the results or to print the working file. But if you want to recomputed the working sheet on Excel without the use of specific add-in to the working file, you will lose the results concerning the functions written in VBA

Last update: