Array constants in VBA Module sheet

[PaulFryer]

I am using XL 2004 (v.11.2.3) and Visual basic for Applications (VBA)
11.2 for Macintosh, and want to do something which should be real
simple - except that I can't find a way! I would really appreciate
help on how to set about this or what I might be doing wrong.

I need to write a module containing 3 simple VBA functions to call from
an XL spreadsheet, which all need access to a 4 x 25 element array of
constants. These constants never change, but are hard to calculate.
How do I make such a table available to all 3 Functions? I have tried:

· Various ways of entering data, of the form "Dim table(4,25) =
{0,1,2,3;4,5,6,7;8 . . . . etc}", or "Const table(0,0) =0, . .. . .
etc" at the module level, but all are rejected by the VBA compiler. It
appears that you can only set simple names as constants - not array
elements. VBA Help is no use at all.

· Defining a Static array at the module level, and then calling a
dummy routine in the module once from the spreadsheet to initialize all
4x25 elements. But you can't have Static variables at the module
level, and if I put the Static array inside the initializing routine
it's obviously not available to the 3 main functions.

· Passing the 4 x 25 array as an argument to each of the 3
Functions, and having the array of constants on the spreadsheet
instead. However, passing arrays (by reference) to VBA functions
doesn't seem to work at all - the VBA code doesn't even begin to
execute, and I get a #VALUE returned to the calling spreadsheet cell.
I must be doing something wrong here, but I can't see what, and I would
appreciate suggestions.

What I really don't want to do is to individually initialize all 4 x 25
constants each time I call any of the 3 Functions, as these functions
are extensively used on the spreadsheet and this would slow down
operation significantly. And this really is an ugly, brute-force way
of doing things!

I have had these 3 routines using the old Macro language from Excel 4.0
days, but this is fairly ugly using two linked XL documents, and I want
to make the whole spreadsheet available to other people, including some
Windows users. Hence I thought I would try and update them to VBA.

So, please, please, suggestions as to how I set about this simple task.

Thanks,

Paul.

 

[JE McGimpsey]

So, please, please, suggestions as to how I set about this simple task.

The easiest way is to store the values in a worksheet, perhaps a hidden
sheet, and assign the variables within each routine - it's very fast:

Public Sub Macro1()
Static vArray As Variant
If IsEmpty(vArray) Then _
vArray = Worksheets("Data").Range("A125").Value
'other stuff here
End Sub

In this case, vArray would be dimensioned as

vArray(1 to 25, 1 to 4)

An alternative would be to declare a public variable, and assign it when
needed:

Public vArray As Variant

Public Sub Macro1()
If IsEmpty(vArray) Then FillArray
'other stuff here
End Sub

Private Sub FillArray()
vArray = Worksheets("Data").Range("A125").Value
End Sub


If you don't want to use a worksheet, you can use something like:

Public vArray As Variant

Private Sub FillArray()
vArray = Array(Array(1,2,3,4), _
Array(5,6,7,8), _
'etc
Array(97,98,99,100))
End Sub


Since Array() always assigns starting with element 0, you'd access the
first element of vArray as, e.g.,

Debug.Print vArray(0)(0)


Yet another alternative would be to store the values in a single array,
then redimension the array. Since it's only done once, it's not a big
drain on resources:

Public vArray As Variant

Private Sub FillArray()
Dim vSource As Variant
Dim i As Long
Dim j As Long
Dim nCount As Long

vSource = Array(1, 2, 3, 4, 5, 6, 7, 8,...)
ReDim vArray(1 To UBound(vSource) / 4, 1 To 4)
For i = 1 To 2
For j = 1 To 4
vArray(i, j) = vSource(nCount)
nCount = nCount + 1
Next j
Next i
Debug.Print vArray(2, 1)
End Sub

 

[PaulFryer]

Thanks for the various suggestions. I have been trying them in my
application, and I can definitely use them. I went for your first
(simplest) suggestion, and it works well, and is about as fast as the
previous Excel 4.0 macros I was using. Putting the constant values on
the main spreadsheet is quite useful, since it makes future tweaking of
them that much easier. There were several constructs that my hitherto
limited use of VBA hadn't used - it seems that the key one was

If IsEmpty(vArray) Then vArray =
Worksheets("Data").Range("A125").Value

so this was a useful learning experience.

It still seems strange that you cannot declare an array of constants in
VBA, just like you can a simple constant, and have to resort to devious
means to work around it.

Also, can you suggest a reason why I have been having difficulty
passing an array reference from a worksheet to a VBA Function. The
manuals I have been looking at (admittedly Windows-based) say it can be
done, but when I try it the Function basically doesn't execute and
gives a #VALUE result, with the explanation "A value used in the
formula is of the wrong data type". I define the Function in VBA as

Function PassArray(testArray())
PassArray = 0
End Function

And the spreadsheet call is

=PassArray(dataTable)
where dataTable is the name on the spreadsheet of a 2-dimensional
table (doesn't make any difference if I use A125 in place of the
name).

Anyway, thanks again,

Paul.