Useful Code snipptes - VBScript

VBScript data structures

Vbscript lacks support of complex data structures unlike other programming languages. This sometimes make life a bit difficult. We need to code our own algorithms to achieve simple tasks like sorting of an array or reversing it. .net has support for complex data structures which come with build-in functions for these simple tasks. So if we can use .net data structure then we can save reinvention of wheel for some of these tasks. Well the good news is some of .net libraries are exposed to COM and can be used in vbscript.

Below are some examples:

ArrayList:


Set objArrayList = CreateObject("System.Collections.ArrayList")

objArrayList.Add 6
objArrayList.Add 8
objArrayList.Add 2
objArrayList.Add 4
objArrayList.Add 1
objArrayList.Add 5
objArrayList.Add 3
objArrayList.Add 3
objArrayList.Add 7

'unlike redefining array size (redim statement) every time before appending an element into an array,
' with arraylist you don’t need to worry about that.

'Converting ArrayList to legacy Array
Debug.Print Join(objArrayList.ToArray, ",") ' output: 6,8,2,4,1,5,3,3,7

'will give u element at mentioned index
Debug.Print objArrayList(0) ' output: 6

'this will give u size of arraylist
Debug.Print objArrayList.Count ' output: 9

'this will tell u if an element exists or not. no need to iterate over all elements
Debug.Print objArrayList.contains(5) ' output: true

'this will sort elements. no need to write long algorithms
objArrayList.Sort


Debug.Print Join(objArrayList.ToArray, ",") ' output: 1,2,3,3,4,5,6,7,8

'this will reverse the order of all elements present
objArrayList.Reverse


Debug.Print Join(objArrayList.ToArray, ",") ' output: 8,7,6,5,4,3,3,2,1

'remove a specific element
objArrayList.Remove 8 'This will remove 8 from arraylist no matter on which index it is on

'remove element by index
objArrayList.RemoveAt 6 'This will remove element present at 6th index

'remove all elements
objArrayList.Clear

For VBScript Dictionary Objects we don’t need to redim while appending/removing elements; also an element can be directly searched without iterating through each of them. Both of these features are available with ArrayList as well but one of the most important dictionary object feature ‘Key-Value pair’ is not present in ArrayList. SortedList is alternative if you want utilize power of both ArrayList and Dictionary.

SortedList :
SortedList is sorted Dictionary. Every time we add/remove a key value pair in dictionary, it automatically gets sorted by Key. Also you can also access elements based on its index (just like arrays) which makes this even more powerful.

'For this example I am maintain employee name and joining date where joining date is the key

Set objSortedList = CreateObject("System.Collections.SortedList")
objSortedList.Add CDate("1-jan-2012"), "Ajay"
objSortedList.Add CDate("3-Mar-2012"), "Amit"
objSortedList.Add CDate("20-dec-2011"), "Jay"
objSortedList.Add CDate("2-jan-2012"), "Vijay"
' All elements will be automatically sorted by Key (joining date in this case)
'Get Count of elements
Debug.Print objSortedList.Count 'output : 4
'access element by its key
Debug.Print objSortedList(CDate("1-Jan-2012")) 'output: Ajay
'accessing elements of dictionary by index
For i = 0 To objSortedList.Count - 1
    Debug.Print objSortedList.GetByIndex(i)
Next

'Check if Keys Exists
Debug.Print objSortedList.ContainsKey(CDate("1-jan-2012")) 'output: True

'Check if Value Exists
Debug.Print objSortedList.ContainsValue("Vijay") 'output: True


'Get Index by Key (zero based index)
Debug.Print objSortedList.IndexOfKey(CDate("1-jan-2012")) 'output: 1


'Get Index By Value
Debug.Print objSortedList.IndexOfValue("Vijay") 'output: 2


'Remove Element by Key
objSortedList.Remove CDate("1-jan-2012")


'Remove Element by Index
objSortedList.RemoveAt 0

'Remove all elements
objSortedList.Clear


Using Stack:


Set objStack = CreateObject("System.Collections.Stack")
'Adding elements in Stack
objStack.Push "Item_1"
objStack.Push "Item_2"
objStack.Push "Item_3"
objStack.Push "Item_4"

'Iterate through each element of Stack
For Each Item In objStack
   Debug.Print Item
Next


'Convert Stack to Array
Debug.Print Join(objStack.ToArray, ",") 'output: Item_4,Item_3,Item_2,Item_1

'Get Stack Element Count
Debug.Print objStack.Count 'output: 4


'Check if an element exists or not
Debug.Print objStack.Contains("Item_2") 'output: True


'Pop the last In element from Stack
Debug.Print objStack.Pop 'output: Item_4

'Get Last in element without popping it out
Debug.Print objStack.Peek 'output: Item_3
Debug.Print objStack.Pop 'output: Item_3
Debug.Print objStack.Pop 'output: Item_2

‘Remove all elements from Stack
objStack.Clear

Using Queue:


Set objQueue = CreateObject("System.Collections.Queue")
'Adding elements in Queue
objQueue.Enqueue "Work_Request_ID_11"
objQueue.Enqueue "Work_Request_ID_2"
objQueue.Enqueue "Work_Request_ID_4"
objQueue.Enqueue "Work_Request_ID_9"

'Iterate through each element of Queue
For Each Item In objQueue
  Debug.Print Item
Next


'Convert Queue to Array
Debug.Print Join(objQueue.ToArray, ",") 'output: Work_Request_ID_11,Work_Request_ID_2,Work_Request_ID_4,Work_Request_ID_9

'Get Queue Element Count
Debug.Print objQueue.Count 'output: 4

'Check if an element exists or not
Debug.Print objQueue.Contains("Work_Request_4") 'output: True

'Get the First In element from Queue
Debug.Print objQueue.Dequeue 'output: Work_Request_ID_11


'Get first in element without removing it
Debug.Print objQueue.Peek 'output: Work_Request_ID_2
Debug.Print objQueue.Dequeue 'output: Work_Request_ID_2
Debug.Print objQueue.Dequeue 'output: Work_Request_ID_4

'Clear Entire Queue
objQueue.Clear

Disconnected RecordSets:
Multi-Level Sorting and Filtering: If you are dealing with data which requires more than just key value pair and fits best in 2D array, and you wanted to perform sorting and filtering on it, then disconnected recordsets will be excellent option.
(Although this is not related to .net but is quite useful in these situations)

Let’s say if I have below 2D array
EmpID EmpName EmpAge JoiningDate
1 Ajay 27 5-Jan-2012
2 Vijay 28 5-Jan-2012
3 Jay 30 2-Mar-2012
4 Amit 26 20-Dec-2011
And you wanted to get employees who joined in 2012 and are have age less than 30, also you wanted to get data sorted on joining Date and if joining date is same then second level of sorting should be applied at Age (in descending order).

Output should look like
EmpID EmpName EmpAge JoiningDate
2 Vijay 28 5-Jan-2012
1 Ajay 27 5-Jan-2012

Building this logic in plain Vbscript might be tedious and error prone, instead we can use a disconnected recordset to first populate data and then filter, sort elements by using its internal function.

Const adDouble = 5
Const adVarChar = 200
Const adDate = 133

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Fields.Append "EmpID", adDouble
objRecordSet.Fields.Append "EmpName", adVarChar, 50
objRecordSet.Fields.Append "EmpAge", adDouble
objRecordSet.Fields.Append "JoiningDate", adDate
objRecordSet.Open
objRecordSet.AddNew Array("EmpID", "EmpName", "EmpAge", "JoiningDate"), Array(1, "Ajay", 27, CDate("5-Jan-2012"))
objRecordSet.AddNew Array("EmpID", "EmpName", "EmpAge", "JoiningDate"), Array(2, "Vijay", 28, CDate("5-Jan-2012"))


objRecordSet.AddNew Array("EmpID", "EmpName", "EmpAge", "JoiningDate"), Array(3, "jay", 30, CDate("2-Mar-2012"))
objRecordSet.AddNew Array("EmpID", "EmpName", "EmpAge", "JoiningDate"), Array(4, "Amit", 28, CDate("20-Dec-2011"))
objRecordSet.Filter = "JoiningDate>'1-jan-2012' and EmpAge<30"
objRecordSet.Sort = "JoiningDate, EmpAge DESC"
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Debug.Print objRecordSet("EmpID").Value & "
    " & objRecordSet("EmpName").Value & "
    " & objRecordSet("EmpAge").Value
    objRecordSet.MoveNext
Loop
objRecordSet.Close