excel-vba
Диапазоны и ячейки
Поиск…
Синтаксис
- Set - оператор, используемый для установки ссылки на объект, например, на диапазон
- Для каждого - оператор, используемый для прокрутки каждого элемента в коллекции
замечания
Обратите внимание, что имена переменных r
, cell
и другие могут быть названы, как вам нравится, но должны быть названы соответствующим образом, чтобы код был более понятным для вас и других.
Создание диапазона
Диапазон нельзя создать или заполнить так же, как строка:
Sub RangeTest()
Dim s As String
Dim r As Range 'Specific Type of Object, with members like Address, WrapText, AutoFill, etc.
' This is how we fill a String:
s = "Hello World!"
' But we cannot do this for a Range:
r = Range("A1") '//Run. Err.: 91 Object variable or With block variable not set//
' We have to use the Object approach, using keyword Set:
Set r = Range("A1")
End Sub
Считается лучшей практикой, чтобы квалифицировать ваши ссылки , поэтому в дальнейшем мы будем использовать один и тот же подход.
Подробнее о создании объектных переменных (например, Range) в MSDN . Подробнее о Set Statement на MSDN .
Существуют разные способы создания одного и того же диапазона:
Sub SetRangeVariable()
Dim ws As Worksheet
Dim r As Range
Set ws = ThisWorkbook.Worksheets(1) ' The first Worksheet in Workbook with this code in it
' These are all equivalent:
Set r = ws.Range("A2")
Set r = ws.Range("A" & 2)
Set r = ws.Cells(2, 1) ' The cell in row number 2, column number 1
Set r = ws.[A2] 'Shorthand notation of Range.
Set r = Range("NamedRangeInA2") 'If the cell A2 is named NamedRangeInA2. Note, that this is Sheet independent.
Set r = ws.Range("A1").Offset(1, 0) ' The cell that is 1 row and 0 columns away from A1
Set r = ws.Range("A1").Cells(2,1) ' Similar to Offset. You can "go outside" the original Range.
Set r = ws.Range("A1:A5").Cells(2) 'Second cell in bigger Range.
Set r = ws.Range("A1:A5").Item(2) 'Second cell in bigger Range.
Set r = ws.Range("A1:A5")(2) 'Second cell in bigger Range.
End Sub
Обратите внимание на пример, что ячейки (2, 1) эквивалентны диапазону («A2»). Это происходит потому, что Cells возвращает объект Range.
Некоторые источники: Chip Pearson-Cells Within Ranges ; Объект диапазона MSDN ; John Walkenback - ссылка на диапазоны в коде VBA .
Также обратите внимание, что в любом случае, когда число используется в объявлении диапазона, а сам номер находится вне кавычек, например Range («A» & 2), вы можете поменять это число на переменную, содержащую целое число / долго. Например:
Sub RangeIteration()
Dim wb As Workbook, ws As Worksheet
Dim r As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
For i = 1 To 10
Set r = ws.Range("A" & i)
' When i = 1, the result will be Range("A1")
' When i = 2, the result will be Range("A2")
' etc.
' Proof:
Debug.Print r.Address
Next i
End Sub
Если вы используете двойные циклы, ячейки лучше:
Sub RangeIteration2()
Dim wb As Workbook, ws As Worksheet
Dim r As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
For i = 1 To 10
For j = 1 To 10
Set r = ws.Cells(i, j)
' When i = 1 and j = 1, the result will be Range("A1")
' When i = 2 and j = 1, the result will be Range("A2")
' When i = 1 and j = 2, the result will be Range("B1")
' etc.
' Proof:
Debug.Print r.Address
Next j
Next i
End Sub
Способы обращения к одной ячейке
Самый простой способ ссылаться на одну ячейку на текущем листе Excel - это просто вставить форму А1 в ссылку в квадратных скобках:
[a3] = "Hello!"
Обратите внимание, что квадратные скобки - это просто удобный синтаксический сахар для метода Evaluate
объекта Application
, так что технически это идентично следующему коду:
Application.Evaluate("a3") = "Hello!"
Вы также можете вызвать метод Cells
который принимает строку и столбец и возвращает ссылку на ячейку.
Cells(3, 1).Formula = "=A1+A2"
Помните, что всякий раз, когда вы передаете строку и столбец в Excel из VBA, строка всегда первая, за ней следует столбец, что запутывает, потому что это противоположно общей нотации A1
где сначала отображается столбец.
В обоих этих примерах мы не указали рабочий лист, поэтому Excel будет использовать активный лист (лист, который находится впереди в пользовательском интерфейсе). Вы можете указать активный лист явно:
ActiveSheet.Cells(3, 1).Formula = "=SUM(A1:A2)"
Или вы можете указать имя определенного листа:
Sheets("Sheet2").Cells(3, 1).Formula = "=SUM(A1:A2)"
Существует множество методов, которые можно использовать для перехода от одного диапазона к другому. Например, метод Rows
может использоваться для доступа к отдельным строкам любого диапазона, и метод Cells
может использоваться для доступа к отдельным ячейкам строки или столбца, поэтому следующий код относится к ячейке C1:
ActiveSheet.Rows(1).Cells(3).Formula = "hi!"
Сохранение ссылки на ячейку переменной
Чтобы сохранить ссылку на ячейку в переменной, вы должны использовать синтаксис Set
, например:
Dim R as Range
Set R = ActiveSheet.Cells(3, 1)
потом...
R.Font.Color = RGB(255, 0, 0)
Почему требуется ключевое слово Set
? Set
указывает Visual Basic, что значение в правой части =
означает объект.
Смещение недвижимости
- Смещение (строки, столбцы) - оператор, используемый для статической ссылки на другую точку из текущей ячейки. Часто используется в циклах. Следует понимать, что положительные числа в разделе строк перемещаются вправо, поскольку негативы перемещаются влево. С положительными позициями столбцов вниз и негативы двигаются вверх.
т.е.
Private Sub this()
ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Select
ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Value = "New Value"
ActiveCell.Offset(-1, -1).Value = ActiveCell.Value
ActiveCell.Value = vbNullString
End Sub
Этот код выбирает B2, помещает туда новую строку, затем перемещает эту строку обратно в A1 после очистки B2.
Как перемещать диапазоны (по горизонтали по вертикали и наоборот)
Sub TransposeRangeValues()
Dim TmpArray() As Variant, FromRange as Range, ToRange as Range
set FromRange = Sheets("Sheet1").Range("a1:a12") 'Worksheets(1).Range("a1:p1")
set ToRange = ThisWorkbook.Sheets("Sheet1").Range("a1") 'ThisWorkbook.Sheets("Sheet1").Range("a1")
TmpArray = Application.Transpose(FromRange.Value)
FromRange.Clear
ToRange.Resize(FromRange.Columns.Count,FromRange.Rows.Count).Value2 = TmpArray
End Sub
Примечание. Copy / PasteSpecial также имеет параметр «Вставить транспонирование», который также обновляет формулы транспонированных ячеек.