Chapter 6: VBA Coding- Objects/Variables/Data Types

        VBA is the language used throughout the Office platform as well as other products, including Visio, AutoCAD. This chapter will prepare you for creating (not recording) your own macros, extending your ability to create truly useful applications with MS Word. The study of this language should begin with how data is manipulated and referred to.

The References Dialog Box

       The first thing that you need to do before you work with VBA objects that are opened by an Office application, you should set a reference to the application that supports those objects by using the References Dialog Box. To open this dialog box, go to the VBE platform, then click on Toolsà References on the toolbars section on top. The References dialog box choices used in the MWEP is seen below:



          You can use another application's objects without setting a reference in the References dialog box by using the CreateObject or GetObject function and declaring object variables as the generic Object type. If you use this technique, the objects in your code will be late-bound, and as a result your procedure will not run as fast.

VBA Objects

            A VBA object represents an item that can be manipulated which has certain properties or attributes that describes it. These objects can perform actions, known as methods.

            A class is a template from which an object is created. Many different objects can be created from a single class and these objects are said to be instances of the class.

            An example of objects can be found in a typical patient's chart:

  • John Doe is white, Hispanic, and divorced.
  • John Doe has an age, his vital signs include a BP, P, R, weight, and height.
  • John Doe has a medical history, surgical history, a brief social history note, and a family history.
  • John Doe has lab work done a week ago, including a sodium level, a platelet count, a PSA, and an ANA.

            This information can be used inside tables, inside consult paragraphs, and in a whole host of other medical reports. With objects, properties and methods VBA can do almost anything.

            VBA has a few objects, augmented by the hundreds found inside MS Word itself. In the example above, Selection.TypeText Text:=" " object is provided by Word and represents the area of a document that has been highlighted, or selected. Word can not only set properties, but retrieve them. For example, the line below can yield what the highlighted area states.

      MsgBox Selection.Text

             The above line might pop up a phrase like- "This world is round!". Almost all of your VBA code will involve working with objects; it is important to understand a few general rules:

  • Objects are items to be manipulated 
  • They have properties and methods
  • Properties are attributes of the object; they describe it .
  • Methods are actions that the object can perform; they do something

             To do anything that is very useful in VBA, we need the ability to keep values around, not just in object properties, but also in some form of storage. This is where variables come in, a way to store data, and the next section will explain what they are and how to use them.THE MS OFFICE EMR PROJECT

VBA Variables

             Variables are found in most programming languages, and serve as a place to store information which can then be used throughout your program to represent the values that have been assigned. The reason to use variables are several:

  • A variable name is often shorter than the referred object data.
  • It can be easily and efficiently reused in an application without having to retype the vast amount of data to which it refers to.
  • You can change a variable to refer to other objects while your code is running.

             Examples of global variables that I've used in the MWEP include the following:

  • act (a selected patient's account number)
  • lun (selected patient's lookup name, which is part last name, part first name, and date of birth)
  • attgname1 (selected physician name)

             The first thing that you need to do is to declare your variables, either in the block, procedure, or module level.

 Variables should always be defined with the smallest scope possible. Global (Public) variables can increase the use of memory and make the logic of an application extremely difficult to understand. Global variables also make the reuse and maintenance of your code much more difficult. In a VBA application, global variables should be used only when there is no other convenient way to share data between forms. When global variables must be used, it is good practice to declare them all in a single module, grouped by function.

             In the MWEP I declare global module variables in the modGlobals module. Here is an example from the MWEP where variables are declared locally in both the block and in the procedure levels:

             The use of variables usually takes three steps:

  1. Declare your variable by using the Dim keyword followed by the variable type (although you can leave the type blank).
  2. Use the variable to attach a value.
  3. Retrieve the value by invoking the variable.

             It is preferable that you declare the type of variable that you are dealing with, since your program will be optimized with the exact amount of memory allocated and will generally run faster than if you leave it undeclared.

             When you declare a variable, remember that:

  • You can force VBA to require explicit declaration be placing the statement Option Explicit at the very top of your code module, above any procedure declaration. With this statement in place, you would receive a Compiler Error - Variable Not Defined message when you attempt to run the code.
  • Must begin with a letter
  • After starting with a letter, can be made of letters, underscores, and digits in another order.
  • Cannot have a period.
  • Can have up to 255 characters.
  • Must be unique inside of the event (or procedure, function or module (we will learn what these things are)) it is used in.
  • Don’t use the variant type variable. Likewise, although VBA does not require you to explicitly declare your variables, if you don't declare a variable using the Dim statement, VBA will automatically declare the variable for you the first time you access it and render it as a variant type variable. Variant type variables significantly slow the execution of your code.
    Don’t use an object type variable. The Object data type can store any type object, such as a Range or an application object. If used, the performance implications of using the Object data type are more serious that those of the Variant data type. If you use the Object type, VBA must determine at run time what properties and methods that object supports. This process is called late binding, and can be vastly slower (up to 200 times slower as per one reference) than early binding (i.e. when declared explicitly).

             Here is an example of early binding of an object type variable:

Dim wdApp As Word.Application
Set wdApp = New Word.Application

             Here is an example of late binding of an object type variable:

Dim objApp As Object
Set objApp = CreateObject("Access.Application")

             These are easy programming concepts, and you will be using them with any EMR solution.

 Data Types

String A string is a character or a combination of characters that constitute text of any kind and almost any length- fixed-length strings range in length from 0 to approximately 63K characters and dynamic strings range in length from 0 to approximately 2 billion characters. A String can include letters, numbers, spaces, and punctuation.
Boolean Denotes a data type with only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers.
Byte Denotes a natural number that ranges from 0 to 255 (no decimals). Byte variables are stored as single, unsigned 8-bit (1-byte) numbers.
Integer Denotes a natural number to declare 2-byte whole numbers in the range -32,768 to 32,767 (no decimals). The percent sign (%) type-declaration character represents an Integer in VBA.
Long Denotes a 4-byte integer ranging in value from -2,147,483,648 to 2,147,483,647.
Single Denotes a single-precision floating-point variables as 32-bit (2-byte) floating-point numbers, ranging in value from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values.
Double Denotes a data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
Currency Denotes a data type is used to deal with monetary values. It is a data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important.
Date A data type used to store dates and times as a real number. Date variables are stored as 64-bit (8-byte) numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time.
Object A data type that represents any Object reference. Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Variant data type A special data type that can contain numeric, string, or date data as well as the special values Empty and Null. The Variant data
Variant A data type that has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text. All variables become Variant data types if not

explicitly declared as some other data type.

Naming Conventions For Variables

Variable Data Types names should also follow a certain standard, as displayed below, with a 3 letter code followed by a capital letter and then the rest of the object name.

  • bln = Boolean

  • byt = Byte

  • col = Collection object

  • cur = Currency

  • dtm = Date (Time)

  • dbl = Double

  • err = Error

  • int = Integer

  • lng = Long

  • obj = Object

  • sng = Single

  • str = String

  • udt = User-defined type

  • vnt = Variant

Scope of Variables

There are 3 main types of scopes possible with variables:

Local Applies only to variables. They are visible only in the procedure from where it is declared.
Module-Level Visible from any code In the same module, s.a. a UserForm module. Controls that can contain module-level code include click buttons, list boxes, and drop down objects.
Global Contains custom macros and functions that are used from any code within your project.

A Review of the MWEP modGlobals Variable Declarations

            The modGlobals module lists a full paragraph of public variable declarations. Notice how this Class Module sets these variables beginning with the word “Public”, making their scope as wide as possible. The first line sets the most important constant, which is the local backend table location where the local parameters of the front-end are kept, including certain passwords and settings. The default location of the back-end tables is stored as one of these settings that are changed depending on which button you choose when the splash screen is shown.

Option Explicit

Public Const conDBpath As String = "C:\ZMDSBE.mdb"

Public conDBpath2 As String, yy As String, tt As String, uu As String, vv As String, ww As String

Public XX As String, xxx As String, zz As String, zzz As String, yyy As String, strTitle As String

Public Const conDebug As Boolean = True

Public lun As String, ptn As String, attgname1 As String, acctno As Long

Public street1 As String, age1 As Long, sex11 As String

Public citystatezip1 As String, NoOfRecords As Long, fvst1 As Date, MyTime, MySecond

Public Phones1 As String, ptaddress1 As String, racee As String, tt11 As String

Public db As DAO.Database, rs As DAO.Recordset, SQLStmt As String

Public xlapp As Object, schdateD As Date, act, aa As String, age2 As String

Public dd As Date, dd2 As Date, ii As Long, I As Long

Public ftit As String, worddoc1 As String, pdfdoc1 As String, szDialogTitle As String

Public lngDBTimesOpened As Long, pty As Property, lngProfileTimesOpened As Long


            When you choose any one of this form’s buttons, you can either keep the current location of the back-end patient files or link to a different back-end location. The constant variable that cannot be changed is the conDBpath; the variable denoting the actual “working” back-end variable is the conDBpath2, set as a string type in line 2 of the code in the modGlobals declaration paragraph, and assigned a value by the splash screen form with code such as seen below:

conDBpath2 = "Z:\ZMDSBE.mdb"

            At various points throughout the program, code checks to see if this assignment has been done, and if it hasn’t (i.e. it’s “nul and l”), then it sets it to the default local folder:

If IsNull(conDBpath2) Or Len(conDBpath2) < 2 Then

    conDBpath2 = "C:\ZMDSBE.mdb"

End If

     Other global variable setting lines of interest that are constantly used in the MWEP are:

  • Global string parameters:

Public lun As String, ptn As String, attgname1 As String

  • Global numerical and date parameters:

Public dd As Date, dd2 As Date, ii As Long, I As Long

  • Global parameters used in establishing a recordset:

Public db As DAO.Database, rs As DAO.Recordset, SQLStmt As String

  • Global patient information parameters used in reports:

Public street1 As String, age1 As Long, sex11 As String

  • Global string variables used in arrays:

Public yy As String, tt As String, uu As String, vv As String, ww As String

Public XX As String, xxx As String, zz As String, zzz As String, yyy As String, strTitle As String

Applying this Tutorial to Your EMR Project

          At this point, you should:

  • Set up the references dialog box.
  • Set up your global variables, applying the naming convention wherever possible. Note that many of my variables don’t follow the naming convention, although some do. This is because occasionally I prefer to use short phrases (s.a. “yy”) for my variables





























Microsoft Office 2000/Visual Basic Programmer's Guide


Special Procedure Types




Microsoft Office 2000/Visual Basic Programmer's Guide







Back to Top
Back to Top
Back to Top
Back to Top
Back to Top
Back to Top
Back to Top