Как сделать переменную в эксель
Добавил пользователь Cypher Обновлено: 05.10.2024
Я ненавижу повторяющиеся функции, особенно в формулах Excel. Есть ли способ, которым я могу избежать чего-то вроде:
[Выше приведен простой пример проблемы, а не конкретная формула, с которой я работаю.]
Вы можете определить имя для части формулы VLOOKUP.
Здесь важен шаг (1): я думаю, во втором ряду вы хотите, чтобы Excel использовал VLOOKUP(A2,B:B, 1, 0) , третий ряд VLOOKUP(A3,B:B, 1, 0) и т.д. На шаге (4) это достигается путем использования относительных ссылок ( A1 и B:B ), а не абсолютных ссылок ( $A$1 и $B:$B ).
Замечания:
Для более новых версий Excel с лентой перейдите на ленту Формулы → Определить имя. Это то же самое после этого. Кроме того, чтобы использовать свое имя, вы можете выполнить "Использовать в формуле" прямо в разделе "Определить имя" при редактировании формулы или начать вводить ее, и Excel предложит имя (предоставлено Майклом Рашем)
Сокращенные шаги: 1. Щелкните правой кнопкой мыши ячейку и выберите Определить имя. 2. Введите имя и формулу, которую вы хотите связать с этим именем/локальной переменной 3. Используйте переменную (credits: Jens Bodal)
Эта статья содержит полное руководство по работе с переменными и использованию VBA Dim.
Первый раздел содержит краткое руководство по использованию оператора Dim, включая примеры и формат оператора Dim.
Остальная часть поста содержит наиболее полное руководство, которое вы найдете в VBA Dim Statement.
Если вы заинтересованы в объявлении параметров, вы можете прочитать о них здесь.
Краткое руководство по использованию VBA Dim Statement
Полезные ссылки
- Объявление параметров в подпрограмме или функции
- Использование объектов в VBA
- Словарь VBA
Что такое VBA Dim Statement?
Ключевое слово Dim — это сокращение от Dimension. Он используется для объявления переменных в VBA.
Объявление означает, что мы говорим VBA о переменной, которую будем использовать позже.
Существует четыре типа Dim Statement. Все они очень похожи по синтаксису.
Ниже приводится краткое описание каждого типа
- Basic variable — этот тип переменной содержит одно значение. Это такие типы, как Long, String, Date, Double, Currency.
- Variant — VBA решает во время выполнения, какой тип будет использоваться. Вы должны избегать вариантов, где это возможно, но в некоторых случаях требуется их использование.
- Object — это переменная, которая может иметь несколько методов (то есть подпрограмм / функций) и несколько свойств (то есть значений). Есть 3 вида:
Объекты Excel, такие как объекты Workbook, Worksheet и Range.
Пользовательские объекты, созданные с использованием модулей классов.
Внешние библиотеки, такие как Словарь. - Array — это группа переменных или объектов.
В следующем разделе мы рассмотрим формат оператора VBA Dim с некоторыми примерами каждого из них.
В последующих разделах мы рассмотрим каждый тип более подробно.
Формат VBA Dim Statement
Формат выражения Dim показан ниже.
Мы рассмотрим эти различные типы операторов Dim в следующих разделах.
Как использовать Dim с несколькими переменными
Мы можем объявить несколько переменных в одном выражении Dim.
Если мы опускаем тип, то VBA автоматически устанавливает тип как Variant. Мы увидим больше о Variant позже.
Когда вы объявляете несколько переменных, вы должны указать тип каждой отдельно.
Вы можете поместить столько переменных, сколько захотите, в одном выражении Dim, но для удобства чтения рекомендуется оставить его равным 3 или 4.
Где я должен поместить Dim Statement?
Оператор Dim может быть помещен в любое место кода. Тем не менее, он должен предшествовать любой строке, где используется переменная.
Когда дело доходит до позиционирования ваших Dim утверждений, вы можете сделать это двумя основными способами. Вы можете разместить все свои Dim заявления в верхней части процедуры.
ИЛИ вы можете объявить переменные непосредственно перед их использованием:
Я лично предпочитаю последнее, так как оно делает код более аккуратным, и его легче читать, обновлять и обнаруживать ошибки.
Использование Dim в циклах
Помещение оператора Dim в цикл не влияет на переменную.
Когда VBA запускает Sub (или Function), первым делом он создает все переменные, которые были объявлены в выражениях Dim.
Следующие 2 фрагмента кода практически одинаковы. Во-первых, переменная Count объявляется перед циклом. Во втором он объявлен в цикле.
Код будет вести себя точно так же, потому что VBA создаст переменные при входе в подпрограмму.
Могу ли я использовать Dim для присвоения значения?
Мы не можем сделать это в VBA. Мы можем использовать оператор двоеточия для размещения объявлений и назначения строк в одной строке.
Мы не объявляем и не присваиваем в одной строке VBA. Что мы делаем, это помещаем эти две строки (ниже) в одну строку в редакторе. Что касается VBA, это две отдельные строки, как здесь:
Здесь мы помещаем 3 строки кода в одну строку редактора, используя двоеточие:
Нет никаких преимуществ или недостатков в назначении и объявлении в одной строке редактора. Все сводится к личным предпочтениям.
Dim действительно требуется?
Ответ в том, что это не обязательно. VBA не требует от вас использовать Dim Statement.
Однако не использовать оператор Dim — плохая практика и может привести к множеству проблем.
Вы можете использовать переменную без предварительного использования оператора Dim. В этом случае переменная автоматически будет типом варианта.
Это может привести к таким проблемам, как
Из-за этих проблем рекомендуется сделать использование Dim обязательным в нашем коде. Мы делаем это с помощью оператора Option Explicit.
Давайте посмотрим на некоторые ошибки, которые могут остаться незамеченными, если мы не будем использовать Dim.
Ошибки Переменной
В приведенном ниже коде мы используем переменную Total без использования оператора Dim.
Если мы случайно написали Total неправильно, VBA сочтет это новой переменной.
В приведенном ниже коде мы неправильно написали переменную Total как Totall.
VBA не обнаружит ошибок в коде, и будет напечатано неверное значение.
Давайте добавим Option Explicit и попробуйте приведенный выше код снова
Когда мы добавим оператор Dim для Total и запустим код, мы получим ошибку, сообщающую, что опечатка Totall не была определена.
Это действительно полезно, так как помогает нам найти ошибку, которая в противном случае осталась бы незамеченной.
Ошибка в ключевом слове
Вот второй пример, который более тонкий.
Когда следующий код выполняется, он должен изменить шрифт в ячейке A1 на синий.
Однако, когда код запускается, ничего не происходит.
Эти два примера очень просты. Если у вас много кода, то подобные ошибки могут стать кошмаром для отслеживания.
Использование Dim с Basic Variables
VBA имеет те же основные типы переменных, которые используются в электронной таблице Excel.
Вы можете увидеть список всех типов переменных VBA здесь.
Тем не менее, большую часть времени вы будете использовать следующие:
| Тип | Хранение | Диапазон | Описание |
| Boolean | 2 байта | ИСТИНА или ЛОЖЬ | Эта переменная может быть ИСТИНА или ЛОЖЬ. |
| Long | 4 байта | от -2,147,483,648 до 2,147,483,647 | Long — это сокращение от Long Integer. Используйте это вместо типа Integer * |
| Currency | 8 байт | от -1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; от 4.94065645841247E-324 до 1.79769313486232E308 для положительных значений | Аналогично Double, но имеет только 4 знака после запятой |
| Double | 8 байт | от -922,337,203,685,477.5808 до 922,337,203,685,477.5807 | |
| Date | 8 байт | С 1 января 100 по 31 декабря 9999 | |
| String | меняется | От 0 до примерно 2 миллиардов | Содержит текст |
* Первоначально мы использовали бы тип Long вместо Integer, потому что Integer был 16-разрядным, и поэтому диапазон был от -32 768 до 32 767, что довольно мало для многих случаев использования целых чисел.
Однако в 32-битной (или выше) системе целое число автоматически преобразуется в длинное. Поскольку Windows была 32-битной начиная с Windows 95 \ NT, нет смысла использовать Integer.
В двух словах, всегда используйте Long для целочисленного типа в VBA.
Фиксированный тип строки
В VBA есть один необычный тип базовой переменной, с которым вы, возможно, не знакомы.
Это фиксированный тип строки. Когда мы создаем нормальную строку в VBA, мы можем добавить текст, и VBA автоматически изменит размер строки для нас.
Фиксированная строка никогда не изменяется. Эта строка всегда будет иметь одинаковый размер независимо от того, что вы ей назначаете
вот несколько примеров:
Использование Dim с Variants
Когда мы объявляем переменную как вариант, VBA решает во время выполнения, какой тип переменной должен быть.
Мы объявляем варианты следующим образом
Однако использование вариантов является плохой практикой, и вот почему:
- VBA не будет замечать неправильных ошибок типа (т. Е. Несоответствие данных).
- Вы не можете получить доступ к Intellisense.
- VBA угадывает лучший тип, и это может быть не то, что вы хотите.
Тип ошибки
Ошибки твои друзья!
Они могут раздражать и расстраивать, когда они случаются, но они предупреждают вас о будущих проблемах, которые не так легко найти.
Ошибка несоответствия типов предупреждает вас, когда используются неверные данные.
Например. Представьте, что у нас есть лист оценок учеников. Если кто-то случайно (или намеренно) заменит метку на текст, данные будут недействительными.
Если мы используем вариант для хранения меток, то ошибки не возникнет
Это не хорошо, потому что в ваших данных есть ошибка, а вы не знаете об этом.
Доступ к Intellisense
Intellisense — удивительная особенность VBA. Он дает вам доступные параметры в зависимости от типа, который вы создали.
Представьте, что вы объявляете переменную листа, используя Dim
Когда вы используете переменную wk с десятичной точкой, VBA автоматически отобразит доступные опции для переменной.
Вы можете увидеть Intellisense на скриншоте ниже
Если вы используете Variant как тип, то Intellisense будет недоступен
Это потому, что VBA не будет знать тип переменной до времени выполнения.
Использование Dim с Objects
Если вы не знаете, что такое Objects, вы можете прочитать мою статью об VBA Objects здесь.
Есть 3 типа объектов:
- Объекты Excel
- Объекты модуля класса
- Внешние объекты библиотеки
Примечание. Объект VBA Collection используется аналогично тому, как мы используем объект Class Module. Мы используем новое, чтобы создать его.
Давайте посмотрим на каждый из них по очереди.
Объекты Excel
При создании или открытии книги Excel автоматически создает связанный объект.
Например, в приведенном ниже коде мы открываем рабочую книгу. VBA создаст объект, а функция Open вернет книгу, которую мы можем сохранить в переменной
Если мы создадим новый лист, произойдет похожая вещь. VBA автоматически создаст его и предоставит доступ к объекту.
Нам не нужно использовать ключевое слово New для этих объектов Excel.
Мы просто присваиваем переменную функции, которая либо создает новый объект, либо дает нам доступ к существующему.
Вот несколько примеров назначения переменных Workbook, Worksheet и range
Если вы хотите узнать больше об этих объектах, вы можете ознакомиться со следующими статьями: Workbook VBA, Worksheet VBA и Cell и Range VBA.
Использование Dim с Class Module Objects
В VBA мы используем Class Modules для создания наших собственных пользовательских объектов. Вы можете прочитать все о Class Modules здесь.
Если мы создаем объект, нам нужно использовать ключевое слово New.
Мы можем сделать это в операторе Dim или в операторе Set.
Следующий код создает объект, используя ключевое слово New в выражении Dim:
Использование New в выражении Dim означает, что каждый раз при запуске нашего кода будет создаваться ровно один объект.
Использование Set дает нам больше гибкости. Мы можем создать много объектов из одной переменной. Мы также можем создать объект на основе условия.
Давайте посмотрим на пример использования Set. В приведенном ниже коде мы хотим прочитать диапазон данных. Мы создаем объект только в том случае, если значение больше 50.
Мы используем Set для создания объекта Class1. Это потому, что количество нужных нам объектов зависит от количества значений более 50.
Я сохранил этот пример простым для ясности. В реальной версии этого кода мы бы заполнили объект Class Module данными и добавили его в структуру данных, такую как Collection или Dictionary.
Вот пример реальной версии, основанной на данных ниже:
Чтобы узнать больше о Set вы можете заглянуть сюда.
Объекты из внешней библиотеки
Действительно полезная часть VBA — это то, как у нас есть доступ к внешним библиотекам. Это открывает целый новый мир тому, что мы можем сделать.
Примерами являются библиотеки Access, Outlook и Word, которые позволяют нам взаимодействовать с этими приложениями.
Мы можем использовать библиотеки для различных типов структур данных, таких как Словарь, Массив, Стек и Очередь.
Существуют библиотеки для очистки веб-сайта (библиотека объектов Microsoft HTML), использования регулярных выражений (регулярные выражения Microsoft VBScript) и многих других задач.
Мы можем создать эти объекты двумя способами:
- Раннее связывание
- Позднее связывание
Давайте посмотрим на это по очереди.
Раннее связывание
Раннее связывание означает, что мы добавляем справочный файл. Как только этот файл добавлен, мы можем рассматривать объект как объект модуля класса.
Мы добавляем ссылку, используя Tools-> Reference, а затем проверяем соответствующий файл в списке.
Как только мы добавим ссылку, мы можем использовать словарь как объект модуля класса
Преимущество раннего связывания заключается в том, что у нас есть доступ к Intellisense. Недостатком является то, что это может вызвать конфликтные проблемы на других компьютерах.
Лучше всего использовать раннюю привязку при написании кода, а затем использовать позднюю привязку при распространении кода другим пользователям.
Позднее связывание
Позднее связывание означает, что мы создаем объект во время выполнения.
Использование Dim с Arrays
В VBA есть два типа массивов:
- Статический — размер массива задается в операторе Dim и не может изменяться.
- Динамический — размер массива не указан в выражении Dim. Это устанавливается позже с помощью оператора ReDim
Динамический массив дает нам гораздо больше гибкости. Мы можем установить размер во время выполнения кода.
Мы объявляем динамический массив, используя инструкцию Dim, и устанавливаем размер позже, используя ReDim.
Использование ReDim
Большая разница между Dim и ReDim заключается в том, что мы можем использовать переменную в выражении ReDim. В операторе Dim размер должен быть постоянным значением.
На самом деле мы можем использовать оператор Redim без предварительного использования оператора Dim.
В первом примере вы можете видеть, что мы используем Dim:
Во втором примере мы не используем Dim:
Преимущество состоит в том, что вам не нужно тусклое утверждение. Недостатком является то, что это может сбить с толку тех, кто читает ваш код. В любом случае это не имеет большого значения.
Вы можете использовать ключевое слово Preserve с ReDim для сохранения существующих данных при изменении размера массива. Вы можете прочитать больше об этом здесь.
Вы можете найти все, что вам нужно знать о массивах в VBA здесь.
Устранение неполадок ошибок Dim
В таблице ниже приведены ошибки, с которыми вы можете столкнуться при использовании Dim. См. Ошибки VBA для объяснения различных типов ошибок.
Локальные и глобальные переменные
Когда мы используем Dim в процедуре (то есть подпрограмме или функции), она считается локальной. Это означает, что это доступно только с этой процедурой.
Глобальные переменные объявляются вне процедур. В зависимости от типа, они могут быть доступны для всех процедур в одном и том же модуле или для всех процедур во всех модулях в текущей рабочей книге.
В приведенном ниже коде мы объявили count как глобальную переменную:
Что произойдет, если у нас будет глобальная переменная и локальная переменная с одинаковым именем?
На самом деле это не вызывает ошибку. VBA дает приоритет локальной декларации.
Подобная ситуация может привести только к проблемам, так как трудно отследить, какой счет используется.
Вообще глобальных переменных следует избегать, где это возможно. Они делают код очень трудным для чтения, потому что их значения могут быть изменены в любом месте кода. Это затрудняет обнаружение и устранение ошибок.
Важно знать и понимать глобальные переменные как вы, поскольку вы можете встретить их в существующем коде.
Dim против Private
В VBA есть ключевое слово Private.
Если мы используем ключевое слово Private с переменной или подфункцией / функцией, то этот элемент доступен только в текущем модуле.
Использование Dim и Private для переменной дает тот же результат
В VBA принято использовать Private для глобальных переменных и Dim для локальных
Local OnlyThere в VBA есть 2 других типа объявлений, которые называются Public и Global.
Ниже приводится краткое изложение всех 4 типов:
- Dim — используется для объявления локальных переменных, т. Е. В процедурах.
- Private — используется для объявления глобальных переменных и процедур. Эти переменные доступны только для текущего модуля.
- Public — используется для объявления глобальных переменных и процедур. Эти переменные доступны во всех модулях.
- Global — старая и устаревшая версия Public. Может использоваться только в стандартных модулях. Он существует только для обратной совместимости.
Заключение
На этом мы заканчиваем статью о VBA Dim Statement. Если у вас есть какие-либо вопросы или мысли, пожалуйста, дайте мне знать в комментариях ниже.
Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:
Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.
Присваивается переменной диапазон ячеек с помощью оператора Set:
В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.
Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.
Адресация ячеек в диапазоне
К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.
Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 |
Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:
Программа электронных таблиц Windows Excel может использовать функции, которые ссылаются на конкретные ссылки на ячейки так же, как формальное программирование использует переменные. Примером использования переменной в Excel было бы взять показатели суммарных продаж по регионам, поместить их в конкретные ячейки и обратиться к этим ячейкам для дополнительного анализа. Во всех случаях при использовании переменных в Excel ячейки должны быть настроены так, чтобы в них были результаты, которые вы хотите получить.
Шаг 1
Выберите ячейку с результатом, который вы хотите объявить как переменную.
Шаг 2
Шаг 3
Введите имя переменной в появившемся диалоговом окне. С этого момента в этой книге вы сможете ссылаться на содержимое этой ячейки, вводя ее имя.
Как объединить переменные в SPSS
Исследователи социальных наук используют Статистический пакет для социальных наук, или SPSS, для анализа данных в таких областях, как психология, социология и политология. SPSS - это .
Как использовать фиктивные переменные в регрессии Excel
Популярная программа Microsoft Excel имеет возможности анализа данных, которые включают проведение регрессионного анализа с фиктивными переменными. Фиктивные переменные являются категориальными переменными .
Как разбить строку на две переменные в PowerShell
Microsoft PowerShell - это среда для запуска сценариев и автоматизации задач с использованием операционной системы Microsoft Windows. PowerShell v2.0 доступен для Windows 7 и может также .
Читайте также: