Содержание
Microsoft Excel уже очень эффективный инструмент анализа данных, но с возможностью автоматизации повторяющихся задач с помощью макросов
написав простой код на (VBA), он становится намного мощнее. Однако при неправильном использовании VBA может вызвать много проблем.
Даже если вы не программист, VBA предлагает простые функции, которые позволяют вам добавить некоторые действительно впечатляющие функциональные возможности в ваши электронные таблицы, так что пока не уходите!
Являетесь ли вы гуру VBA, который создает информационные панели в Excel
или новичок, который знает только, как писать простые сценарии, которые выполняют базовые вычисления ячеек, вы можете следовать простым методам программирования, которые помогут вам повысить шансы написания чистого и безошибочного кода.
Начало работы с VBA
Если вы ранее не программировали в VBA в Excel, включить инструменты разработчика на самом деле довольно просто. Просто иди в файл > Опции а потом Настроить ленту. Просто переместите разработчик Командная группа с левой панели справа.
Убедитесь, что флажок включен, и теперь вкладка Разработчик появится в вашем меню Excel.
Самый простой способ попасть в окно редактора кода на данный момент — просто нажать на Посмотреть код кнопка под управления в меню разработчика.
1. Ужасные имена переменных
Теперь, когда вы находитесь в окне кода, пришло время начать писать код VBA
, Первым важным шагом в большинстве программ, будь то VBA или любой другой язык, является определение ваших переменных.
За пару десятилетий написания кода я натолкнулся на множество идей, когда речь шла о соглашениях по именованию переменных, и усвоил некоторые вещи на своем пути. Вот быстрые советы по созданию имен переменных:
- Сделайте их как можно короче.
- Сделайте их как можно более описательными.
- Предварите их типом переменной (логическое, целое и т. Д.).
- Не забудьте использовать правильную область (см. Ниже).
Вот пример скриншота из программы, которую я часто использую для вызовов WMIC Windows из Excel для сбора информации о ПК
,
Если вы хотите использовать переменные внутри любой функции внутри модуля или объекта (я объясню это ниже), вам нужно объявить ее как «публичную» переменную, предварительно указав объявление с помощью общественного. В противном случае переменные объявляются с помощью предваряющего их слова тусклый.
Как вы можете видеть, если переменная является целым числом, ей предшествует ИНТ. Если это строка, то ул. Это помогает позже, когда вы программируете, потому что вы всегда будете знать, какой тип данных хранится в переменной, просто взглянув на имя. Вы также заметите, что если есть что-то вроде строки, содержащей имя компьютера, то переменная называется strComputerName.
Избегайте создания очень запутанных или запутанных имен переменных, которые понятны только вам. Сделайте так, чтобы другому программисту было легче прийти за вами и понять, что все это значит!
Еще одна ошибка, которую допускают люди, — оставить имена листов по умолчанию «Sheet1», «Sheet2» и т. Д. Это еще больше запутывает программу. Вместо этого назовите листы так, чтобы они имели смысл.
Таким образом, когда вы ссылаетесь на имя листа в коде Excel VBA
Вы ссылаетесь на имя, которое имеет смысл. В приведенном выше примере у меня есть лист, где я получаю информацию о сети, поэтому я называю лист «Сеть». Теперь в коде, когда бы я ни захотел сослаться на лист сети, я могу сделать это быстро, не просматривая, какой это номер листа.
2. Разорвать вместо цикла
Одна из наиболее распространенных проблем, возникающих у новичка
когда они начинают писать код правильно работает с циклами. А так как многие люди, использующие Excel VBA, являются новичками в коде, плохое зацикливание является эпидемией.
Циклы очень распространены в Excel, потому что часто вы обрабатываете значения данных по всей строке или столбцу, поэтому вам нужно выполнить цикл для обработки всех их. Новые программисты часто хотят просто вырваться из цикла (либо цикл For, либо взгляд while), когда определенное условие выполняется.
Вы можете игнорировать сложность приведенного выше кода, просто отметьте, что внутри внутреннего оператора IF есть возможность выйти из цикла For, если условие истинно. Вот более простой пример:
For x = 1 To 20
If x = 6 Then Exit For
y = x + intRoomTemp
Next i
Новые программисты используют этот подход, потому что это легко. Когда возникает условие, которого вы ждете, чтобы выйти из цикла, возникает соблазн просто выпрыгнуть из него, но не делайте этого.
Чаще всего код, который следует после этого «перерыва», важен для обработки, даже в последний раз в цикле перед выходом. Гораздо более чистый и профессиональный способ обработки условий, в которых вы хотите оставить цикл на полпути, состоит в том, чтобы просто включить это условие выхода в нечто вроде оператора While.
While (x>=1 AND x<=20 AND x6)
For x = 1 To 20
y = x + intRoomTemp
Next i
Wend
Это учитывает логический поток вашего кода, с последним прогоном, когда x равен 5, и затем плавным выходом, когда цикл For считает до 6. Нет необходимости включать неловкие команды EXIT или BREAK в середине цикла.
3. Не использовать массивы
Еще одна интересная ошибка, что новые программисты VBA
make пытается обработать все внутри многочисленных вложенных циклов, которые фильтруют строки и столбцы в процессе вычисления.
Хотя это может работать, это также может привести к серьезным проблемам с производительностью, если вам постоянно приходится выполнять одни и те же вычисления для одних и тех же чисел в одном и том же столбце. Циклический просмотр этого столбца и извлечение значений каждый раз не только утомительны для программирования, но и убивают ваш процессор. Более эффективным способом обработки длинных списков чисел является использование массива.
Если вы никогда раньше не использовали массив, не бойтесь. Представьте массив в виде лотка для кубиков льда с определенным количеством «кубиков», в которые вы можете поместить информацию. Кубы пронумерованы от 1 до 12, и именно так вы «помещаете» в них данные.
Вы можете легко определить массив, просто набрав Dim arrMyArray (12) как целое число.
Это создает «лоток» с 12 слотами, которые вы можете заполнить.
Вот как может выглядеть код цикла строки без массива:
Sub Test1()
Dim x As Integer
intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
Range("A2").Select
For x = 1 To intNumRows
If Range("A" & str(x)).value < 100 then
intTemp = (Range("A" & str(x)).value) * 32 - 100
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub
В этом примере код обрабатывает каждую ячейку в диапазоне и выполняет расчет температуры.
Позже в программе, если вы когда-нибудь захотите выполнить какой-то другой расчет для этих же значений, вам придется продублировать этот код, обработать все эти ячейки и выполнить новый расчет.
Теперь, если вы вместо этого используете массив, вы можете сохранить 12 значений в строке в вашем удобном массиве хранения. Затем, когда вы захотите выполнить вычисления для этих чисел, они уже находятся в памяти и готовы к работе.
Sub Test1()
Dim x As Integer
intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
Range("A2").Select
For x = 1 To intNumRows
arrMyArray(x-1) = Range("A" & str(x)).value)
ActiveCell.Offset(1, 0).Select
Next
End Sub
«X-1» для указания на элемент массива необходим только потому, что цикл For начинается с 1. Элементы массива должны начинаться с 0.
Но как только вы загрузите весь массив со значениями из строки, позже в программе вы можете просто собрать любые вычисления, используя этот массив.
Sub TempCalc()
For x = 0 To UBound(arrMyArray)
arrMyTemps(y) = arrMyArray(x) * 32 - 100
Next
End Sub
Этот пример просматривает весь массив строк (UBound дает вам количество значений данных в массиве), выполняет расчет температуры, а затем помещает его в другой массив с именем arrMyTemps.
Вы можете увидеть, насколько проще второй код расчета. И с этого момента, каждый раз, когда вы захотите выполнить больше вычислений для того же набора чисел, ваш массив уже предварительно загружен и готов к работе.
4. Использование слишком много ссылок
Независимо от того, программируете ли вы в полнофункциональном Visual Basic или VBA, вам нужно будет включить «ссылки» для доступа к определенным функциям, таким как доступ к базе данных Access или запись выходных данных в текстовый файл.
Ссылки - это что-то вроде «библиотек», наполненных функциями, которые вы можете использовать, если вы включите этот файл. Вы можете найти ссылки в представлении разработчика, нажав на инструменты в меню, а затем нажмите на Рекомендации.
В этом окне вы найдете все выбранные ссылки для вашего текущего проекта VBA.
По моему опыту, выбор здесь может меняться от одной установки Excel к другой и с одного компьютера на другой. Многое зависит от того, что другие люди сделали с Excel на этом ПК, были ли добавлены или включены определенные надстройки или функции, или некоторые другие программисты могли использовать определенные ссылки в прошлых проектах.
Причина, по которой стоит проверить этот список, состоит в том, что ненужные ссылки приводят к потере системных ресурсов. Если вы не используете какие-либо манипуляции с XML-файлами, тогда зачем выбирать Microsoft XML? если вы не общаетесь с базой данных, удалите Microsoft DAO. Если вы не записываете вывод в текстовый файл, удалите Microsoft Scripting Runtime.
Если вы не уверены, что делают эти выбранные ссылки, нажмите F2 и вы увидите обозреватель объектов. В верхней части этого окна вы можете выбрать справочную библиотеку для просмотра.
После выбора вы увидите все объекты и доступные функции, на которые вы можете нажать, чтобы узнать больше.
Например, когда я нажимаю на библиотеку DAO, быстро становится ясно, что это все о подключении к базам данных и связи с ними.
Сокращение количества ссылок, которые вы используете в своем программном проекте, - это просто здравый смысл и поможет повысить эффективность работы всего приложения.
Программирование в Excel VBA
Вся идея собственно написания кода в Excel
пугает многих людей, но этот страх действительно не нужен. Visual Basic для приложений - это очень простой язык для изучения, и если вы будете следовать основным распространенным методам, упомянутым выше, вы убедитесь, что ваш код чистый, эффективный и простой для понимания.
Вы код в VBA? Какие уроки вы извлекли за те годы, которыми вы можете поделиться с другими читателями, впервые изучающими VBA? Поделитесь своими советами в разделе комментариев ниже!
Изображение предоставлено: Компьютер от www.BillionPhotos.com через Shutterstock, красочные лотки для кубиков льда от hahauk через Shutterstock.com
