Полезные функции VBA в Excel

Здесь приведены полезные функции, предназначенные для ускорения процесса разработки и улучшения функционирования программ на VBA в среде MS Excel.

Оптимизация Excel при использовании VBA

MS Excel обладает огромным количеством возможностей. За подобное разнообразие нужно платить. При элементарном использовании MS Excel пользователи не замечают нагрузку на персональный компьютер. Однако, при автоматизированной обработке больших объемов данных или исполнении сложных функций на VBA, нагрузка становится ощутимой, вплоть до «зависания» компьютера. Для нормализации нагрузки на MS Excel при использовании VBA требуется отключить большинство предустановленных возможностей табличного процессора с помощью функции MacroOptimization().

Sub MacroOptimization()
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
    
End Sub

После исполнения вышеуказанного кода и программы на VBA требуется восстановить заблокированные возможности табличного процессора MS Excel. Для этого нужно выполнить функцию ReturnMacroOptimization(), описанную ниже.

Sub ReturnMacroOptimization()
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
    
End Sub

Взаимодействие с файлами

Для начала работы с файлами их нужно уметь открывать. Файл можно открыть с помощью функции из стандартной библиотеки VBA. Однако в случае, если программа содержит отдельные переменные с названием файлов, путей к ним и их разрешений, то удобнее будет воспользоваться функцией OpenFile().

'----- Open file -----
Sub OpenFile(fileadress As String, bookname As String, ext As String)

    Dim s As String
    s = fileadress & "\" & bookname & "." & ext
    Workbooks.Open (s)
'    Workbooks.Open Filename = s

End Sub

В некоторых случаях требуется передать фокус на определенную книгу, чтобы после взаимодействовать с ней с помощью другой функции. Передать фокус на книгу можно с помощью функции BookSelection().

'----- Selection (book) -----
Sub BookSelection(wb As Workbook, bookname As String, ext As String)
    
    If bookname = "ActiveWorkbook" Then
        Set wb = ActiveWorkbook
    ElseIf bookname = "ThisWorkbook" Then
        Set wb = ThisWorkbook
    Else
        Set wb = Workbooks(bookname & "." & ext)
    End If
    
End Sub

Также частой операцией является передача фокуса на определенный лист MS Excel. Это можно сделать с помощью функции SheetSelection().

'----- Selection (sheet) -----
Sub SheetSelection(ws As Worksheet, bookname As String, ext As String, sheetname As String)
    
    Dim wb As Workbook
    Call BookSelection(wb, bookname, ext)
    If bookname = "ActiveSheet" Then
        Set ws = ActiveSheet
    Else
'        Set ws = wb.Worksheets(sheetname)
        Set ws = wb.Sheets(sheetname)
    End If
    
End Sub

Часто требуется обработать данные, находящиеся в определенном диапазоне определенного листа в файле MS Excel. Сначала данные из диапазона требуется скопировать в массив с помощью функции CopyingRangeToArray().

'----- Copying data from sheet to array -----
Sub CopyingRangeToArray(a() As Variant, _
bookname As String, ext As String, _
sheetname As String, rangename As String)
    
    Dim ws As Worksheet
    Call SheetSelection(ws, bookname, ext, sheetname)
    ws.Range(rangename).CurrentRegion.Select
    a = Selection
    
End Sub

Иногда требуется произвести манипуляции с данными во всем файле. Для этого требуется скопировать данные из файла в массив с помощью функции CopyingFileToArray().

'----- Copying data from file to array -----
Sub CopyingFileToArray(a() As Variant, fileadress As String, _
bookname As String, ext As String, _
sheetname As String, rangename As String)
    
    Call OpenFile(fileadress, bookname, ext)
    Call CopyingRangeToArray(a, bookname, ext, sheetname, rangename)
    Workbooks(bookname & "." & ext).Close SaveChanges = False
    
End Sub

После обработки данные обычно требуется вернуть в файл. Это можно сделать с помощью функции CopyingArrayToRange().

'----- Copying data from array to sheet -----
Sub CopyingArrayToRange(a() As Variant, _
bookname As String, ext As String, _
sheetname As String, rangename As String)
    
    Dim ws As Worksheet
    Call SheetSelection(ws, bookname, ext, sheetname)
    ws.Range(rangename).Value = a
    
End Sub

После обработки данных в программе VBA данные можно выгрузить в файл с помощью функции CopyingArrayToFile().

'----- Copying data from array to file -----
Sub CopyingArrayToFile(a() As Variant, fileadress As String, _
bookname As String, ext As String, _
sheetname As String, rangename As String)
    
    Call OpenFile(fileadress)
    Call CopyingArrayToRange(a, bookname, ext, sheetname, rangename)
    Workbooks(bookname & "." & ext).Close SaveChanges = True
    
End Sub

excel VBA практика