ИЗМЕНИТЬ
В самом деле, нет прямого способа редактирования значений диапазона в памяти.
Спасибо @AndASM за подробный ответ и Карл; хорошая догадка, это было пятно. Я, должно быть, слишком запутался во всем обратном в тот момент, забыв, что Value2
- это просто свойство.
Между тем, я немного углубился в некоторые другие тесты и отлаживал с OllyDbg и нашел несколько интересных вещей:
- Ячейки расположены в 16 x 1024 областях. Структура, в которой области могут очень хорошо быть рабочим листом, но я пока не могу подтвердить;
- Каждый раз, когда вызывается свойство
Value
, абсолютный лист смещения (строка, столбец) используются для поиска соответствующей области и затем для некоторой индексации внутри области, чтобы получить фактическое значение; - Создается 2D SAFEARRAY типа VARIANT;
- Значения не извлекаются в смежном блоке, но индивидуально. Это означает, что каждая "точка" в диапазоне (строка, столбец) отправляется на индексации, чтобы вернуть значение (вариант, очевидно) для его соответствующий элемент SAFEARRAY;
- В результате вышесказанного каждый раз, когда вы получаете значение через
Range.Value2(row,col)
, весь процесс повторяется для всех значения в диапазоне. Представьте, что вы достигли успеха, если вы это сделаете несколько раз внутри процедуры или, что еще хуже, внутри цикла. Только не надо; вам лучше создать копиюValue2
и адресация через индексирование; -
Наконец, не в последнюю очередь, распределение значений внутри
SAFEARRAY.pvData
основан на столбцах(col,row)
, а не на основе строк, что могут быть встречены интуитивно понятными и противоречащими индексированию VBA режим(row,col)
. Это может пригодиться, если вам нужно доступ к pvData непосредственно в памяти и сохранение согласованности измерений. Например, диапазон, подобный приведенному ниже1, 2, 3, 4 5, 6, 7, 8
хранится в
pvData
в следующем порядке:1, 5, 2, 6, 3, 7, 4, 8
Я надеюсь, что это поможет.
Подводя итог, в отсутствие какой-либо такой экспортируемой функции в Excel лучший способ создания копии Value2
, сортировки/манипулирования ею по желаемому результату и назначения ее обратно в свойство диапазона.
Недавно я закончил вариант QuickSort и намерен внедрить его в Excel. Алгоритм эффективен и действительно приносит значение в качестве надстройки, если не для дополнительного времени, затраченного на то, чтобы помещать значения массива в диапазон. Транспонирование работает только для менее 65537, в то время как "массив вариантов вставки палитры в диапазон занимает слишком много времени при больших сортировках".
Итак, я написал несколько процедур, которые позволили бы скопировать 2D-значения из диапазона в 1D-массив (1D необходим для сортировки) и (после сортировки), вернув их, все на основе SAFEARRAY и MemCopy ( RtlMoveMemory) и, альтернативно, WriteProcessMemory.
Все работает хорошо, что касается операций с памятью: - значения диапазона копируются в массив (от одного файла SafeArray.pvData до другого); - значения массива (после запуска сортировочного алгоритма) успешно скопированы в файл Range.Value2 SafeArray.pvData.
Тем не менее диапазон не обновляется, поскольку, похоже, он возвращается к старым значениям (подробнее об этом в коде ниже). Почему "Range.Value2 = SomeOther2dArray" работает и не изменяет данные непосредственно в памяти? У меня такое чувство, что я здесь что-то не хватает. Требуется также сортировка/обновление формулы?
Вот основная процедура:
Public Sub XLSORT_Array2()
With Application
screenUpdateState = .ScreenUpdating
statusBarState = .DisplayStatusBar
calcState = .Calculation
eventsState = .EnableEvents
.ScreenUpdating = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
Dim rngSort As Range
Dim arrSort() As Variant
Dim arrTemp As Variant
Dim i As Long
Dim dblTime As Double
Dim dblInitTime As Double: dblInitTime = Timer
Set rngSort = Selection
If Not rngSort Is Nothing Then
If rngSort.Cells.Count > 1 And rngSort.Areas.Count = 1 Then
dblTime = Timer
ReDim arrSort(1 To rngSort.Cells.Count)
Debug.Print Timer - dblTime & vbTab & "(Redim)"
'just testing Excel memory location
'Debug.Print VarPtr(rngSort.Value2(1, 1))
dblTime = Timer
SA_Duplicate arrSort, rngSort.Value2
Debug.Print Timer - dblTime & vbTab & "(Copy)"
dblTime = Timer
SORTVAR_QSWrapper arrSort, 1, rngSort.Cells.Count
Debug.Print Timer - dblTime & vbTab & "(Sort)"
'this would be the fastest method
'variants are copied to memory
'yet the range does not update with the new values
SA_Duplicate rngSort.Value2, arrSort
'dblTime = Timer
'looping = too slow
'For i = 1 To rngSort.Cells.Count
' rngSort.Cells(i).Value = arrSort(i)
'Next
'this works, but it too slow, as well
'If rngSort.Cells.Count > 65536 Then
' ReDim arrTemp(LBound(rngSort.Value2, 1) To UBound(rngSort.Value2, 1), LBound(rngSort.Value2, 2) To UBound(rngSort.Value2, 2))
' SA_Duplicate arrTemp, arrSort
' rngSort.Value2 = arrTemp
'Else
' rngSort.Value2 = WorksheetFunction.Transpose(arrSort)
' Debug.Print "Transposed"
'End If
'Debug.Print Timer - dblTime & vbTab & "(Paste)"
End If
End If
With Application
.ScreenUpdating = screenUpdateState
.DisplayStatusBar = statusBarState
.Calculation = calcState
.EnableEvents = eventsState
End With
Debug.Print VarPtr(rngSort.Value2(1, 1)) & vbTab & Mem_ReadHex(ByVal VarPtr(rngSort.Value2(1, 1)), rngSort.Cells.Count * 16)
Set rngSort = Nothing
Debug.Print Timer - dblInitTime & vbTab & "(Total Time)" & vbNewLine
End Sub
Скажем, значения в диапазоне 4, 3, 2 и 1.
Перед SA_Duplicate arrSort, rngSort.Value2
память считывает:
130836704 05000000 00000000 00000000 00001040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00000040 05000000 00000000 00000000 0000F03F
129997032 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
где 130836704
- Range.Value2 SafeArray.pvData
, а 129997032
- SortArray SafeArray.pvData
. Каждая 16-байтная партия представляет собой фактические данные варианта, считанные из памяти (без перевода LE, только в шестнадцатеричном формате), с первыми двумя байтами, указывающими VarType. В этом случае vbDouble.
После копирования, как и ожидалось, память читает:
130836704 05000000 00000000 00000000 00001040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00000040 05000000 00000000 00000000 0000F03F
129997032 05000000 00000000 00000000 00001040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00000040 05000000 00000000 00000000 0000F03F
После завершения сортировки SortArray SafeArray.pvData читает:
129997032 05000000 00000000 00000000 0000F03F 05000000 00000000 00000000 00000040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00001040
После выполнения SA_Duplicate rngSort.Value2, arrSort
память показывает, что Range.Value2 SafeArray.pvData обновлен:
129997032 05000000 00000000 00000000 0000F03F 05000000 00000000 00000000 00000040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00001040
130836704 05000000 00000000 00000000 0000F03F 05000000 00000000 00000000 00000040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00001040
Пока все выглядит хорошо, за исключением того, что Debug.Print VarPtr(rngSort.Value2(1, 1)) & vbTab & Mem_ReadHex[...]
показывает, что значения вернулись в исходный порядок:
130836704 05000000 00000000 00000000 00001040 05000000 00000000 00000000 00000840 05000000 00000000 00000000 00000040 05000000 00000000 00000000 0000F03F
Поделитесь своими мыслями или методами, которые вы нашли эффективными. Любая помощь приветствуется. Это разочаровывает необходимость ждать Excel около 4 секунд (сортировка 1 000 000 + клеток), когда даже самый сложный вид занимает меньше этого.
Спасибо заранее!