Using Index from VBA

[jmbob666]

I recently discovered the power of the Index function. It would be very helpful if I could only get it to work from VBA. For example:

Dim rYD as range
Set rYD = Range("C3:F10")
Data = Application.WorksheetFunction.Index(rYD, 1, 4)

Will return the value of what is in row 1, column 4 of range rYD, IE the value of F3. Not a problem. I on the other hand want to return the entire 4th column of data, as shown below:

Dim rYD as range
dim Data as range
Set rYD = Range("C3:F10")
Data = Application.WorksheetFunction.Index(rYD, , 4)

Unfortunately you get "Compile Error: Argument not Optional" when you run the macro. In a spreadsheet I can get the column if I array-enter the formula. Does anyone know how to get Index to put the data into a range variable?

 

[AlphaFrog]

VBA has sort of built-in indexing...

Dim rYD As Range
Set rYD = Range("C3:F10")(1, 4)
...this returns cell F3


Dim rYD As Range
Set rYD = Range("C3:F10").Columns(4)
...this returns range F3:F10

 

[rs2k]

jmbob666, welcome to the board.

I can't answer your question, but have you tried 'recording' a macro to see what the syntax is?

Colin

 

[Rick Rothstein]

Why are you trying to vector through the worksheet function when you can simply use the properties of the Range directly?

Single Cell ==> Range("C3:F10").Cells(1, 4)

Range of Cells ==> Range("C3:F10").Columns(4)

And you can use the Rows property to get a range of rows.

 

[Jonmo1]

You should never need to use Index in VBA
We have very similar built in functionality..

Try

Dim rYD As Range
Set rYD = Range("C3:F10")
Data = rYD(1, 4)

And

Dim rYD As Range
Dim Data As Range
Set rYD = Range("C3:F10")
Set Data = rYD.Columns(4)

MsgBox Data.Address


Hope that helps

 

[jmbob666]

Thanks AlphaFrog! I never realized you could cut the column or a row out of a range so easily!! As other posters have mentioned, with this kind of built in minipulation of a range I really don't need Index in VBA.

Appreciate your help!!

 

[jmbob666]

No I didn't try the macro recorder, rs2k. I was too focused on using the Index fuction. I just tried it but I wasn't able to get the macro recorder to reflect that I wanted to cut one column out of a defined range. Good reminder to try the macro recorder though! Thanks!!

 

[jmbob666]

I appreciate all the help you guys have given me! Thanks again!!

 

[mikerickson]

If you are working with arrays rather than ranges, Index is very useful in VBA

Dim myArray(1 To 10, 1 To 10) As Long
Dim mySingleColumn As Variant
Dim i As Long, j As Long

For i = 1 To 10
    For j = 1 To 10
        myArray(i, j) = 100 * i + j
Next: Next

mySingleColumn = Application.Index(myArray, 0, 3)

MsgBox Application.Sum(mySingleColumn)

 

[acontrario]

The compile error is normal.
I am no Excel specialist at all but I am here because I have another problem to solve (exceptionally, some data cleansing in various very long tables (unknown length) where I have to treat specifically some columns. So I have to move ranges to inverted arrays with their last dim adjusted to length (= nbr or rows), process in memory (in arrays and not in worksheet), then update the sheet with the again inverted matrix)). Not difficult but fastidious. And if badly programmed, it takes ages. The Excel old transpose function does not work beyond a certain size. Working on ranges is not efficient enough for me, so I must work on arrays, just like in the above example.

I was in fact looking for a solution to a dynamic table so the above is still static & it does not suit me enough. But the above instruction is still a very interesting question.
I checked and, so far, I use only "= range" instructions as mentioned below as a solutions by others, not the "application.index", and this is why it caught my eye (I did not know its existence).

So, to provide a precise response to your very specific question, very very late..., and to correct the syntax error encountered, for what it is worth, and for others coming across this hurdle, here is what I wrote in a "option explicit" context.


Option explicit
Option base 0

Sub test1

'
' all tests to open file & worksheet
'

Dim rYD As Range
Dim Data As Variant
ReDim Data(1 To 4, 1 To 2) 'not necessary as Data instruction should create the array - however this programming is just for the illustration of the matching instruction "Data =" below
' (not to be used in real life as dimensions are usually not known (need to be tested & more hurdles to the right ReDim in a dynamic (<> static) context),
' a simple spelling error can lead to surprises (even when a Dim creates the array, the "Data =" below may create somethging else),
' references are not clear (square braquets = evaluate what is in it but no worksheet formally specified followed by a dot, so by default the current sheet is activated but it can be any sometimes),
' etc.


Set rYD = Range("C3:F10")


Data = Application.Index(rYD, [row(4:7)], Array(2, 4))
'[ ] = evaluate( ) or = range( ) I am not sure as I did not find (& did not look for) a MS reference but it works, comments welcomed
'here, I choose a subset of rYD on purpose for illustration only - subset of rows, subset of columns
'


MsgBox "D6 = " & Data(1, 1)
'this displays the desired result proving that it works very well

End Sub


This test sub worked for me and I tried also various other subsets before posting it.
I still struggle with the nature of Data. It can in fact be created as an array without a Dim created ahead, unless mistaken. So, this frightens me because of the "option explicit" before my test sub. I will re-test but I am almost sure I saw the Data in the "Locals" variable window fully populated (variant/variant) without me having defined Data with a Dim Data() as variant. I added the Dim / Redim afterwards. So, the "option explicit" seems ineffective in this situation.

I am not sure why it works. A see it as a little miracle as I piled things one on top of another from previous subs written without any in depth mastering this topic, just for the fun. I write a VBA macro every 2 years, so a steep learning curve each time.

What I found great with .index, is that with this .index method, you can easily slice & dice in any 2 dim an array to get only what you want and place it easily as output in another memory area. So, I relate very well to Mikerickson's comment below. It can offer a huge time saver when running macros on large volumes that can fit in memory (I tested this on a 50.000 lines table). But I did not tested it yet for speed against the classic range instruction.

My initial issue is still to get this to work more generically and elegantly in a flexible environment.
So for example, in a table where the numbers of lines (rows) need to be tested and the array (last dim) automatically redimensioned using a programmed matrix transpose function with a little loop in memory (array rather range).
My above lines seems to me not secure programming for my needs at this stage.

I will try making them dynamic using Rows.Count).End(xlUp), CurrentRegion.Rows.Count and UBound(). And, for programming security, references of ranges need also to be formalised in front of the ranges for my "real life" personal little macro.