Excel vba вычислить формулу
БлогNot. Решаем счётные задачи с помощью Excel VBA
Решаем счётные задачи с помощью Excel VBA
Visual Basic for Applications (VBA) – диалект языка Visual Basic, включённый в состав пакета Microsoft Office. Программы на VBA, называемые макросами, могут выполняться прямо из документа Word или Excel, используя при этом в качестве интерфейса пользователя стандартные для Windows кнопки, поля ввода, списки, окна диалога или переключатели.
Изучение языка - тема отдельная, хотя он совсем несложен. В этой заметке я покажу лишь самые очевидные вещи - как ввести данные и вывести результаты работы двумя основными способами - с помощью окон диалога и непосредственно в ячейки рабочего листа Excel.
Перед началом работы:
1. Включите настройку Кнопка Office - Параметры Excel (или Word) - Основные - "Показывать вкладку Разработчик на ленте". В последних версиях офиса настройка может называться иначе, но она там есть :)
2. На вкладке Разработчик нажмите кнопку Безопасность макросов и разрешите выполнение макросов:
параметры макросов
Когда цикл разработки окончен, лучше вернуть настройку на место, чтобы не открыть вирусный документ, полученный откуда-нибудь со стороны.
3. Нажмите вкладку Разработчик – Макросы, дайте новой программе имя и нажмите кнопку Создать:
создание макроса
Откроется редактор Visual Basic, в котором можно писать, отлаживать, выполнять и сохранять программы.
На скрине ниже показана программа, позволяющая вычислить, сколько процентов составляет значение A от B.
пример программы
Вот листинг почти программки такого же типа, только ещё проще.
Теперь можно нажать зелёный треугольничек или клавишу F5 в редакторе VBA, чтобы запустить программу. Если доступно несколько программ или текстовый курсор не установлен внутри программы, компьютер может попросить выбрать нужную по имени:
запуск программы из редактора Visual Basic
Чтобы макросы не пропали, при первом сохранении рабочей книги нужно выбрать пункт меню "Сохранить как" и указать в списке "Тип файла" значение "Книга Excel с поддержкой макросов (*.xlsm)".
Обычно мы хотим запускать программу не из Visual Basic, а прямо из документа, например, нажимая кнопку.
Чтобы встроить кнопку непосредственно в документ Word или Excel, действуем так:
1. На вкладке разработчика нажмём кнопку "Режим конструктора" и выберем нужный элемент управления, например, кнопку:
выбор инструмента "Кнопка"
2. Потом курсором-крестиком "нарисуем" кнопку в документе и нажмём "Создать" в окне "Назначить макрос объекту", чтобы кнопке была назначена пустая процедура-обработчик её основного события (то есть, нажатия):
добавление кнопки на лист
3. После этого можно запрограммировать процедуру обработки нажатия нашей кнопки.
Обращаться к ячейкам Excel из программы VBA тоже очень легко, вот несколько примеров:
Ну и немного более законченного кода.
Попробуйте скопировать в VBA и выполнить эти 2 несложных программы, и начальный опыт программирования в нём у Вас появится :)
Первая программа может быть назначена кнопке и позволяет ввести из столбца A текущего рабочего листа столько числовых значений, сколько их там набрано, но не больше 100.
Полученные значения заносятся в массив A, заполнение прекращается по достижении пустой ячейки, ячейки, заполненной не числом или когда набрано 100 элементов.
Затем от введённых чисел рассчитывается сумма и записывается в ячейку B6.
Вторая программа предполагает, что в ячейках B12 и B13 рабочего листа записаны 2 даты. Это могут быть строки, интерпретируемые Вашим Excel как даты, например, 01.01.2001 или даты, полученные формулой, скажем, =СЕГОДНЯ()
В противном случае мы вычисляем и выводим в ячейку B16 количество дней между датами, а в C12 и C13 - дни недели по русски. Добавьте небольшое оформление и получите простейший калькулятор дат:
пример "интерфейса" для макроса VBA
Преобразует имя Microsoft Excel в объект или значение.
Синтаксис
выражения. Оценка (Имя)
выражение: переменная, представляющая объект Application.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Name | Обязательный | Variant | Формула или имя объекта с помощью конвенции именования Microsoft Excel. Длина имени должна быть меньше или равна 255 символам. |
Возвращаемое значение
Примечания
Следующие типы имен в Microsoft Excel можно использовать с помощью этого метода:
Формулы.
Ссылки в стиле A1. Используйте любую ссылку на одну ячейку в нотации в стиле A1. Все ссылки считаются абсолютными ссылками.
Диапазоны. Используйте диапазон, интерсект и операторы профсоюзов (двоеточие, пространство и запятая соответственно) со ссылками.
Определенные имена. Можно указать любое имя на языке макроса.
Внешние ссылки. Используйте оператора ! для ссылки на ячейку или имя, определенное в другой книге; например. Evaluate("[BOOK1.XLS]Sheet1!A1")
Объекты диаграммы. Вы можете указать любое имя объекта диаграммы, например Legend, Plot Area или Series 1, чтобы получить доступ к свойствам и методам этого объекта. Например, Charts("Chart1").Evaluate("Legend").Font.Name возвращает имя шрифта, используемого в легенде.
Номер управления формами. Вы можете указать номер, а также имя, чтобы сослаться на управление формами на таблице. Например, для Метки, расположенной на таблице (вкладка разработчика - Вставка - Элементы управления формами . Метка), Evaluate("Label 1").Caption = "Hello" Evaluate("1").Caption = "Hello" и сделать то же самое.
Использование квадратных скобок (например, "[A1:C5]") идентично вызову метода Оценка с аргументом строки. Например, следующие пары выражений эквивалентны.
Преимущество использования квадратных скобок заключается в том, что код короче. Преимущество использования Оценки заключается в том, что аргумент — это строка, поэтому строку можно построить в коде или использовать переменную Visual Basic.
Пример
В этом примере включается жирный форматирование в ячейке A1 на листе1.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Здравствуйте. Есть файл там основная формула: 10+ЕСЛИ(F100=$ALR$100;0;(AMB2506*2)-10)
остальные формул похожие, только рядом с другими диапазонами работают.
Так вот как такую формулу превратить в VBA, чтобы вычиcлять методом VBA.
Решил обратить к VBA, т.к. исчерпал возможности EXCEL. если в Excel сделать таких формул на 100тыс строк и 1тыс столбцов, то время вычисления сильно увеличивается и что самое главное размер файла увеличивается, и потом этот файл просто не открыть, так как не хватает оперативной памяти. Поэтому и решил обратить к VBA, чтобы возможно ускорить вычисления и уменьшить объём книги, за счёт того, что формул не будет, а будет методом VBA вычисляться.
Можно ли как определить как писать формулы в яцейки из VBA по русски или английски?
в общем проблема в том можно ли как определить как писать формулы в яцейки из VBA по русски или.
Подскажите,как мне здесь формулы свои писать?
Есть какое-то приложение для Word,где можно писать математические формулы. Подскажите пожалуйста.
Как можно в Excel, в VBA писать команды на SQL?
Как можно в Excel, в VBA писать команды на SQL, обрабатывая таблицы из Access? Я вроде подключил.
Как сместить формулы в VBA при добавлении новых строк в Excel
Имеется очень много формул в таблице, что бы избежать их нечаянного удаления или при добавлении.
Вашу формулу можно переписать так :
А вычислить (без ввода в ячейку), можно например так :
или так (без формулы)
pashulka, а вы можете помочь мне запихнуть все эти 100тыс строк формул в массив? я это делать не умею к сожалению.
файл примера если надо я пришлю вам.
выскакивает какое то окошко, но в ячейку ничего не проставляется
Добавлено через 5 минут
pashulka, и нужно именно так как я указал в формуле: 10+ЕСЛИ(F100=$ALR$100;0;(AMB2506*2)-10).
просто вы не видели файл и не поняли суть вычисления. по вашей упрощённой формуле будет совсем другой результат при дальнейших вычислениях.
Добавлено через 1 час 20 минут
pashulka, хотя проверил только что. результат с формулой =ЕСЛИ(F100=$ALR$100;10;AMB2506*2) одинаковый.
но это не решило проблему.
Есть макрос (пример приложил), он сейчас получается банально копирует значения из ячейки с формулами, а если их нет то он не работает.
Нужно получить расчет именно кодом а не формулой в ячейке, макрос должен посчитать как пример формулы в примере приложенном и результат выводить в указанный диапазон.
Самое главное, формулы будут меняться, нужно получить вычисления макросом данной формулы внутри макроса.
В моем примере он берет данные из одного столбца и вставляет значения в другом.
Но так же нужно будет менять места вывода данных и даже место изначальных данных будет выводом
Это тоже нужно учесть.
Заранее огромное спасибо всем кто откликнется =)
Рекурентные формулы, вычисление элементарных функций (sin/cos)
Приветствую. Помогите, пожалуйста, с д/з. 1)вывесте рекурентные формулы для вычесления.
Как остановить выполнение макроса в самом макросе?
Подскажите, пожалуйста, можно ли остановить выполнение макроса в самом макросе.. имею ввиду.
Как сделать, чтобы макрос видел текст в документе, а не в самом макросе пришлось бы вводить
Как сделать, чтобы макрос видел текст в документе, а не в самом макросе пришлось бы вводить? Т.е.
Записать сразу 2 формулы в макросе для VBA
Помогите, пожалуйста, как мне записать 2 формулы в макросе . Одну формулу для нахождения конечного.
Есть макрос (пример приложил), он сейчас получается банально копирует значения из ячейки с формулами, а если их нет то он не работает.
Нужно получить расчет именно кодом а не формулой в ячейке, макрос должен посчитать как пример формулы в примере приложенном и результат выводить в указанный диапазон.
Но так же нужно будет менять места вывода данных и даже место изначальных данных будет выводом
Это тоже нужно учесть.
Вот можно пример? Как это должно выглядеть?! Как оно должно выглядеть до и после смены места вывода данных?! Желательно пример, как сейчас, и как должно быть. Можно в картинках.
1. Нужно формулу переписать в макрос?
Нужно что бы макрос делал вычисления формулы не обращаясь к формула в ячейках, внутри кода должен делаться расчет формулы (я так понимаю для этого нужно формулу прописывать в коде как условия и переменные указывать для этого) хотелось бы к примеру: в макросе указать как стандартную формулу, он ее просчитал в макросе и вставил везде где нужно результат вычислений формулы.
2. Вот можно пример? Как это должно выглядеть?! Как оно должно выглядеть до и после смены места вывода данных?! Желательно пример, как сейчас, и как должно быть. Можно в картинках.
Имелось в виду, что он к примеру берет текст с столбца А просчитывает его формулой в макросе и значения вставляет в тот же столбец, либо если поменять то в соседний. Нужна будет такая возможность. Просто уточнил, что бы небыло привязки к определенному диапазону и был учет что в исходные данные вставка была.
Попробуйте :
Range("A1").Value = Range("A1").Value + 10
[a2].Value = [a2].Value * 2
Cells(3,1).Value= (Cells(1,1).Value+2 )*8
Добавлено через 3 минуты
Для диапазона ( как вариант)
Здравствуйте!
В продолжение темы
У меня есть макрос, который обрабатывает исходную переменную (строку) формулой, чтобы получить из неё другую (год):
Для этого он прописывает в ячейку формулу,
подставляя в неё значение исходной переменной (строки)
приравнивает переменную (год) значению ячейки и очищает ячейку.
Можно ли как-то обойтись без записи этих данных в ячейку?
Спасибо!
= Мир MS Excel/Статьи об Excel
Функции листа. Функции баз данных [1] |
Функции листа. Функции даты и времени [5] |
Функции листа. Функции инженерные [1] |
Функции листа. Функции информационные [2] |
Функции листа. Функции логические [10] |
Функции листа. Функции математические [4] |
Функции листа. Функции статистические [1] |
Функции листа. Функции текстовые [1] |
Функции листа. Функции финансовые [1] |
Функции листа. Функции ссылок и массивов [2] |
Функции листа. Функции аналитические [1] |
Функции листа. Функции определённые пользователем [1] |
Функция диаграмм [1] |
Макрофункции [37] |
ВЫЧИСЛИТЬ( текст_формулы )
Текст_формулы -- выражение в виде текста, которое необходимо вычислить.
Использование функции ВЫЧИСЛИТЬ аналогично выделению выражения внутри формулы в строке формул и нажатию кнопки «Вычислить сейчас» (F9 в Microsoft Excel для Windows и COMMAND+= в Microsoft Excel для Macintosh). Фукнция ВЫЧИСЛИТЬ заменяет выражение значением.
Предположим, необходимо узнать значение ячейки с именем РезИзмер1, РезИзмер2 или РезИзмер3, где 1, 2 или 3 заданы именем НомТройки, значение которой может изменяться во время работы макроса. Для вычисления этого значения можно воспользоваться следующей формулой:
Читайте также: