Программирование математических выражений в вба эксель
Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?
Здравствуйте, Сергей!
Задавайте вопрос, постараюсь ответить.
Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.
Да, это возможно:
Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?
Просто отследить, какая кнопка нажата.
По предыдущему вопросу. Я вставил sendkeys… в конец макроса, который запускается при нажатии кнопки на листе. Окно фильтра появляется и тут же закрывается. А как сделать, чтобы окно осталось, и пользователь мог выбрать данные?
Евгений, я разобрался, почему не выводилось окно с фильтром.
Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.
Вставьте в стандартный модуль (в примере — Module1) объявление глобальной переменной и код процедуры для кнопки:
Range("A1").CurrentRegion можно заменить на имя таблицы.
Евгений, а формулу в поле B2 оставлять или её можно удалить?
Можно удалить, если в таблице есть другие формулы. Я изменил код процедуры Worksheet_Calculate() в предыдущем примере, чтобы она не реагировала на изменение формул, а только на нажатие кнопки «OK» автофильтра. Range("A1") — это ячейка с кнопкой фильтра.
Евгений, спасибо за корректировку процедуры. Думаю, в ней и поле myString1 необязательно (и связанная проверка с ним), т.к. пользователь может выбрать все значения.
Но… после команды SendKeys "%" событие Worksheet_Calculate() отрабатывается только один раз :(.
Сергей, событие Worksheet_Calculate() не зависит от команды SendKeys "%" . Ограничение стоит здесь:
Код в процедуре Worksheet_Calculate() срабатывает только когда активна ячейка Range("A1") . Если заменить в этой строке Range("A1") на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.
В переменную myString записывается состояние таблицы до применения фильтра, в переменную myString1 — после применения. Затем их содержимое сравнивается: если они содержат разные значения — значит, фильтр был применен.
Здравствуйте! может я не туда пишу. вопрос
нужно посчитать кол-во строк в столбце «В» (от 100 до 5000) и вставить в формулу вместо 744
table = Range("B2:C744")
спасибо.
В столбце «B» не должно быть пустых ячеек до последней строки таблицы.
Как определить в VBA есть узор в ячейке?
Спасибо за достаточно полную информацию.
Был бы очень вам признателен, если бы вы подсказали:
как изменить ширину блоков верхнего колонтитула.
«Введена слишком длинная строка. Уменьшите число знаков.»
При изменении шрифта с полужирного на обычный всё нормально
воспринимается,
Прошу прощения, если не по адресу.
Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:
Добрый день, Владислав!
Замените строку
Добрый день, Евгений!
ОГРОМНОЕ СПАСИБО. :))
Здравствуйте, помогите, пожалуйста, решить задачу((
Разработать программу «Кредитный калькулятор» для расчета выплат по кредиту в условиях начисления процентов по аннуитетной схеме постнумерандо. Форма должна предусматривать ввод:
1) сумма кредита, допустимы 2 знака после запятой;
2) процентная ставка (годовая), допустимы 2 знака после запятой;
3) количество периодов (месяцев), целое положительное число.
Также форма должна содержать две кнопки:
1) «Ok» (выполнить расчет, заполнить лист Excel, освободить форму и закончить работу программы);
2) «Отмена» (освободить форму и закончить работу программы).
На лист Excel следует вывести 5 колонок:
1) номер периода;
2) остаток кредита;
3) сумма процентов за пользование кредитом, подлежащая к оплате;
4) сумма погашения основного долга (кредита);
5) общая сумма выплат за период.
Форма должна быть открыта из макроса «main» и там же освобождена по завершению своего использования. Форма должна содержать только код работы с формой и ввода данных. Логика верификации данных должна быть, по возможности, отделена от кода формы. Бизнес-логика обязательно должна быть отделена от кода формы.
Здравствуйте, Рафия!
С разработкой кредитного калькулятора вам помогут только за плату на бирже фриланса.
Добрый день, Евгений! Я только начинаю работать с VBA. Помогите, пожалуйста, прописать код для автоматического перемещения курсора на ячейку вправо после заполнения предыдущей.
Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).
Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.
Арифметические операторы используются для выполнения многих знакомых арифметических операций, использующих вычисление числовых значений, представленных литералами, переменными, другими выражениями, вызовами функций и свойств и констант. Кроме того, классификация с помощью арифметических операторов — это операторы сдвига в битах, которые действуют на уровне отдельных битов операндов и сдвигаются их битовые шаблоны влево или вправо.
Арифметические операции
можно добавить два значения в выражение вместе с оператором +или вычесть одно из другого с помощью оператора-operator (Visual Basic), как показано в следующем примере.
отрицание также использует оператор-Operator (Visual Basic), но только с одним операндом, как показано в следующем примере.
при умножении и разделении используются оператор * и оператор (Visual Basic)соответственно, как показано в следующем примере.
В возведение в степень используется оператор ^, как показано в следующем примере.
Целочисленное деление выполняется с помощью оператора \ (Visual Basic). Целочисленное деление Возвращает частное, то есть целое число, представляющее количество, которое делитель может разделить на делимое, без учета остатка. Делитель и делимое должны быть целочисленными типами ( SByte , Byte , Short , UShort UInteger Integer . Long и ULong ) для этого оператора. Сначала необходимо преобразовать все остальные типы в целочисленный тип. В следующем примере показано целочисленное деление.
Арифметическая операция деления выполняется с помощью оператора MOD. Этот оператор возвращает остаток от деления делителя на делимое на целое число раз. Если и делитель, и делим являются целочисленными типами, возвращаемое значение является целочисленным. Если делитель и делимые являются типами с плавающей запятой, возвращаемое значение также будет плавающей запятой. Следующий пример демонстрирует эту ситуацию.
Попыток деления на ноль
В подразделениях с плавающей запятой, включающих Double тип данных, исключение не создается, а результатом является член класса, представляющий NaN , PositiveInfinity или NegativeInfinity , в зависимости от делимого. В следующей таблице перечислены различные результаты попытки деления Double значения на ноль.
Тип данных делимого | Тип данных делителя | Делимое значение | Результат |
---|---|---|---|
Double | Double | 0 | NaN (не является математически определенным числом) |
Double | Double | > 0 | PositiveInfinity |
Double | Double | < 0 | NegativeInfinity |
Bit-Shift операции
Операция сдвига в битах выполняет арифметический сдвиг для битового шаблона. Шаблон содержится в операнде слева, а операнд справа указывает количество позиций для сдвига шаблона. Можно сдвинуть шаблон вправо с помощью оператора или влево с помощью оператора.
Тип данных операнда шаблона должен быть SByte , Byte , Short , UShort Integer ,, UInteger , Long или ULong . Тип данных операнда суммы сдвига должен быть или должен быть Integer расширен до Integer .
Арифметические сдвиги не являются циклическими, то есть биты, сдвинутые за пределы результата, не переносятся на другой конец. Позиции битов, освобожденные сдвигом, устанавливаются следующим образом:
0 для арифметического сдвига влево
0 для арифметического сдвига вправо положительного числа
0 для арифметического сдвига вправо неподписанного типа данных ( Byte , UShort , UInteger , ULong )
1 для арифметического сдвига вправо отрицательного числа ( SByte , Short , Integer или Long )
В следующем примере значение сдвигается Integer влево и вправо.
Арифметические сдвиги никогда не создают исключений переполнения.
Битовые операции
В дополнение к логическим операторам Not Or And . и Xor также выполняют побитовую арифметическую операцию при использовании числовых значений. Дополнительные сведения см. в разделе "битовые операции" в логических и побитовых операторах в Visual Basic.
Безопасность типов
Обычно операнды должны иметь один и тот же тип. Например, если вы делаете сложение с Integer переменной, то следует добавить ее в другую Integer переменную, а результат также следует присвоить переменной типа Integer .
Одним из способов обеспечения хорошей строгой типизации кода является использование оператора Option строго. если задано Option Strict On , Visual Basic автоматически выполняет строго Option Strict On преобразования. Например, если попытаться добавить Integer переменную в Double переменную и присвоить ей значение Double , операция выполняется обычным образом, так как Integer значение может быть преобразовано в Double без потери данных. Типы-ненадежные преобразования, с другой стороны, вызывают ошибку компилятора с Option Strict On . Например, при попытке добавить Integer переменную в Double переменную и присвоить ей значение Integer , результатом ошибки компилятора является то, что Double переменная не может быть неявно преобразована в тип Integer .
однако если задать Option Strict Off , то Visual Basic допускает неявные сужающие преобразования, хотя они могут привести к непредвиденной утрате данных или точности. По этой причине рекомендуется использовать Option Strict On при написании рабочего кода. Для получения дополнительной информации см. Widening and Narrowing Conversions.
Подробно рассмотрим то, как VBA выполняет такие арифметические операции, как сложение, вычитание, умножение, деление и возведение в степень, а также особые операции, такие как целочисленное деление и деление по модулю. Ниже, в таблице, представлены знаки операций, используемые при написании арифметических VBA-выражений.
Выражение - это значение либо группа значений, выражающая отдельное значение. Результат выражения - одно значение определенного типа данных. Знаки (обозначения) операций используются для действий над определенными значениями в выражениях. Для присваивания результата выражения переменной используется оператор присваивания (=), который сохраняет любое значение, представленное выражением справа от оператора присваивания в ячейке памяти, на которую ссылается переменная слева от этого оператора.
Во всех операциях приведенных ниже, оба операнда должны быть численными выражениями или строками, которые VBA может преобразовать в число.
Сложение
Знак (+) используется для выполнения операции сложения. Слагаемые должны быть численными выражениями, строками, которые VBA может преобразовать в числа либо датами, с которыми также возможны арифметические действия.
Тип данных результата выражения сложения обычно тот же, что и наиболее точный тип в этом выражении за некоторыми исключениями. Все исключения из этого правила наглядно представлены в примерах.
Вычитание
Знак (-) используется для выполнения операции вычитания, а также для обозначения отрицательных чисел (когда ставится перед переменной или выражением и означает тоже самое, что и умножение на -1). Знак минуса, который помещают перед числом для обозначения того, что число отрицательное, называют унарным минусом.
Для определения типа данных результата выражения вычитания VBA следует тем же правилам, что и для выражений, использующих знак операции сложения, но имеются два дополнительные правила.
Правило 1. Если в выражении вычитания один из операндов является типом Date, то и результат выражения будет иметь тип Date.
Правило 2. Если в выражении вычитания оба операнда являются типом Date, то результат выражения будет иметь тип Double.
Умножение
Знак (*) используется для выполнения операции умножения, результатом этой операции является произведение операндов. Для определения типа данных результата выражения умножения VBA использует те же правила, что и для выражений, использующих сложение. В выражениях умножения все переменные Variant, содержащие значения типа Date, преобразуются в численные значения.
Деление
Знак (/) используется для выполнения операции деления, этот знак называют знаком деления действительных чисел. В выражениях деления один операнд делится на другой, а результатом деления является частное.
Если любой операнд в выражении деления имеет значение Null, то результатом выражения также будет Null. Тип данных в выражениях деления действительных чисел обычно Double, но есть и исключение.
Если в выражении деления оба операнда имеют тип Integer или Single, то результат выражения деления имеет тип Single. Если результат переполняет диапазон для типа Single, то VBA преобразует его в тип Double.
Целочисленное деление
Знак (\) используется для выполнения операции целочисленного деления, при котором результатом деления всегда является целое число без дробной части. VBA не округляет частное целочисленного деления, а просто укорачивает его до целого числа, отбрасывая дробную часть.
Тип данных результата выражения целочисленного деления - либо Integer, либо Long. VBA использует наименьший тип данных, который соответствует результату выражения.
Деление по модулю
Знак (Mod) используется для выполнения операции деления по модулю. При делении по модулю выражение возвращает только остаток от деления как целое.
Доступное для понимания объяснение этой математической операции приведено на одном из форумов программистов. Приведу цитату оттуда: "представь, что есть полная 50л канистра и 3л банка. И ты начинаешь вычерпывать из канистры банкой воду (набирать можно только полную банку). 48л вычерпал, осталось 2 литра. Это и есть остаток от деления 50 на 3 по модулю." Другими словами 50 Mod 3 возвращает 2.
Тип данных результата выражения деления по модулю - это Integer или Long. VBA использует наименьший тип, который подходит для результата выражения.
Возведение в степень
Знак (^) используется для выполнения операции возведения в степень числа или выражения. Показатель степени показывает, сколько раз число или выражение должно быть умножено на само себя.
В этой статье мы рассмотрим основные операторы языка VBA, которые отвечают за арифметические операции, операции сравнения и присвоения, а также конкатенации.
Давайте приступим к небольшому теоретическому изложению.
Арифметические операторы языка VBA
Арифметических операторов VBA есть всего семь, фактически, они позволяют выполнять основные арифметические операции:
- Сложение (+) – собственно, суммирование двух значений
- Вычитание (-) – вычитание двух операндов, операнд – это элемент, который участвует в вычислении.
- Умножение (*) – отвечает за умножение одного операнда на другой
- Деление (/) – отвечает за деление одного значения на другое, в итоге можем получить как целое так и вещественное число
- Возведение в степень (^) – данный оператор должен быть знакомым всем, кто осваивал еще в школе старый добрый Basic.
- Целочисленное деление (\) – позволяет произвести первого операнда на другой, при этом, будет отброшена дробная часть, если она существует, не стоит путать с округлением.
- Деление по модулю (Mod) – при делении вернет остаток от деления
Оператор присвоения языка VBA
Собственно, ничего сложно, просто знак равенства после имени переменной, после знака равенства идет присваиваемое значение, неважно, это может быть число, дата, строка и так далее.
Let MyVar=100 или, аналогично, MyVar=100
Тут мы переменной MyVar присваиваем целое число 100.
Операторы сравнения языка VBA
Данный тип операторов VBA используется при сравнении условий, например, в условном операторе.
- Равенство (=) – проверка условия, равна ли переменная заданному значению
- Больше (>) или меньше ( <) – позволяет проверить значение переменной на предмет, больше оно или меньше заданного параметра
- Не равно (<>) – условие типа, если переменная не равна заданному значению
- Сравнение объектов (is) – данный оператор vba позволяет проверять переменные-объекты, переменные-объекты – переменные, которые хранят не значение, а ссылку на объект. То есть, ссылаются ли заданные переменные на заданный объект, если да, то операция вернет логическое true.
- Подобие (Like) – позволяет произвести сравнение строкового объекта с заданным шаблоном и проверить, будет ли подходить заданный объект.
Стоит всегда помнить, что vba операторы сравнения всегда возвращают логические true (истина) или false (ложь). При произведении сравнения строк, их регистр будет учитываться, пробелы также учитываются. В процессе сравнения текстовых строк на предмет больше или меньше, происходит простое сравнивание двоичных кодов символов, который больше, а который меньше, если же надо учитывать алфавитный порядок, то используется выражение Option Compare Text .
Для оператора сравнения VBA – Like, как уже говорилось, надо прописать два выражения, первое – это тестовая строка, а второе – шаблон. Шаблон может содержать следующие подстановочные знаки:
VBA логические операторы
Логические операторы VBA позволяют проверять сразу несколько условий.
- AND — логическое И, то есть, оба условия должны выполниться
- OR — логическое ИЛИ, то есть, должно произойти выполнение хотя бы одного условия
- NOT — логическое НЕ, оно вернет TRUE, если проверяемое условие является ложным
- XOR — логическое ИСКЛЮЧАЮЩЕЕ ИЛИ, то есть, вернет true, только если один из операндов равен true, если оба равны true, то условие вернет false.
- EQV – эквивалентность двух значений
- IMP — импликация, вернет FALSE, если A1 = TRUE и A2 = FALSE, в противном случае, вернет TRUE.
VBA операторы конкатенации
& - данный оператор производит склейку двух строк или даже значений, при чем, числовые значения автоматически преобразуются в строки.
+ - данный оператор при сложении строки и значения Null (пустое значение) вернет Null, поэтому, для склейки предпочтительней использовать &.
Порядок применения операторов VBA следующий:
- арифметические
- конкатенации,
- сравнения
- логические
Использование круглых скобок позволяет регулировать порядок выполнения, собственно, как и в математике.
Спасибо за внимание. Автор блога Владимир Баталий
Приведенные ниже математические функции vba позволяют производить тригонометрические вычисления (нахождение sin, cos и так далее), обрабатывать числовые значения на предмет получения целой части от дробного, округления или генерации случайных чисел.
Если переданный параметр для следующих математических функций vba не будет распознан как числовое значение, возникнет ошибка.
ABS ( num) – Функция возвращает значение числа по модулю.
Exp ( num) – Функция позволяет получить значение экспоненты, передаваемый параметр (максимальное значение ровно 709 782712893) является степенью, в которую нужно возвести экспоненту. Соответственно, если параметр равен 1, то мы получить чистое значение экспоненты.
Fix ( num) – Функция возвращает целую часть от переданного дробного значения. Если переданное значение является отрицательным, то возвращается ближайшее отрицательное целое число, большее, либо равное указанному.
Int ( num) – Математическая функция vba возвращает целую часть от переданного дробного значения. Если переданное значение является отрицательным, то возвращается ближайшее отрицательное целое число, меньшее, либо равное указанному.
Rnd (random) – Функция вернет число, входящее в диапазон от 0 до 1 в случайном порядке, при этом, само число 1 не входит в диапазон, а 0 – входит. Перед вызовом функции надо прописывать конструкцию Randomize, которая отвечает за включение генератора случайных чисел.
В зависимости от значения переданного параметра random, данная математическая функция vba работает следующим образом:
- Параметр отсутствует или больше 0 - Генерация следующего случайного числа в последовательности. Повторный запуск программы генерирует аналогичную последовательность.
- Параметр меньше 0 – Происходит генерация одного и того же числа, опираясь на переданный аргумент.
- Параметр равен 0 - Происходит генерация одного и того же числа, опираясь на число из предыдущего вызова.
Round (num, [accr]) - Округление заданного числа (num) до заданной точности (accr).
Sgn (num) – Данная математическая функция vba позволяет узнать знак числа, переданного как аргумент. Функция vba вернет следующие значения:
- 0 – Число равно 0
- -1 – Число является отрицательным
- 1 – Число является положительным.
Sql (num) – Квадратный корень из переданного числового параметра num.
Следующие математические функции vba являются тригонометрическими. В качестве параметра для большинства указывается значение угла в радианах. Для перевода градусов в радианы используется формула:
Угол (в радианах) = угол (в градусах) * pi / 180, где pi = 3.14159265358979
Atn (par) – Арктангенс переданного числа (угол в радианах), функция вернет значение в диапазоне от -pi/2 до pi/2 радиан
Cos (par) - Косинус переданного числа (угол в радианах), функция вернет значение в диапазоне от -1 до 1.
Sin (par) - Синус переданного числа (угол в радианах), функция вернет значение в диапазоне от -1 до 1.
Tan (par) - Тангенс переданного числа (угол в радианах).
Log (par) – Натуральный логарифм переданного числа.
Я не буду приводить примеры для всех приведенных выше математических функций VBA, а приведу только один пример кода для функций sin и cos. Откройте редактор VBA (комбинация клавиш Alt + F11), добавьте в проект новый модуль (меню Insert/Module). В моем случае имя модуля – module3, в редакторе кода для заданного модуля пропишите следующие строки:
В данном случае мы создаем новый документ Word, добавляем в него форматированный (объект Font) текст, содержащий параметры значений для синуса и косинуса. Я выбрал диапазон значений от 0 до 360 градусов, используя цикл vba do…loop.
Спасибо за внимание. Автор блога Владимир Баталий
Читайте также: