Using a two-dimensional array in Excel 2002 or 2003
 

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

Using a two-dimensional array in Excel 2002 or 2003
 

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)



"rwjack" wrote in message
...
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

Using a two-dimensional array in Excel 2002 or 2003
 

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" wrote:

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 = " & _
cum
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)



"rwjack" wrote in message
...
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

Using a two-dimensional array in Excel 2002 or 2003
 

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)



"rwjack" wrote in message
...
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" wrote:

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 = " & _
cum
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)



"rwjack" wrote in message
...
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

Using a two-dimensional array in Excel 2002 or 2003
 

Thanks Bob. That's right. Integer & Long just display whole numbers and
Double or Single different ranges of floating-point values.
--
rw


"Bob Phillips" wrote:

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)



"rwjack" wrote in message
...
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" wrote:

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 = " & _
cum
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)



"rwjack" wrote in message
...
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