About C:Amie · Technical Articles · GFX & Artwork · Analog C:Amie Edition Tuesday, 19 September 2017

VBScript Multidimensional Arrays, limits, annoyances and ways around them

System Requirements:

  • Windows 95, 98, 98SE, Millennium
  • Windows NT 4.0, 2000, XP, 2003, Vista, 2008, 7, 8, 2012, 8.1, 10
  • VB 4, 5, 6, VBScript, ASP 3, ASP Classic

The Problem:

Microsoft implemented a proprietary method of handling multi-dimensional arrays in VBScript, one which doesn't quite allow a full range of functionality that you would expect from creating an array primitive.

This article discusses a few of those issues and some solutions to the problems.

More Info

I like using arrays, I use them a lot, especially if I want to get off of a database server quickly and only need a unidirectional cursor - frankly you seldom need anything other than a forward only cursor if you're creative enough.

What is a Multidimensional Array

The problem with that is that in order to return a database query you have to use multi-dimensional arrays. Not a big deal, a multi-dimensional array is nothing special. In pretty much any programming language a multi-dimensional array is nothing more than an array of arrays.

A Two Dimensional (2D) array is an array or more than one array.

array(array(), array())

A Three Dimensional (3D) array is an array or more than one array each themselves containing more than one array.

array(array(array(), array()), array(array(), array()))

Things work a little differently in the VBScript, VB 6 and ASP world. Not so much in the logic of how an array is structured as an array of arrays, but as how you an interface with that structure.

Dim array(1,4)

Would create a 2 column, 5 row 2D array

0,0 1,0
0,1 1,1
0,2 1,2
0,3 1,3
0,4 1,4

The Undimensioned Dynamic Array Problem

In VB you can also do something like this

Dim myArray()
' Things happen here
...
ReDim myArray(1,4)
...

This step would create an empty dynamic array [Dim myArray()] - a memory pointer to a storage location that the system expects will eventually become an array sequence. Later in the code execution the array is redimensioned to become a 2x5 2D array.

Imagine that the ReDim never happens

Dim i
Dim arrOut()
if (1 > 2) then      ' <----------- 1 is never greater than 2, so it will never be ReDim'd
   ReDim arrOut(2,10)
   arrOut(0,0) = 1
end if

wscript.echo IsArray(arrOut)

IsArray(arrOut) will return True (-1), it is a VB type of array after all. In our multidimensional structure this is asking whether the wrapper array is an array, which it is. It just doesn't have any contents yet. So what happens now if we do this:

if (IsArray(arrOut)) then
   for i = 0 to UBound(arrOut,2)
      wscript.echo arrOut(0,i)
   next
end if

... or even you might assume this

if (IsArray(arrOut)) then
   if (UBound(arrOut,2) > -1) then
      for i = 0 to UBound(arrOut,2)
         wscript.echo arrOut(0,i)
      next
   end if
end if

Funnily enough, with both examples you will receive:

array.vbs (11, 1) Microsoft VBScript runtime error: Subscript out of range.

The UBound function is a VB Native function that has two parameters. UBound(<array>,<dimension>).

We can issue both UBound(arrOut,1) AND UBound(arrOut,2) against the variable arrOut and both a scan for the size of the First Dimension and Second Dimension will result in a "Subscript out of range" error.

Now, despite the fact that arrOut is an array, it is just an empty memory pointer, there is no array structure behind it - think of it like the array being there but its value is null with 0 dimensions.

OK! We have an empty array. So lets confirm that!

if NOT (IsEmpty(arrOut)) then    ' <----- Our array has no dimensions and null contents
   for i = 0 to UBound(arrOut,2)
      wscript.echo arrOut(0,i)
   next
end if

Yet the IsEmpty function will return FALSE: the array is NOT empty.

So, according to VB, we have an array that is an array but that has no dimensions and no contents but isn't empty!

To clarify, with the above example the following conditions occur

IsEmpty(arrOut) = false
IsArray(arrOut) = true
UBound(arrOut,1) = <crash: subscript out of range>
UBound(arrOut,2) = <crash: subscript out of range>

This is just a quirk of the language, it's the way it is. But how do you test your way out of this?

Dim iRows
On Error Resume Next
   iRows = UBound(arrOut,2)

   if (err.number <> 9) then     ' <-------- 9 is the return code for subscript out of range
      for i = 0 to UBound(arrOut,2)
         wscript.echo arrOut(0,i)
      next
   end if
On Error Goto 0

Yes, you can do that. But it is very expensive and hardly eloquent unless you put it into an equally expensive boolean procedure for "isDimensioned()". So what else can be done?

The problem is due to the simplicity of the language and some unfortunate design decisions by Microsoft on what to cut from VB compared to VBScript - it is a lot like Java vs. JavaScript.

If you are using VB rather than VBScript/ASP, there is a language construct that you can use to escape from this problem without resorting to exception handling.

Dim arrOut()
if (NOT arrOut) then          ' <----- This checks to see if there is a pointer for the array
   ' Code if the array is not initialised is run here
end if

Again, it does not however work in VBScript/ASP and to my knowledge there is absolutely no way to get past it in a pure, one line language construct in VBScript. There is however a way to solve the problem. Be it one that uses more lines.

Never use the Dynamic Array Declaration

The issue that is causing you to experience the subscript out of range statement is not in fact the VB array, but the way you declare a Dynamic array.

Dim arrOut()

With this declaration, it will always throw an exception if you call UBound() on the array before it has been ReDim'd.

Instead, you should declare your arrays using the following

Dim arrOut : arrOut = Array()

So to summarise, against this syntax the following conditions will be present:

IsEmpty(arrOut) = true
IsArray(arrOut) = true
UBound(arrOut,1) = -1

That is actually want you want. What we are doing is declaring the arrOut. As this is VBScript, the variable will be created of type Variant. The variable is then converted into a new Variant Array with no size (-1), however this time the three logic tests that we are looking to see some common sense from actually tell the truth.

Dim arrOut
if (IsArray(arrOut)) then      ' <----- This obviously isn't an array
   ' Code if the array is not initialised is run here
end if

Put another way. It works, but it is sloppy, the variable is never technically initialised as an array, you are actually just relying on Variant manipulation. It can be tidied up by using

Dim arrOut
if (IsArray(arrOut)) then      ' <----- This obviously isn't an array
   arrOut = Array()
   ' Code if the array is not initialised is run here
end if

Instead of using colon notation (":") you can be more clever and write it long hand. In the example below, arrOut is legitimately becomes a VB Variant Array (type 8192 + 12) at the line arrOut = Array(). This process is closer to how you would dimension an array in Java, however, unfortunately, there is a new problem. You cannot call 'ReDim Preserve' on arrOut otherwise you will receive the dreaded 'Microsoft VBScript runtime error: Subscript out of range.' e.g.

Dim arrOut
if (IsArray(arrOut)) then      ' <----- This obviously is NOT an array
   arrOut = Array()            ' <----- It is an array now
   ReDim Preserve arrOut(4,5)  ' <----- This will throw an exception
   ' Code if the array is not initialised is run here
end if

This is only going to be a problem if you are going to dynamically resize a multidimensional array and include the preserve key word. Oddly, everything is fine if you resize it to a single dimension array and include the preserve. The solution is to ensure that the first time that you ReDim the array, you are NOT calling the Preserve key word e.g.

Dim arrOut
if (IsArray(arrOut)) then      ' <----- This obviously isn't an array
   arrOut = Array()            ' <----- It is an array now
   ReDim arrOut(4,5)           ' <----- This will NOT throw an exception
   ReDim Preserve arrOut(4,10) ' <----- This will NOT throw an exception
   ' Code if the array is not initialised is run here
end if

You can then immediately and safely use the preserve key word... however by now you have already lost your data.

Again, this is another unfortunate quirk of the language.

Applying it to an Example using Manually Created Multi-dimensional Arrays

Logically take a One Dimensional (1D) array.

Dim myArray(4)

If you UBound(myArray,1) this, it returns 4. Effectively the logical difference is that you created the wrapper array, and into THAT put an array with 5 rows. Calling UBound correctly identifies that there is an array within the wrapper array and returns its size - 4 (5 rows).

Repeating the same process with a 2D array

Dim myArray(1,4)

If you now call UBound(myArray,2) it returns 4. Here we logically created the wrapper array, put two arrays into that, each with 5 rows. A scan of the second column dimension reveals 5 rows.

So take a practical example.

SELECT id, name, date FROM user;

Using ADO getRows return the results into an array.

If there are 5 users in the table, we get a 2D array that responds to UBound(myArray,1) [3 == id, name, date] and UBound(myArray,2) [5 users each with an id, name and date].

What if there are no users? We can't UBound() the array or test it, so the program crashes unless we use On Error Resume Next.

There is a solution to this.

What VB's built-in functions lack is the ability to test for the presence of a dimensioned inner array. Effectively what we want to do is:

IsArray(myArray(0))

If that looks familiar it is because it is how virtually every C-derived language tests for a multi-dimensional array, get the array and then test to see if there is a first element in that array which itself is an array. Logically, an initialised Column set with an uninitialised Row Set would look like

myArray(0) null
myArray(1) null

While initialised Column set with an initialised Row set would look like

myArray(0) Array()
myArray(1) Array()

We know that myArray - the wrapper array - is an array, but is its first element also an array? If the element doesn't exist we want to see FALSE come back. Sadly the VB multi-dimensional array structure is such that it is not possible to test this dimension using the proprietary methods; however you can if you do it manually using nested arrays (primitive arrays).

Dim arrWrapper(1)           ' <-------- This is the Column set, there are 2 initialised columns (always)
Dim arrCol1(4)              ' <-------- This is the Row set for Column 1
Dim arrCol2(4)              ' <-------- This is the Row set for Column 2

arrWrapper(0) = arrCol1     ' Inset the Row sets
arrWrapper(1) = arrCol2
  • We now have a 2x5 multi-dimensional array
  • We have an Array Wrapper (arrWrapper)
  • It has a legal first dimension
  • We put an array into both of its rows, each with 5 rows

Logically, this looks like

myArray(0)
arrCol1(0) null
arrCol1(1) null
arrCol1(2) null
arrCol1(3) null
arrCol1(4) null
myArray(1)
arrCol2(0) null
arrCol2(1) null
arrCol2(2) null
arrCol2(3) null
arrCol2(4) null

Now if we test IsArray(arrWrapper) we get True just as before, the Column set has been initialised and is easy to test. We can also test IsArray(arrWrapper(0)) to see if there is any Row data available. In other words we can now categorically know that arrWrapper HAS dimensions and has been properly initialised.

The same also applies if we don't initialise the inner arrays; we can now explicitly test whether the inner array is a dimensioned array:

Dim arrWrapper(1)
Dim arrCol1(4)
Dim arrCol2(4)

if (1 > 2) then    ' <----------- 1 is never greater than 2
  arrWrapper(0) = arrCol1
  arrWrapper(1) = arrCol2
end if

if (IsArray(arrWrapper(0)) then
  ' Happy days had by all as this will never execute as column(0)'s contents is NOT an array
end if

What is the functional difference between this and the VB Native method?

' To Call Column 1 row 3 in the VB Native Array:
response.write myArray(0,2)

' To Call Column 1 row 3 in the Primitives Array:
response.write myArray(0)(2)

If you are wondering why this looks spookily familiar, don't fret. It's exactly what you do in most other procedural C derived languages. e.g. Java

int[][] myArray = new int[1][4];

System.out.println myArray[0][2];

Now to preserve your sanity (and line count) you can achieve all of the above using an in-line statement and deal with the sizing of the array at a later time using the following

Dim arrTest : arrTest = Array(Array(), Array())

' Now to prove that it isn't sized yet (it has zero rows)
response.write UBound(arrTest,1)    <---- This will return: 1 (i.e. 2 columns)
response.write UBound(arrTest,2)    <---- This will crash: <Subscript out of range>
response.write UBound(arrTest(0))   <---- This will return: -1 (unintialised)
response.write UBound(arrTest(1))   <---- This will return: -1 (unintialised)

Advantages of Traditional C-Like Arrays

So why else might you want to build your array structures manually rather than by using the native functions?

There is are two useful and occasionally very helpful reasons why managing your arrays in the traditional C-like sense can be helpful.

Number 1: Column extraction. If you need to extract an entire columns from an array and manipulate it separately without passing a large data set around, manually defined multi-dimensional arrays is how you do it

Dim arrWrapper(1)
Dim arrCol1(4)
Dim arrCol2(4)

arrWrapper(0) = arrCol1
arrWrapper(1) = arrCol2


arrWrapper(0)(0) = 5
arrWrapper(0)(1) = 10
arrWrapper(0)(2) = 12
arrWrapper(0)(3) = 8
arrWrapper(0)(4) = 2

arrWrapper(1)(0) = "Hello"
arrWrapper(1)(1) = "World"
arrWrapper(1)(2) = "How"
arrWrapper(1)(3) = "Are"
arrWrapper(1)(4) = "You?"


Dim arrStrings : arrStrings = arrWrapper(1)

The arrStrings variable now contains the entire contents of column 1. no if's not but's. If you want to do that with a VB Native array you have to iterate the data out, which itself carries a performance penalty:

Dim i
Dim myArray(1,4)

myArray(0,0) = 5
myArray(0,1) = 10
myArray(0,2) = 12
myArray(0,3) = 8
myArray(0,4) = 2

myArray(1,0) = "Hello"
myArray(1,1) = "World"
myArray(1,2) = "How"
myArray(1,3) = "Are"
myArray(1,4) = "You?"

Dim arrString()
ReDim arrString(UBound(myArray,2)) ' Hopefully this is actually dimensioned!
for i = 0 to UBound(myArray,2)     ' At this pint we have sized myArray 3 times, costly
   arrString(i) = myArray(1,i)
next

Number 2: Jagged Arrays. you generally don't use Jagged Arrays very often as programmers tend to use arrays as table sets, however when operating with large volume data sets if you use VB native arrays and don't need to logically think of your data as being tabular, you can wind up wasting a large amount of memory on empty array slots e.g.

myArray(0)
arrCol1(0) 1
arrCol1(1) 2
arrCol1(2) 3
arrCol1(3) 4
arrCol1(4) 5
myArray(1)
arrCol2(0) 1
arrCol2(1) null
arrCol2(2) null
arrCol2(3) 6
arrCol2(4) null

In a Jagged Array you do not need to over bound the inner array at all, as long as the context of your program permits it. Visually this would look like the following (note the removal of the Null values from the second column's Row set).

myArray(0)
arrCol1(0) 1
arrCol1(1) 2
arrCol1(2) 3
arrCol1(3) 4
arrCol1(4) 5
myArray(1)
arrCol2(0) 1
arrCol2(1) 6

If we simplify the maths and pretend that each unallocated array slot is using a 32-bit pointer + a 32-bit null, we just saved 24 bytes of memory from being consumed for absolutely no reason.

The advantage therefore is that you can only manipulate Jagged Arrays using manually created primitive VB Arrays and not through the use of native multi-dimensional arrays.

Disadvantages of Traditional C-like Arrays

The technique clearly has some uses. But is there any reason not to do it?

Yes, there is and it depends upon application and the size of the array.

The VB Native functions are such that they call much faster C++ code at a lower level, where as VB activities itself happen in a slower, less efficient language. This is reflected in the RAS/CAS seek times for the arrays.

A 43 column 9,000,001 row (9 million and 1) array was tested for read and write operations using the two scripts below:

Manual (Primitive):

Option Explicit

Dim i
Dim dblStart
Dim dblEnd

Dim bol
Dim str
Dim i1
Dim i2

Dim arrMain(3)
Dim arr1(9000000)
Dim arr2(9000000)
Dim arr3(9000000)
Dim arr4(9000000)

arrMain(0) = arr1
arrMain(1) = arr2
arrMain(2) = arr3
arrMain(3) = arr4

' WRITE

dblStart = Timer()

for i = 0 to UBound(arrMain(0),1)
arrMain(0)(i) = true
arrMain(1)(i) = 17
arrMain(2)(i) = "hello"
arrMain(3)(i) = (10 * i)
next

dblEnd = Timer()

wscript.echo (dblEnd - dblStart)

' READ

dblStart = Timer()

for i = 0 to UBound(arrMain(0),1)
bol = arrMain(0)(i)
i1 = arrMain(1)(i)
str = arrMain(2)(i)
i2 = arrMain(3)(i)
next

dblEnd = Timer()

wscript.echo (dblEnd - dblStart)

VB Native:

Option Explicit

Dim i
Dim dblStart
Dim dblEnd

Dim bol
Dim str
Dim i1
Dim i2

Dim arrMain(3,9000000)

' WRITE

dblStart = Timer()

for i = 0 to UBound(arrMain,2)
arrMain(0,i) = true
arrMain(1,i) = 17
arrMain(2,i) = "hello"
arrMain(3,i) = (10 * i)
next

dblEnd = Timer()

wscript.echo (dblEnd - dblStart)

' READ

dblStart = Timer()

for i = 0 to UBound(arrMain,2)
bol = arrMain(0,i)
i1 = arrMain(1,i)
str = arrMain(2,i)
i2 = arrMain(3,i)
next

dblEnd = Timer()

wscript.echo (dblEnd - dblStart)

the first thing that you will note is that in using Manually created multi-dimensional arrays it has been necessary to write more lines of code to do the same thing,. This itself may be a disadvantage for you!

If you are willing to proceduralise the process however, the line count can be reduced back down again by using the sample generator function provided at the end of this article.

Results of the experiment (Time shown in seconds):

VB Array Method Experiment Results

  Seconds
Primitive Write 5.154063
Primitive Read 4.843750
Native Write 3.695313
Native Read 3.593750

As you can see, the write process was 28.3% faster and the read 25.8% faster for the native array structure.

There is a clear and very evident performance hindrance to this method, however keep in mind that this array structure contained over 9 million rows and 3 columns - 27,000,003 'intersections' to be exact. If you are writing a program that requires an array of that size that will suffer from the performance drop that comes from this method, you may want to consider changing to a compiled language for what you are doing.

Summary

There are some limits to the native VB array structure system, and as this has demonstrated there are ways around that thanks to more traditional array structure paradigms. It cannot be denied that there is a slight performance hit for larger array, possibly to do with more of the work of managing the array being performed by VB itself rather than by underlying and cleaner C++.

The benefits of the alternative cannot be ignored however, particularly when array extraction and content presence validation are required without resorting to litter you code with On Error Resume Next statements.

If you got this far into the document, well done for reading through. A function can easily be created to automate the build process for multi-dimensional VB primitive arrays. For those interested I have included an example of one below.

' Create a Primitive in Primitive (nested) VB Array
'   iCols = Number of Columns
'   iRows = Number of Rows
' © C:Amie (http://www.c-amie.co.uk/)

public function mdArray(ByVal iCols, ByVal iRows)
   Dim arrOut
   Dim arrTemp

   ReDim arrOut(iCols)

   for i = 0 to iCols
      ReDim arrTemp(iRows)
      arrOut(i) = arrTemp
   next

   mdArray = arrOut
end function


' Example of its use
Dim myArr
Dim i

myArr = mdArray(1,4) ' <---- Here we create the empty array

wscript.echo UBound(myArr)       ' <---- This returns the column count (1 i.e. 2 columns)
wscript.echo UBound(myArr(0))    ' <---- This returns the row count (4 i.e. 5 rows)
wscript.echo IsArray(myArr)      ' <---- This returns: true
wscript.echo IsArray(myArr(0))   ' <---- This returns: true
wscript.echo "======================"

myArr(0)(0) = 1
myArr(0)(1) = 2
myArr(0)(2) = 3
myArr(0)(3) = 4
myArr(0)(4) = 5

myArr(1)(0) = "a"
myArr(1)(1) = "b"
myArr(1)(2) = "c"
myArr(1)(3) = "d"
myArr(1)(4) = "e"

for i = 0 to UBound(myArr(0))
   wscript.echo myArr(0)(i)
   wscript.echo myArr(1)(i)
next

Happy coding.

 

Article Published: Sunday, 27 March, 2011
Article Revision Date: Sunday, 11 December, 2016

This site is not associated with the Microsoft Corporation. The information on this page is provided as is and is free for those who visit to use. Microsoft Operating Systems, Internet Browsers and applications are the property of the Microsoft Corporation. Windows software patches and updates are the property of the Microsoft Corporation and are provided through the hard work and dedication of the Microsoft Security, Operating System, and Application development teams.

© C:Amie
1996 - 2017. All Rights Reserved (56,481)
All trademarks mentioned are the property of their respective owners.