Macro Correction(.txt to xlsb)

[Doc.AElstein]

Macro for last post

See that last post
for explanation

Code: Select all

' Improved Text Import using  Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})    https://eileenslounge.com/viewtopic.php?p=274367#p274367
Sub TextFileToExcel_IndxUnJgJgWay()  '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35100          http://www.eileenslounge.com/viewtopic.php?p=268809#p268809
Rem 1 Workbooks,  Worksheets info
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("macro.xlsb")      ' CHANGE TO SUIT
' Set Ws = Wb.Worksheets.Item(2)      ' second worksheet
 Set Ws = Wb.Worksheets("IndxUnJgJgWay") ' CHANGE TO SUIT
Dim lr As Long: Let lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row       '   http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
Dim NxtRw As Long
    If lr = 1 And Ws.Range("A1").Value = "" Then
     Let NxtRw = 2      '  If there is no data in the worksheet we want the second row to be the start row
    Else
     Let NxtRw = lr + 1 '  If there is data in the worksheet, we want the data to be posted after the last used row
    End If
Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\" & "NSEVAR_UnjJg.txt" ' "NSEVAR.txt"   ' CHANGE TO SUIT    From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629     DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic  NSEVER.txt: https://app.box.com/s/245h7i5nh6an8vw08g8t08fvu30ylih2
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by vbCr & vbLf ( Note vbCr & vbLf = vbNewLine )
Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5   etc...
' we can now make an array for all the rows, and we know our columns are A-J = 10 columns
Dim arrOut() As Variant

Rem 3 A 1D array of 1D arrays is built up by _....
Dim Cnt As Long
Dim arrUnjgdJgdRws() As Variant: ReDim arrUnjgdJgdRws(1 To RwCnt)
    For Cnt = 1 To RwCnt '               _.. considering each row of data
    Dim arrClms() As String
     Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
     Let arrUnjgdJgdRws(Cnt) = arrClms() ' Fill the next 1 D array element in the unjagged jagged array of rows
    Next Cnt
'3b   Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})
 Let arrOut() = Application.Index(arrUnjgdJgdRws(), Evaluate("=Row(1:" & RwCnt & ")"), Evaluate("=Column(A:J)"))
Rem 4  Finally the array is pasted to the worksheet at the next free row
' Let Ws.Range("A" & NxtRw & "").Resize(RwCnt, 10).Value2 = arrOut()
 Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = arrOut()
' Ws.Columns("A:J").AutoFit
Rem 5 to remove  http://www.eileenslounge.com/viewtopic.php?p=272606&sid=7e8ad1b708dd49a811498ccac6b1e092#p272606    .....     when i click on any cell that has output   there is an option numbers stored as text, convert to numbers,help on this error,ignore error,edit in formula bar,error checking options(these are the options coming)
'This does not need to be done in the  Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})  way
' Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISNUMBER(1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "),1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & ",A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "))")  '   Based on  =IF(F2="";"";IF(ISNUMBER(1*F2);1*F2;F2))
' Let Ws.Range("A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISERROR(1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "),A" & NxtRw & ":J" & RwCnt & ",1*A" & NxtRw & ":J" & RwCnt + (NxtRw - 1) & "))")
' Let Ws.Range("B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "").Value2 = Evaluate("=IF(B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "="""","""",IF(ISERROR(1*B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "),B" & NxtRw & ":D" & RwCnt & ",1*B" & NxtRw & ":D" & RwCnt + (NxtRw - 1) & "))")

End Sub

[DocAElstein]

Hello,
I did a text file to Excel answer yesterday, that was not particularly much use, but by coincidence another obvious general way to put a text file in Excel occurred to me whilst I was doing it….
I remember this old Thread has collected a few different ways including some unusual novel ideas, so I thought I would add this idea as well, just for future reference…
It’s very simple, perhaps the simplest, and might even be very efficient. It also works if you have different numbers of elements in different lines in the text file. (At first glance it seems also not to suffer from the problem of numbers in the text file coming out as text in the Excel File, the problem we had with some other ways used in this Thread originally)

Its so simple, its silly I never noticed it before.
The start of the macro follows one of the typical .. VBA Open For Get the whole string, manipulate it , then paste it in .. type way, with just a few differences: Instead of messing about looping , Spliting, building up an array, we replace all that codswollop with just one single code line like
TotalFile = Replace(TotalFile, ",", vbTab)
(where TotalFile is a string variable containing the entire text file)

Assuming the text file uses the typical line separator, vbCr & vbLf , then all we do is change the separator used in a line, ( I am using as an example the typical comma , in that example code line ) to what is used in the Windows Clipboard for the “cell wall” or cell separator, when it holds the values from, ( or for ) , an Excel range
( In the Windows Clipboard, the vbCr & vbLf is used to separate the lines / rows of an Excel range of valuer that it holds, so we don’t need to change that )

So then we just put that slightly modified text string of the entire file in the Windows Clipboard, and paste it out.
Simple
All in all, this seems a very simple and efficient way, and I don’ know why it never crossed my mind before.

Maybe I have missed some good reason why this way is not used?, and someone smarter can tell me?

_._____

Simple example. (Download both uploaded files and put them anywhere as long as they are in the same place, then run Sub Eeyore() )

Here’s the text file
A,B,c
1,2
Eeyore



Here’s the macro to put it in Excel. Rem 2 is the simple idea bit. In many other macros that section has a lot of looping, splitting, and stuff to build up the final array that you then finally paste out. If you have different numbers of text elements on different lines, as in the example, then that section would typically be even more complicated.

Code: Select all

 Sub Eeyore()  '   https://eileenslounge.com/viewtopic.php?f=30&t=35100&start=20
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                    ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Eeyore.txt"   '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum

' Rem 2 The simple stuff, change seperator to  vbTab
 Let TotalFile = Replace(TotalFile, ",", vbTab, 1, -1, vbBinaryCompare)

' The clipboard stuff, and paste out
Dim objClip As Object '                                                                        If you declare a variable as Object, you are late binding it.  https://web.archive.org/web/20141119223828/http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
 Set objClip = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")                       ' https://web.archive.org/web/20140610055224/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
objClip.SetText TotalFile
 objClip.PutInClipboard

ThisWorkbook.Worksheets.Item(1).Paste Destination:=Range("A1")
End Sub

Here’s the results you should get:

Eeyore.JPG

Alan

[HansV]

You might shorten the lines

Code: Select all

Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile

to

Code: Select all

    Open PathAndFileName For Input As #FileNum
    TotalFile = Input(LOF(FileNum), FileNum)

Warning: your method will produce incorrect results if the text file contains commas within a value. For example, in a file with contents

aa,"bb,cc",dd,"ee,ff"
"gg,hh",ii,"jj,kk",ll

the more traditional methods will result in

S1479.png

but yours will produce

S1480.png

[DocAElstein]

I never saw that Input( , ) function way before, useful to know about, Thx.


I can’t think of any easy way to get over that problem with the element with a separator in it, at least not one that wouldn’t make it very complicated.
(I guess what ever process allows for that in the traditional ways, by the convention of having, and checking for, the enclosing quotes, must itself add a complexity, but one which we just don’t see. So for the case of that situation not occurring then the clipboard way might still be an efficient alternative.
I am a bit annoyed I never thought of this before. - In the meantime I thought of quite a few situations I had in the past when this way would have considerable simplified a few things. )

( I think it was strange choice to have a common convention of using a separator which is a fairly common character like the comma . I wonder what bright spark came up with that stupid idea back when computers came about.
For now I would like to assume it was, and therefore blame, Chris Greaves, if I may. He often claims to have been around computers in the early days, so he is a good candidate. If I was around computers back then , I would have gone on the streets to protest against it.
I realise a lot of other people were about as well, but I don’t know them all. And if Chris sees this he will have to prove his innocence by explaining why or who or what else was responsible for such a stupid decision, then maybe I will learn something about why such a thing came about.

The first time I put things in a text file, I never knew what things like csv or delimiter were. I decided myself to separate things with something, which was the pipe, just because I saw it on my keyboard and it seemed instinctively a good idea and choice at the time. The pipe looked like a wall, so like the vertical cell wall.
Now I know a bit more about these things my choice would probably be either the pipe or some other obscure character that was unlikely to appear in typical text or numbers.
Since the day before yesterday, I might consider using the Tab , as a good idea, as it would simplify the import into Excel further, particularly if there is a directer way in VBA to put the contents of a text file into the windows clipboard, which I expect there might be. )

[LisaGreen]

WOW!!

Alan!!! You're on a roll!! Great stuff!!

Lisa

[DocAElstein]

Hi Lisa,
It’s quite frustrating that I missed the obvious Clipboard way in the past, its so simple its stupid.
( I have seen some single liner formulas done by people to get at fields in a Delimited Text String, which you could apply to the main string to get at , for example, specific rows. So potentially you could do some manipulation of the text file all on the same single line, which would once again simplify some more complicated macros involving looping).

I suppose one other small warning about this way though, is that it relies on the Microsoft Windows Clipboard working properly. - One of a few reasons I prefer older Office versions is because the clipboard is a bit quirky in newer versions. Up to about 2013 its reliable and stable, after that is can give problems. To overcome those problems, a fix often involves using much more complicated API coding to use the Clipboard, so that extra complication might offset the advantages of the otherwise simplified Clipboard way.

I don’t know how good this sort of Clipboard way would be for very big files.
Alan

[SpeakEasy]

>one other small warning about this way though, is that it relies on the Microsoft Windows Clipboard working properly

To be pedantic, your method actually relies on the somewhat limited msForms dataobject working properly - and Microsoft have in the past acknowledged there to be issues with the msForms dataobject under windows 8 and 10 (having Explorer windows open, for example, can cause issues!)

[DocAElstein]

Not pedantic, IMO, - its good to get things right, good to know, thanks.
If I remember correctly, problems I ever experienced in the past, or know of, would have been when using coding using the dataobject thing.

[DocAElstein]

Hello
I caught a recent post from snb recently, https://eileenslounge.com/viewtopic.php ... 65#p310565 that gave me a another idea for a way to get a text file into Excel in a compact way.
I thought it would do no harm for future reference to add it to the preliferry of different ways we have here already:

What’s going on in that post is that in one line the "scripting filesystemobject opentextfile( )readall" thingy gets hold of the whole text of a text file, and manipulates it all in single line of string manipulation stuff to get some of the text into Excel. A neat idea, which you can even actually get to work if you correct it … - @snb – don’t forget … "In VBA almost every double quote should be followed by a 'closing' quote" … do that and then it works
join(filter(split(replace(createobject("scripting.filesystemobject").opentextfile("G:\OF\nomefilecsv.txt").readall,vbcrlf,"/"),"/"),".csv"),vblf)

So what I thought was, why not try to do similar to get the whole text file into Excel in a similar one line.
I did it, like this:
_The entire text file as a string of text is got using the CreateObject("scripting.filesystemobject").opentextfile("TextFile") .readall thingy
_ the line separator ( I assume for the example is the typical invisible character pair, vbCr & vbLf ) is replaced by the column separator , the thing which most commonly is the comma , - which is what I use for the example
_ the Split of the modified string by the comma , separator , gives a long single array 1 dimensional array,
_ finally the 2Darray = Index ( 1Darray, 1 , Clms() ) ideas , ( for example here ) is used to give us the final array or range of values

Working demo Example
Take for example you have a simple text file, and you know the dimensions, as example 3 rows and 2 columns, looking like this ,
a, b
c, d
e, f
, then this’ll do the job

Code: Select all

 ' https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=310720#p310720
 Let Range("A1").Resize(3, 2) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))

If you don’t know the dimensions of the text file, then you can do it still in all in one code line, but the line itself is too long to fit in the code window so you have to split it with the _

Code: Select all

' https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=310720#p310720
 Let Range("A5").Resize((Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
 Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))

Beautiful :)

Here is a bit more detailed workings showing how I got those code lines.

_.___________________________________________-

To try this out, download both files, put them anywhere, but both in the same Folder, then open just the Excel file and run the subroutines, Won and Too
You should then get this:

Runed Sub Won() and SubToo().JPG

Alan

[snb]

@Doc

Can you please upload both files into this forum ?

A 'jagged array' is an array of arrays, that are different in size:

sn=array(split("a b c"),split("d e"),split("f g h i j k"))

It's a strange phenomenon, that several phenomena get their singular and plural form from the classical Greek language the words derive from.

[DocAElstein]

...Can you please upload both files into this forum ?

???? - I did ?

I did.JPG

[snb]

Yes, you did while I was writing my response.

Let Excel/VBA do the job:
Avoid redundant variables.

Code: Select all

Sub M_snb()
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText Replace(CreateObject("scripting.filesystemobject").opentextfile("J:\download\3Row2ColumnTextFile.txt").readall, ",", vbTab)
    .PutInClipboard
  End With
    
  Tabelle1.Paste Cells(1)
End Sub

or simpler still:

Code: Select all

Sub M_snb()
   Sheets.Add(, , , "J:\download\3Row2ColumnTextFile.txt").Columns(1).TextToColumns , 1, -4142, , True, True, True, False, False
End Sub

or

Code: Select all

Sub M_snb()
   Workbooks.OpenText "J:\download\3Row2ColumnTextFile.txt", , , , , , True, True, True, False,False
End Sub

If you prefer to control the whole process:

Code: Select all

Sub M_snb()
    sn = Split(CreateObject("scripting.filesystemobject").opentextfile("J:\download\3Row2ColumnTextFile.txt").readall, vbCrLf)
    
    For j = 0 To UBound(sn)
      sp = Split(sn(j), ",")
      Cells(j + 10, 1).Resize(, UBound(sp) + 1) = sp
    Next
End Sub

[DocAElstein]

Yes, you did while I was writing my response.

No, I am fairly sure I had them uploaded from the start - I saw them straight away, and I only did some Typo editing after, – perhaps some anti spam thing delays others seeing them for a while?, or you need glasses?, or you are pulling my chain again?
_._________________

Code: Select all

Sub M_snb()
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText Replace(CreateObject("scripting.filesystemobject").opentextfile("J:\download\3Row2ColumnTextFile.txt").readall, ",", vbTab)
    .PutInClipboard
  End With
    
  Tabelle1.Paste Cells(1)
End Sub

Interesting, that looks like a slightly neater way of doing the clipboard way I did a few posts back ( https://eileenslounge.com/viewtopic.php ... 80#p295780 ) , - changing text file "column separator" , to the separator vbTab used as the column separator in the clipboard for an excel range. (Fortunately,
_ the Excel row separator
and
_ ( not always but mostly ) the text file ,line separator
, are both the pair "carriage return" and "line feed" pair ( vbCr & vbLf ) )

_.___________________

...
Sheets.Add(, , , "J:\download\3Row2ColumnTextFile.txt").Columns(1).TextToColumns , 1, -4142, , True, True, True, False, False

Workbooks.OpenText "J:\download\3Row2ColumnTextFile.txt", , , , , , True, True, True, False,False

Interesting to see some worksheet function alternatives, ( or would/ will be when I check out all the arguments)
I am less keen myself on worksheet function alternatives, just personal preference, although that is slight contradicting as I do like Index stuff, which is a worksheet function, in any form , even in the two VBA syntaxes ( http://www.eileenslounge.com/viewtopic. ... 74#p297474
)


_.___________________________

A 'jagged array' is an array of arrays, that are different in size:
sn=array(split("a b c"),split("d e"),split("f g h i j k"))
It's a strange phenomenon, that several phenomena get their singular and plural form from the classical Greek language the words derive from.

I don’t know what you are trying to say with all that,
As for arrays of arrays, jagged or unjagged, one quite interesting phenomena is that
_ if our array of arrays are not jagged**, like, arrIn1() = Array(Array("a", "b"), Array("c", "d")) ( or Array(Split("a b"), Split("c d")) if you prefer)
_then in Index things they behave like a 2D array,
a, b
c d
( already shown in this Thread http://www.eileenslounge.com/viewtopic. ... 67#p274367 )

Code: Select all

 Sub UnJaggedJaggedJagger() '    http://www.eileenslounge.com/viewtopic.php?p=310721#p310721
Dim arrIn1() As Variant
 Let arrIn1() = Array(Array("a", "b"), Array("c", "d"))
 Let arrIn1() = Array(Split("a b"), Split("c d"))
'   arrIn1() becomes like    { {a b} {c d} }

Dim StrOut As String
 Let StrOut = Application.Index(arrIn1(), 2, 2)
Debug.Print StrOut ' gives   d

Dim arrIn2() As Variant
 Let arrIn2() = Evaluate("{""a"",""b"";""c"",""d""}")
'   arrIn2() becomes like      {a b
'                               c d}
  
 Let StrOut = Application.Index(arrIn2(), 2, 2)
Debug.Print StrOut ' gives   d
End Sub 

I am not sure why that works or what it might be telling us about how computers generally hold things we see as 2 dimensional, but it does allow some interesting possibilities at getting at things in dictionary type things when the Items/Elements of such dictionary things are 1 dimensional arrays
(**If the array of arrays is jagged, then Index things like in the above coding does not work )

[snb]

I amended my previous post.

Sheets.Add and Workbooks.OpenText are no worksheetfunctions. Please consult your Excel Handbook. These are Excel-methods (or generally speaking application.methods): please consult the Object Browser in the VBEditor: F4.

NB.
- VBA contains vbCrLf
- you can't use application.Index for a Dictionary that contains items with non-identically sized Arrays (jagged Arrays)
- you can only use application.index for identically sized arrays.
- 1 phenomenon, 2 phenomena; one phenomena is wrong.

[DocAElstein]

...If you prefer to control the whole process:

Code: Select all

Sub M_snb()
    sn = Split(CreateObject("scripting.filesystemobject").opentextfile("J:\download\3Row2ColumnTextFile.txt").readall, vbCrLf)
    
    For j = 0 To UBound(sn)
      sp = Split(sn(j), ",")
      Cells(j + 10, 1).Resize(, UBound(sp) + 1) = sp
    Next
End Sub

My preferences are not strongly in one way of the other in these things.
I often prefer not to loop, as you do, yourself,- often we can find ways to avoid looping and doing one liner things. Almost always we can find novel ways to avoid looping. (One thing that has avoided me so far is putting an array of arrays into something like a dictionary , without looping.)
On the other hand, for me personally, the longer one liners are out of academic interest, half the time. Often I would do looping for coding in my own personally used coding, as performance is not so critical and looping alternatives are easier for me to debug, modify and easier to understand/ remember wot is going on, later.
Occasionally I do use myself shorter one liner alternatives to looping because the performance can sometimes be noticeable better.
At the end of the day it’s about 50/50 whether I use a beautiful one liner code or a looping code. In any case all my coding , including my verboser long looping coding is always very beautiful with many colours in the coding and long horizontal 'green comments that meander gracefully to and fro as I transverse them with the horizontal scroll bar.
_.¬¬¬¬¬_________

- VBA contains vbCrLf

I think if I had a Euro for every time I posted vbCr & vbLf and someone told me about the VBA vbCrLf , then I could give up my current attempt to turn my Computer hobby into something more productive and profitable!
… I don’t like using vbCrLf in anything I post in an open media, as I think it can be misleading to beginners: it is often interpreted as a single character, making a new line, which it isn’t. It’s a string variable with two characters in it, sort of, (I think?), like pseudo
Dim vbCrLf As String
Let vbCrLf = vbCr & vbLf
_._____________

1 phenomenon, 2 phenomena; one phenomena is wrong.

Thanks for the English language lessen – impressive for a non native English speaker, although it’s not so uncommon that foreigners seem to know English grammar better than like wot most of us British learneded it.
I am not sure I want to fill my limited brain capacity with subtitles of grammar, though. Maybe I will forget all the phenomeneyes , play safe, and use thingy instead in future
_._____________________

- you can't use application.Index for a Dictionary that contains items with non-identically sized Arrays (jagged Arrays)
- you can only use application.index for identically sized arrays....

Yes I agree. That is the point I was trying to make. It is a shame, and I don’t know what the underlying reason is for that, is, but I agree that is what appears to be the case.
_.__________________________

: please consult the Object Browser in the VBEditor: F4....

No thanks. I find that a total waste of time. I can only get near any answer there if I already know the answer.

[snb]

Application.Index is a worksheetfunction.
It is based on the properties of a worksheet.
Like Usedrange, currentregion, Databodyrange, or e.g. Range("A1:K12"), every 2-dimensional range (Array) is a rectangle. Index assumes only rectangular arrays. That's why you can't apply this worksheetfunction to a jagged Array in VBA. The same prerequisites to the worksheet apply to the VBA-Array.

I can only get near any answer there if I already know the answer

So there are still lessons for you to learn.
You can't make a statement on the cases where you don't know the answer. Logic requires 4 quadrants of the combination matrix: A_true B_true, A_True B_false, A_False B_True, A_False B_False.

[DocAElstein]

Application.Index is a worksheetfunction.
It is based on the properties of a worksheet.

Yes, as I understand it , through Application. , the Excel spreadsheet things like Index were made available in VBA. ( Later, following Microsoft’s Policy of ..if it isn’t broken, break it, and try to encourage everyone not to use the non broken version, it was put in a Application.WorksheetFunction. wrapper which does a few things like checks the arguments and errors terminally if the arguments are the arrays that people like you and me do beautiful things with. I think they intended making the more useful Application.Index obsolete, and luckily for us, just forgot.
_._____________________

Like Usedrange, currentregion, Databodyrange, or e.g. Range("A1:K12"), every 2-dimensional range (Array) is a rectangle. Index assumes only rectangular arrays. That's why you can't apply this worksheetfunction to a jagged Array in VBA. The same prerequisites to the worksheet apply to the VBA-Array.

I don’t understand all you are saying there. You seem to be mixing up a bit randomly Excel ranges and arrays which are not the same things. You might not be intending that , or you might be. I don’t know, that’s just the way it comes across to me, that's all.



If you use Index(array() ….) you are restricted to 3 arguments,
_ the array, and
_ the row, and
_ the column

If you use Index(Range ….) you are have an extra 4th argument, _ the Area ( I think you, snb, never looked at that 4th argument , for example here, http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0 , which is fair enough, array are the things with Index that seem to be the most useful to get into. Having said that I have however found a one liner of this form quite useful sometimes
= Index(Cells , Rws() , Clms() ) ) , where that first argument would be effectively a whole worksheet range, but I never looked much at having a multi Area range there )


That extra argument does not help much with anything to do with the Index and jagged array issues, at least I have not yet found it any use in that.
You can mess about with non rectangular things though with it in the spreadsheet, (also in VBA via the Evaluate(" ").
( In the Evaluate(" ") you have to do some of those awkward empirical things to get array results out, at least in all but the most recent Office versions that throw up or spill out everywhere and make an even bigger mess) ).

The 4th argument also seems to let you do beautiful things with it as it accepts arrays. But I have not found any useful use of it yet. But I have not looked into it much yet.

Example using Index with 4th Argument:
The yellow range is the Range that Index in the macro below is messing with, That is the first argument Range

That aint a square rectagular range.JPG

It then gives out a array result from it, (which I could paste in a range like this:

Array Out.JPG

)

Code: Select all

 Sub IndexRangeArears() '  https://eileenslounge.com/viewtopic.php?p=310741#p310741
 Let Range("A10:B11") = Evaluate("=IF({1},INDEX((A1:B2,C2:D2),N(IF({1},{1;1})),N(IF({1},{1,2})),N(IF({1},{1,1;2,2}))))")
End Sub

_.__________________________________

FileSystemObjectOpenTextFileReadAll.xls https://app.box.com/s/qz5t1za2lth9z1ztxl88rsnvd951nztf