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

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:

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

Bibliograp

hy

Microsoft Office 2000/Visual Basic Programmer's Guide

http://msdn.microsoft.com/en-us/library/aa164510(office.10).aspx#

Special Procedure Types

Bibliography

Microsoft Office 2000/Visual Basic Programmer's Guide

http://msdn.microsoft.com/en-us/library/aa164510(office.10).aspx#