Handling of arrays in VBA macros for Excel

Question

I'm trying to write a simulator in VB (Excel macro) where the input to a simulation is taken from cells in one sheet. The input will be placed in a number of arrays, for example timePerUser(10) and bytesPerUser(10). Then there will be some simple if/for/while stuff to make calculations based on the arrays and finally I will write the results back to Excel. SO, Excel will only be used to provide input data and to display the results, everything else is happening inside of the macro, including changing values in the arrays.

I am used working with Matlab but can't use it for this simulator, so here are my questions:

1. Are there any existing matrix/array operations I could use within an Excel macro? For example, is there some command to check the smallest or the next smallest value in an array? The Excel function "SMALL" would be perfect, but it doesn't seem to work in macros. Or do I simply need to solve this with for-loops?

2. Are there any other suggestions on how to create the arrays? Is it better to have one big matrix where each row corresponds to time, data, user etc (an NxM matrix) or is it better to have separate arrays for each parameter?

3. How to speed up matrix/array operations? Any general suggestions?

Thanks! Oscar

Answer 1

Excel does have some simple array operations - SUMPRODUCT (Matlab equivalent: A*B'), MIN, MAX, ... Most of these, including SMALL, need to be called using Application.WorksheetFunction.xxx ; for example, the function SMALL can be called in VBA with the following:

nthSmallest = Application.WorksheetFunction.Small(r, n)

where r is an array or range, and n is an integer between 1 and r.Cells.Count

I would strongly urge you to use one variable per conceptual variable, rather than trying to be clever with multiple entities in one 2D array. Speedwise I suspect the difference is small; but the opportunity to write unreadable code is significant.

As for speeding things up: it really helps to define your variables (and especially your arrays) as some fixed type (rather than Variant), e.g.:

Dim a(1 To 10) as Double
Dim ii as Integer

and it may help a little bit to have all of the arrays use the same (default) base. Since I'm a Matlab junkie myself, I often work with array indices starting at 1 - you enforce this in VBA by adding

Option Base 1

at the start of your module. At this point, if you declare

Dim a(10) as Double

it is equivalent to

Dim a(1 To 10) as Double

Is also good practice for someone who is not proficient in VBA, to add

Option Explicit

at the start of the module, as it will ensure that any variables you did not declare (or more often, that you misspelled) will generate an interpreter error.

One other thing about arrays: as a Matlab person you are used to Matlab increasing array sizes as needed. In VBA it is possible to change array size dynamically at runtime, using, for example

ReDim Preserve a(1 To 20)

which will make the array a 20 elements in size. If it was shorter, it will be padded with zeros; if it was longer, it will be trimmed. This CAN be useful, but is QUITE expensive, since often the entire array needs to be copied to a new location. When you don't know in advance how large your array will be, it's better to overestimate (and check bounds) than to ReDim every time it needs to get bigger - and do a final ReDim at the end to get it to the right size.

Hope this is enough to get your started!