VBA Chapter 2 of 24: Programming Language in Excel

NOTE: This section  is about programming vocabulary. If you are looking for code (the words used to talk to Excel in VBA) go to VBA sections 10 to 25.

In the universe of VBA for Excel (Macros) there is some vocabulary that you might want to adopt so that others can understand you and so that you can understand the others.

Excel Macros: Short for "macro commands" . A series of instructions performed by Excel rather than you.

VBA Procedure: A set of instructions that you want the computer to execute. In the old days when computers were small and slow and VBA was not well developed these instructions were called Excel MACROS.

Code: When you are writing instructions in VBA it is said that your are coding or writing code. Show me your code and I'll show you mine. All over this website pieces of code are presented in bold green font and can be copied/pasted from this website directly into the Visual Basic Editor.

To write VBA code you will be using 5 types of components: objects, properties, methods, functions and statements

Objects: They are the building blocks of your Excel projects they are (among others) the Application (Excel), the Workbooks, the Worksheets, the Cells and the Ranges, the Charts, the Drawings, the Controls (command buttons, text boxes, list boxes, etc.) and others.

Properties: Think of the verb to be. All objects have properties that you can set and modify through VBA. The font can be bold (Selection.Font.Bold = True), the sheet can be visible or not (Sheets("SuchandSuch").Visible = True), a workbook has a name (ActiveWorkbook.Name), a cell can carry a value or a formula (Activecell.Value = 10, Activecell.Formula = "=A1+B2"), etc. The set of properties differ from one object to the other. A worksheet cannot be bold and   a workbook cannot carry a formula. Excel will tell you when you are trying to use a property that doesn't exist for the object that you are working with.

Methods: Think of the verb to do. You might want an object to close, to open, to be copied or pasted, etc. (ActiveWorkbook.Close, ActiveCell.Copy). Again Excel will tell you when you are trying to use a method that doesn't apply  to the object that you are working with.

Functions: You can and you will use all the Excel functions within your VBA code but you can also use VBA functions like UCASE, LCASE, NOW(), etc. (Activecell.Value=NOW())...

Statements: IF..THEN, DO...LOOP, FOR...NEXT, WITH...END WITH, EXIT FOR, EXIT DO, EXIT SUB

Events: For a procedure to start and be executed an event must happen. One event that everybody knows about is the click on the button. Other events include opening a workbook, activating a sheet, modifying the value of a cell, etc... .

A VBA project can comprise 4  types of components: a workbook, worksheets, modules and userforms

Workbooks: A workbook is an Excel file (something.xls) also called spreadsheet. The object "ThisWorkbook" is the workbook within which the macro is created. The code ThisWorkbook.Close will close the workbook within which the active macro is running.

Worksheets: An Excel workbook can comprise 256 worksheets each having 65,536 rows and 256 columns. In a future version of Excel there will be 1,000,000 lines per sheet.

Modules: It is kind of a file in which you store most of your VBA procedures (macros). Modules are created and named in the Visual Basic Editor.

UserForms: They are specialized sheets that you create to allow the user to submit parameters (values). They are used extensively when working with VB but much less with VBA for Excel. They are also used extensively in databases, accounting, manufacturing and sales  programs because there are no regular sheets in these environments. When you work with Excel it is so much easier to ask you users to submit parameters on a regular worksheet.

And then a final word

Controls: They are the command buttons, the check boxes, the labels, the text boxes, the list boxes, the option buttons and other gizmos that you put on userforms or worksheets.

Example:

Sub proTest ()
  Range("A2").Value= 2
  ThisWorkbook.Save
  ThisWorkbook.Close
  Application.Quit
End Sub

These four lines constitute a procedure (macro) named "proTest" . Always name your macros starting with the prefix "pro" and a first capital letter for any significant word comprised in the name like "proInfo" , "proRawData" , "proWhatever" . You will see later how important this prefix and capital letter habit becomes. 

"Range("A2")" , "ThisWorkbook" and "Application" are objects, "Value" is a property and "Close" and "Quit" are methods.

This VBA procedure will be assigned to a button (control) and when a user clicks on it (the event) the VBA procedure will run.

This VBA procedure tells the computer that when the user clicks on the button, cell "A2" takes a value of 2, the workbook is saved and closed and so isExcel.