Почему у меня возникают проблемы с назначением диапазона для массива вариантов

У меня есть несколько проблем с некоторыми ОЧЕНЬ простыми строками кода. Позвольте мне подробно изложить факты и посмотреть, сможет ли кто-нибудь еще воспроизвести это поведение. Если кто-то может реплицировать, я хотел бы получить объяснение, почему это происходит.

Итак, давайте начнем с очень простой строки кода, КОТОРЫЙ РАБОТАЕТ:

Dim arr() As Variant
arr = Range("A1:A10")

выполняется так, как ожидалось, arr присваивается значения A1:A10

теперь почему не будет работать следующая строка кода?

Dim arr() As Variant
arr = WorkSheets("Sheet1").Range("A1:A10")

Я получаю несоответствие типа "13" времени выполнения, хотя тот же диапазон был успешно присвоен массиву, без значения рабочего листа.

Но

Dim arr As Variant
arr = Worksheets("Sheet1").Range("A1:A10")

Dim arr() As Variant
arr = Application.Transpose(Application.Transpose(Worksheets("Sheet1").Range("A1:A10")))

РАБОТАЕТ

Теперь, прежде чем ответить, позвольте мне дать вам еще несколько фактов.

Dim arr() As Variant
arr = Worksheets(1).Range("A1:A10")

Не работает

и использование Sheets вместо Worksheets также дает одну и ту же ошибку.

Я убедился, что это тот же лист, что и активный ссылочный лист, используя Range("A1:A10").Worksheet.Name. После рабочего кода он действительно говорит Sheet1 на выходе.

Никакие другие книги не открыты, поэтому они не могут ссылаться на другую книгу.

Теперь этот последний бит кода добавляет к моей путанице, поскольку он полностью работает!

Dim arr() As Variant
Dim SampleRange As Range

Set SampleRange = Worksheets("Sheet1").Range("A1:A10")
arr = SampleRange

Таким образом, использование SAME RANGE, определенного таким же образом на том же листе, теперь работает, когда я назначаю его переменной диапазона. и использовать это! И, как и ожидалось, это работает как с функциями Worksheets, так и Sheets независимо от того, как я определяю лист (я могу использовать индекс или имя рабочего листа и все нормально работать)

Если это кому-то помогает, я тестирую это с помощью Excel 2007 на компьютере под управлением Windows XP. Я еще не тестировал его на каких-либо других машинах, но я планирую протестировать в 2003 и 2010 годах на Windows 7 и 8, просто еще не было возможности.

ОБНОВЛЕНИЕ: Не 100% уверены, что это то же самое, что и в случае с массивом, но из мелкого представления это выглядит как:

 Range("B1:B3") = Range("A1:A3")

Вышеприведенный код не будет работать, даже если заполняется A1: A3, даты, числовые значения, строки, формула, он будет записывать пробелы в B1: B3

Но

Range("B1:B3").Value = Range("A1:A3").Value

Range("B1") = Range("A1")

работает!

Также работает:

Range("B1:B3") = Application.Transpose(Application.Transpose(Range("A1:A3")))

Ответ 1

Нет, это не ошибка.

Дело в том, что Value является свойством по умолчанию объекта Range, поэтому почему он неявно используется? Вы посмотрели на вопрос, который я связал? (FROM CHAT)

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

Сначала поймем наши объекты. Я создал эту небольшую таблицу, которая четко показывает, что мы обрабатываем, чтобы не было путаницы.

Вы также можете добавить Watch, чтобы увидеть Type для определенного объекта, как показано на рисунке ниже.

Итак, когда вы говорите

arr = Range("A1:A10")

Excel знает, что свойство по умолчанию .Value. Однако в другом случае он не знает, потому что Excel не является читателем разума или не говорит достаточно интеллектуально, чтобы понять, хотите ли вы использовать Worksheets("Sheet1").Range("A1:A10") как Range или Variant

Как только вы явно укажете свой объект как Range, тогда Excel знает, чего вы хотите. Например, это работает.

Dim arr() As Variant
Dim Rng As Range  
Set Rng = Worksheets("Sheet1").Range("A1:A10")
arr = Rng

Ответ 2

Позвольте мне уточнить мой комментарий.
Он не может уместиться, чтобы комментировать, чтобы я опубликовал его как ответ, чтобы хотя бы очистить мою точку зрения.

Dim arr As Variant '~~> you declare arr as Variant as what Tim said

что это значит?
Это означает, что arr может принимать любую форму (например, целое число, строку, массив, объект и все остальные Variable Type)

Dim arr() as Variant '~~> you declare arr() as array which may contain Varying `Data Type`

что это значит?
Это означает, что переменная массива arr() может хранить разные типы Data.
Это исключает Objects или Collection of Objects.

Теперь, почему работает следующее:

1. Dim arr() As Variant: arr = Range("A1:A10")
2. Dim arr() As Variant: arr = Sheet1.Range("A1:A10")
3. Dim arr() As Variant: arr = Sheets("Sheet1").Range("A1:A10").Value

Это также работает:

4. Dim arr() as Variant
   Dim rng as Range

   Set rng = Sheets("Sheet1").Range("A1:A10")
   arr = rng

Выше работает, потому что вы не пытаетесь назначить Collections of Objects в массив.
Вместо этого вы назначаете конкретный объект или значение.
Range - это объект, но не Collection of Objects.
Пример №1 прямой, без доступа к Sheets Collection Object.
То же самое верно при использовании №2, поскольку вы работаете с Sheet1, который является объектом Sheet, но не Collection of Sheet Objects.
No.3 является самоочевидным, вы назначаете .Value массиву arr.
No.4 работает, потому что rng уже является Range объектом Set, который снова не является Collection of Objects.

Итак, это:

Dim arr() As Variant

arr = Sheets("Sheet1").Range("A1:A10")

не работает, потому что Excel будет читать это как попытку назначить Object из Sheets Collection of Objects и, следовательно, возникнет ошибка.
Надеюсь, это имеет смысл.

Ответ 3

Я бы сказал, что Array of Something не то же самое, что Something, так как это что-то может быть массивом других вещей. Если вы определяете что-то в качестве массива, то, что вы назначаете ему, должно быть массивом, будь то массив чисел, текста, диапазона, объектов диаграммы и т.д.

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

Например, строки и Cols имеют тип Long, но вы можете набросить на него тип Byte/Double:

Cells(1,1.5) дает значение Cells(1,2)

Вам не нужно конвертировать 1,5 в Long; Excel делает все это в фоновом режиме для вас.

Когда вы определяете массив чего-то и присваиваете ему что-то, Excel выполняет тип соответствия позади сцены и задает значения, когда это возможно.

Проверьте их в окне "Немедленное":

?typename(Range("A1:A10").Value) дает вам Variant() < - поэтому он работает на Dim arr() As Variant без каких-либо проблем.

?typename(Range("A1:A10")) дает вам Range. Но когда вы назначаете его arr, где Dim arr() As Variant, Excel преобразует диапазон в массив с использованием значений этого диапазона.

Однако Excel, похоже, не выполняет преобразование, если у него нет прямого доступа к объекту, если только вы не создали для него память. Например:

Dim arr() As Variant, oRng As Range
Set oRng = Range("A1:A10")
arr = oRng
Set oRng = Worksheets("Sheet1").Range("A1:A10")
arr = oRng

Вышеприведенный код все в порядке, но он не может преобразовать и назначить arr = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10") за один проход, если вы не набросаете на него массив (ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Valueимеет тип Вариант()).