2. Главная
  3. MS Office
  4. Excel
  5. Базовый набор функций VBA

Базовый набор функций VBA

Программируя в VBA, часто можно столкнуться с ситуацией, когда приходится решать одни и те же задачи снова и снова из-за того, что в языке нет удобных встроенных средств для работы с объектами. В данной статье собраны аналоги функций Python для работы с объектами в VBA.

Содержание

Table of Contents:

Функция для добавления элемента к массиву VBA

Аналог метода append из Python для массива MS Office VBA. 

# Метод в Python
MyList.append(ItemToAppend)

Вариант аналога в VBA:

'Функция append:
Function append(InArray, Value)             'InArray - массив, Value - элемент, который необходимо добавить к нему
ReDim Preserve InArray(UBound(InArray) + 1) 'увеличиваем размер массива с сохранением имеющихся в нём значений
InArray(UBound(InArray)) = Value            'дописываем Value в массив
append = InArray                            'функция возвращает новый массив
End Function



'Пример использования функции:

Sub MySub ()

'Для работы необходимо вставить эту функцию в свой проект VBA 
'и использовать в качестве первого аргумента динамический массив:

ReDim InArray(0) as Variant  'Наш динамический массив
ReDim Value as String        'Произвольная переменная, например, типа String

Value = "MyStringValue"      'записываем произвольный текст в переменную
InArray = append(InArray, Value)    'вызов функции append
Debug.Print (InArray(1))            'Убедиться в том, что первый элемент добавлен можно, например, так - в окне Immediate
MsgBox(InArray(1))                  'либо так – в сообщении

End Sub

 UPD 2022. Вариант проще:

ReDim Preserve MyArray (UBound(MyArray) + 1)
MyArray (UBound(MyArray))= "VALUE"

Последняя строка/столбец VBA

Метод 1 - UsedRange

LastRow1 = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
LastCol1 = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

UsedRange - область на листе, в которой что-то есть. Использование отдельно UsedRange.Rows.Count для поиска последнего ряда/столбца ненадёжно, т.к. в случае, если на листе есть пустые столбцы/строки, будет получен неверный результат.

Метод 2 - End(xlUp).Row

Метод следует использовать, если нужно найти последний заполненный столбец/строку по конкретной строке/столбцу.

LastRow2 = Cells(Rows.Count, 7).End(xlUp).Row ' Найти последнюю строку в столбце 7
LastCol2 = Cells(13, Columns.Count).End(xlToLeft).Column 'Найти послдений столбец в строке 13

Метод 3 - пользовательская функция. Получить последнюю ячейку как объект Range

В данном случае используется поиск -1 значения относительно первой ячейки по столбцам и по строкам. По умолчанию работает на активном листе. Можно указать на вход лист (как объект Worksheet).

Function Last_Cell(Optional ws As Worksheet) As Range
   Dim LastCol As Long, LastRow As Long
   LastCol = 1
   LastRow = 1
   On Error Resume Next
   If ws Is Nothing Then Set ws = ActiveSheet

      LastCol = ws.UsedRange.Cells.Find(what:="*", after:=ws.UsedRange.Cells(1), _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, searchformat:=False).Column
      LastRow = ws.UsedRange.Cells.Find(what:="*", after:=ws.UsedRange.Cells(1), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious, searchformat:=False).Row

   Set Last_Cell = ws.Cells(LastRow, LastCol)

End Function

Все функции:

Скачать пример xlsm

Sub findlastrow()

'1 - using UsedRange
LastRow1 = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
LastCol1 = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
'where UsedRange begins  + number of rows/columns in the usedrange - 1

Debug.Print ("Method 1")
Debug.Print ("Row" + Str(LastRow1) + " Column " + Str(LastCol1))
Debug.Print ("Value " + Str(ThisWorkbook.ActiveSheet.Cells(LastRow1, LastCol1).Value))

Debug.Print ("  Usedrange params")
Debug.Print ("  ActiveSheet.UsedRange.Rows.Count " + Str(ActiveSheet.UsedRange.Rows.Count))
Debug.Print ("  ActiveSheet.UsedRange.Row " + Str(ActiveSheet.UsedRange.Row))
Debug.Print ("LastCol " + Str(LastCol1))

Debug.Print (" ")

'2 if you know which column/row to choose to count as last:
LastRow2 = Cells(Rows.Count, 7).End(xlUp).Row '!!!! 7 - column to look for last row
LastCol2 = Cells(13, Columns.Count).End(xlToLeft).Column '!!! row to look for last column

Debug.Print ("Method 2")
Debug.Print ("LastRow " + Str(LastRow2) + " LastCol " + Str(LastCol2))
Debug.Print (" ")

'3 - ultimate solution - custom function -  using Search (previous from the first cell -> last cell
' output of the funciton Last_Cell - object (range) - lastcell


LastRow = Last_Cell.Row
LastCol = Last_Cell.Column
LastVal = Last_Cell.Value


Debug.Print ("Method 3")
Debug.Print ("Last_Cell.Row " + Str(Last_Cell.Row))
Debug.Print ("Last_Cell.Column " + Str(Last_Cell.Column))
Debug.Print ("Last_Cell.Value " + Str(Last_Cell.Value))


End Sub

Function Last_Cell(Optional ws As Worksheet) As Range
   Dim LastCol As Long, LastRow As Long
   LastCol = 1
   LastRow = 1
   On Error Resume Next
   If ws Is Nothing Then Set ws = ActiveSheet

      LastCol = ws.UsedRange.Cells.Find(what:="*", after:=ws.UsedRange.Cells(1), _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, searchformat:=False).Column
      LastRow = ws.UsedRange.Cells.Find(what:="*", after:=ws.UsedRange.Cells(1), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious, searchformat:=False).Row

   Set Last_Cell = ws.Cells(LastRow, LastCol)

End Function

'OUTPUT:

'Method 1
'Row 13 Column  7
'Value  1234
'  Usedrange params
'  ActiveSheet.UsedRange.Rows.Count  1
'  ActiveSheet.UsedRange.Row  13
'LastCol  7
 
'Method 2
'LastRow  13 LastCol  7
 
'Method 3
'Last_Cell.Row  13
'Last_Cell.Column  7
'Last_Cell.Value  1234