VbaFin.com

Visual Basic for Financial Professionals
Home      VBA Syntax
VBA code consists of procedures which use variables to achieve a desired objective. The code can be placed in Modules, Class Modules, UserForms and under the specific objects of the particular application (i.e. Sheets and ThisWorkbook in Excel, ThisDocument in Word). In most cases, however, organizing your code in Modules would be quite sufficient.

These are the types of Procedures:
  • Subroutines – Subroutines perform actions and can change the values of a calling Subroutine (or Function) if their parameters are passed by reference (ByRef)
  • Functions – Functions are quite similar to Subroutines except that they return values (a single value or an array of values) and can not be used to change properties of the respective application (i.e. you can not change the value of a cell or activate a sheet in Excel from a Function)
  • Properties – There are three properties used in Classes – Let, Get, and Set. Using classes with VBA is outside the scope of this material
Procedures like variables can be local or global in scope. Local procedures can only be accessed from the module they reside. Local variable can be accessed only from the procedure they are part of.



To achieve the desired objective with the code you would generally need to check for conditions, iterate through arrays and collections or until a condition is met, and assign values to variables. Here’s an example using If … Then and Select … Case constructs to check conditions, Do … Loop and While … Wend constructs to loop until certain conditions are met.



And finally to efficiently process data you might need to use Arrays, Structures and Collections. Arrays in Visual Basic can be static or dynamic. Here’s an example.