Banner Image About Visual Basic Lesson Information User Information guide/help on the site Return to Visual Basic Home Page Go to Previous Page Go to Next Page This guide provides some rules/standards around VBA governance



VBA Governance

VBA Governance
Whats VB About
Objects
Data Types
Labels
Decision Making
Properties
Loops
Case Statements
Message Boxes
Error Handling
Frame Options
Combo Boxes
Scroll bars
Local variables
Procedures
File List
Arrays
Records
File Handling
Timer Controls
String handling
Menu Control
Database Programming
Common Dialog Control
Governance
Standards
Peer review

Many organisations face a conundrum of issues when developing VBA applications. Often VBA takes an unstructured approach to programming making it very cumbersome to understand and often is left untouched due to fear of breaking something in the engine.
This lecture explores the route of adding structure and supportability to your VBA framework with a set of rigid rules around governance


VBA Governance

Lets start from the beginning when VBA was first used it was a business language to engage business users to write their own macros. It was never designed for the geeky/competent programmer, Microsofts objective was quite clear loosely typed language that would embrace a novice user with the ability to write sequemtial coding. However, the novice has embraced the language and inherited the lack of standard and technique for the discipline.

You will find in VBA you do not need to explicitly define variables or we have variant types that can almost take any data type. However, with this flexibility comes many issues

  • Inefficient coding - sequential coding, use of no programming constructs
  • Difficult to understand - engine becomes a 'black box', with users only understanding what they think the program does as opposed to what it actually does
  • platform dependent - If there is no enforcement of declaration of variables, if the program is moved to another machne may cause errors
  • Advanced VBA Users - an undisciplined approach creates a complex unsuitable solution that is difficult to support and understand and provides a fear factor to change for the organisation

Key Principles - a must!

When writing VBA code there must be an underlying objective
  1. VBA must NOT BE WRITTEN by a MACRO RECORDER
  2. The macro recorder is a sequential coding generator - used to allow business users to write macros. This works outside Excel/Access object model, and therefore results in hard coded literals and values.

    There are no iterations and are considered to be very repetitive and innefficient, and can result in most cases redundant coding. Its a great and clever tool, but also an hinderence in terms of reputation and standard within VBA. From a strategic development view should AVOIDED at all costs.

  3. VBA must be clear and concise
  4. Less is more, in VBA instance this is a pre-requisite, and often repetitive code in a non modular context is seen as complexity.If its broken down in its simpliest form about 33% of the coding can be removed, and the code optimised to run faster. Also, users need to get away from process driven task, rather than micro managing each task, more thought should be given in terms of what the programming is trying to achieve. In the real IT world 90% is analysis and 20% is developement is a good rule of thumb.

    Programming constructs are the enabler for this, such as loops, case statements, iterations etc

  5. VBA must not be ambiguous and use of comments and declarations wherever
  6. Lets take a simple program

    Private Sub Command1_Click()
      Dim x 
      Dim y
      x = "Do you wish to exit the application?"
      y = MsgBox(x, 4,"Exit Application")
      If y = 6 Then
         MsgBox "Goodbye"
         End 
      End If
    End Sub
    
    Now lets standardise it
    Private Sub s_cmdEnd_Click()
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'Author: Vas Rabani
    'Date         : 31/03/2011
    'Version      : 1.0
    'Description  : Procedure to terminate program based on user input
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    '***Declare variables***
    
      Dim l_str_Msg As String
      Dim l_int_response As Integer
      'declare variable to store function result
      
      '*** MAIN PROGRAM ***
      
      l_str_Msg = "Do you wish to exit the application?"
      'Alert message to the user, store message in variable
      l_int_response = MsgBox(l_str_Msg, 4,"Exit Application")
    
      'Note vbNo = 7
      'vbYes = 6
      If int_response = vbYes Then 'if user entered Yes
      'we can use... if response = vbYes
         MsgBox "Goodbye" 'display message
         End    'End the application
      End If
      
       '*** END PROGRAM ***
    End Sub
    

  7. VBA must have error handling capabilties so the program has somewhere to go when there is a error
  8. Private Sub s_cmdEnd_Click()
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'Author: Vas Rabani
    'Date         : 31/03/2011
    'Version      : 1.0
    'Description  : Procedure to terminate program based on user input
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Const C_STR_PROCESS As String = "s_cmdEnd_Click"
        Const c_CLASS_STR_NAME AS String = "frm_Search_Query"
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    '***Declare variables***
    
      Dim l_str_Msg As String
      Dim l_int_response As Integer
      'declare variable to store function result
       On Error GoTo ERR_f_Determine_Range_Size
    
        
      '*** MAIN PROGRAM ***
      
      l_str_Msg = "Do you wish to exit the application?"
      'Alert message to the user, store message in variable
      l_int_response = MsgBox(l_str_Msg, 4,"Exit Application")
    
      'Note vbNo = 7
      'vbYes = 6
      If int_response = vbYes Then 'if user entered Yes
      'we can use... if response = vbYes
         MsgBox "Goodbye" 'display message
         End    'End the application
      End If
      
       '*** END PROGRAM ***
    End Sub
    
    EXIT_f_Determine_Range_Size:
    
        On Error Resume Next
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        '??[Exit procedure and trace the exit process...
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Exit Function
    
    ERR_f_Determine_Range_Size:
    
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        '??[Load the Error Procedure components, Description and Number...
        MsgBox Err.Number & ":" & Err.Description
    
        Resume EXIT_f_Determine_Range_Size
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
  9. VBA must contain structure with the use of standardised templates for procedures, functions and properties etc
  10. VBA must contain naming conventions so the program reader will understand the use of the variables the data type being employed and the objective for its use
  11. VBA must be coded with a view for simplicity - complexity should be driven by the relevant VBA object model
  12. VBA must be sourced from reliable knowledge base sources - the nature of VBA allows so many ways to do a particular task. Users often troll the internet for code downloads etc. Internet should be used as a loose guide in terms of solving a task, but the implementation must be a structured end goal.

Naming conventions

You are not authorised to use this material without the written permission of the author. The contents of this material must not be distributed or used for any teaching purposes. Failure to comply may result in criminal prosecution. Go to Previous Page Go to Next Page Contact Vas Rabani for any comments on the website or queries