Microsoft Office VBA Glossary

Argument

Subroutines or functions need to be able to communicate with one another.

The wrong way to do it is by using global variables.

The correct way is to pass an argument, this ties in with the concept of data hiding, as an argument clearly shows how the function relates to other code in your project.

Therefore you the programmer know that you can change any variables or code outside the routine and you will not effect the contents of the routine, provided you have not changed the argument values.

Example in VBA…

01Dim bIsBMW as boolean02Dim szRegistrationNumber as string0304 szRegistrationNumber = "Some Car Reg"
05' Here we pass in an argument of Car Reg type as szRegistrationNumber06 bIsBMW = isBMW(szRegistrationNumber)
0708' This function is given a Car Reg variable, it then check against a09' database to see car type and return true or false to calling routine.10 Function isBMW(byval szReg as string) as boolean
11Dim bCarIsBMW as boolean12' Code to check for bmw13 isBMW = bCarIsBMW
14 End Function
15

Break Point

This is used when debugging your code. You can put in a temporary "break point" by clicking on the left margin of your code editor or put in a permanent one by typing "STOP" on a line of your code.

The practical use of this is to examine variables or behaviour of your application in detail within a specific function or subroutine, you want the code to run at full speed until it reaches this point and then it stops.

Data Type

Data Hiding

Excel Object Model

This is the vba blueprint to manipulate Excel in code. Using the Object Model enables you to cleanly and clearly instruct Excel or any Microsoft Office Application to do Stuff.
An example of a simple “Object Model” in Excel is contained in this post under the heading Excel Specific Objects.

Excel Range and Range Object

An Excel range refers to to an area of the spreadsheet, spreadsheets are layed out in a grid format with columns labeled as letters and rows labeled as numbers.

So "B20:E32" refers to the "range" between column 2 row 20 and column 5 row 32

A range object is the vba code equivelent of this range and it is used to "get a reference" to this range and them manipulate it with code.

Example of an excel range object is below…

01Dim rngOther as Range' Declare a variable of type range02' Assuming the code is contained within the workbook03' that it refers to, then you can use the variable "ThisWorkbook"04 set rngOther = ThisWorkbook.worksheets(1).Range("B20:E32")
05 rngOther.Font.Size = 25

Excel Workbook

This is the proper (Microsoft) name for an Excel file, it is refered to in the Excel object model. An Excel workbook can have an unspecified number of Worksheets/tabs (limited only by system resources).
The vba object name for this is workbook which is part of the workbooks collection.
For Example..

Excel Worksheet

This is the page or TAB in the workbook and is most commanly called a spreadsheet it has approx 65000 rows and 256 columns up to Excel 2003 and 1,048,576 rows and 16,384 in Excel 2010 and above. The vba object name for this is worksheet or sheet.

Procedural Programming

This can be loosely defined as a program that starts at the beginning, runs and then ends sort of like executing instructions in a list, as opposed to event driven programming and or object oriented programming. It can also be used however to define a basic set of rules you need to follow on the journey to writing good code. The name says it all PROCEDURAL programming i.e
put you code in procedures (plural) as opposed to using one procedure that becomes 1000 lines long. It covers rules such as …

Object Oriented Programming

Object Model

In object oriented programming this is the blue print or architectural plan of the program that your are using. It is incredibly useful because when programs expose an object model for programmers to implement such as with Word, Excel or Access, you the business programmer can navigate it in code and get things done in a readable, easy, maintainable and concise manner.