How to Populate an Array with a Discontiguous Values

[PMC1]

Hi,

Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array
with a Discontiguous values.

For example, instead of fully declaring a range like this
Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range
and then use something like a loop to populate the array e.g.

Dim myArRanges(1 to 3, 7 to 9)
Redim MyArray(myArRanges)

Could anyone suggest how this might be done.

Thanks

...pc

 

[Rick Rothstein \(MVP - VB\)]

I guess you could do this...

Dim MyArray()
MyArray = Array(1, 2, 3, , , , 7, 8, 9)

If MyArray is a Variant array as you have shown, elements 4, 5 and 6 will be
"missing" in the sense that no value is assigned to them at all... they are
not empty or null, they just don't have anything assigned to them. You will
get an error if you try to address them although you can test for that using
the IsError function. If you declare your array with an actual type
(Integer, Long, String, etc.), then elements 4, 5, and 6 will take on the
default value for those data types (0, 0, "", etc.).

Rick

 

[XP]

You can loop thru cells and test for a condition, for example, the following
untested code only adds data to the array if the cell is not blank:

Dim rCell as Range
Dim lX as long
Dim vArray() as Variant
Dim sMSG

'Load the array:
For Each rCell in ActiveSheet.UsedRange.Columns(1).Rows
If Trim(rCell.Formular1c1) <> "" Then '<<< filter out blank cells
lX = lX + 1
ReDim Preserve vArray(lX)
vArray(lX) = rcell.Value
End If
Next rCell

'now check the contents:
For lX = 1 to Ubound(vArray)
sMsg = sMsg & vArray(lX) & vbCr
Next lX
Msgbox sMsg

HTH

 

[Alan Beban]

I guess you could do this...

Dim MyArray()
MyArray = Array(1, 2, 3, , , , 7, 8, 9)

. . . If you
declare your array with an actual type (Integer, Long, String, etc.),
then elements 4, 5, and 6 will take on the default value for those data
types (0, 0, "", etc.).

Rick

No. You will get a Type mismatch error.

Alan Beban

 

[Alan Beban]

Hi,

Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array
with a Discontiguous values.

For example, instead of fully declaring a range like this
Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range
and then use something like a loop to populate the array e.g.

Dim myArRanges(1 to 3, 7 to 9)
Redim MyArray(myArRanges)

Could anyone suggest how this might be done.

Thanks

..pc

I'm not 100% sure I know what you are asking, but if the functions in
the freely downloadable file at http://home.pacbell.net/beban are
available to your workbook, then with 1,2,3,4,5,6,7,8,9 in A1 to A9, The
following will generate a 1-D array of six elements: 1,2,3,7,8,9

Dim rng As Range
Dim MyArray() As Integer 'or Variant or Long or Single or Double or 'Byte
Set rng = Range("a11:a13,a17:a19")
Assign rng, MyArray

Alan Beban

 

[Rick Rothstein \(MVP - VB\)]

Thanks for catching that Alan.

<<Note to self: Test **before** you post!>>

Rick