+ Reply to Thread
Results 1 to 9 of 9

Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    Hi
    . This, I felt could well fit in a s a follow up in many Threads I have been in, for example
    http://www.excelforum.com/excel-new-...ml#post4093924
    http://www.excelforum.com/excel-prog...ml#post4139923
    .. but they are a bit over used.. so here goes with a new one:
    .
    . Background:-
    . (i) With one exception I never use or pass on code lines that I do not understand. The exception is the use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays.

    . (i) I could not resist using it as I found it so useful and quick As A “One Liner” at producing an Output Array derived from an Input Array using a code line of this form

    arrOut() = Application.Index(arrIn(), rws(), clms())


    where the Arrays rws() and clms() contain a list of "row" and "column" Indices of the rows and columns in the Input Array required in the Output Array.

    . As often I have got close to answering the question in carefully preparing my Thread.. But any confirmation of my ideas or further comments from anyone that knows or thinks they may know would be very grateful. Up until now I have had the impression that no one alive really knows for sure what is going on.
    .
    . So
    . 1 ) Main Question. How exactly is .Index working here.
    . 2) Minor Question. Why doesn’t .WorksheetFunction.Index not work. ( Up until now most people have thought they must work identically ) ( The error comes at compilation being that the second “row” argument has an incompatible type )
    . .....

    . To elaborate and demonstrate the situation.

    . Consider a simple 4 x 4 , ( “A1:D4” ) , Range which I capture to my Input Array arrIn(). The retuned Output Array arrOut() I paste out to some arbitrary Range whose Top Left Corner is cell F6

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    A1
    B1
    C1
    D1
    1
    2
    A2
    B2
    C2
    D2
    2
    3
    A3
    B3
    C3
    D3
    3
    4
    A4
    B4
    C4
    D4
    4
    5
    1
    2
    3
    4
    clms()\rws()
    6
    7
    8
    9
    10
    ExcelFGyanArraySlicingIndex

    ....
    . On and off I have done endless experiments on the above sheet..., ( a useful code which I developed to aid in this I share appended in the Test Area ...
    http://www.excelforum.com/developmen...ml#post4167308
    ......).
    I do my best to summarise, conventionally I give row and column indices in the r c sequence.: )( And Note a “Horizontal” Table can represents both a 1 dimensional Array or a 2 dimensional Array of 1 row for rws() or clms() : - up until now all results are similar choosing either of these as Arguments- That is to say results are never affected by whether any Array is One dimension or 2 dimension with row, be it rws() or clms() )
    ..
    .. If I have Arrays of such indices: rws()-
    2
    3
    4



    Clms()-
    1
    2
    3
    4
    ..
    then I obtain typically arrOut()=
    A2 B3 C4
    ###


    .. It appears VBA attempts a pair wise Analysis Along the lines of how CSE Array formulas work ( giving an error for the non complete pair ) , but then chooses to do a “quasi ReDim Preserve and tack on the result of each Pair wise evaluation in a new column.

    . But straight away we have an exception to this rule, the case of a 1 element Array in one argument
    Rws()-
    3


    Clms()-
    1
    2
    3
    4


    Returning
    arrOut()=
    A3 B3 C3 D3


    ( .....This incidentally gives the same results as the classic Array “slicing” ideas which are more commonly known and discussed:
    arrOut() = Application.Index(arrIn(), 3, 0)
    .......... But with the advantage of the extra characteristic we find of this:


    Rws()-
    3


    Clms()-
    1
    3
    4


    Returning this
    arrOut()=
    A3 C3 D3
    ...............................)
    ....................
    . One of endless Interesting things to do is to try this combination ( Note a vertical Table represents a 2 dimensional 1 column Array for rws() or clms() ).
    Rws()-
    2
    3
    4


    Clms()-
    1
    3
    4


    Returning
    arrOut()=
    A2 C2 D2
    A3 C3 D3
    A4 C4 D4

    ... I see what is happening, can and have developed endless empirically derived rules to very effectively use these and endless further characteristics, but cannot see a clear explanation of how / why it is happening. As soon as I think I have a theory, some other experiment does not follow it!!. Why , for example does VBA “Re Dim Preserve” in the horizontal here ? . Have I “fooled it” into transposing it’s “tacking on” of the next row from “to the right” to “to the bottom”

    . Thread space limitations ( and reader patience ) will not let me give more than a small posting of my experiments... just for fun This
    Rws()-
    2
    3
    4


    Clms()-
    1
    2
    3
    4


    Returning
    arrOut()=
    A2
    B3
    C4
    ###

    .. Again a VBA doing the same “pair wise evaluation, but somehow “fooled into” taking on below rather than to the right??
    ..
    .Has anyone any comments. Can anyone point me into clear documentation to these characteristic. ( Why does .WorksheetFunction.Index not share the characteristics )

    Thanks Alan.
    P.s. No Rush I shall continue to give good Thread solutions using these characteristics , but I would very much like at some point to understand what I am doing

    P.P.s. Just noticed that the Bl__dy thing will take Multidimensional Attays for rws() and clms(). Wish it hadn’t - It's gonna be a long night of experimenting

    Referrences:
    http://www.excelforum.com/excel-new-...ml#post4093924
    http://www.excelforum.com/excel-new-...ml#post4093843
    http://www.excelforum.com/excel-prog...ml#post4139923
    http://www.excelforum.com/excel-prog...ml#post4093653
    http://www.excelforum.com/excel-prog...ml#post4088055
    http://www.excelforum.com/excel-prog...-based-on-cell
    http://www.mrexcel.com/forum/excel-q...l?#post4174322
    http://www.excelforum.com/excel-new-...ba-arrays.html
    Attached Files Attached Files
    Last edited by Doc.AElstein; 08-27-2015 at 07:49 AM. Reason: Wankin a Awrays
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    Why on earth would you post this in New Users/Basics??
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    Hi Rory
    Quote Originally Posted by romperstomper View Post
    Why on earth would you post this in New Users/Basics??
    Thanks for the surprise .. reply. (.. - Must be one of the longest No reply posts ever caught!! , thanks for bringing it up at least!! )

    To try and answer your question:
    I thought at the time it was a basic Fundamental Question, - not knowing the answer myself but thinking the Index Function itself was so widespread that someone somewhere would understand it.. fully. So I had no idea that it was out of place here to ask, sorry.
    Was Difficult to know when I don’t know the answer myself to know it was such an “advanced” question, if that is wot you mean?

    There are no Forums I know of for “Advanced” but fundamental questions. The Main Forums seem to be for a specific “trouble shooting” Problem, getting a working answer quickly where understanding takes a minor or no role. I am getting good at going through the motions with a “working answer”. Maybe i should be satisfied with that.
    Sorry if I posted “wrong” somehow. Playing with Big Arrays does seem to be getting popular
    http://www.excelforum.com/tips-and-t...ml#post4208853
    , and the technique I detailed in Post #1, seems to be useful enough to warrant someone somewhere understanding it. Seems no one does, which always worries me a bit...
    http://www.mrexcel.com/forum/lounge-...ml#post4080108




    _....................

    Anyways, FWTFIW, I sort of asked along the way here and there in the meantime. The nearest I got anywhere was a partial answer to the minor second question 2) , the theory I made up or guessed myself, but it sort of ties up, partly with anything anyone knows.....
    http://www.excelforum.com/excel-prog...ml#post4199234



    I guess it is not a big deal. I just find it helpful to understand code as I can use it better, and I just do not like using code I do not understand. And I like learning. An affliction I should try to get over I suppose.

    Hope that sort of answers your question !! I think i am getting the point i should not post things like this

    Thanks again for replying Rory, ( I know that still ‘aint easy for you just now. ! me too....sometimes..
    http://www.excelforum.com/suggestion...ml#post4210251
    )
    Alan

    P.s.
    So I suppose maybe I should mark the Thread as solved then , the answer being it is not solvable ( by man ) ??

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    The fact that you don't get an answer in a forum thread doesn't necessarily mean that nobody knows!

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Excel VBA Index Function with multiple value row and column arguments

    Hi, Somewhere along the line I think I figured out enough to answer this Thread, so I thought I would solve the Thread for future reference.

    Something needs to be understood I think to see what is going on here. That is how Excel works in, and in particular how it works in terms what we visualise as a spreadsheet. That is quite a tall order, so it is not surprising it took me a while to suss it out.

    It might be better to do just a quick graphical demo of what is going on in this post without saying too much detail first, and then go into more exact detail and theoretical explanations in the following posts.
    _..........

    So I have a rectangular spreadsheet range area or an Array or Grid or box or something similar.
    It has stuff in it.
    This 3x3 will do:

    Row\Col
    T
    U
    V
    Grid
    26
    0
    10
    20
    27
    2
    12
    22
    28
    4
    14
    24


    In Excel and VBA You can do simple single Index function type wonks a lot. That is something like map reading to get to see somewhere in a map using the coordinates on a grid.
    So look at the above screenshot as a sort of map with x , ( “column” ) , axis of 1, 2, 3 and y , ( “row” ) , axis of
    1;
    2;
    3

    What Excel or VBA Index is all about is returning ( giving ) you what is at the place specified by the coordinates, ( normalised so that top left is 1, 1 ) . VBA Index code lines or spreadsheet Index formulas look like , pseudo:
    VBA
    Singlevalue = Index ( Grid , row , column )
    Singlevalue = Index ( Grid , 2 , 2 )
    Singlevalue = 12
    So I end up with the variable , Singlevalue , sort of “holding” the number 12

    Excel [ written in a cell ( seen in formula bar) ] :
    [ = Index ( range , row , column ) ]
    [ = Index ( T26:V28 , row , column)
    [ = Index ( T26:V28 , 2 , 2 ) ]
    Finally seeing in the cell as value :
    __ [ 12 ]

    In other words “Give me from Grid what is at position 2, 2 in it
    ( In excel and VBA top left is usually taken as the origin, and we “count ”Items”” , or “Index” numbers , going along columns to the right then down to start again at the next row from left. - In Excel we often refer to A1 as the first cell and B1 as the second cell )

    Sometimes you can do multiple wonks in one go pseudo like
    Multiplevalues = Index ( Grid , rowS , columnS )
    Multiplevalues = Index ( Grid , {2, 3} , {2, 3} )
    Multiplevalues = {12, 24}
    Multiplevalues will be a variable holding an array or values, in this case a 1”vertical” x 2”horizontal” array._..

    _.. and in the spreadsheet there is a way to get like shown as final values across two cells :
    ________ [ 12 ][ 24 ]
    Sometimes, not always, Excel or VBA lets you do multiple value evaluation like that.
    In other words, sometimes, it lets you put multiple values as arguments in formulas and functions , ( possibly in the form of ranges references, or as arrays of values ) , in the places where you usually fill in just single value arguments

    If it does let you do that, and if you are lucky, it will give you the output in a simple to understand form which mirrors the order and orientation you gave it stuff.

    Also, often if you can give multiple values where normally you just put one, then you can just as well give it 2 Dimensional grids or arrays of indices as the rowS and columns and get a 2 Dimensional grid out, like:

    Row\Col
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    Out Grid
    =Index(
    In Grid
    ,
    RowS
    ,
    ColumnS
    )
    26
    0
    22
    0
    10
    20
    1
    2
    1
    3
    27
    14
    12
    2
    12
    22
    3
    2
    2
    2
    28
    4
    14
    24

    _......
    So:
    When Excel or VBA has arrays of values in arguments where normally it takes single values, it seems to do something like this, ( not necessarily in the order of things as I write them):
    _ Usually takes the argument arrays as all having the same top left point.
    _ Has a look at the size and dimensions of those argument arrays. Makes available an array for output. That output array has an orientation and maximum sizes , ( dimensions ) , equal to the largest of any of those argument arrays.
    _ It then considers for each output position in the output array like this: It starts top left and then like is going along the columns to the right , then down to the next row, whipping back to the left then going along the columns to the right , then down to the next row, whipping back to the left then going along the columns to the right , then down to the next row, whipping back to the left then going ...
    To get each output value it evaluates the formula or function as if it had single values using the values from the argument arrays, ( if there are any ) , in the same corresponding position to the position being considered for the output. In that position in the output array is put the result of that evaluation. Follow the colours in the above screen shot: Excel or VBA is sort of pseudo like doing it for as many places it has in the array for output, like doing the formula evaluation 4 times using single values for the arguments like:
    =Index(InGrid , 1 , 1, ) ____ =Index(InGrid , 2 , 3, )
    =Index(InGrid , 3 , 2, ) ____ =Index(InGrid , 2 , 2, )

    ( the type of the member elements in the returned output array is Variant. Variant allows, for example, for Empty or error. ( An error , for example, might be returned if an argument array is not large enough to have a member element to match the current member in the output array under consideration, ( evaluation ) )


    _..................

    If you play around with the numbers you can get this
    Out Grid
    =Index(
    In Grid
    ,
    RowS
    ,
    ColumnS
    26
    0
    20
    0
    10
    20
    1
    1
    1
    3
    27
    4
    24
    2
    12
    22
    3
    3
    1
    3
    28
    4
    14
    24
    What I have done there in the last screenshot is effectively picked out of the Input Grid, the first and last row, and the first and last column.
    _....

    Where and why I started this thread was getting the same results as in that last screenshot with this sort of situation where the rowS were in a single “breadth” “vertical” array and the columns were in a single “breadth” “horizontal” array_.......
    _.. like this: ( Pseudo like _..
    ___________ =Index( _ Grid , ______ { 1; 3} , __ { 1, 2 } _ )

    Out Grid
    =Index(
    In Grid
    ,
    RowS
    ,
    ColumnS
    )
    26
    0
    20
    0
    10
    20
    1
    1
    3
    27
    4
    24
    2
    12
    22
    3
    28
    4
    14
    24


    I really pissed off a lot of people off , including myself, investigating what was going on here. So now I know, I will annoy us again now one last time and explain it all in the next posts.
    Last edited by Doc.AElstein; 01-29-2017 at 04:07 PM.

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Excel works like this: We know why don’t we :) '_-

    Excel works like this: '_-

    I see, with my little eye, a spreadsheet

    Excel only has a model of one cell that includes all the stuff of a cell. That information is stored in a massive thing ( http://www.excelforum.com/showthread...11#post4551080 ) called a Range object.

    A spreadsheet is made to look like lots of cells on our screen by the software holding similar information as in the one cell at a specific offsets in memory locations. Those offsets are somehow synchronised to the rate at which the information is put on the screen. So we are fooled into thinking we have a sheet with cells spread out across our screen. You could think of it as a spreadsheet if you like. ( I expect someone did ? ).


    I tend to consider these theoretical things as both VBA and Excel. One can think of Excel as a lot of pre written VBA codes which kick in when you do stuff like hitting Enter. So the way Excel and VBA “works” is pretty well the same as it is pretty well the same thing.


    It is all relative like
    One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread...t=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. ) )

    Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value) .

    Another thing you pick up along the way is that if you change anything anywhere on a spreadsheet then all cells are updated, even if they do not need to be . Excel just is written that way. It does that as it does not know, for example, what cells are dependent on others through formulas and the such. ( That is one reason you often here that VBA codes that “do things” to a worksheet can be slow as every interaction “slams the brakes on” whilst the whole worksheet is updated. Often in a code you will see Screen Updating turned off until the end of the code when all the updating is then done in one go rather than at every interaction. All the “VBA Array stuff” is about is like, for example, making an internal copy matrix of cell values, doing any processing you want to on them internally, then “pasting them out” to the worksheet in one go. You can do that all in one go stuff efficiently as you just like use the same single offset value for all the values to put them in the place for those things in the referenced Range object)

    Quick “revealing” . Implicit Intercept Wonks
    I expect Excel may have been written by someone who knew a bit about maths and computing at the time when computers were not yet so mega fast with almost infinite memory, so he / she probably tried to make it as efficient as possible.
    That means making the recalculation quicker. ( Here I am talking about the first calculation as being done for example, when I type a formula into a cell and hit Enter. It is not usually a big problem if that takes a while. But the point is that if I now do any change anywhere on the worksheet, then that formulas and all other formulas are recalculated. As noted, Excel recalculates , even if it does not need to, as it does not necessarily know what formulas effect which ).
    This may at this stage all seem a bit of an aside, ( if not already), but it is relevant to what actually makes the “magic code line” work which is the subject of what this Thread is all about.

    =A1 “works” straight away
    As part of making Excel and VBA a bit more efficient, I am thinking that any reference to a cell or group of cells ( so in other words referring to any Range object ) is given some help matrix of initial, if you like, default, ( often referred to as implicit ) offsets. Or another way of looking at this is to say that every simple cell or cells reference has, or has “held associated with it in memory” an initial set of help values available. When a =A1 reference in a cell is “done” either by Hitting Enter in that cell, or as a result of the recalculation when hitting Enter anywhere else, I have some offset value which has been used to synchronise what I appear to “see” as a cell position. For every simple single cell reference, like =A1 , the value in this matrix is given that of the cell value A1. Excel is set to work on the implicit default initially. This will always return immediately the correct value

    =A1:B2 doesn’t “work” straight away
    Consider as an alternative a multiple cell reference like =A1:B2. I am thinking Excel and VBA is set to work differently. I believe the implicit default process is “not switched on”. Or it is switched on but has no values assigned in it. I am not sure. It is probably a conceptual thing. In any case , attempting to get something out of it , by, for example, typing =A1:B2 in a cell and hitting Enter, will not “work”. Excel will error. I believe the help matrix could be there. Possibly it goes by a different name now. In any case I believe it contains all values of the spreadsheet. The process to get =A1:B2 not to error is to somehow Control a Shifting of that help matrix so that it ( or the cell being considered – it is all relatively speaking ) is effectively at the correct value when then a hit of Enter will “reveal” the correct value. This would need to be done as many times as there are cells in the reference. It makes sense from the offset ideas already discussed that the presented output of values will somehow somewhere have a form of a sub matrix or array of values with dimensions mirroring the range A1:B2. This process appears to be available to us. By some strange coincidence you can get such as reference not to error by hitting instead of Enter, the following three keys
    Ctrl+Shift+Enter

    =A1:C1 sometimes “works” straight away.
    This is a a single “breadth” “horizontal” . Once again , for similar reasons , this will not work in any cells. It requires the CSE Entry to ensure that it does not error. However, the Controlled Shifting done before Entering process has been made to work a bit more efficiently. I expect something to aid in the efficiency has in fact been done. I expect the help matrix available is filled for all “rows” over a column range of A:C with the values in A1:C1. This allows that the process must only Control its Shift in one dimension before Entering. This is because for every cell in a worksheet, such a reference does not need to shift to different rows in the help matrix, if all the values in each row are given the same value as in the first row over the column range of A:C. The use of Hit sets in the default process of revealing the value in the matrix at that relatively seen cell. So if that formula , =A1:C1 , is placed anywhere in column A, ( other than A1 ) , the value in A1 is returned. Similarly if that formula , =A1:C1 , is placed anywhere in column B, ( other than B1 ) , the value in B1 is returned. Similarly if that formula , =A1:C1 , is placed anywhere in column C, ( other than C1 ) , the value in C1 is returned.
    This idea goes by the name of Implicit Intersection. It is a by product that we can use it to our advantage. In a spreadsheet it can be useful to speed up formula calculations. It is known about , but not too widely. See here for example, for a recent blog on it:

    https://fastexcel.wordpress.com/2016.../#comment-4074



    Implicit Intercepts and VBA
    This is my crutch of understanding the way I am using Application.Index with Look Up Rows and Columns Arguments as VBA Arrays in the form discussed in this Thread. I am still looking initially here at spreadsheet things as it does help to give a graphical description of what processes I am actually using in that VBA “magic code line”. I will do that graphically in the next post.
    But here just a quick review of the CSE stuff. Consider a simple Worksheet range
    Row\Col
    A
    B
    C
    1
    a ba
    2
    b bb
    3
    c bc

    Now do some CSE wonks on it

    CSE type 0 ( Like not doing CSE )
    Write this, =A1:B2 , in any cell and hit Enter. It errors. We know why now , '_- don’t we
    Write this , =A1:B1 , in any cell somewhere down in column B and Hit Enter. It gives you “ba”. Write it in any column other than A or B. It errors. We know why now , '_- don’t we

    CSE type 1 Wonk
    Write =A1:B2 in any cell. Do this
    _ Click in the formula bar or hit F2 or don’t bother.
    _ Hit Ctrl+Shift+Enter
    It does not error. We know why now , don’t we .
    It gives us “a”. Why is that ? We said that this works and the returned values are somewhere held ”internally” and mirror the layout of that range area in the worksheet like this:
    a ba
    b bb

    We talked about us seeing what is synchronised to something like a time base going across the screen. That is set to default to the cell selected at the point of first using the formula, just after the hitting of Enter.
    Things tend to be normalised to “top left” being like 1, 1. Our discussions would suggest that the extra values returned are offset from this top left. So we are only seeing the first
    There are occasions in formula work where we want those values that are “available” to use in a formula, but do not necessarily want to see them
    Consider this
    Row\Col
    A
    B
    1
    1
    2
    Now type anywhere this: Sum(A1:B1). The Excel Sum Function recognises that argument, A1:B1 as a range, ( because the Excel Function Sum is expecting that syntaxly ) , and it will take the values in like this .. Sum({1, 2}) .. and will sum them to give 3. That is what Sum does when given a range reference as its argument.
    Try this formula in any column other than A or B Sum(A1:B1*3). Excel will not know what to do with A1:B1*3 as it does not recognise that as a range. It will error. ( As an aside to refresh what we have learnt, put that formula somewhere down in column B. Implicit intercept gives us the value in B1 , 2 , for the reference A1:B1 . So we effectively “sum” just one value which is 2x3=6 ). Put the formula in again anywhere and do the CSE stuff. Excel will be return for A1:B1*3 , { 3, 6 } . The Excel Function recognises that argument, ( it accepts an Array as an alternative to a range reference ), and returns 9.
    So that is useful
    )
    So we will leave that and call it a CSE type 1 Wonk.

    CSE type 2 Wonks
    Following on from the last section, we may have a situation where we want to “see” all the available evaluated values.
    Whether by design, or accident, the spreadsheet cell area “revealed” as the synchronised raster or whatever goes across the screen is the area ( cell ) displayed is that selected just before hitting Enter, ( in the normal Enter or C+S+Enter ) . We know now, don’t we, , that the values are “there” offset a bit from the first value, ( which somewhere along the way we said is normalised to top left.. – we “see” top left as the first cell, (1, 1) ). So it is probably a good guess to try selecting an area big enough and in the correct orientation to “reveal” all the values.
    So lets try:
    Using the same couple of values in a worksheet as shown in the last screen shot.
    _ Select any spreadsheet area of two adjacent cells , ( 1 row, 2 columns )
    _ Hit F2 or click in the formula bar
    _ Type in =A1:B1
    _ Hit Ctrl+Shift+Enter
    You should get this
    1
    2
    We probably get the point why, don’t we

    _...

    _...Next post , is based on the things going on “behind the scenes” , or “internally” in Excel which gives the results discussed in this post, and goes on to explain that it is the application of that which allows for and explains the “magic” code line which this thread is all about. ..... '_-....


    http://www.mrexcel.com/articles/CSE-...ulas-excel.php
    https://fastexcel.wordpress.com/2016.../#comment-4074
    https://app.box.com/s/avk6paydbtame1hz7ge5zenh6ll1p35e
    https://fastexcel.wordpress.com/2016...subscription-3
    https://fastexcel.wordpress.com/2016.../#comment-4074
    http://www.excelfox.com/forum/showth...on-and-VLookUp
    http://www.excelforum.com/showthread.php?t=1172587
    Last edited by Doc.AElstein; 02-08-2017 at 12:13 PM.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Hold my implicit offset intersexual intercept excepting intersect automagically wonks.



    In the last couple of posts we were looking at spreadsheets, but also mentioned that Excel and VBA are pretty well the same sort of thing. Certainly it is reasonable that any “workings going on” will be using the same basic processes.

    Coming back to a simple spreadsheet example like this:
    Row\Col
    A
    B
    C
    1
    1000
    2000
    2
    3000
    4000
    3


    Following directly from the last section on CSE type 2, _..
    _..but do it a bit wrong:

    Select any 3 x 3 cell area in the above spreadsheet, ( other than A1:B2 )
    _ Hit F2 or select the formula bar
    _ Type in =A1:B2
    _ Do the Ctrl+Shift+Enter wonk stuff

    You get this
    1000
    2000
    #NV
    3000
    4000
    #NV
    #NV
    #NV
    #NV


    We know why, don’t we , .: - '_-EP we “reveal” too much, that is to say, for relative to top left, excel is only “holding” an array of values ( or offset coordinates ) of ( 1, 1 ) , ( 2, 1 ) ; ( 2, 1 ) , ( 2, 2 ) , and so we error for outside that range.

    _........................

    Try this instead
    Select any 3 x 3 cell area in the above spreadsheet, ( other than A1:B2 )
    _ Hit F2 or select the formula bar
    _ Type in =A1
    _ Do the Ctrl+Shift+Enter wonk stuff

    You get this
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    1000
    and why we are at it , try this as well:
    Select any 3 x 3 cell area in the above spreadsheet, ( other than A1:B2 )
    _ Hit F2 or select the formula bar
    _ Type in =A1:B1
    _ Do the Ctrl+Shift+Enter wonk stuff

    You get this
    1000
    2000
    #NV
    1000
    2000
    #NV
    1000
    2000
    #NV

    '_-EPWe know why... Don’t we?
    Excel “holds” a single cell reference ( or holds “implicit offset intersexual intercept excepting intersect automagically wonks” or whatever ) as all the values in a matrix of equal to the spreadsheet size.
    Excel “holds” a single “breadth” cell reference ( or holds “implicit offset intersexual intercept excepting intersect automagically wonks” or whatever ) as all the values in that extended range in a matrix of equal to the “width” of that and extending in the dimension “not used” across ( or down as appropriate ) to a size equal to the spreadsheet columns ( or rows as appropriate ) count.

    _..............................

    I gave this sort of thing in a formula in VBA :
    =Index( _ Grid , ______ { 1; 3} , __ { 1, 2 } _ )
    =Index(
    In Grid
    ,
    RowS
    ,
    ColumnS
    )
    0
    10
    20
    1
    1
    3
    2
    12
    22
    3
    4
    14
    24
    I got this out
    Out Grid
    0
    20
    4
    24
    Why is that?
    It does not matter where a single value or a single “breadth” of values is held in any spreadsheet, or Array in Excel or VBA. It uses the same basic processes. In order for its intercept offset intersect process to work efficiently it “holds” a single reference as all the values in a matrix of equal to the spreadsheet size and similarly “holds” a single breadth reference( as in the two index arguments here ) as all the values in that extended range in a matrix of equal to the “width” of that and extending in the dimension “not used” across ( or down as appropriate ) to a size equal to the spreadsheet columns ( or rows as appropriate ) count.

    I am looking at two single breadth arrays of length 2 in a “horizontal” and “vertical” orientation. VBA allows us sometimes us to use multiple values in formulas and functions in the place of arguments where we usually give single values. It is very easy for Excel or VBA to do that. Basically all it ever does is the same thing with offset values.

    In my example it looks at the argument of 2 horizontal and 2 vertical so does the evaluation 4 times giving me the results in a 2x2 Array. But my 1 “breadth” Array arguments are “seen as” extending to the other dimension of the spreadsheet, ( or undefined length depending on your relative point of viewing it ).
    Take for example the RowS argument example. When VBA shifts along to the right to the position relative to top left of ( 1, 2 ), it does not find an empty. ( We might have expected this initially as the RowS argument only has 1 “column” ).
    Instead it finds the same value that was in ( 1, 1 ) .
    We know why now don’t we
    ( I am basically in VBA doing the equivalent of the CSE type 2 stuff on a single “breadth” range reference, whereby I select an extended area beyond the “width” of the “breadth”, ... if you get my drifts ( or Shifts )

    _......














    https://www.mrexcel.com/forum/excel-...ml#post4375354
    https://usefulgyaan.wordpress.com/20...e/#comment-737
    https://fastexcel.wordpress.com/2016...subscription-3

  8. #8
    Registered User
    Join Date
    03-19-2013
    Location
    Eugene, Oregon, USA
    MS-Off Ver
    Excel 2003, 2013, 2016, 365
    Posts
    5

    Re: Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    Given that there is, apparently, no official Microsoft reference documentation for Application.Index, even under the documentation for the Application object, I really appreciate Doc.AElstein’s thorough investigation of what it does and how it works. Really helpful!
    And, I do have to say that, given how useful the Application.Index method is, it is mind boggling that Microsoft provides no reference documentation for it.
    Last edited by pstraton; 06-03-2022 at 12:54 PM. Reason: Formatting

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

    It’s basically Application.Worksheetfunction.Index but with different error handling.
    Rory

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel arrays going down rows then across columns
    By JonT-W in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-01-2014, 02:15 AM
  2. Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  3. [SOLVED] Is it possible to use Application.Index to write 2 adjoining columns in one go?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-13-2013, 07:11 AM
  4. [SOLVED] Do User-Defined-Functions accept named arrays as arguments?
    By RogeratCCCC in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 05-02-2012, 04:17 AM
  5. Arrays - Rows and Columns
    By kraljb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2006, 06:55 AM
  6. [SOLVED] creating function with multiple arrays in arguments
    By Dave Marden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2005, 11:05 PM
  7. [SOLVED] passing arguments by postion through application.run
    By ben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1