Using a two-dimensional array in Excel 2002 or 2003

  • Thread starter Thread starter rwjack
  • Start date Start date
R

rwjack

I am trying to improve my coding skills in Excel VBA and I just can't seem to
find a good, very basic, example of how to use a 2-dimensional array. I've
looked in several reference books & browsed VBA Forums but can't find an
example I can understand. What I was trying to do was define a 3 x 4 Array
that has a data type of Single. My sample data is located in Cells E1:H3.
E F G H
1 3.4 8.1 11.6 100.3
2 5.2 7.3 45.2 632.4
3 1.7 9.6 39.7 161.5

I've been trying to use the LBound and UBound functions too, thinking this
might be the best approach but none of my coding attempts work. That is why I
did not include any.

I would like to be able, using code, see what is in each Array element and
then be able to Sum the values and see the results in a variable.

I was able to create a 1-dimensional Array, shown below, but it is pretty
basic.

Sub SampleArray()
Dim Data(9) As Single
Dim i As Integer
Dim cum As Single

For i = LBound(Data) To UBound(Data)
Data(i) = Cells(i, 1)
MsgBox prompt:=Data(i)
cum = cum + Data(i)
MsgBox prompt:=cum
Next i
End Sub

I would appreciate any help submitted. Thanks
 
You can pickup the array directly

Dim Data

Data = Range("E1:H3")

and then you address both dimensions via the lower and upper bounds


For i = LBound(Data, 1) To UBound(Data, 1)

For j = LBound(Data, 2) To UBound(Data, 2)


Total code

Sub SampleArray()
Dim Data
Dim i As Long, j As Long

Data = Range("E1:H3")

For i = LBound(Data, 1) To UBound(Data, 1)

For j = LBound(Data, 2) To UBound(Data, 2)
cum = cum + Data(i, j)
MsgBox prompt:=Data(i, j) & " - " & cum
Next j
Next i
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
This works great! Thanks. I do have one more question, however. The cell
values display as they are in each cell (e.g. 3.4). However, when the cum
value is displayed in the MsgBox that same value displays as 3. What format
code do you use so it displays the whole number value?

Thanks
--
rw


Bob Phillips said:
You can pickup the array directly

Dim Data

Data = Range("E1:H3")

and then you address both dimensions via the lower and upper bounds


For i = LBound(Data, 1) To UBound(Data, 1)

For j = LBound(Data, 2) To UBound(Data, 2)


Total code

Sub SampleArray()
Dim Data
Dim i As Long, j As Long

Data = Range("E1:H3")

For i = LBound(Data, 1) To UBound(Data, 1)

For j = LBound(Data, 2) To UBound(Data, 2)
cum = cum + Data(i, j)
MsgBox prompt:=Data(i, j) & ", cum = " & _
Click to expand...
cum
prompt:=Data(i, j) & " - " & cum
 
Just make sure that you dim cum as double, not long or integer.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob. That's right. Integer & Long just display whole numbers and
Double or Single different ranges of floating-point values.
 
Back
Top