Я пытаюсь создать массив в 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 видит формулу как тип диапазона (даже результатом формулы является строка) и дает мне ошибку несоответствия типа.
Evaluate
.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. Это не идеально, так как усложняет некоторые другие процессы, но это был полезный обходной путь.