Using .Net arrays in VBA crashes Word

BrianHoard · #1 11-08-2022, 01:27 PM

We have written several scripts using .Net arrays (forgive me if that's the wrong name, that's what we have been calling them). We are now seeing that Word crashes immediately if certain users try to run these scripts. No warning, no locking up or spinning cursor, Word just closes.

We have found the common problem is that any script where we have used .Net arrays are the culprit. We are using them like this...

Code:

' .net arrays
Dim enDupes_LL as Object

' Create the empty array
Set enDupes_LL = CreateObject("System.Collections.ArrayList")

' We append to this array like this...
enDupes_LL.Add Array(int_master, eNumber, enRefText)

' We can get the size of the array using...
If enDupes_LL.Count == 0  Then

Please let me know your advice how we can either solve the problem to allow these arrays to work on all users computers, or a better approach instead of using them.
I'm guessing that it has to do with what version of .Net a user has installed. But still troubleshooting that.

I recall originally looking into using VBA arrays, but could not see how I would append to them, or get their size, like with using .Count

Thank you for any help.

macropod · #2 11-08-2022, 02:44 PM

A trivial demonstration of using arrays in Word:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim MyArray() As String, i As Long, j As Long
For i = 10 To 1 Step -1
  ReDim Preserve MyArray(j)
  StrTxt(j) = i: j = j + 1
Next
MsgBox UBound(MyArray) ' Count of array elements (0-based)
MsgBox Join(MyArray(), ", ") ' Unsorted list of array elements
WordBasic.SortArray MyArray() ' Sort the array
MsgBox Join(MyArray(), ", ") ' Sorted list of array elements
Application.ScreenUpdating = True
End Sub

BrianHoard · #3 11-08-2022, 08:04 PM

Thank you Macropod, this is very helpful.
I see that I can work with an array of a single data type, is it possible to create an array of arrays? Where each array element is an array itself?

A goal structure like this, where the first 2 items in each array are ints, followed by a string.

multipleDataArrayName{
{1, 2, "some text"},
{3, 4, "some text2"},
etc
}

Then be able to access this info something like...
multipleDataArrayName(0)(2)
would return: some text2

Is this possible?

macropod · #4 11-09-2022, 02:27 PM

You could use a multi-dimensional array. For example, for a 4-dimensional array, you might use something like:

Code:

Sub Demo()
Dim DataSet As String, i As Long, j As Long
Dim MyArray() ' Array
ReDim Preserve MyArray(4, 0)
DataSet = DataSet & "A,N/A,999,999,"
DataSet = DataSet & "B,10x8,254.6,199.4,"
DataSet = DataSet & "C,7x5,178,123.2,"
DataSet = DataSet & "D,5x4,127,98.6,"
DataSet = DataSet & "E,6x9,84,56,"
DataSet = DataSet & "F,6x7,70,56,"
DataSet = DataSet & "G,6x6,56,56,"
DataSet = DataSet & "H,6x4.5,56,42,"
DataSet = DataSet & "I,135,36,24,"
DataSet = DataSet & "J,126,26.5,26.5,"
DataSet = DataSet & "K,H,30.2,16.733,"
DataSet = DataSet & "L,P,30.2,9.5,"
DataSet = DataSet & "M,C,25.1,16.733,"
DataSet = DataSet & "N,110,17,13,"
DataSet = DataSet & "O,4/3.0,18,13.5,"
DataSet = DataSet & "P,1.8,23.63,15.75,"
DataSet = DataSet & "Q,1.0,12.70,9.525,"
DataSet = DataSet & "R,2/3.0,8.80,6.60,"
DataSet = DataSet & "S,1/1.7,7.60,5.70,"
DataSet = DataSet & "T,1/1.80,7.176,5.319,"
DataSet = DataSet & "U,1/2.0,6.40,4.80,"
DataSet = DataSet & "V,1/2.3,6.16,4.62,"
DataSet = DataSet & "W,1/2.5,5.74,4.30,"
DataSet = DataSet & "X,1/2.6,5.60,4.20,"
DataSet = DataSet & "Y,1/2.7,5.27,3.96,"
DataSet = DataSet & "Z,1/3.0,4.80,3.60,"
' Initialise the MyArray array
ReDim MyArray(4, UBound(Split(DataSet, ",")) / 4 + 1)
' Add the data to the MyArray array
For i = 1 To UBound(Split(DataSet, ",")) / 4
  For j = 1 To 4
    MyArray(j, i) = Split(DataSet, ",")((i - 1) * 4 + j - 1)
  Next j
  MsgBox MyArray(1, i) & vbTab & MyArray(2, i) & vbTab & MyArray(3, i) & vbTab & MyArray(4, i)
Next i
End Sub

To resize the array, you would vary the second number, viz:
ReDim Preserve MyArray(4, n)
Where n is the number of elements. Using 'Preserve' maintains any data already present in the resized array.

BrianHoard · #5 11-09-2022, 08:38 PM

Thank you again, can't wait to try this out!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to use arrays?	Simplifier	Excel	1	09-03-2016 03:50 AM
Create an input box for arrays	rajani	Word VBA	2	08-17-2015 06:28 AM
Variable arrays from user input	SeattleITguy	Excel Programming	1	01-29-2015 09:19 AM
Searching Arrays without Loops?	ptmuldoon	Word VBA	3	12-13-2014 11:21 AM
re-naming arrays in VBA?	JDevsFan	Excel Programming	4	03-15-2012 08:44 AM