MS Excel and arrays

  toga 21:13 22 Jul 2006

I am not sure if this is the correct place for this thread. I am trying to trach my self visual basic for applications with excel. I am currently attempting to get my head around using arrays, with limited success.

I am trying to expand an array while preserving the initial data contained in the array.

If anyone has any ideas on this subject I can include the code (I do not know how to attach a file to this thread).

  VoG II 22:30 22 Jul 2006

You are in exactly the right place - or maybe you should be in the Helproom click here - but no matter.

You cannot attach your file but you can post your code. Here is an example of what you want to do:

ReDim Preserve Titles(1 To UBound(Titles) + 1)
For iT = UBound(Titles) To 2 Step -1
Titles(iT) = Titles(iT - 1)
Next iT

No doubt this is as clear as mud. Pleaser post back withy your example and what you want to achieve.

  VoG II 09:02 23 Jul 2006

Actually click here may make this a little clearer.

  toga 13:00 23 Jul 2006


Thanks for replying. I think I am trying to run before I can walk, as you will see from my sample code below:

What I am trying to do is create a 2 dimentional array (ok so far), pass the array to a function and display the data in 1 element (again ok).

I am then trying to pass the array to another function and expand the second dimention of that array, return to the original calling procedure and display, then populate the additional elements and display again. However, the array seems to only display 3 elements (the initial number of elements).

Her are the details I have been entering:

initial number of elements = 3
data elements:
samsung; 500GB; 400GB
Western Digital; 600GB; 250GB
Fijitsu; 700GB; 800GB

Additional number of elements = 3

Code as follows (sorry if it is overly complicated):

Option Base 1
Type MyType
diskdrives() As String
End Type
Sub arytest()
Dim i As Integer, j As Integer, num As Integer
Static a As MyType ' declare array
num = InputBox("Enter the number of array elements: ")
ReDim a.diskdrives(num, num)
For i = 1 To num
For j = 1 To num
a.diskdrives(i, j) = InputBox("Enter Details: ") ' initialise data elements
Next j
Next i
ret = retr(a) ' call array function to retrieve data
MsgBox "position at i is: " & ret ' display data
retexp = expand(a) ' call array function to expand array and preserve existing data
For i = 1 To 3
For j = 1 To UBound(a.diskdrives) ' dimension second element
MsgBox "position at i is: " & a.diskdrives(i, j) 'display data at elements
Next j
Next i
For i = 1 To 3 ' input new data into elements
For j = 4 To UBound(a.diskdrives)
a.diskdrives(i, j) = InputBox("Enter Details: ")
Next j
Next i
For i = 1 To 3 'display data in expanded array
For j = 1 To UBound(a.diskdrives)
MsgBox "position at i is: " & a.diskdrives(i, j)
Next j
Next i

End Sub
Function retr(a As MyType) 'pass array and retrieve specific data at given element
num1 = InputBox("Enter array elements i to retrieve data: ")
num2 = InputBox("Enter array elements j to retrieve data: ")
retr = a.diskdrives(num1, num2)
End Function
Function expand(a As MyType)
MsgBox "Do you want to expand Array? ", vbYesNo
If vbYes Then 'expand array, second dimention upper bound
num3 = InputBox("Enter the number of additional records required: ") 'number of elements
ReDim Preserve a.diskdrives(num3, UBound(a.diskdrives, 2) + num3) 'expand and preserve array
End If
expand = a.diskdrives() ' return expanded arrays
MsgBox (UBound(a.diskdrives))
End Function

  VoG II 13:30 23 Jul 2006

I get a subscript out of range error at this line

ReDim Preserve a.diskdrives(num3, UBound(a.diskdrives, 2) + num3) 'expand and preserve array

and I can't immediately see why.

Before going much deeper into this, I am wondering why you are using arrays. Presumably the data is going to be written to a worksheet? So why not just write the data directly to the sheet instead of storing it in an array? i could correspond to row number and j to column number, or vice versa.

  toga 17:56 23 Jul 2006

I am just working through an old excel book that I was given (from the mid 90's) but it doesn't expand any further on arrays. I was just trying to understand how to do this (its nothing important, just my curiosity).

The line of code you mention, I don't fully understan yet.

I thought that, after the initial 3 elements, when I passed the array into the function to an extra elements that the "MsgBox (UBound(a.diskdrives))" would show a value of the initial 3 elements plus any additional. Then the error "subscript out of range" would be solved. e.g. if I replace the UBound on the following bits of code with a 6 (to indicate the initail 3 elements + an additional 3) it seems to work:

For i = 1 To 3 'display data in expanded array
For j = 1 To 6 ' replace UBound(a.diskdrives)
MsgBox "position at i is: " & a.diskdrives(i, j)
Next j
Next i

  VoG II 18:16 23 Jul 2006

No I don't understand why it isn't working. My experience with arrays is limited to one-dimensional ones - the snippet of code that I gave in my first post was to populate an array to be used in turn to populate a drop-down list on a userform.

If you really want to solve this I suggest that you post at click here

  toga 18:23 23 Jul 2006


Thanks for your replies, advice and help. Its much appreciated.

  squillary 02:46 24 Jul 2006

This site may be of interest for Excel, VBA and arrays click here

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017