Thursday, 22 December 2011

Recursion vs. Loops in VBA and SQL

Recursion, when I was first taught about it in our class rooms our professor mentioned, this is one topic which every student ask to repeat once again.

Though after a while in the business I am learning actually it’s not that bad too. But it’s just a concept which has to be used wisely. In the past the context I have used recursion is for file/directory searches and listing. But I was also taught to us in the context of factorial and mathematical concepts which could be achieved via loops.

Thus I am embarking in this post to figure out which is a better one to choose:

With recursion, though our mathematical models can be represented well in comparison to loops programmatically. The concept of recursion has some underlying concerns which also need to be taken into account while designing the programs.

The following concerns are sourced from MSDN (for further details please refer to the detailed MSDN documentation):

1. Limiting condition: This is the most important part of recursion and upon which many times my programs have crashed up. Design the recursion ending condition well for all scenarios of the input, if not you might end up into stack overflow error.

2. Memory Usage/Performance: This is one of the most important considerations to take into account while writing code for recursion. As the function/procedure calls upon itself each time the copy of local variable for each instance of execution is created on stack and the overhead of argument passing has to be taken.

3. Debug: Painful to debug in recursive codes.

So rather stick with Looping?? Well it’s an answer you as developer have to decide which route to take; personally I have used recursion for scenarios of file/directory listing arenas and mostly stick to the simple forms of looping for any other tasks of computations. But your comments are valuable for me in this space .. !!!

Below are the codes to illustrate the use of recursion in VBA and in SQL (too but only limited to 32 levels)

Option Explicit

Public Function recursive_fact(n As Integer) As Integer
    If n < 1 Then
        recursive_fact = 1
        Exit Function
    End If
    recursive_fact = n * recursive_fact(n - 1)
End Function

Public Function looping_fact(n As Integer) As Integer
    Dim jCount As Integer
    looping_fact = 1
    For jCount = n To 1 Step -1
        looping_fact = looping_fact * jCount
    Next jCount
End Function

Sub test_functions()
    Dim iCount As Integer
    Dim jCount As Integer
    iCount = 5
    jCount = 5
    iCount = recursive_fact(iCount)
    jCount = looping_fact(jCount)
    Debug.Print "Factorial of iCount: " & iCount
    Debug.Print "Factorial of jCount: " & jCount
End Sub
'Factorial of iCount: 120
'Factorial of jCount: 120
--Create a sample table
CREATE TABLE employee(
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    start_Date  DATETIME,
    region      VARCHAR(10),
    city        VARCHAR(20),
    managerid   INTEGER
--Insert some records into it
INSERT INTO employee VALUES (1, 'Jason' ,  'Martin', 5890,'2005-03-22','North','Vancouver',3);
INSERT INTO employee VALUES (2, 'Alison',  'Mathews',4789,'2003-07-21','South','Utown',4);
INSERT INTO employee VALUES (3, 'James' ,  'Smith',  6678,'2001-12-01','North','Paris',5);
INSERT INTO employee VALUES (4, 'Celia' ,  'Rice',   5567,'2006-03-03','South','London',6);
INSERT INTO employee VALUES (5, 'Robert',  'Black',  4467,'2004-07-02','East','Newton',7);
INSERT INTO employee VALUES (6, 'Linda' ,  'Green' , 6456,'2002-05-19','East','Calgary',8);
INSERT INTO employee VALUES (7, 'David' ,  'Larry',  5345,'2008-03-18','West','New York',9);
INSERT INTO employee VALUES (8, 'James' ,  'Cat',    4234,'2007-07-17','West','Regina',9);
INSERT INTO employee VALUES (9, 'Joan'  ,  'Act',    6123,'2001-04-16','North','Toronto',10);
--Verify the data
SELECT * FROM employee;
--Create the procedure
CREATE PROC usp_FindBoss(
   @EmployeeID int
 DECLARE @ReportsTo int
   @ReportsTo = managerid
     Id = @EmployeeID
     @EmployeeID AS Employee,
     @ReportsTo  AS Manager
   EXEC usp_FindBoss
--Execute the procedure
SELECT * FROM employee
EXEC usp_FindBoss 2

Note: For SQL Stored Procedures CAN call Stored Procedures & Functions(Both), but Functions CAN call Functions (Only).

Link1 (MSDN Recursion):

Wednesday, 21 December 2011

Variable Declaration – Sounds Easy in VBA..

Well its easy up to the point you like to keep it easy by single individual variable declaration, but when the smart ways kicks in even simple things don’t remain easy in VBA.

Well some time I myself got confused about some codes of what’s happening and what these symbols interpret. So I wanted to clarify some bits of variable declaration styles in this post of mine.

Combined Declaration:

When multiple variables are declared in one Dim statement, the types have to be individually defined for each of them using the “as” operator.

Data Type Symbol declaration:

This is another way of declaring variables whereby you are escaped from writing a whole big line specifying the data type a variable is declared of.In this you just use the specified symbols and works done!!
The following, lists the symbols used for the same:

! = Single Precision
% = Integer
& = Long Integer
@ = Currency
# = Double Precision
$ = String

Also along with this post I am providing some quick referencing links for the data types and their capacity with byte consumption to efficiently design your code.

1. Quick ref 1:
2. Quick ref 2:

The following VBA code illustrates the declaration styles.

Public Sub dataTypes_Declarations()

    '--Old ways of declaration

    Dim r As Integer
    Dim s As Double

    Debug.Print "--Old ways of Declaration--"
    Debug.Print "r is of type: " & TypeName(r)
    Debug.Print "s is of type: " & TypeName(s)

    '--Combined declaration BEWARE

    Dim i, j, k As Integer

    Debug.Print "--Combined Declaration --"
    Debug.Print "i is of type: " & TypeName(i)
    Debug.Print "j is of type: " & TypeName(j)
    Debug.Print "k is of type: " & TypeName(k)
    '--Declaration Shortcuts
    '! = Single Precision
    '% = Integer
    '& = Long Integer
    '@ = Currency
    '# = Double Precision
    '$ = String

    Dim a!, b%, c&, d@, e#, f$

    Debug.Print "--Declaration Shortcuts --"
    Debug.Print "a (!) is of type: " & TypeName(a)
    Debug.Print "b (%) is of type: " & TypeName(b)
    Debug.Print "c (&) is of type: " & TypeName(c)
    Debug.Print "d (@) is of type: " & TypeName(d)
    Debug.Print "e (#) is of type: " & TypeName(e)
    Debug.Print "f ($) is of type: " & TypeName(f)
End Sub


Performance & Memory – Large Collection vs. UDT Array in VBA

This article is focused on the use of “UDT” user defined types in VBA. I came across this while looking upon performance and memory consumption issues in VBA while dealing with large datasets.

As classes in object oriented concept provide a wrapper and functionality for one or more related elements. So UDT in VBA is also a wrapper mechanism where it can encapsulate the properties of an object, subtracting the custom functionality as provided by the classes.

The primary utility of an UDT is that, it’s a very light weight composition of object (similar in lines to struct in C#). Its more performance oriented and can be used for representing data series for stocks (encapsulating ISIN, Price, volume and more).

This UDT mechanism is commonly used in counter part of collection object because of the following 2 reasons:

1. VBA Collection contains nothing but variant data types, which, as you know, have one of the highest costs with regard to system resources.

2. Collection object uses a linked list storage structure that also adds to its overhead.

As illustrated in the code below a similar structure of data when implemented using user defined type (UDT) against classes, the performance was considerably higher with the UDT mechanism.

Main Module (Module.bas) :

Option Explicit
'--Declaration for getting millisecond count since system startup --
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

'--Define the Max Loop count for the interation to measure performance --
Private Const MAXCOUNT As Integer = 30000

'--User Type Defination--
Public Type StockPrice
    Price As Integer
    volume As Long
End Type

'--Routine creating and adding elements to array/collections
Sub compare_UDT()

    Dim stock_array(MAXCOUNT) As StockPrice
    Dim iCount As Integer
    Dim stock_collection As Collection
    Dim stock As clsStock
    Dim stTime As Long

    Set stock_collection = New Collection

    'User Defined Type
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        With stock_array(iCount)
            .Price = Rnd
            .volume = Rnd
        End With
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for UDT"

    'Class Objects
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        Set stock = New clsStock
        With stock
            .Price = Rnd
            .volume = Rnd
        End With
        stock_collection.Add stock
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for Collection"

End Sub
'--Results --
'31 Milliseconds for UDT
'171 Milliseconds for Collection

Class Object (clsStock.bas):

Option Explicit

Private m_iPrice As Integer
Private m_lvolume As Long

Public Property Get volume() As Long

    volume = m_lvolume

End Property

Public Property Let volume(ByVal lvolume As Long)

    m_lvolume = lvolume

End Property

Public Property Get Price() As Integer

    Price = m_iPrice

End Property

Public Property Let Price(ByVal iPrice As Integer)

    m_iPrice = iPrice

End Property 

Filter Unique items list in VBA

Many times I have been presented with this challenge to extract a unique item list from a pre-defined array containing non unique items.
For the purposes I tend to use a collection object in VBA and use its “key” element to uniquely identify my items being added to the collection and extracting it back to another unique array.

This functionality should be implemented as function, its a subroutine only for illustration purposes.
Sub uniqueItems()

    Dim repArray(10) As Integer
    Dim unqColl As Collection
    Dim unqArray() As Integer
    Dim iCount As Integer

    '--Allocate repArray
    repArray(0) = 2
    repArray(1) = 2
    repArray(2) = 2
    repArray(3) = 2
    repArray(4) = 2
    repArray(5) = 2
    repArray(6) = 2
    repArray(7) = 2
    repArray(8) = 2
    repArray(9) = 2
    repArray(10) = 20

    '--Filtering via Collection
    Set unqColl = New Collection

    On Error Resume Next
    For iCount = 0 To UBound(repArray)
        '--The identifying key has to be string
        unqColl.Add repArray(iCount), CStr(repArray(iCount))
        '--In case of refrential object variables can also use VarPtr for idnetifying unique address
    Next iCount
    On Error GoTo 0

    '--Resize the unique array based upon the unique items in the collection
    ReDim unqArray(unqColl.Count - 1) As Integer
    '--Populate the unique array
    For iCount = 0 To (unqColl.Count - 1)
        unqArray(iCount) = unqColl(iCount + 1)
    Next iCount

    '--unqArray Content
    'unqArray(0) = 2
    'unqArray(1) = 20

    '--Free up the space
    Set unqColl = Nothing
    Erase repArray
    Erase unqArray

End Sub

Tuesday, 20 December 2011

Pointers in VBA.. Huhh !!! (delegates in VBA)

“Pointers“ – aaah the word which is not pleasing to most of the programmers is a very powerful functionality in programmers world. (Rightly said power and pain goes along)

I thought working in VBA arena one would be safe from the world of pointers and I wouldn’t have to deal with it. But embarking on voyage to explore the object oriented nature of VBA and its support to OOP’s (Object oriented programming) I stumbled upon an article and resources in line of using pointers in VBA.

So keywords associated with pointers in VBA are as follows:

Note: All the Address values of pointers are stored in the “Long” data type

1. AddressOf: This functionality in VBA provides the addresses of objects (i.e. module, projects, but Not Classes), functions & procedures address.
2. VarPtr: Returns the address of a variable.
3. VarPtrArray: Returns the address of an array.
4. StrPtr: Returns the address of the UNICODE string buffer.
5. VarPtrStringArray: Returns the address of an array of strings.
6. ObjPtr: Returns the pointer to the interface referenced by an object variable.

Using the concept of pointers I have designed a VBA code to illustrate the implementation of “delegate” (function pointers) in VBA and also illustrated the use of the above mentioned keywords.

Also to mention this concept of pointers is also associated with the Callback functionality of VBA with external DLL’s.

Delegate Sample Code:
Option Explicit

'-----External Library Declaration which helps call the Proc by Address -----
Private Declare Function CallWindowProc _
                          Lib "user32.dll" Alias "CallWindowProcA" ( _
                              ByVal lpPrevWndFunc As Long, _
                              ByVal hwnd As Long, _
                              ByVal msg As Long, _
                              ByVal wParam As Long, _
                              ByVal lParam As Long) As Long

'-----This is the main function calling upon the proc via pointer -----
Public Sub test_delegate()
    Dim sMessage As String
    Dim nSubAddress    'As Long

    'This message will be passed to our Sub as an argument
    sMessage = InputBox("Please input a short message")
    'Get the address to the sub we are going to call
    nSubAddress = ProcPtr(AddressOf ShowMessage)
    'Do the magic! Function Called via Pointer...
    CallWindowProc nSubAddress, VarPtr(sMessage), 0&, 0&, 0&
End Sub

'-----This is the subroutine we want to call by address-----
Private Sub ShowMessage( _
        msg As String, _
        ByVal nUnused1 As Long, _
        ByVal nUnused2 As Long, _
        ByVal nUnused3 As Long)
'This is the Sub we will call by address
'it only use one argument but we need to pull the others
'from the stack, so they are just declared as Long values
    MsgBox msg
End Sub

'-----This function is used to extract the address of value to long -----
Private Function ProcPtr(ByVal nAddress As Long) As Long
'Just return the address we just got
    ProcPtr = nAddress
End Function

Pointer Sample Code:
Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                               (ByVal dst As Long, _
                                ByVal src As Long, _
                                ByVal nBytes As Long)

Sub pointerDemo()

    Dim iCount As Double
    Dim jCount As Double
    Dim ptr_iCount As Long
    Dim ptr_jCount As Long

    iCount = 100.23
    jCount = 200.45

    ptr_iCount = VarPtr(iCount)
    ptr_jCount = VarPtr(jCount)

    '--Common mistake: This will only overwrite the pointer variables
    '--The underlying values refrneced by the pointers will remina the same.
    'ptr_jCount = ptr_iCount

    '--The below mentioned method is the correct version to change the
    '--value of the underlying variables refrenced by the pointers.(2 version)
    'CopyMemory VarPtr(jCount), VarPtr(iCount), Len(iCount)
    CopyMemory ptr_jCount, ptr_iCount, Len(iCount)

    Debug.Print iCount
    Debug.Print jCount

End Sub


Download Solution
Download solution


Function Overloading – VBA

Function overloading, another wonders of the object oriented world where by the same name functions with different signatures provide the respective functionality based on the invoking arguments passed and their types.

Picking upon this and some google’ing I attempted to implement a function overloading mechanism via the help of “Variant” within VBA, as sadly function overloading is not inherently supported by VBA and it screams error of “Ambiguous names detected …”

Thus in order to implement the function overloading, we would have first construct a wrapper function which evaluates the signature of arguments sent and match to the respective function call and based upon which it executes and return the respective function.

Not very optimistic and delightful way of doing things but it’s something good to have in the repository for just one day when everything else fails.

'This enforces all the variables used within this module mush have their declartions explicitly
Option Explicit

'This enforces all the arrays implemented within this module starts from 0 'Zero'
Option Base 0

'This prevents the avilabiltiy of the function within this module to external refrencing projects
'and UDF's
Option Private Module

'Constant declaration
Private Const ConstIntVal As Integer = 10
Private Const ConstStrVal As String = "Hello"

'----- Overloading Implementation ------
Public Function addValue(Optional args As Variant) As Variant
    'Function signature identification and call relevant function component
    If IsMissing(args) Then
        addValue = addValue_Null
        Exit Function
    End If
    If Not IsArray(args) Then
        If TypeName(args) = "Integer" Then
            addValue = addValue_Int_Const(CInt(args))
            Exit Function
        ElseIf TypeName(args) = "String" Then
            addValue = addValue_Str_Const(CStr(args))
            Exit Function
        End If
        If TypeName(args(0)) = "Integer" Then
            addValue = addValue_Int(CInt(args(0)), CInt(args(1)))
            Exit Function
        ElseIf TypeName(args(0)) = "String" Then
            addValue = addValue_Str(CStr(args(0)), CStr(args(1)))
            Exit Function
        End If
    End If
End Function

'------ OverLoad Components ------

'Return 0 'Zero' for no arguments
Private Function addValue_Null() As Integer
    addValue_Null = 0
End Function

'Returns the constant added value to the argument (Integer)
Private Function addValue_Int_Const(val As Integer) As Integer
    addValue_Int_Const = val + ConstIntVal
End Function

'Returns the constant added value to the argument (String)
Private Function addValue_Str_Const(val As String) As String
    addValue_Str_Const = ConstStrVal & val
End Function

'Returns addtion of two integers
Private Function addValue_Int(val1 As Integer, val2 As Integer) As Integer
    addValue_Int = val1 + val2
End Function

'Returns concatenation of two strings
Private Function addValue_Str(val1 As String, val2 As String) As String
    addValue_Str = val1 + val2
End Function

Attached file illustrates the function overload mechanism.

Download Solution
Download solution


“Implements” Keyword, Interface – Class in VBA

Recently I was puzzled with a question about the levels of Object oriented structure can VBA support?
Suddenly my thoughts shifted to the key word I was always suspicious about but didn’t found time to explore. This post is all about key word “Implements” in VBA.

Learning about “Implements” in the context of VBA I am quite amazed about its potential. This keyword is used in conjunction to provide the interface-class implementation structure to VBA.
The code in the attached file is separated into 3 class objects (out of which 2 are implementation of the 1 interface) and 1 module object to illustrate the usage.

Code Explanation:
The attached file along with this post creates the following VBA structure:
1. IHuman (Interface)
2. clsMan (Class implementing IHuman)
3. clsWomen (Class implementing IHuman)
4. modTest (Module illustrating the usage of the class/interface)

The code also illustrates the additional method defined in the implementing classes and their usage patterns. Please explore the attached file VBA for the entire code.


Download Solution
Download solution


Late & Early Binding VBA

Recently I came across this term of bindings (Late/Early). Being a VBA developer for past few years and having worked with the concept numerous times, I realized lately it as Late/Early Binding. (I am bad with names and terms, I must confess)
So the concept goes as follows according to MSDN:

Early binding (MSDN):
An object is early bound when it is assigned to a variable declared to be of a specific object type

Sub test_LateEarlyBindings()

     'Early Binding
     Dim fs As Scripting.FileSystemObject

     Set fs = New Scripting.FileSystemObject

End Sub

Late Binding (MSDN):
An object is late bound when it is assigned to a variable declared to be of type Object.

Sub test_LateEarlyBindings()

    'Late Binding
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")

End Sub

The advantage of early binding over late binding is they allow compiler to allocate memory and perform optimization before an application executes. In addition to this when objects are declared explicitly as their types the VBA editor provide a helpful intellisense to assist the developer with object associated methods and properties, which becomes quite difficult in terms of late bound objects.


Then why use late binding ???

I came to know the utility of the late binding when I had to develop VBA based solutions which addressed users with varying office application versions (2003/2007).
Sub test_LateEarlyBindings()

    'Early Binding
    Dim outlookApp As Outlook.Application
    Set outlookApp = New Outlook.Application

End Sub

In the above code the instantiation of the Outlook object within VBA if flexible to work with different version of outlook installed on user PC (2003/2007) as this is instantiated as late bound object to the variable. This code relives the developer to explicitly add the outlook reference to the solution as it is picked up at run time and provides higher flexibility to the solution. This case was also sometimes used by myself when referencing the PDFCreator library for generating PDF reports.

Coding Tip (Late bound object):

As we discussed earlier since the late bound object doesn’t offer intellisense help while coding, I becomes tedious to develop the functionality and call upon the right name of methods and properties associated with the object.
A simple tip to overcome this problem is, initially develop the code as an early bound object and once the required functionality works well replace the object instantiation part by “CreateObject” method and uncheck the external references relating to the object from the reference library of the VBA project.