Playing w/ Arrays

[YellowLabPro]

I am tinkering w/ Arrays- new for me... but the following code does not deliver any visible results....

[VBA]
Sub myarray()
Dim myarray(1 To 10, 1 To 10) As Integer
myarray(3, 4) = 125
End Sub
[/VBA]

[Bob Phillips]

All you have done is to declare the dimensions and bounds of the array, and loaded a value into an element of the array.

Beyond that, you have done nothing. You don't use that element or any others in any calculations, any presentation, or any user feedback, i.e. nothing visible.

[YellowLabPro]

Ok, I was guessing this may be the issue. I am picking certain items out of Walkenbach's book and plopping them in a workbook to see the results. How do I proceed here to see what sort of results might be delivered by declaring an array of such?

[Bob Phillips]

Taking it a bit further, arrays are not necessarily for doing anything visible, they are a compact way of storing a number of like data elements, that you would use within code.

Working with arrays in VBA is fast, for instance it is much quicker to load a range into an array and work on an array than working directy on the range.

As an example

[vba]

Sub Macro1()
Dim myArray
Dim rng As Range
Dim i As Long, j As Long, k As Long
Dim nTimer As Double
Dim cnt As Double

Set rng = Range("A1:C3")

myArray = rng
nTimer = Timer
For k = 1 To 100000
cnt = 0
For j = UBound(myArray, 2) To UBound(myArray, 2)
For i = UBound(myArray, 1) To UBound(myArray, 1)
cnt = cnt + myArray(j, i)
Next i
Next j
Next k
Debug.Print "Array time: " & Timer - nTimer

nTimer = Timer
For k = 1 To 10000
cnt = 0
For j = 1 To rng.Rows.Count
For i = 1 To rng.Columns.Count
cnt = cnt + rng.Cells(j, i)
Next i
Next j
Next k
Debug.Print "Range time: " & Timer - nTimer

End Sub
[/vba]

and note that the array loops is 10 times greater than the range loop, and is still much quicker.

BTW, NEVER use the same name for variables and Sub, or subs and module names, it is asking for trouble.

[Bob Phillips]

The other thing that an array is indexed, so as we tend to do lots of thinsk wher an index is being used, an array is a convenient place to store them for use later on.

[YellowLabPro]

Ok, good.
But lets take the code submitted:
[vba]
Sub testarray()
Dim myArray(1 To 10, 1 To 10) As Integer
myArray(3, 4) = 125
End Sub
I have created a 2 dimensional matrix, assigned the value of 125 to the array- how could this be used?
(sorry if it is redundant question and is missing your point from the prior post)

[/vba]

BTW, NEVER use the same name for variables and Sub, or subs and module names, it is asking for trouble.

Ok

[Bob Phillips]

Doug,

For a single element, an array is redundant, you might just as well use a single variable. Arrays come into their own when processing many items.

As for how could it be used, how long is a piece of string? The question is not so much how could the value in an array be used, but rather why and where to use an array, and that is what I tried to suggest earlier.

[p45cal]

re:"does not deliver any visible results.... "

to see visible results, in the vbe, ensure that you can see the Locals window (under View|Locals Window dropdown). Then put the cursor anywhere in your sub and press the F8 key. This steps through your code. Press F8 until the yellow highlight is sitting on 'End Sub'. Look at the Locals Window and you should see 'myarray' with a + sign next to it, click on this + sign, 10 new lines appear, click on the plus sign next to myarray(3) and you should see your value in myarray(3,4).

In the Immediate pane, (go through the dropdowns to make it visible as above if it's not) typing
?myarray(3,4)
and pressing Enter should produce the result 125 (the '?' means print in the immediate pane).
You need still to be in the module to see this. Once End sub has been executed it's all gone (in this case).

p45cal

[YellowLabPro]

Thanks P45cal.
Xld's point about a one dimension array is not useful is reinforced by your post showing me what is going on .... :-)
But your post gives me some more to see for both using the Locals and the Immediate windows and what the Array is doing, very cool!

Thanks again,

Doug

[Bob Phillips]

It wasn't a one dimension array Doug, use them all the time, but an array where one element is being specifically used, you might as well use an explicit variable.

[YellowLabPro]

Ah- yes, sorry used wrong term.

[david000]

[vba]
Sub Macro3()
Dim buf As Variant
buf = Array(1, 2, 3)
Range("A1:C1").Value = buf
Stop 'See your Locals window
End Sub
[/vba]
Via Colo's Excel Junck Room - VBA Tips.

I think this is a GREAT simple example of using the Stop function, because it shows the element "1" indexed to "0" in the locals window. Keeps me on my toes!

[CodeMakr]

Also, if you compare your initial code w/david000's, the reason you didn't "see" anything is because you created an array and placed a value in it, but didn't do anything with it (i.e., david000 passed his array back into excel with his Range statement).

1 - Dimension the array
2 - set worksheet range
3 - fill the array
4 - transfer array to worksheet

[Bob Phillips]

Also, if you compare your initial code w/david000's, the reason you didn't "see" anything is because you created an array and placed a value in it, but didn't do anything with it

As was pointed out in post #2