Code Tag Test with Long Comments . Code Window Horitontal scroll bar

**DocAElstein** · 09-07-2023, 10:00 AM

Some Notes in support of this Thread Post
https://www.excelfox.com/forum/showt...ll=1#post23290

This line is a bit big,

Code:

 Let Range("A20").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 & ")"))

How much?

Code:

Sub LineLenfTest() ' https://www.excelfox.com/forum/showthread.php/1976-Code-Tag-Test-with-Long-Comments-Code-Window-Horitontal-scroll-bar?p=23291&viewfull=1#post23291https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23290&viewfull=1#post23290
'    Let Range("A20").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 & ")"))
Dim Str1 As String, Str2 As String
 Let Str1 = "Let Range(""A20"").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,"
Debug.Print Len(Str1) '  738
 Let Str2 = "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 & "")""))"
Debug.Print Len(Str2) '  897
End Sub

**2kvemperor** · 09-22-2023, 01:23 PM

**DocAElstein** · 09-22-2023, 03:53 PM

Hello 2kvemperor
Welcome to ExcelFox
Perhaps you are both a twat and a cunt as well?
I spent some time looking at your file. I expect some sort of names issue.
This problem is rather difficult for me to understand or work on, because you are using a lot of non-standard and non-English characters. Those do not show in my English or German Excel.
Also what makes it very difficult for me is that you are using a lot of named ranges and other strange names that I don’t understand. This makes it very difficult and time consuming to try and make any sense of what is going on where.
Your formulas look very complicated to me. I don’t understand them. But I am not a formula expert.

For example, I have no idea what this is referring to?
ΣΚΦ2022[[ΗΜΕΡΕΣ]:[ΔΕΚΕΜΒΡΙΟΣ]]
I think perhaps ΣΚΦ2022 is something to do with the worksheet ΣΚΦ 2022 , but I have no idea what is ΗΜΕΡΕΣ or ΔΕΚΕΜΒΡΙΟΣ ?

I am sorry, but I think currently that we do not have any formula experts at excelfox that can help you, or that would be willing to spend the time to try and break down your formulas, translating them into English characters, and to actual specified ranges.

I think you will need help from a good formula expert
You could try at excelforum.com as they have many more helpers there currently.
Here is the link to their Formula sub forum : --

( Alternatively, if you could reproduce the problem in an example workbook using just English characters throughout, and not named ranges, but explicitly referencing ranges, then perhaps somebody here might be able to help further. I expect also that you will increase your chances of getting help at any of the major forums if you could restrict yourself to using English characters. This is because some of the tools we use to help analyse and debug formulas often only work with English characters )
Sorry that excelfox probably is not currently the place to get help with complicated formulas

Alan