Tag: VBScript

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.

A Three Dimensional (3D) array is an array or more than one array each themselves containing more than one 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.

Would create a 2 column, 5 row 2D array

The Undimensioned Dynamic Array Problem

In VB you can also do something like this

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 2×5 2D array.

Imagine that the ReDim never happens

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:

… or even you might assume this

Funnily enough, with both examples you will receive:

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!

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

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

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.

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.

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

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

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.

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

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.

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.

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.

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

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:

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

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

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).

• We now have a 2×5 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

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:

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

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

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

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

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:

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.

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).

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):

VB Native:

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):

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.

Happy coding.