Function for last Three Posts



Code:
Function FuR_Alan(ByVal rngIn As Range, ByVal FoutRw As Long) As Variant
10  ' use "neat magic" code line    arrOut() = Application.Index(arrIn(), rwsT(), clms())  '                 http://www.excelforum.com/excel-new-...ba-arrays.html                      http://www.mrexcel.com/forum/excel-q...ml#post4375354
20  ' BUT in Cells form             arrOut() = Application.Index(Cells, rwsT(), clms())    '                      http://www.excelforum.com/excel-prog...t-range-2.html
30  Dim ws As Worksheet '                                      ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template   etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense )
40  Set ws = rngIn.Parent                                      ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-prog...ml#post4387191
50                                ws.Range("K30").ClearContents: ws.Range("K30").Value = "Here I am, in this Worksheet!"
60  'clms()
70  Dim sClm As Long, Cs As Long 'Variable for Count of, Start Column. - This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for when the variable is filled in. (Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647). If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.-upon/after 32-bit, Integers (Short) need converted internally anyway, so a Long is actually faster)
80  Let sClm = rngIn.Areas.Item(1).Column: Cs = rngIn.Areas.Item(1).Columns.Count '                    A Range in a Worksheet must not be continuous.  The way I understand it, a Range object is a group of cells in a worksheet.  The list of the cells of the range is organised in rectangular groups of cells, each one named an Area.  Post #27  http://www.mrexcel.com/forum/excel-q...-column-3.html       http://www.eileenslounge.com/viewtop...181736#p181736
90  Dim clms() As Variant '           Evaluate Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
95  Let clms() = Evaluate("column(" & CL(sClm) & ":" & CL(sClm + (Cs - 1)) & ")")
100
160 'rwsT()
170 Dim sRw As Long, Rs As Long '
180 Let sRw = rngIn.Areas.Item(1).Row: Let Rs = rngIn.Areas.Item(1).Rows.Count
240 'Get Full row indicies convenientally ( As 1 D "pseudo horizontal" Array ) from Spreadsheet Column() Function
250 Dim rws() As Variant: Let rws() = Evaluate("column(" & CL(sRw) & ":" & CL(sRw + (Rs - 1)) & ")")
260
280 'Get full sequential row indicies in a string.
290 Dim strRws As String: Let strRws = VBA.Strings$.Join(rws(), "|") '            'The VBA strings collection such as Join in there basic form must not returnn a string, they can also return for example Null, a special type of variant. That lies within it's "powers. - It will coerce even an Empty, or Null to a variant type and return that. That takes extra ( unecerssary work here ). If the result of a function is used as a string or assigned it to a string variable, use the $ form of the function. This results in faster executing code, because a conversion from a variant to a string is unnecessary.      http://www.excelforum.com/excel-new-...ml#post4084783      I believe that without $ a Strings collection Function coerces the first parameter into  Variant, with $ does not - that's why $ is preferable over no $ , it's theoretically more efficient.        http://www.xoc.net/standards/rvbacc....rSignFunctions
300
330 'Get String with missing row
340 Dim strrwsD As String: Let strrwsD = Replace(strRws, "|" & FoutRw & "", "", 1, -1)
350
360 'Get Array ( 1 D Pseudo Horizontal ) of required row indicies
370 Dim rwsS() As String '              The VBA Strings Collection Function, Split, used below returns a Field of String Element Types so the Array Elemments must be Declared appropriately. It must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
375 Let rwsS() = VBA.Strings$.Split(strrwsD, "|", -1)
380 'final Transposed Array for "magic neat" code line
390 Dim rwsT() As String: ReDim rwsT(0 To (UBound(rwsS())), 1 To 1) '          Both the type and size of Array is known so can be decared initially appropriatelly. Re Dim must be used as Dim only takes values, not variables
400 Dim Cnt As Long
410     For Cnt = 0 To UBound(rwsS())
420      Let rwsT(Cnt, 1) = rwsS(Cnt)
430     Next Cnt

440 'Output Array
450 Dim arrOut() As Variant
460 Let arrOut() = Application.Index(ws.Cells, rwsT(), clms()) '"Magic neat" Code line in Cells first argument Form
470
480 Let FuR_Alan = arrOut()
490
500 ' . Transpose
510 Dim rwsDotT() As Variant '         Transpose Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
520 Let rwsDotT() = Application.Transpose(rwsS())
530 Let arrOut() = Application.Index(ws.Cells, rwsDotT(), clms())
540
550 Let FuR_Alan = arrOut()
'
End Function

Function Code for getting Column Letter from Column Number is in main Thread

http://www.excelfox.com/forum/showth...=9837#post9837
Public Function CL(ByVal lclm As Long) As String
and Further given again along with an 'opened up' and 'Commented version in next post