Пришла мысль выложить мои маленькие скрипты для Excel. Постараюсь идти от простого к сложному (ну… от маленького к большему) - вдруг кому изучить поможет. Впрочем, скриптов у меня мало.

Итак, в приложения Microsoft Office встроен язык Visual Basic for Application (VBA) - близнец обычного Visual Basic (по крайней мере до 6 версии VB включительно). Его можно и нужно использовать для автоматизации рутинных операций. Все нижеизложенное относится к Excel 2003.

Вчитываться имеет смысл только если вы вообще представляете, что такое бейсик, иначе можно просто копировать текст скрипта.

Для начала скриптик, который меняет формулы в ячейках на вычисленные значения. На случай, если надо зафиксировать результат. Открывайте Excel, нажимайте Alt + F11 (или Сервис > Макрос > Редактор Visual Basic), создавайте новый модуль (Insert > Module) и поехали. Впрочем, задержитесь на секунду и дайте модулю осмысленное имя, желательно латиницей. И еще, если открыта не одна книга, посмотрите, где вы создаете модуль. Слева торчит дерево проекта (если не торчит, View > Project Explorer), там и выбирайте нужный. К сожалению, майкрософтовский офис не имеет общего хранилища для инструментов; скрипты обязательно привязываются к книге.

Теперь текст скрипта:

' FormulaToValue
' преобразует формулу в значение в выделенных ячейках

' Объявляем процедуру
Sub FormulaToValue()
' объявляем переменную cl, она будет иметь тип Range
Dim cl As Range

' перебираем все ячейки (автоматически записываемые в переменную cl)
' в выделеной пользователем части листа (Selection)
' для каждой из этих ячеек по отдельности выполняем инструкции в
' строчках между For... и Next
For Each cl In Selection
' присваиваем свойству значение (Value) каждой ячейки
' это же самое значение (смешно, да?)
cl.Value = cl.Value
Next
' выходим из процедуры
End Sub

По моему скромному, но верному мнению, комментариев достаточно. Кроме самой главной строчки.

cl.Value = cl.Value

Мы обращаемся к свойству Value объекта cl, а там лежит значение ячейки. Если ячейка вычисляемая, то все равно там содержится вычисленное значение, а формула - в свойстве Formula. Это же самое значение мы записываем обратно. При этом старое содержимое свойства Formula теряется, и ячейка сохраняет просто число (или текст, что там у вас было).

И о типе Range. Переменная такого типа является объектной, и может ссылаться на некоторый набор ячеек (или, в нашем случае, одну), которые и называется здесь объектом.

Запускать процедуру можно, выбирая ее в окошке макрос (Сервис > Макрос > Макросы… или Alt + F8). Там же можно задать горячую клавишу для каждой процедуры. А можно повесить макрос на созданную кнопку в панели инструментов. Перед запуском необходимо выделить ячейки, над которыми будет проводиться операция. Помните, что зажав Ctrl, можно выделять несмежные ячейки.

Также читайте всю серию постов по VBA.

  
Электронная книга: как правильно выбрать?
В настоящее время, электронные книги среди многих пользователей становятся всё более и более популяр
Какой правильно выбирать планшетный ПК?
Подобрать планшетный ПК достаточно непросто. Сегодня присутствует множество предложений, среди котор
Влияние музыки на человека трудно переоценить
Лечите душу ощущениями и…музыкой. Влияние музыки на человека поистине значительное, и это факт, о

12 комментариев на «VBA.Excel: Замена формул значениями»

  1. [...] продолжим разбирать простенькие примеры скриптов на VBA для Excel. первая часть. [...]

  2. Geepers пишет:

    а не легче сделать “copy/paste special”?

  3. павел рындин пишет:

    copy/paste special не легче по двум причинам:
    1 скрипт, запускаемый кнопкой или хоткеем — одно движение
    2 скрипт работает с выделением нескольких несмежных ячеек, copy/paste — нет

  4. безимяный пишет:

    люди как быстро научиться програмировать? читаю VBA для чайников. как то там всё запутано. или я такой тупой.

  5. Павел Рындин пишет:

    Ну если в «для чайников» запутано, может ну его?

  6. Андрей пишет:

    Спасибо за скрипт. Очень удобно.
    но вот есть одно неудобство, не знаю можно ли его в скрипте исправить:

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

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

  7. пэтлээца пишет:

    спасибо за скрипт использовал его для иных целей :)

  8. Arhangel87 пишет:

    Спасибо за скрипт!
    Как можно применить этот скрипт только к ячейкам, в формулах которых содержаться ссылки на внешние листы (например =СУММ([c:\1.xlsx]Лист1!$A$1;100) ) ?

  9. Александр пишет:

    Доброе время суток!
    Как защитить ссылку что бы она не изменялась?
    Есть ссылка например:
    =’D:\Отчеты\Отчет отдела [имя]\[Отчет отдела [имя].xlsx]1′!D11 на какой документ она ссылается, и что интересно что при открытии этого документа и внесении изменений в нем эта ссылка почему то становится =’[Отчет отдела [имя].xlsx]1′!D11 в чем проблема или ошибка подскажите пожалуйста.

    • Павел Рындин пишет:

      Документы лежат в одной папке? Здесь, похоже, Excel отбрасывает путь к файлу. Если вам не надо передавать никуда эти файлы, и все считается правильно, не трогайте, пусть работает.

Оставить комментарий