Как передать массив (Range.Value) на собственный .NET-тип без цикла?

То, что я пытаюсь сделать, это заполнить ArrayList используя .AddRange() в VBA, используя последнее связывание на родном C # ArrayList , но я не могу понять, как передать объект, отличный от другого ArrayList в качестве аргумента … что угодно иначе я пробовал до сих пор не удается …

Итак, в основном, что я делаю сейчас (Примечание: list – это ArrayList C # через mscorlib.dll)

 Dim list as Object Set list = CreateObject("System.Collections.ArrayList") Dim i As Long For i = 1 To 5 list.Add Range("A" & i).Value2 Next 

Но это довольно неэффективно и уродливо, если, например, i могу быть = 500K .

В VBA это также работает:

 ArrayList1.AddRange ArrayList2 

Но мне действительно нужно / нужно передать массив вместо ArrayList2

Поэтому я слышал, что могу передать массив параметру .AddRange() в .NET. Я тестировал его в небольшом консольном приложении на C #, и, похоже, он работал нормально. Нижеследующее прекрасно работает в чистом консольном приложении C #.

 ArrayList list = new ArrayList(); string[] strArr = new string[1]; strArr[0] = "hello"; list.AddRange(strArr); 

Поэтому, возвращаясь к моему модулю VBA, пытаюсь сделать то же самое, он терпит неудачу.

 Dim arr(1) As Variant arr(0) = "WHY!?" Dim arrr As Variant arrr = Range("A1:A5").Value list.AddRange arr ' Fail list.AddRange arrr ' Fail list.AddRange Range("A1:A5").Value ' Fail 

Примечание. Я попытался передать собственный VBA-массив вариантов и Collection , диапазонов – все, кроме другого ArrayList не удалось.

Как передать собственный массив VBA в качестве параметра в ArrayList ?

Или любая альтернатива для создания коллекции из диапазона без цикла?

Бонусный вопрос: * Или, может быть, есть еще одна встроенная .Net COM Visible Collection, которая может быть заполнена из объекта или массива VBA Range без цикла и у которого уже есть .Reverse ?

ПРИМЕЧАНИЕ. Я знаю, что я могу сделать оболочку .dll для этого, но меня интересуют собственные решения – если они существуют .


Обновить

Чтобы лучше проиллюстрировать, почему я хочу полностью избежать явной итерации – вот пример ( для простоты он использует только один столбец )

 Sub Main() ' Initialize the .NET's ArrayList Dim list As Object Set list = CreateObject("System.Collections.ArrayList") ' There are two ways to populate the list. ' I selected this one as it's more efficient than a loop on a small set ' For details, see: http://www.dotnetperls.com/array-optimization list.Add Range("A1") list.Add Range("A2") list.Add Range("A3") list.Add Range("A4") list.Add Range("A5") ' It's OK with only five values but not with 50K. ' Alternative way to populate the list ' According to the above link this method has a worse performance 'Dim i As Long 'Dim arr2 As Variant 'arr2 = Range("A1:A5").Value2 'For i = 1 To 5 ' list.Add arr2(i, 1) 'Next ' Call native ArrayList.Reverse ' Note: no looping required! list.Reverse ' Get an array from the list Dim arr As Variant arr = list.ToArray ' Print reversed to Immediate Window 'Debug.Print Join(arr, Chr(10)) ' Print reversed list to spreadsheet starting at B1 Range("B1").Resize(UBound(arr) + 1, 1) = Application.Transpose(arr) End Sub 

Обратите внимание: единственный раз, когда я должен зацикливаться, – это заполнить список (ArrayList), что я хотел бы сделать, это просто найти способ загрузить arr2 в ArrayList или другой совместимый с .NET тип без циклов.

На этом этапе я вижу, что нет встроенного / встроенного способа сделать это, поэтому я думаю, что я попытаюсь реализовать свой собственный путь, и, возможно, если он сработает, отправьте обновление для библиотеки Interop.

  list.AddRange Range("A1:A5").Value 

Значение Value получает маршалирование в виде массива . Это самый простой тип .NET, который вы можете себе представить, конечно. На этом, однако, есть колокола, это не «нормальный» .NET-массив. VBA – среда выполнения, которая любит создавать массивы, первый элемент которых начинается с индекса 1. Это несоответствующий тип массива в .NET. CLR любит массивы, первый элемент которых начинается с индекса 0. Единственный тип .NET, который вы можете использовать для это class System.Array.

Дополнительным осложнением является то, что массив представляет собой двумерный массив. Это ставит кибош в ваши попытки превратить их в ArrayList, многомерные массивы не имеют перечислителя.

Так что этот код работает отлично:

  public void AddRange(object arg) { var arr = (Array)arg; for (int ix = ar.GetLowerBound(0); ix < = arr2.GetUpperBound(0); ++ix) { Debug.Print(arr.GetValue(ix, 1).ToString()); } } 

Вы, наверное, не слишком заботитесь об этом. Вы можете использовать небольшой class accessor, который обертывает неудобный массив и действует как вектор:

 class Vba1DRange : IEnumerable { private Array arr; public Vba1DRange(object vba) { arr = (Array)vba; } public double this[int index] { get { return Convert.ToDouble(arr.GetValue(index + 1, 1)); } set { arr.SetValue(value, index + 1, 1); } } public int Length { get { return arr.GetUpperBound(0); } } public IEnumerator GetEnumerator() { int upper = Length; for (int index = 0; index < upper; ++index) yield return this[index]; } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return GetEnumerator(); } 

Теперь вы можете написать «естественный» способ:

  public void AddRange(object arg) { var arr = new Vba1DRange(arg); foreach (double elem in arr) { Debug.Print(elem.ToString()); } // or: for (int ix = 0; ix < arr.Length; ++ix) { Debug.Print(arr[ix].ToString()); } // or: var list = new List(arr); } 

Вот доказательство концепции как расширение комментария @ phoog. Как он указывает, метод AddRange принимает ICollection .

Реализация ICollection

В VBA IDE добавьте ссылку на mscorlib.tlb: Инструменты —> Ссылки, а затем найдите свою платформу .NET Framework mscorlib.tlb. Моя была в папке «C: \ Windows \ Microsoft.NET \ Framework \ vX.X.XXXXX \ mscorlib.tlb».

Создайте новый class под названием «clsWrapLongArray» следующим образом:

 Option Compare Database Option Explicit Implements ICollection Dim m_lngArr() As Long Public Sub LoadArray(lngArr() As Long) m_lngArr = lngArr End Sub Private Sub ICollection_CopyTo(ByVal arr As mscorlib.Array, ByVal index As Long) Dim i As Long Dim j As Long j = LBound(m_lngArr) For i = index To index + (ICollection_Count - 1) arr.SetValue m_lngArr(j), i j = j + 1 Next End Sub Private Property Get ICollection_Count() As Long ICollection_Count = UBound(m_lngArr) - LBound(m_lngArr) + 1 End Property Private Property Get ICollection_IsSynchronized() As Boolean 'Never called for this example, so I'm leaving it blank End Property Private Property Get ICollection_SyncRoot() As Variant 'Never called for this example, so I'm leaving it blank End Property 

Здесь Array.SetValue метод Array.SetValue .

Создайте новый модуль под названием «mdlMain», чтобы запустить пример:

 Option Compare Database Option Explicit Public Sub Main() Dim arr(0 To 3) As Long arr(0) = 1 arr(1) = 2 arr(2) = 3 arr(3) = 4 Dim ArrList As ArrayList Set ArrList = New ArrayList Dim wrap As clsWrapLongArray Set wrap = New clsWrapLongArray wrap.LoadArray arr ArrList.AddRange wrap End Sub 

Если вы поместите контрольную ArrList в End Sub и запустите Main() , вы можете увидеть, ArrList в ближайшем окне, что она содержит 4 значения, добавленные из массива Long. Вы также можете пройти через код, чтобы увидеть, что ArrayList самом деле вызывает члены интерфейса ICollection Count и CopyTo чтобы это произошло.

Я мог видеть, что можно было бы расширить эту идею, чтобы создать Factory-функции для переноса типов, таких как Excel Ranges, массивы VBA, коллекции и т. Д.

Этот же метод работает со строковыми массивами! : D

Я нашел очень близкие ответы Googling "System.ArrayList" "SAFEARRAY" и "System.Array" "SAFEARRAY" и уточнил с помощью «COM-маршалинга», поскольку массивы VBA являются COM SAFEARRAY, но ничто не объяснило, как их можно преобразовать для использовать в вызовах .NET.

Это неэффективно, потому что .Value2 – это COM- вызов. Вызов тысячи раз добавляет много накладных расходов. Добавление элементов в массив в цикле должно быть МНОГО быстрее:

 Dim list as Object Set list = CreateObject("System.Collections.ArrayList") Dim i As Long Dim a as Variant a = Range("A1:A5").Value2 For i = 1 To 5 list.Add a(i,1) Next 

Как передать собственный массив VBA в качестве параметра в ArrayList?

Я не думаю, что вы можете – собственный массив VBA не является ICollection , поэтому единственный способ создать его – скопировать значения в цикле.

Причина, по которой он работает в консольном приложении C #, заключается в том, что массивы на C # являются ICollection s, поэтому метод AddRange принимает их просто отлично.