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…

  01  Dim bIsBMW as boolean
  02  Dim szRegistrationNumber as string
  03
  04  szRegistrationNumber = "Some Car Reg"
  05  ' Here we pass in an argument of Car Reg type as szRegistrationNumber
  06  bIsBMW = isBMW(szRegistrationNumber)
  07
  08  ' This function is given a Car Reg variable, it then check against a
  09  ' database to see car type and return true or false to calling routine.
  10  Function isBMW(byval szReg as string) as boolean
  11    Dim bCarIsBMW as boolean  
  12    ' Code to check for bmw
  13    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.

• Class Module

  A Class Modules enables you to code in an object oriented style thus enabling you to build robust modular programs

  They are also great for managing complexity and indeed delivering projects that seem impossible to those who do not understand Object Orientation.

  Class Modules are explained in detail here.

• Consolidate Data

  This is more a process rather than a feature of VBA, but if you need to get data from various sources into one place, this video will show you how to do that.

  A good consolidation macro can save you tons of time, getting you out of the office earlier or making you look like a hero with all the work you crank out 🙂

• Data Type

  This is the “type of” variable, String (text), Double, Integer, Long, Single (Number), worksheet, range ,form

• Data Hiding

  This is explained in detail on the data hiding post.

• Developer Ribbon

  Following Video will show you how to enable the developer ribbon for VBA.

• Macros

  This first one isn’t alphabetical order I know (the rest are though), but this is where a lot of you may have started, the thing is, some seasoned pro’s still use it now and again, either to get something out in a quick and dirty manner or, if they want to get to the API without having to look up the documentation and then adjust the code after the fact.

  In any event check out the following two "Macro Recording" videos.

  This one takes you through an example of recording a macro, then editing the code to maket it useful for regular running.

The following video demonstrates importing some text data using a macro and why I used a macro, then using the information from the macro I rewrite the code more concisely.

• MsgBox – Excels MessageBox function

  The MsgBox enables you to communicate information to your program user and or request that they take an action to influence the program flow.

  Click to read the complete MsgBox Tutorial on that page.

• 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 …

  • One function one task
  • Data Hiding
  • Declare All Variables (that you can) Locally
  • Use a naming convention – Hungarian for example
  • Pass variables between functions using arguments.

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

• Object Oriented Programming

  Have a look at this first post on Object Orientation.

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

• 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…

  01  Dim rngOther as Range  ' Declare a variable of type range
  02  ' Assuming the code is contained within the workbook
  03  ' 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
  

• Visual Basic For Applications

  What is VBA and why should you learn it find out more at All about VBA.

• VBA Project

  This is part of the Visual Basic For Applications IDE, More details at VBA Project.

• Variable

  This provides a similar function in a program to the memory key in a calculator, only difference is that you can have an unlimited amount of variables in a program.

  Variables represent a piece of computer memory that you have reserved to store a number or text or some form of object.

  Examples of variables, declared in various languages are below.

  01  dim strLastName as string  ' LastName as Text in VBA
  02  
  03  strLastName = "Johnson"  ' Assigning a value to the variable
  04  msgBox strLastName  ' Displaying the contents of the variable
  

• Vba Macro Editor or VBA IDE or Integrated Development Environment

  This is where all you macro code is written, This will be explained in detail elsewhere on the site.

  Following Video will show you how to setup the VBA IDE.

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

  01  Dim wbk as Workbook
  02  ' Notice we access the workbooks (plural) collection to get
  03  ' a reference to the workbook (file) c:\data\workbookname.xls.
  04  set wbk=workbooks.open("c:\data\workbookname.xls")
  05  wbk.Worksheets(1).Range("A1").Value="BusinessProgrammer.com"
  06  Msgbox "workbook opened"
  

• Worksheet

  This is the page or TAB in the workbook and is most commanly called a spreadsheet it has approx 65,536 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.