Describe the bug

    Dim arr As BetterArray
    Set arr = New BetterArray
    arr.LowerBound = 1
    arr.Items = Array(1, 2, 3)
    Debug.Print arr.Slice(4)(1)

output 3

Expected behavior
arr.Slice(4) should be empty

I made a modification on line 661, but I'm uncertain if there are any side effects.
OldIndex = Min(RelativeStart, LocalLength + LBound(LocalItems))

Thank you for your great work!

The title says it all.

One of the array elements created is 252,002128362656 (in the Immediate Window), so in English it would be 252.002128362656. In StringBuilder Fragment (at least for me) unexpectedly arrives as 252002128362656,. This of course later causes CLng to fail because the number is to large.

Maybe this is related to #24?

Also here I could dig deeper ...

Describe the bug
Using a 1D BetterArras for storing Filepaths from folder with recursion. Each (sub)folder returns its own BetterArray, which is "merged" with BetterArray from parent folder using Concat method.

Result 1D BetterArray contains a lot of empty Items.

To Reproduce
Consider function:

Public Function GetFilesArrRecusively(ByVal Folder As Folder) As BetterArray
    Dim File As File
    Dim SubFolder As Folder
    Dim TempArray As BetterArray
    Set TempArray = New BetterArray
    
    ' Loops trought all files in given folder
    For Each File In Folder.Files
        ' Test for hidden and system files
        Dim IsHidden As Boolean
        Dim IsSystem As Boolean
        Dim Attributes As Long
        
        Attributes = File.Attributes
        IsHidden = Attributes And 2
        IsSystem = Attributes And 3
        
        If Not (IsHidden Or IsSystem) Then
            Call TempArray.Push(File.Path)
        End If
    Next File
    ' Now all files in the given folder are in the arr
       
    ' Go trought all subfolders of the given folder
    For Each SubFolder In Folder.SubFolders
        Dim SubFolderArr As BetterArray
        Set SubFolderArr = New BetterArray
        Set SubFolderArr = GetFilesArrRecusively(SubFolder)
        
        Dim TempArrClone As BetterArray
        Set TempArrClone = New BetterArray
        Set TempArrClone = TempArray.Clone ' Save state of TempArray before Concat
        
        ' Appends filepaths from subfolder
        If SubFolderArr.Length > 0 Then
            Call TempArray.Concat(SubFolderArr.Items)
            If TempArray.Includes(Empty) Then
                Debug.Print "EMPTY" '<------ Place breakpoint HERE
            End If
        End If
    Next SubFolder
    Set GetFilesArrRecusively = TempArray
End Function

Expected behavior
1D BetterArray filled with Filepaths (As String). For each file in given folder (except hidden and system files.
Screenshots
This are BetterArrays going to Concat - Call TempArray.Concat(SubFolderArr.Items) (remember: TempArr = TempArray.Clone)

This is how TempArray looks like after Concat (see Items(40..63))

Operating System:

• Win 10 Pro

Host Application:

• Excel 2013 (32bit)

Additional context
I debugged it step by step from my Concat call (line 36 in my code snippet). And when I stepped on your line 1402 (BetterArray.cls v1.7.3), found that Me.Items has 40 Items BUT This.Items has 64 items - all extra items are Empty.

thank you so much for providing this amazing cls!
I love this feature very much

dim arr1(1 to 2) as variant,arr2(1 to 1) as variant
arr1(1,1)=1 : arr1(1,2)="A"
arr1(2,1)=2 : arr1(2,2)="B"

arr2(1,1)=1 : arr2(1,2)="C"

dim arr as new BetterArray
arr.items=arr1
arr.concat arr2

Dim ba As New BetterArray
Debug.Print ba.Length '0

ba.Push 1, 2
Debug.Print ba.Length '2

ba.Shift
Debug.Print ba.Length '1

ba.Shift
Debug.Print ba.Length '1

Windows 10 x64
MS Excel 2016
BetterArray v1.7.4

can betterarray sort descending?
First use betterarray.sort， then use betterarray.Reverse

am i right？ Thank you again.

Describe the bug
User is not able to go to Items setter from ArrayType Let Accessor.

To Reproduce
Steps to reproduce the behavior:

1. Go to ArrayType Let Accessor page.
2. Click Items setter hyperlink
3. See error

Expected behavior
User should see page correct page with documentation.

Screenshots

caption: faulty link

hey ,it's me again.
I hope i am not annoying you...

I hunger for a feature, something like zip

like in Excel adding a new caculate field,

So that I can use do everything in memory, when all things finished then output to the worksheet just by one step:

BetterArray.ToExcelRange Range("A1")

That would be perfect!

or something like the bellow

Dim arr As New BetterArray
arr.Items = Array(Array("A", "B"), Array(100, 200)) ' jagged array or a 2d array

arr.LowerBound = 1
arr.zip arr.keys, arr.Items

result expected :

arr.zip arr.keys, arr.Items => Array(Array(1,2), Array("A", "B"), Array(100, 200))
arr.zip arr.Items, arr.keys => Array(Array("A", "B"), Array(100, 200), Array(1,2))

or something like

dim arr(1 to 2 ,1 to 2) as variant
arr(1,1)=1:arr(1,2)=2
arr(2,1)="A":arr(2,2)="B"

dim betterArr  As New BetterArray
betterArr.items=arr

then do something like :
redim preserve betterArr.items(1 to 2, 1 to 3)

so that i can use in this way

betterArr.items(1,3)="Perfect"
betterArr.items(2,3)="Array"

Has there been any thought to adding iterable support for Better Array? You can do that by adding a NewEnum property like so:

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Dim it() As Variant
    Static pCol As Collection
    Dim elem As Variant
    
    Set pCol = New Collection
    
    it = Items
    
    For Each elem In it
        pCol.Add elem
    Next elem
    
    Set NewEnum = pCol.[_NewEnum]

End Property

If you did that, you could iterate through a BetterArray object. So you could write code like so:

Option Explicit

Sub subby()
    Dim ba As New BetterArray
    Dim elem As Variant
    
    ba.Push 1,2,3
    
    For Each elem In ba
        Debug.Print elem
    Next elem
End Sub

With this approach, you don't have to call the items property directly to do iteration.

Describe the bug
It seems if the length of the table is less or equal than 3, the Splice method erases the last value even when asking not to.

To Reproduce
Steps to reproduce the behavior:

Sub TestSplice()
    Dim colData As BetterArray
    
    Set colData = New BetterArray
    colData.LowerBound = 1
    
    colData.Push "1", "2", "3"
    'Items are "1", "2", "3"
    Debug.Print colData.Length
    'Length is 3
    
    colData.Splice 2, 0, ""
    'Items are "1", "", "2"
    'Expected Items are "1", "", "2", "3"
    
    Debug.Print colData.Length
    'Value is 3 instead of 4.

End Sub

Operating System:

• Windows

Host Application:

• MS Excel

Remove method it doesn't remove the only element of a 1 length array
I made a script that stores various string elements in a BetterArray object and then with a For...Next structure if a certain condition is met it removes one specific element of the array with the .Remove(Index) method. The problem is when the BetterArray object remains with 1 element and I want to remove it because the mentioned condition is met again at that point. BetterArray.Remove(0) simply returns the same BetterArray with one element.

To Reproduce
Steps to reproduce the behavior:

1. Simply run the following Sub procedure:
   Sub BetterArrayTest()
Dim BA As New BetterArray
BA.Items = Array("Hello")
BA.Remove 0
Debug.Print BA.ToString
End Sub

2. See the result in the Inmediate Window (see screenshot). It doesn't remove the "Hello" element.

Expected behavior
If a BetterArray object has one element (Lenght = 1), then the syntax BetterArray.Remove(0) really remove that element.

Screenshots

Operating System:

• Windows 7 64 bits

Host Application:

• MS Excel

Additional context
The problem is only when the BetterArray object has one element. If it has more than one element the .Remove(0) method effectively removes the first element of the array.

hi,please,i want to copyfromdictionary,just like copyfromrange.

what I have tried :

Dim arrResult As New BetterArray
arrResult.Items = dict.keys
arrResult.Transpose
arrResult.Push dict.Items

But it didn't work.

I expected result is：
a 2d array，(two columns), the first Column is The dictionary keys,the second is items,

Could you please do some help? Many Thanks.

I feel very upset for my poor English.🥺

Dear @Senipah, I still testing the CSV Interface class, and today got the ImportFromCSV and ImportFromCSVstring methods working fine. Trying to use your FromCSVString method, over a special syntax CSV file, the returned results are incorrect.

The procedure used for testing is like the given bellow

Sub test()
	Dim WGNEWstrArray() As String, filePath As String, Senipah As BetterArray
	Dim SenipahArray As Variant

	‘Some code here

	Call Senipah.FromCSVString(tmpCSV)
	SenipahArray = Senipah.Items

	‘Some code here
End Sub

I let you the used CSV file in addition to an Immediate and Watches windows screenshot.

I’ll wait for your reply to update my benchmarks. Best regards!

Expected behavior
The CSV file contains 3 records, each one of which contains 4 fields.

Screenshots

Operating System:

• Windows 10 Pro.

Host Application:

• MS Excel

CSV file
RFC-4180_OH.zip

Dear @Senipah , in the last stage of testing for the CSV interface I found a little bug in the method FromCSVString of the VBA Better Array class. Specifically, I was trying to make my project able to handle empty CSV files and records having zero length fields inside them. More details: the error occurs over the UnquoteString function (line 2450). See the attached screenshot

To Reproduce
The test string is the following:

"",[vbCrLf]
,"" [vbCrLf]
Field1,Field2[vbCrLf]

Screenshot

Operating System:

• Windows 10 Pro

Host Application:

• MS Excel 32 bits

Is your feature request related to a problem?

NO

Describe the solution you'd like

There is a little mistake in the ToCSVFile method description.

The ToCSVFile() returns an RFC 4180 compliant string representation of the stored array.

It is the same as ToCSVFile() but accepts an output path to which the CSV data will be written.
A clear and concise description of what you want to happen.

Maybe you mean ToCSVString

Hi, I have been interested to explore the features of Better Array. It seems to offer some really convenient and powerful capabilities.

Today I was looking to use it for filtering data from a 2d array. I saw in the documentation I can assign such an array through .Items. However, I don't see now how I can filter a 2d array. The method arguments don't seem to include the index of the column that I would filter on, and the examples refer to 1d arrays.

Am I correct that currently these methods only support 1d arrays, and if so is there any intention to expand them to 2d arrays?

Hallo Senipah !

Thanks for your excellent development work on the VBA class "BetterArray".

In the method "FromCSV" I have detected an error.

If the CSV file has no final CRLF this last CSV line is not included in the array.

For example, the following sample CSV file fails:

-------------- BEGIN CSV FILE -----------------
HeaderCol1,HeaderCol2,HeaderCol3,HeaderCol4,HeaderCol15[CRLF]
Row1Col1,Row1Col2,Row1Col3,Row1Col4,Row1Col5[CRLF]
Row2Col1,Row2Col2,Row2Col3,Row2Col4,Row2Col5
-------------- BEGIN CSV FILE -----------------

NOTE: [CRLF] above should represent the character CRLF

See also screenshots below

Is your feature request related to a problem?
YES

Please describe.
Dear @Senipah, it’s me, AGAIN! I was testing the CSV Interface class trying to find the memory limits of it and, in the same path of work, check if the change from String array to Variant jagged array has the potential to make the class run out of memory for a CSV file of couple of megabytes of size.
I used both your solution and sdkn's as benchmarks. The test’s objective was invoking the import procedure, bypassing the result in the case of @sdkn104, and avoiding copies of arrays in the case of your project and mine, so that the imported data was only present in the class members.
As a result of the above, I get a surprising error when invoke yours FromCSVString method. It’s about the error 14: “Out of string space”.

Describe the solution you'd like
The question here is only one: it’s possible to deal with the memory consumption, when working with String data type, for your solution?

Additional context
Attached to this I let you the file used as “guinea pig” (Demo_1.6MM_records.zip), hoping you can find the error source, and, additional to that, some screen shoots with the memory usage for both, @sdkn104 and mine, solutions.

NOTE: due to file size, the Demo_1.6MM_records.zip contains a .7z file inside.

I currently work on a CSV-VBA interface that I suposses'll to allow users exchange data between VBA arrays and CSV files, like a method of your project do, but at really high speed. Your code is able to read CSV data, but in my test, on a fairly outdated laptop, the performance of it is frustrating. Some suggestion?

Describe the bug
User is not able to go back to ArrayType Overview from ArrayType Get Accessor using Back to ArrayType overview hyperlink.

To Reproduce
Steps to reproduce the behavior:

1. Go to ArrayType Get Accessor page.
2. Click Back to ArrayType overview hyperlink
3. See error

Expected behavior
User should see ArrayType page.

Screenshots
Caption: click Back to ArrayType overview hyperlink

Caption: missing page

Additional info

The same error does appear when if you press ArrayTypes Enumeration and ArrayTypes Enumeration hyperlinks.

Hi Senipah! As usual, I was digging into the sorting methods and the strengths/weaknesses of these algorithms. As you may know, VBA CSV interface uses, even these days, the fast dual pivot sorting method described by Yaroslavskiy, whose main disadvantage is its "instability". This is the reason why many developers opt for alternatives that could be labeled as less efficient, but that provide "stability" when it becomes imperative to sort successively using multiple columns/sort keys.

Among the "stable" algorithms recognized and acclaimed as the most efficient are MergeSort and TimSort. At that point I remembered that one of the modifications you made to BetterArray mentioned the adoption of the latter (simplified version) as the default sorting method of your wonderful library. So, after having implemented MergeSort, HeapSort and IntroSort (the latter with "stable" output when the user specifies all columns in the sort command), I wanted to take a look at the options offered by your implementation.

There I discovered, for example, that BetterArray does not take advantage of the most sought-after feature of TimSort: "stability". So, your library does not allow to sort the data using 3 keys with ascending order and 1 key with descending order that maintains, partially, the ordering given by the previous three keys.

Attached are the screenshots of the result after trying the Sort method and then Reverse that you recommend in this comment; as well as the result obtained with my implementation of MergeSort.

You may also want to do something about the performance of TimSort, I think it can be improved when the targets of the operation are not objects.

Regards!

Note: column A is sorted in ascending order, then columns B and E are sorted in the same order, finally, column C is sorted in descending order.

Expected result:

Actual result:

The title says it all.

In the Immediate Window I get the following values

for Expected

Sub-Saharan Africa,Chad,Office Supplies,Online,L,1/27/2011,"292,494,523.00",12/02/2011,"4,484.00",651.21,524.96,"2,920,025.64","2,353,920.64","566,105.00"
Europe,Latvia,Beverages,Online,C,12/28/2015,"361,825,549.00",1/23/2016,"1,075.00",47.45,31.79,"51,008.75","34,174.25","16,834.50"

and for Actual

Sub-Saharan Africa,Chad,Office Supplies,Online,L,1.27.2011,"292.494.523,00",12.02.2011,"4.484,00","651,21","524,96","2.920.025,64","2.353.920,64","566.105,00"
Europe,Latvia,Beverages,Online,C,12.28.2015,"361.825.549,00",1.23.2016,"1.075,00","47,45","31,79","51.008,75","34.174,25","16.834,50"

So the Doubles, Dates are returned "wrong". Without digging deeper I think I can understand why the Doubles are "wrong" I don't have an idea why the Dates are returned wrong when the output format is given as spec (DateFormat:="m/dd/yyyy").

When you are interested in making class/tests "language" aware I could dig deeper ...