Создайте массив в VBA из формулы массива в Excel (из диспетчера имен)

Я пытаюсь создать массив в VBA из формулы динамического массива в Excel. В идеале формула Excel должна храниться в диспетчере имен, но я могу согласиться, если формула хранится в ячейке.

Конечной целью является использование этого массива для сопоставления блоков ячеек на основе определенных условий (с помощью цикла For), чтобы определить, какой блок строк следует удалить из листа Excel. Он должен быть динамическим, потому что я не знаю, сколько строк будет на листе или какие из этих строк нужно удалить до времени выполнения.

Ссылка на файл Excel здесь: https://www.dropbox.com/s/rz1qej15afht6bx/BRE5560_RFQ.xlsx?dl=0

Я пытался заставить это работать в течение нескольких дней и пробовал много разных вариантов, но я не могу заставить его работать. Что бесит, так это то, что она отлично работает для другой формулы, которая возвращает массив Doubles, но этот массив String бросает мне вызов.

Function CreateRFQEmail(sRFQFileName As String)
Dim arrRows() As Variant
Dim arrRFQs2() As Variant 

objExcel.Workbooks.Open (sRFQFileName)
Set sht = objExcel.ActiveWorkbook.Worksheets(2)

'This creates the array of Doubles without issue:
   arrRows = sht.Evaluate(sht.Names.Item("Quote!vbaRows").Value)

'The best result I have with this line is to have an array of Strings in VBA with the 
'correct number of elements, but the first element from Excel is repeated for each 
'element in the array:
   arrRFQs2 = sht.Evaluate(sht.Names.Item("Quote!vbaRFQs").Value)

End Function

Я также пытался использовать:

arrRFQs2 = sht.Names.Item("Quote!vbaRFQs").Value   'Error - Can't assign to array
arrRFQs2 = sht.Names("Quote!vbaRFQs").Value        'Error - Can't assign to array
arrRFQs2 = sht.Range("BY5").Value                  'Error - Type mismatch

Я пробовал несколько других вариантов, но не могу вспомнить их все сразу. (Я буду обновлять сообщение, если кто-нибудь предложит что-то, что я уже пробовал) Наиболее распространенной ошибкой было несоответствие типов, хотя я мог подтвердить, что сторона формулы в строке (т.е. sht.Range("BY5").Value) была Строка или диапазон

Формулы Excel, хранящиеся в диспетчере имен:

'vbaRFQs:
=TOROW(IF(Quote!vbaRows,INDEX(Quote!$A:$A,Quote!vbaRows),""))
'The first IF is needed to get VBA to return the correct number of elements, 
'without it I just get an array with a single element even though in Excel the 
'formula returns an array correctly

'vbaRows:
=IFERROR(TOROW(SMALL(IF(Quote!$A:$A<>"",ROW(Quote!$A:$A),""),ROW(OFFSET(Quote!$A:$A,0,0,COUNTA(Quote!$A:$A),1)))),"")

Я также попытался поместить их в ячейку вместо диспетчера имен без изменений.

Я попытался сделать vbaRFQs одной длинной формулой вместо использования vbaRows внутри нее, но это работает, только если я удаляю первый IF (потому что он будет слишком длинным для Evaluate), а затем я получаю только один массив элементов в VBA.

Если я использую формулу индекса для vbaRFQ, тогда VBA видит формулу как тип диапазона (даже результатом формулы является строка) и дает мне ошибку несоответствия типа.

Структурированный массив Numpy
Структурированный массив Numpy
Однако в реальных проектах я чаще всего имею дело со списками, состоящими из нескольких типов данных. Как мы можем использовать массивы numpy, чтобы...
T - 1Bits: Генерация последовательного массива
T - 1Bits: Генерация последовательного массива
По мере того, как мы пишем все больше кода, мы привыкаем к определенным способам действий. То тут, то там мы находим код, который заставляет нас...
Что такое деструктуризация массива в JavaScript?
Что такое деструктуризация массива в JavaScript?
Деструктуризация позволяет распаковывать значения из массивов и добавлять их в отдельные переменные.
1
0
68
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Заполните массив с помощью именованной формулы

  • Я думаю, что формула слишком сложна для использования с Evaluate.
  • Вот обходной путь, который использует значения из 1-го столбца и информацию (строки) в массиве строк.
Sub CreateRFQEmailTEST()
    CreateRFQEmail "C:\Test\BRE5560_RFQ.xlsx"
End Sub
Sub CreateRFQEmail(ByVal sRFQFilePath As String)
    
    Dim swb As Workbook: Set swb = Workbooks.Open(sRFQFilePath)
    Dim sws As Worksheet: Set sws = swb.Sheets("Quote")
    Dim sData(): sData = sws.UsedRange.Columns(1).Value
    
    ' Get Rows.
    Dim ArrRows(): ArrRows = sws.Evaluate("vbaRows")
    
    ' Get RQFs.
    
    Dim ArrRFQs(): ReDim ArrRFQs(1 To UBound(ArrRows))
    
    Dim rItem, dc As Long
    
    For Each rItem In ArrRows
        dc = dc + 1
        ArrRFQs(dc) = sData(rItem, 1)
    Next rItem
            
    ' Print both.
    Debug.Print Join(ArrRows, ",")
    Debug.Print Join(ArrRFQs, ",")
    
End Sub

Спасибо за помощь. В итоге я пришел к тому же выводу и сделал что-то очень похожее: поскольку у меня были номера строк в массиве, я использовал их для извлечения значений ячеек и помещения их в отдельный массив, все из VBA. Это не идеально, так как усложняет некоторые другие процессы, но это был полезный обходной путь.

Raymond Evans 03.01.2023 21:02

Другие вопросы по теме