Very powerful. Your Sub provides to me a quantum leap in internalizing arrays.

I have wondered how I could sum a "column" in Arrays. Previously, I had been unknowingly using
arrays (via w/s Row,Column ranges). Never would I have thought of using the Index function coupled
with arrays to Sum.

Do you have any other quick examples? I.e. a VLookup for Arrays?

Would it be something like:

Sub Demo()
Dim m, t
Const All As Long = 0
m = [{1,2,3; 21,22,23; 31,32,33}]
With WorksheetFunction
t = .VLookup(21,(.Index(m, All, 1),FALSE)
End With
End Sub

Also, why use "Const" vs All = 0?

Also, what is advantage for m=("One", "Two", "Three") vs
m=({"One", "Two", "Three"}) ?

EagleOne

Dana DeLouis wrote:

I am new to Array VBA code.
Therefore I would never have expected that one could use a worksheet
function to manipulate a VBA created Array.

Hi. Just some ideas to add to your Library you may find interesting.
Given a 3*3 array, take the 3rd column of 'All' rows, and sum them up.

Sub Demo()
Dim m, t
Const All As Long = 0
m = [{1,2,3; 21,22,23; 31,32,33}]
With WorksheetFunction
t = .Sum(.Index(m, All, 3)) 'Or just use 0 for 'All
End With
End Sub
'3+23+33 = 59

A good technique when working with Arrays is to use the "Locals Window"
when stepping through code. It's a good way to check one's array dimensions.
= = = =
Dana DeLouis


wrote:
Dana, much appreciated!

As you can tell, I am new to Array VBA code.

Therefore I would never have expected that one could use a worksheet function to manipulate a VBA
created Array.

EagleOne

Dana DeLouis wrote:

wrote:
Dim strArr() As String
Dim iCtr as Long

ReDim strArr(1 To 100, 1 To 3)
......
......

Getting an error if I code:
ReDim Preserve strArr(1 To iCtr).elements(1 to 3)

Any thoughts appreciated!

EagleOne
Any ideas here you can use?

Sub Demo()
Dim m()
ReDim m(1 To 1, 1 To 1)
m(1, 1) = "Test Data"

With WorksheetFunction
ReDim Preserve m(1 To 1, 1 To 3)
m = .Transpose(m)
ReDim Preserve m(1 To 3, 1 To 100)
m = .Transpose(m)
End With
End Sub

'It's resized to (100 by 3)
= = =
HTH :)
Dana DeLouis