excel – VBA Как получить значения из выбранных ячеек – Question-It.com

Мне нужно поместить данные из выбранных ячеек в массив. Как это сделать? Я не могу найти ни одного примера. Я знаю, как получить значение из одной ячейки с помощью ActiveCell.Value, но как это работает с множественным выбором. Думаю, я должен выбрать диапазон, помещенный в переменную как объект, и о….

Обращение к конкретной ячейке

Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Workbooks(“Книга1.xls”).Sheets(“Лист1”).Range(“A3”) ‘ Обратиться к ячейке A3Workbooks(“Книга1.xls”).Sheets(“Лист1”).Cells(3, 1) ‘ Обратиться к ячейке в 3-й строке и 1-й колонке (A3)

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Range(“A1”)Cells(1, 1)

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале “.” (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

With Workbooks(“Книга1”).Sheets(“Лист2”) ‘ Вывести значение ячейки A1, которая находится на Листе2 MsgBox .Range(“A1”) ‘ Вывести значение ячейки B1, которая находится на Листе2 MsgBox .Range(“B1”)End With

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Application.ActiveCell ‘ полная записьActiveCell ‘ краткая запись

Объектная модель Excel

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

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets(“result”).Activate
Dim r As Range
Set r = Range(“A1”)
r.Value = “123”
Set r = Range(“A3,A5”)
r.Font.Color = vbRed
r.Value = “456”
Set r = Range(“A6:A7”)
r.Value = “=A1+A3”

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

  1. Считали группы из очередной строки.
  2. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
    1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
  3. После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными.

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As IntegerAs String
    GetCol = Chr(Asc(“A”) + Col)
End Function

Function GetCellS(Sheet As String, Col As Integer, Row As IntegerAs Range
    Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
End Function

Function GetCell(Col As Integer, Row As IntegerAs Range
    Set GetCell = Range(GetCol(Col) + CStr(Row))
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
    Dim I As Integer ‘ строка в data
    CurRow = 1
    Dim Groups(1 To GroupsCount) As String
    Dim PrGroups(1 To GroupsCount) As String

    Sheets(“data”).Activate
    I = 2
    Do While True
        If GetCell(0, I).Value = “” Then Exit Do
        ‘ …
        I = I + 1
    Loop
End Sub

Теперь надо заполнить массив Groups:

На месте многоточия

Dim I2 As Integer
For I2 = 1 To GroupsCount
    Groups(I2) = GetCell(I2, I)
Next I2
‘ …
For I2 = 1 To GroupsCount ‘ VB не умеет копировать массивы
    PrGroups(I2) = Groups(I2)
Next I2
I =  I + 1

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
    If Groups(I2) <> PrGroups(I2) Then
        Dim I3 As Integer
        For I3 = I2 To GroupsCount
            AddHeader I3, Groups(I3)
        Next I3
        Exit For
    End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Sub AddHeader(Ty As Integer, Name As String)
    GetCellS(“result”, 1, CurRow).Value = Name
    CurRow = CurRow + 1
End Sub

Теперь надо перенести всякую информацию в result

For I2 = 0 To DataCount – 1
    GetCellS(“result”, I2, CurRow).Value = GetCell(I2, I)
Next I2

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets(“Result”).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

28a7a89d52499b6a865993ac81a883dc.png

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Sub AddHeader(Ty As Integer, Name As String)
    Sheets(“result”).Range(“A” + CStr(CurRow) + “:C” + CStr(CurRow)).Merge
    ‘ Чтобы не заводить переменную и не писать каждый раз длинный вызов
    ‘ можно воспользоваться блоком With
    With GetCellS(“result”, 0, CurRow)
        .Value = Name
        .Font.Italic = True
        .Font.Name = “Cambria”
        Select Case Ty
        Case 1 ‘ Тип
            .Font.Bold = True
            .Font.Size = 16
        Case 2 ‘ Производитель
            .Font.Size = 12
        End Select
        .HorizontalAlignment = xlCenter
    End With
    CurRow = CurRow + 1
End Sub

Уже лучше:

8805063bd924b908fe6d475ecd2d40cf.png

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

42d0a65c401d03271f56aaf7eabd3266.png

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Sub AddHeader(Ty As Integer, Name As String)
    With Sheets(“result”).Range(“A” + CStr(CurRow) + “:C” + CStr(CurRow))
        .Merge
        .Value = Name
        .Font.Italic = True
        .Font.Name = “Cambria”
        .HorizontalAlignment = xlCenter

        Select Case Ty
        Case 1 ‘ Тип
            .Font.Bold = True
            .Font.Size = 16
            .Borders(xlTop).Weight = xlThick
        Case 2 ‘ Производитель
            .Font.Size = 12
            .Borders(xlTop).Weight = xlMedium
        End Select
        .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
    End With
    CurRow = CurRow + 1
End Sub

53bfa46bf1e5f6aa251a86e2508b3107.png

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ‘ строка в  data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
    CurRow = CurRow + 1
    Dim I3 As Integer

96ddc98d13ee026ae82cb335c92d8ef4.png

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки “ID, Название, Цена” в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Выбор ячейки на активном листе

Чтобы выбрать ячейку D5 на активном листе, можно использовать любой из следующих примеров:

ActiveSheet.Cells(5, 4).Select-or-ActiveSheet.Range(“D5”).Select

Выбор ячейки на другом листе в той же книге

Чтобы выбрать ячейку E6 на другом листе той же книги, можно использовать любой из следующих примеров:

Application.Goto ActiveWorkbook.Sheets(“Sheet2”).Cells(6, 5) -or-Application.Goto (ActiveWorkbook.Sheets(“Sheet2”).Range(“E6”))

Вы также можете активировать лист, а затем использовать метод 1, чтобы выбрать ячейку:

Sheets(“Sheet2”).ActivateActiveSheet.Cells(6, 5).Select

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

  • Value2 – базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 – самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value – значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text – визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде “число месяц прописью год”, то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде “#####” то Text вернет вам не само значение, а эти самые “решетки”.

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат “14 марта 2001 г.”. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Cells(1, 1) ‘ выведет 01.03.2018MsgBox Cells(1, 1).Value ‘ выведет 01.03.2018MsgBox Cells(1, 1).Value2 ‘ выведет 43160MsgBox Cells(1, 1).Text ‘ выведет 01 марта 2018 г.Dim d As Dated = Cells(1, 1).Value2 ‘ числовое представление даты преобразуется в тип DateMsgBox d ‘ выведет 01.03.2018

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат “Денежный” с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Range(“C1”) ‘ выведет 123,4568MsgBox Range(“C1”).Value ‘ выведет 123,4568MsgBox Range(“C1”).Value2 ‘ выведет 123,456789MsgBox Range(“C1”).Text ‘ выведет 123,457р.Dim c As Currencyc = Range(“C1”).Value2 ‘ значение преобразуется в тип CurrencyMsgBox c ‘ выведет 123,4568Dim d As Doubled = Range(“C1”).Value2 ‘ значение преобразуется в тип DoubleMsgBox d ‘ выведет 123,456789

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка “Type mismatch”. Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Dim s As StringDim i As Integers = Range(“B1”).Value2 ‘ успехi = Range(“B1”).Value2 ‘ ошибка

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Cells(2,4).Value=15

Cells(2,4)=15

Range(“A1”)=“Этот текст записываем в ячейку”

ActiveCell=28+10*36

Вместе с числами и текстом можно использовать переменные. Примеры здесь и ниже приведены для активного листа. Для неактивных листов дополнительно необходимо указывать имя листа, как в разделе «Обращение к ячейке».

Выбор последней ячейки столбца с непрерывными данными

Чтобы выбрать последнюю ячейку в непрерывном столбце, используйте следующий пример:

ActiveSheet.Range(“a1”).End(xlDown).Select

Если этот код используется с образцом таблицы, будет выбрана ячейка A4.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...