vba best practice

Предупреждали меня друзья: будешь прогать на Visual Basic - хапнешь горя. Они были правы, и вот почему.
Сам по себе язык как язык, есть где развернуться. Но в основном его используют для автоматизации действий в Excel. И в основном для верстки. Но на этом моменте уже возникают проблемы с понимаем документации: сам черт ногу сломит какие методы есть у объекта Range и как правильно их использовать.

Верстка и манипуляция с данными

Одна из распространенных задач для автоматизации - слияние данных из книг в одну книгу. Первое что надо усвоить конструкция For Each element in Range (workboob, worksheet, etc) работает быстрее, чем for i=1 to N. Логично, что в masterbook, куда происходит вставка, должны быть определены заголовки, которые надо искать в sourcebook.  Далее, когда уже получится вставлять данные часто возникает проблема, что книга-исходник sourcebook может быть с поломанными ссылками и их следует открывать так

Set sourceBook = Workbooks.Open(fileName:=targetDir & fileName, UpdateLinks:=0)

Так следует сбросить фильтры:

Sub deleteFilter(ws As Worksheet)
    ws.Activate
    If (ws.AutoFilterMode And ws.FilterMode) Or ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

и вообще сразу после открытия книги или запуска первого макроса логично инициализировать настройки

Sub init()
'иницииализируются настройки, характерные для проекта/листа
    Application.EnableEvents = False ' нафиг эти события
    Application.ScreenUpdating = False ' на время исполнения макроса не дергаем отрисовку
    Application.CutCopyMode = False ' очистка буфера
    Set masterBook = ActiveWorkbook
    Set masterSheet = masterBook.ActiveSheet
    ' еще что-нибудь там
End Sub

И самое приятное - даже простой логикой можно вогнать vba в бесконечный цикл. А если все настроено так, что по Private Sub Workbook_open() как раз все это и происходит, то придется глобально отключать исполнение макросов, все откатываться на шаг на зад и заново активировать. Это несколько утомительно я форкнул и заточил под себя проект vbaDeveloper. Даже если что то случилось - у меня есть исходники с предыдущего сохранения, которые можно импортнуть в один клик. Исхода в свою очередь можно хранить в системе контроле версий и проводить из рефакторинг в sublime c добавленным модулем VBScript.
И главное: всегда, ВСЕГДА, используйте Option Explicit при разработке. Это сэкономит кучу нервов при дебаге уже разросшегося проекта.

Модульность

Модульность проекта - залог масштабируемости. Казалось бы все просто называем модуль modSQL для всякой для взаимодействия с базами данных, wsData - листом для данных, а frmLogin  - форму авторизации. Но не туто было. Усть такая подлая вещь как области видимости переменных и процедур. По началу все будет ломаться в неожиданных местах, но гугление "vba scope" остановит боль. И под конец вы уже будете делать большинство процедур приватными, что бы их не было видно по нажатию на F5 из VBE.
Кстати очень полезная фича необязательные параметны в процедуре:

Public Function getConnStr(Optional dbtype As String = "mysql") As String

По умолчанию будет передаваться "mysql".

Особенности на чужих компьютерах

Это отдельная больная тема. Начнем с того, что есть Excel для MacOs и там нет и половины всего того есть на винде. Продолжим тем, что все что вы по отмечали в Tools/References - не будет работать. Их заново придется активировать ручками или подключать зависимости програмно при открытии книги. И вот тут то есть нюансы.
Одно из распространенных решений - позднее связывание с использованием конструкции CreateObject. Например, что бы создать инстанс соединения до базы данных нужно сделать так:

Set conn = CreateObject("ADODB.Connection")

По теме можно прочитать статью
Менее  распространенный ответ в интерентах - импортировать dllки по guid. Фишка в том, что какого то софта может просто не быть. Например:
Допустим вы сделали класный логгер и если пользователь взламывает лист, то должно мылиться пистьмо админу. Отправка письма естественно происходит с мпомощью создания объекта Outlook. Проблема в том, что аутлука может не быть и все свалится с ошибкой. Такие пироги.
Вот тут то как раз можно импортнуть по гуиду все что нужно, а если что то не смогло подтянутся - не использовать. Или вывалить очередной бесполезный MessageBox о том что все сломалось.

Отладка

Приготовленный Sublime с VBScrip'ом это конечно хорошо, но в VBE очень мощный отладчик. Для начала ставьте принты во все возможные места: Debug.Print "все пропало". Так хотя бы будете знать что присходит. А вот когда все ломается придется отлавливать ошибки, притом довольно убогим способом: переходом на метку errorHappen "on error goto errorHappen". И забудьте про конструкцию "on error resume next", хапнете горя. И не раз.
Так вот, есть у вас возможный проблемный кусок кода, к примеру открытие книги в целевой папке. Конечно же книги в папке может просто не быть и этот учсток следует окружить обработчиком: перед ним уже знакомой "on error goto errOpenWB" и после него - "on error goto 0". Последнее нужно для того, что бы обнулить Err.Numberr, который характерен для состояния "что то сломалось". А потом используя Case Select определяем что случилось и пишем в лог. Вот такой обработчик у меня процедуре подключении к базе данных:

Sub connect()
'blablba
errHandler:
Dim answ As Integer
Dim errMsg As String
Dim openBrowser As Boolean
  openBrowser = False
  Select Case Err.Number
    Case Is = -2147467259
      If InStr(Err.Description, "Access denied for user") Then
        errMsg = "bad configuration of database, mail to admin" ' maybe new pass on server
      Else
        errMsg = "install mysql odbc connector for your system"
        openBrowser = True
      End If
      Debug.Print errMsg
      answ = MsgBox(errMsg, vbOKOnly)
      If openBrowser Then
        ActiveWorkbook.FollowHyperlink Address:="http://dev.mysql.com/downloads/connector/odbc/3.51.html", NewWindow:=True
      End If
    Case Else 'An unknown error was encountered, so alert the user
      Debug.Print Err.Number & Err.Description
  End Select
  Debug.Print Err.Number & Err.Description
End Sub

Хорошей практикой разработки является запись происходящего в лог. Если не знаете куда писать, ответ прост - в во временную директорию пользователя, рецепт просто гуглится по запросу "vba environ temp".

Работа с базой данных

Следует заметить, что даже если вы используете одну БД, то способов подключиться к ней целая куча. Если опустить момент о раннем/позднем связывании, то есть еще целая гора драйверов. Если работаете с MySQL, то скорее всего придется поставить odbc драйвер.  А их тоже много версий :) Вот классная статья по теме. Вообще тема с подключением к БД очень обильна на ошибки, и в конце концов каждый пилит свою библиотеку с преферансом и куртизантками.
Если вы собираете данные из sourcebook в masterbook - не спешите, сразу заливать данные в базу. Почистите дубликаты встроенной функой и процесс будет по быстрее. К тому же не мешает проверить все столбцы на разрыв по строкам, в противном случае процеес заливики может оборваться по середине. И будет иметь на это полное право, ибо жестче надо проверять пользовательские данные.
И самое интересное: на надо делать смешение кода: sql оставьте на откуп базе. И вот почему: 1) БД обычно имеют превосходное IDE c отладчиком и написание трехэтажных запросов будет быстрее.
2)sql-процедуры можно (и нужно) хранить, как объекты базы. потом и можно просто дрергать из экселя.
3) эти портянки с code mixing банально сложно читать: вот у вас процедурный язык, и вот у вас пошла фунциональщина. У меня шарики за ролики заезжают, когда такое вижу.
4) Рано или поздно у каждого разработчика или аналитика настает момент вынужденной автоматизации. И эти самые процедуры можно дергать job'ами по ночам и результаты сгружать в отчетные таблицы.
5) Брать уже предрасчитаенные данные из отчетных таблиц гораздо быстрее, чем ждать выполнение запроса из под оболочки Экселя. Это вообще распространенная практика у аналитиков, именно по этому они придумали OLAP-кубы и пытаются раскрутить руководство на Oracle Business Intelligence ;)