Excel vba присвоить переменной значение ячейки

7410 просмотра

1 ответ

1 Репутация автора

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

Моя проблема в том, что когда я пытаюсь присвоить переменную ячейке с помощью VBA, ничего не происходит.

Этот тестовый код работал:

Этот код (код, который мне небезразличен) не:

Это также сработало, хотя он использовал Rnd () только один раз (что я и ожидал):

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

Рабочий лист, на который ссылается sheetName, создается без каких-либо проблем.

Спасибо за любую помощь, которую вы можете предоставить

Ответы (1)

2 плюса

0 Репутация автора

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

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

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

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

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале "." (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

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

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде "число месяц прописью год", то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде "#####" то Text вернет вам не само значение, а эти самые "решетки".

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат "14 марта 2001 г.". Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат "Денежный" с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка "Type mismatch". Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.

Обращение к ячейке на листе Excel из кода VBA. Запись информации в ячейку. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.

Обращение к ячейке

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

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

Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере — это ячейка "A1".

Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках — имя ярлыка.

Кроме того к ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз. Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе — зависит от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее. Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

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

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:

Точно также можно обмениваться информацией между ячейками:

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля или пустой строки:

VBA Excel. Ячейки (обращение, запись, чтение, очистка) : 65 комментариев

Книга состоит из 3-х Листов
Лист2 — рабочий

надо на Лист3 в cells (2, 6) вывести второе наибольшее значение из Range ("F4:D12") Лист1
на формулах это будет так :
F2=НАИБОЛЬШИЙ(Лист1! F4:D12;2)

спасибо за ответ
все работает

Здравствуйте, как записать число в ячейку, если есть OptionButton.
То есть 4 варианта ответа, при выборе правильного в ячейку записывается 1, если нет то 0.

Привет, Руслан!
Допустим, правильный ответ соответствует выбору OptionButton3, тогда

Добрый день!
как узнать номер страницы ( которая будет отпечатана) для ячейки?

Добрый день, Александр!
Если я правильно понял вопрос, имя листа вы можете узнать в окне редактора VBA в проводнике проекта, который обычно расположен слева.
Без скобок отображается основное имя листа, которое используется так: Лист1.Cells(1, 1) = 33 .
В скобках отображается имя ярлыка, которое используется так: Sheets("Имя ярлыка").Cells(2, 1) = 22 .

Добрый день!
Можно ли реализовать следующий алгоритм: Ячейка, допустим N=A+B+C+. +(N-1). Если сумма ячейки N

Привет, Илья!
Эта задача в подавляющем большинстве случаев не имеет решения из-за редко выполнимого условия: «пока N не станет равным X». Она всегда будет иметь решение только при условии: «пока N не станет больше или равным X». Если второй вариант интересует, напишите.

Евгений, доброе утро!
Да, этот вариант тоже подходит! 🙂

Доброе утро, Илья!
Чтобы пример был рабочим, заменим в формуле N=A+B+C+. +(N-1) условные обозначения ячеек переменными:
N на n,
A на a,
B+C+. на y,
ячейку для сравнения X на x, тогда

Добрый день, Евгений.
Давно ковырял VBA, сейчас со скрипом пытаюсь вспомнить.
Вопрос такой: С помощью ВПР делаю поиск некой позиции. Цена может быть в евро или в рублях. Для этого отдельная ячейка (Рубли или евро там тоже ставятся ВПР из "прайса").Пытаюсь написать коротенькую функцию, что если в указанной ячейке (С5) значение Евро, то значение ячейки С7 будет равно: цена в евро (ячейка С4) умножить на курс (ячейка С11). Иначе (то есть не не Евро) значение ячейки С7 присваиваем значение ячейки С4.
Вот что нагромодил))))):

Когда вставляю функцию в ячейку, то после ConV открывается автоматом скобка (зачем. ) а потом ругается на имя файла.
Понимаю что туплю, но не хочу заново лопатить весь VBA.
Если не трудно, подскажите правильный путь

Привет, Константин!
Функции должно присваиваться возвращаемое значение, текст ("Евро") в коде функции заключается в прямые кавычки:
код подсказки удален, так как оказался ошибочным

Пока не помогло.
Сейчас выглядит так:
код с ошибочной подсказкой удален

Может в скобках задать тип данных надо?

Адрес ячейки, в которую записывается результат в вашем примере, надо заменить на имя функции:

Сообщите о результатах. Тесты показали, что работает, но обновляется значение в ячейке с функцией только после вставки в нее курсора и нажатия клавиши Enter.

Не знаю, решит ли это ваши задачи, но я предложил бы следующую полностью рабочую функцию:

Вставляете в мастере функций начальные ячейки, ячейку kurs делаете абсолютной и функцию можно копировать протягиванием.

Добрый день, Евгений!
Спасибо, все работает. И первый вариант но с Enter, и второй без него.

Евгений, Вам по работе VBA вопросы задавать можно?


[an error occurred while processing the directive]
Карта сайта