Написание кода в VBA Excel

@trarater · Регистрация: 28.09.2022

Студворк — интернет-сервис помощи студентам

Извиняйте может за глупупый вопрос, но я нуб.

Изучая большое количество информации выявил следующее, что повергло меня в некое замешательство, так как не компетентен в отношении VBA:
1) Многие статьи рекомендуют в параметрах программы Excel установить настройку безопасности макроса «Отключить все макросы с уведомлением» и активировать галочку «Доверять доступ к объектной модели проектов VBA».
2) Так же некоторые статьи советуют редакторе Visual Basic в пункте Tools-Options на вкладке Editor установить галочку на параметр «Require Variable Declaration» и изменить зхначение «Tab Width» на 2.
Уточните пожалуйста, нужно ли проводить данные настройки, либо использовать какие-либо другие манипуляции по настройке табличного редактора Excel и редактора Visual Basic?

Далее о проблеме, по которой и попал сюда.
Имеется таблица Excel, созданная в версии 2003 и редактируемая, на сегодняшний день, в версии 2016. Таблица имеет несколько тысяч строк с десятками примечаний в каждой строке. Спустя много лет использования файла, большая часть примечания при открытии для редактирования оказывается на 2-5 тысяч строк ниже материнской ячейки. В ручную это все перетаскивать ну очень не удобно, вследствие чего решил написать макрос, который восстановит стандартное положение примечаний.

Задача. Создать отдельную книгу Excel с макросом, которую можно будет переместить на другой компьютер и с помощью которой можно восcтановить расположение примечаний в проблемном файле.

Здесь возникло огромное количество вопросов. Опишу процесс поэтапно и прошу поправить, если где-то что-то делаю некорректно.
1) Создаю модуль insert-Module
2) Двойной клик на Module1, в пункте Insert-Procedure выбираю Sub + Public, и тут не знаю, нужно ли устанавливать галочку «All local variables as Static»
3) Далее в правом окне пишу код. Здесь в смятении, что писать, так как нашел много разных вариантов восстановления расположения примечаний.
У меня получилось вот такие варианты кода, в которых сомневаюсь.

Первый:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Option Explicit ' не знаю надо писать эту строку или нет '
 
Public Sub Fix()
 Dim comm As Comment ' иногда эту строку советуют написать так: Dim iComment As Comment, A_Size As Boolean '
 
    For Each comm In ActiveSheet.Comments
        With comm 
' иногда эти 2 строки советуют написать так: For Each iComment In ActiveSheet.Comments / With iComment.Parent.Cells '
            .Shape.Top = .Parent.Top 
            .Shape.Left = .Parent.Offset(, 1).Left    
' иногда эти 2 строки советуют написать так: iComment.Shape.Top = .Top - 10 / iComment.Shape.Left = .Left + .Width + 10 '     
        End With
    Next comm
    MsgBox "Положения примечаний исправлены" ' иногда эту строку советуют написать так: MsgBox "Положения примечаний исправлены", vbInformation, "" '
End Sub

Второй (без выполнения п.2):

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
Sub Fix()
    Dim oComm As Comment
 
    For Each oComm In ActiveSheet.Comments
        With oComm
            .Shape.Top = .Parent.Cells.Top - 10
            .Shape.Left = .Parent.Cells.Left + .Parent.Cells.Width + 10
        End With
    Next oComm
    MsgBox "Положения примечаний исправлены"
End Sub

4) Закрываю редактор VBA.
5) Закрываю файл Excel. здесь подтверждаю сохранение и соглашаюсь с политикой конфиденциальности.
6) Переношу файл с макросом на другой компьютер, запускаю его.
7) Запускаю проблемную книгу, перехожу на лист со сместившимися примечаниями, запускаю макрос.
Правильно ли выбран порядок действий и как правильнее оформить код, на Ваш взгляд?

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

@trarater · 28.09.2022, 15:58 **[ТС]**

АЕ, shanemac51,
понял. принял. осознал.
Спасибо огромное за помощь. Форум отличный. В скором времени перееду сюда основательно.

@trarater · 29.09.2022, 08:25 **[ТС]**

АЕ, Можно поинтересоваться?

Сообщение от АЕ

Например так

если сюда допишу

Code
1
2
            .Shape.Height = 200 
            .Shape.Width = 200

это должно изменить размер.
Правильно будет, или лучше по-другому это прописать?

Добавлено через 10 минут
АЕ, Еще вот такой кусок нашел, пишут, что автоподбор размера

Code
1
If A_Size Then iComment.Shape.TextFrame.AutoSize = True

Но не понимаю, как его уместить в текущий код, так как знаний нет
Желательно авторазмер поставить. За совет буду благодарен
предполагаю. что строку прописатьнадо так

Code
1
.Shape.TextFrame.AutoSize = True

@АЕ · 29.09.2022, 09:10

trarater,метод проб и ошибок никто не отменял. Экспериментируйте.
Очевидно, что A_Size это переменная, которая в вашем коде не определена .

@trarater · 29.09.2022, 13:30 **[ТС]**

АЕ, О чудо. Моему восторгу нет описания
порпечатал так

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Sub CommentsAndSize()
    Dim oComm As Comment
    For Each oComm In ActiveSheet.Comments
        With oComm
            .Shape.Top = .Parent.Cells.Top - 10
            .Shape.Left = .Parent.Cells.Left + .Parent.Cells.Width + 10
            .Shape.TextFrame.AutoSize = True
        End With
    Next oComm
    MsgBox "Примечания профикшены"
    Exit Sub
es:
MsgBox "Ошибка" & Err
End Sub

При выполнении компьютер напрегся, но потом все. примечания на местах и размер везде нормальный.
На Ваш взгляд код правильно прописан?

@Zeag · 29.09.2022, 13:45

1. Добавьте в начале Application.ScreenUpdating = False и в конце Application.ScreenUpdating = True, это ускорит дело
2. Строка MsgBox "Ошибка" & Err никогда не будет выполняться, т.к. нет On Error GoTo es

@АЕ · 29.09.2022, 14:02

Сообщение от trarater

порпечатал так

учитесь порпечатывать с пониманием того, что именно пишите.

@trarater · 29.09.2022, 15:19 **[ТС]**

Zeag, Добавил оптимизация синтаксисами и прописал оператора

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit
 
 
Public Sub FNAS()
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo blin
    Dim oComm As Comment
    For Each oComm In ActiveSheet.Comments
        With oComm
            .Shape.Top = .Parent.Cells.Top - 10
            .Shape.Left = .Parent.Cells.Left + .Parent.Cells.Width + 10
            .Shape.TextFrame.AutoSize = True
        End With
    Next oComm
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "Примечания профикшены"
    Exit Sub
blin:
MsgBox "Ошибка" & Err
End Sub

Сообщение от АЕ

учитесь порпечатывать с пониманием того, что именно пишите

Учусь, но совета спрашиваю все-равно.
Гляньте код пожалуйста. немного оптимизировал и порписал оператора ошибки.
Так же интересует автоподбор размера. Правильно ли прописал?

@АЕ · 29.09.2022, 15:38

Сообщение от trarater

Правильно ли прописал?

да, все верно. В таком варианте на больших файлах будет работать быстрее.

@trarater · 30.09.2022, 15:01 **[ТС]**

Сообщение от АЕ

да, все верно

Не могу понять, что делает оператор

Code
1
On Error Resume Next

Он следит за ошибками и передает действие следующему оператору при обнаружении ошибки. Есть ли смысл мне его прописать, или лучше GoTo?

почему некоторые пишут так?

Code
1
2
3
4
With Comment
                  .Comment.Shape.TextFrame.AutoSize = True
' и эту строку
           For Each Comment In Application.ActiveSheet.Comments

мучал поисковик, ответа не нашел.
понял, что Comment, oComm и т.п это названия операторов.

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

@АЕ · 30.09.2022, 15:19

Сообщение от trarater

мучал поисковик, ответа не нашел.

я нашел сразу.

@Zeag · 30.09.2022, 15:21

Сообщение от trarater

Он следит за ошибками и передает действие следующему оператору при обнаружении ошибки. Есть ли смысл мне его прописать, или лучше GoTo?

Да, следующему. Смысл есть. Например:

Visual Basic
1
2
3
4
5
6
7
With FP2.Sheets(n)      ' Process sheet
   On Error Resume Next
   iLastRow = -1
   iLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   On Error GoTo 0
   If iLastRow = -1 Then ...
End With

Определяем количество строк на листе. А если их нет? Возникнет ошибка. Перехватываем ее и на следующей строке проверяем заранее установленное в -1 значение (при ошибке оно не изменится). И делаем что-то, скажем, добавляем строки из другого файла.

Сообщение от trarater

почему некоторые пишут так?

Потому что разные стили написания и потребности.

Оператор With позволяет избежать написания полного префикса перед свойством или методом - короче запись и несколько быстрее исполнение.

For Each - перебор всех элементов без использования индекса.

Сообщение от trarater

понял, что Comment, oComm и т.п это названия операторов.

Не совсем. Посмотрите на определение выше:

Сообщение от trarater

Dim oComm As Comment

Здесь Comment это тип переменной oComm

Сообщение от trarater

порядок строк, отступы и тп.

Будь порядок строк неправильным, оно бы не работало. Искусство программиста - а) построить модель решения задачи в голове, б) перенести ее в код, в) сделать код правильным

Что до отступов - кто как, стандартный в VBA "из коробки" - 4 знака, я предпочитаю 3. Вложенные операторы - один Tab направо, чтобы видеть, где закрываются. Писать "в столбик" можно, но дурной тон и трата чужого времени.

@АЕ · 30.09.2022, 15:23

И еще, если беретесь свой опус выкладывать, то обрамляйте его не в CODE а в VB
Обработчики ошибок ставят на сложный код с возможными неожиданнастями. Вам, в вашем 2+2 он ни к чему.

@Zeag · 30.09.2022, 15:26

Сообщение от АЕ

Обработчики ошибок ставят на сложный код с возможными неожиданнастями. Вам, в вашем 2+2 он ни к чему.

Ах, хорошо бы так оно везде было. На Java пишешь 2*2 - изволь добавить try-catch на арифметику. Конкатенируешь строки - на проблемы с ними try-catch. И это растет и растет...

@trarater · 30.09.2022, 15:38 **[ТС]**

Zeag, АЕ, Спасибо огромное друзья за советы и ссылки. очень признателен.
Это

Сообщение от Zeag

Искусство программиста - а) построить модель решения задачи в голове, б) перенести ее в код, в) сделать код правильным
Что до отступов - кто как, стандартный в VBA "из коробки" - 4 знака, я предпочитаю 3. Вложенные операторы - один Tab направо, чтобы видеть, где закрываются. Писать "в столбик" можно, но дурной тон и трата чужого времени.

и это

Сообщение от АЕ

не в CODE а в VB

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

Сообщение от АЕ

Вам, в вашем 2+2 он ни к чему

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

@АЕ · 30.09.2022, 16:09

Сообщение от trarater

отступы и тп.

Что до отступов, то можно и без них. НО в этом случае читаемость и восприятие кода будет совершенно другим. Легче пропустить чего или потерять логику кода. Ну а на форуме это как признак хороших манер в придачу. Т.к. облегчаете помогающим чтение своего кода. Я и про VB не зря сказал. Раскраска операторов и функций облегчает восприятие.

@trarater · 30.09.2022, 16:41 **[ТС]**

АЕ, В общем все работает. Моя первая программа. очень доволен. К тому же оптимизаторы ускоряют процесс на 75%, специально замерял секундомером )))

@Zeag · 30.09.2022, 18:55

Сообщение от trarater

специально замерял секундомером

Ручным? ))

Visual Basic
1
2
3
4
Dim tStart As Single
tStart = Timer
' ...
Application.StatusBar = "Выполнено за " & Round(Timer - tStart, 2) & " сек"

@АЕ · 30.09.2022, 20:38

Сообщение от trarater

Моя первая программа. очень доволен.

Поздравляю, однако не следует писать об этом более 3-х раз. Могут не понять читающие.
Лучше воспользуйтесь методом измерения времени как предложил Zeag и покажите результаты.
Остальным они могут быть полезны для понимания использования подобной оптимизации.

@trarater · 03.10.2022, 09:50 **[ТС]**

Сообщение от Zeag

Ручным? ))

Да. На телефоне засек. В обычном режиме файл исправлялся 12 минут, а с оптимизацией - 3.

Сообщение от АЕ

Лучше воспользуйтесь методом измерения времени как предложил Zeag

так понимаю, эту часть необходимо вставить в код, только куда не знаю. И каким образом провести выгрузку результата тестирвоания сюда на форум? фото, если не ошибаюсь, не реккомендуется выкладывать.

@Zeag · 04.10.2022, 22:01

Сообщение от trarater

так понимаю, эту часть необходимо вставить в код, только куда не знаю

До и после, обрамив вычисления. Просто сказать, можно без фото.

Новые блоги и статьи Все статьи Все блоги /
Запрет дублирования строк в табличной части Maks 13.09.2026 Реализация из решения ниже выполнена на нетиповом справочнике "Нормы ТО" с табличной часть "Виды ТО", разработанного в КА2, со следующими реквизитами: - ВидТО (СправочникСсылка. ВидыТО); - ВидГСМ. . .	Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Jin X 06.09.2026 Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Работая с форумом и нейросетями в браузере часто хочется что-то подкорректировать или добавить какого-то функционала. Ниже прикреплён. . .	Программа опроса у.з. расходомера SLS-720F Argus19 02.09.2026 Программа опроса у. з. расходомера SLS-720F Программа опрашивает один раз в минуту три ультразвуковых расходомера SLS-720F через интерфейс RS-485 по протоколу Modbus RTU. Опрашиваются регистры. . .	Hyper-V: Компьютер должен поддерживать доверенный платформенный модуль 2.0. Maks 31.08.2026 При установке Windows 11 на виртуальную машину Hyper-V 2-го поколения вылезла такая ошибка: Решение: в параметрах виртуальной машины, в разделе "Безопасность" (Security) активировать флаг. . .
Архитектура биовида Стива в Майнкрафте: Зачем бонобо кубический каннибализм anaschu 30.08.2026 Кубический Вагинокапитализм в Minecraft: Математический инвариант ОДУ и рок Стивов-бонобо Главная задача разработанной «Модели Всего» — наглядно продемонстрировать наличие системной «судьбы». . .	Оттачиваю умение писать js программы. russiannick 30.08.2026 Проектом выходного дня стало написание Книги шифров Виженера. Итогом стала версия 200, синий туман. Синий туман назван так, потому что замораживает текст под собой. Нажатие синих кнопок управляют. . .	мат медиц модель 30. презентация проекта anaschu 27.08.2026 хоп хоп хоп хидахоп, а я кладую))	Как у меня протекала болезнь zorxor 27.08.2026 Здравствуйте, друзья! Эта запись блога предназначена именно для вас - для моих дорогих друзей, которые знали меня лично. Чтобы ответить на вопрос - а что же со мной произошло на самом деле? Я учился. . .

@trarater 1 / 1 / 0 Регистрация: 28.09.2022 Сообщений: 29
	28.09.2022, 15:58 [ТС]
	АЕ, shanemac51, понял. принял. осознал. Спасибо огромное за помощь. Форум отличный. В скором времени перееду сюда основательно. 1

@АЕ ᴁ © 4184 / 2468 / 514 Регистрация: 13.12.2016 Сообщений: 8,394 Записей в блоге: 5
	29.09.2022, 09:10
	trarater,метод проб и ошибок никто не отменял. Экспериментируйте. Очевидно, что A_Size это переменная, которая в вашем коде не определена . 0

@Zeag 860 / 510 / 187 Регистрация: 09.03.2009 Сообщений: 1,731
	29.09.2022, 13:45
	1. Добавьте в начале Application.ScreenUpdating = False и в конце Application.ScreenUpdating = True, это ускорит дело 2. Строка MsgBox "Ошибка" & Err никогда не будет выполняться, т.к. нет On Error GoTo es 1

@АЕ ᴁ © 4184 / 2468 / 514 Регистрация: 13.12.2016 Сообщений: 8,394 Записей в блоге: 5
	30.09.2022, 15:23
	И еще, если беретесь свой опус выкладывать, то обрамляйте его не в CODE а в VB Обработчики ошибок ставят на сложный код с возможными неожиданнастями. Вам, в вашем 2+2 он ни к чему. 1

@trarater 1 / 1 / 0 Регистрация: 28.09.2022 Сообщений: 29
	30.09.2022, 16:41 [ТС]
	АЕ, В общем все работает. Моя первая программа. очень доволен. К тому же оптимизаторы ускоряют процесс на 75%, специально замерял секундомером ))) 0