Информатика. Книга 7 Быстрое размножение рабочей книги 7 Сохранение рабочей книги с именем, представляющим собой текущую дату 7

• Название: Книга 7 Быстрое размножение рабочей книги 7 Сохранение рабочей книги с именем, представляющим собой текущую дату 7
• Анкор: Информатика
• Дата: 16.05.2022
• Размер: 1.27 Mb.
• Имя файла: VBA_v_primerakh.doc
• Тип: Книга; #532661
• страница: 4 из 21

Ячейка и диапазон

Автоматизация ввода данных в ячейки

Листинг 2.18. Ввод данных в ячейки

Sub SetCellData()

' Заполнение значениями ячеек А3 и В4

Range("A3") = "Данные для ячейки A3"

Range("B4") = "Данные для ячейки B4"

End Sub

Листинг 2.19. Ввод данных с использованием формул

Sub SetCellFormula()

' Запись в ячейку А6 формулы "=A5+B5"

Range("A6") = "=A5+B5"

End Sub

Выделение диапазона над текущей ячейкой

Листинг 2.20. Выделение диапазона над текущей ячейкой

Sub SelectCellRange()

Dim strSelTop As String, strSelBottom As String

' Получение адресов нижней и верхней ячеек диапазона для выделения

strSelBottom = ActiveCell.Address

strSelTop = Cells(1, ActiveCell.Column).Address

' Выделяем все ячейки выше текущей (вместе с текущей ячейкой)

Range(strSelTop & ":" & strSelBottom).Select

End Sub

Поиск ближайшей пустой ячейки столбца

Листинг 2.21. Поиск ближайшей пустой ячейки столбца

Sub FindEmptyCell()

' Поиск ближайшей пустой ячейки в текущем столбце

Do While Not IsEmpty(ActiveCell.Value)

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Поиск максимального значения в диапазоне

Листинг 2.22. Поиск максимального значения

Sub FindMaxValue()

On Error Goto NoCell

If Selection.Count > 1 Then

' Поиск максимального значения в выделенных ячейках

Selection.Find(Application.Max(Selection)).Select

Else

' Поиск максимального значения во всех ячейках листа

ActiveSheet.Cells.Find(Application.Max(ActiveSheet.Cells)).Select

End If

Exit Sub

NoCell:

MsgBox "Максимальное значение не найдено"

End Sub

Автоматическая замена значений диапазона

Листинг 2.23. Автоматическая замена значений

Sub ReplaceValues()

Dim cell As Range

' Проверка каждой ячейки диапазона на возможность замены _

значения в ней (отрицательные значения заменяются на -1, _

положительные - на 1)

For Each cell In Range("C1:C3").Cells

If cell.Value < 0 Then

cell.Value = -1

ElseIf cell.Value > 0 Then

cell.Value = 1

End If

Next

End Sub

Быстрое заполнение диапазона

Первый способ

Листинг 2.24. Быстрое заполнение диапазона

Sub FillCells()

Dim intStartVal As Integer ' Начальное значение

Dim intStep As Integer ' Шаг при изменении значения

Dim intEndVal As Integer ' Конечное значение

Dim intVal As Integer ' Текущее значение

Dim intCellOffset As Integer ' Смещение от начальной ячейки

' Установка параметров заполнения

intStartVal = 1

intStep = 1

intEndVal = 100

' Заполнение ячеек текущего столбца значениями от 1 до 100

For intVal = intStartVal To intEndVal Step intStep

ActiveCell.Offset(intCellOffset, 0).Value = intVal

intCellOffset = intCellOffset + 1

Next intVal

End Sub

Листинг 2.25. Заполнение через интервал

Sub FillCells()

Dim intStartVal As Integer ' Начальное значение

Dim intStep As Integer ' Шаг при изменении значения

Dim intEndVal As Integer ' Конечное значение

Dim intVal As Integer ' Текущее значение

Dim intCellOffset As Integer ' Смещение от начальной ячейки

Dim intCellStep As Integer ' Шаг при перемещении между _

заполняемыми ячейками

' Установка параметров заполнения

intStartVal = 3

intStep = 3

intEndVal = 30

intCellStep = 3

' Заполнение ячеек текущего столбца значениями от 3 до 30

For intVal = intStartVal To intEndVal Step intStep

ActiveCell.Offset(intCellOffset, 0).Value = intVal

intCellOffset = intCellOffset + intCellStep

Next intVal

End Sub

Второй способ

Листинг 2.26. Заполнение указанного диапазона

Sub FillCellRect()

Dim lngRows As Long, intCols As Integer ' Количество ячеек по _

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

Dim lngRow As Long, intCol As Integer ' Координаты текущей ячейки

Dim lngStep As Long, lngVal As Long

' Установка начального значения и шага заполнения

lngVal = 1

lngStep = 1

' Ввод количества ячеек по горизонтали и вертикали, которое _

необходимо заполнить

lngRows = Val(InputBox("Количество ячеек в высоту"))

intCols = Val(InputBox("Количество ячеек в ширину"))

' Отключение обновления экрана

Application.ScreenUpdating = False

' Заполнение ячеек значениями

For lngRow = 1 To lngRows

For intCol = 1 To intCols

ActiveCell.Offset(lngRow, intCol).Value = lngVal

lngVal = lngVal + lngStep

Next intCol

Next lngRow

' Включение обновления экрана

Application.ScreenUpdating = True

End Sub

Третий способ

Листинг 2.27. Заполнение диапазона

Sub FillCellRect1()

Dim lngRows As Long, intCols As Integer

Dim lngRow As Long, intCol As Integer

Dim lngStep As Long, lngVal As Long

Dim alngValues() As Long

Dim rgRange As Range

' Установка начального значения и шага заполнения

lngVal = 1

lngStep = 1

' Ввод количества ячеек по горизонтали и вертикали, которое _

необходимо заполнить

lngRows = Val(InputBox("Количество ячеек в высоту"))

intCols = Val(InputBox("Количество ячеек в ширину"))

ReDim alngValues(1 To lngRows, 1 To intCols)

Set rgRange = ActiveCell.Range(Cells(1, 1), _

Cells(lngRows, intCols))

' Заполнение массива alngValues значениями

For lngRow = 1 To lngRows

For intCol = 1 To intCols

alngValues(lngRow, intCol) = lngVal

lngVal = lngVal + lngStep

Next intCol

Next lngRow

' Перенос значений из массива в таблицу

rgRange.Value = alngValues

End Sub

Помещение в ячейку электронных часов

Листинг 2.28. Размещение в ячейке электронных часов

Sub UpdateTime()

Dim varNextCall As Variant

' Записываем в ячейку текущее время

Cells(1, 1).Value = Now

' Записываем в varNextCall время, когда вызвать этот макрос _

в следующий раз (через 1 секунду)

varNextCall = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 1)

' Уведомляем Excel в необходимости вызова макроса

Application.OnTime varNextCall, "UpdateTime"

End Sub

«Будильник»

Листинг 2.29. «Будильник»

Sub Clock()

' Уведомляем Excel, что процедуру Alarm нужно вызвать в 20:55

Application.OnTime TimeValue("20:55:00"), "Alarm"

End Sub

Sub Alarm()

MsgBox "Пора ужинать!!!"

End Sub

Поиск данных в диапазоне

Поиск в диапазоне значения по шаблону

Листинг 2.30. Поиск и замена по шаблону

Sub ReplaceCellsData()

Dim cell As Range

' Просмотр всех ячеек диапазона G1:K20 и замена искомого текста

For Each cell In [G1:K20]

If cell.Value Like "*Доход*" Then

cell.Value = "Выручка"

cell.Interior.Color = RGB(255, 255, 0)

Else

cell.Interior.Color = RGB(255, 255, 255)

End If

Next

End Sub

Поиск значения с выводом результата

Листинг 2.31. Поиск значения с отображением результата в отдельном окне

Sub Search()

Dim rgResult As Range

' Поиск заданного значения в диапазоне B1:B20 и вывод результата

Set rgResult = Range("B1:B20").Find(9999, , xlValues)

If rgResult Is Nothing Then

MsgBox "Поиск не дал результатов"

Else

MsgBox rgResult.Address

End If

End Sub

Поиск с выделением найденных данных

Листинг 2.32. Выделение найденных данных

Sub FindAndSelect()

Dim strStartAddr As String ' Хранит координаты первого найденного _

значения

Dim rgResult As Range

' Поиск первого входжения искомого слова

Set rgResult = Range("B1:B10").Find("Прибыль", , xlValues)

If Not rgResult Is Nothing Then

' Сохраним адрес найденной ячейки (чтобы контролировать _

зацикливание поиска)

strStartAddr = rgResult.Address

End If

Do While Not rgResult Is Nothing

' Обработка результата поиска

rgResult.Interior.Color = RGB(255, 255, 0)

' Новый поиск

Set rgResult = Range("B1:B10").FindNext(rgResult)

If rgResult.Address = strStartAddr Then

' Поиск завершен

Exit Do

End If

Loop

End Sub

Создание цветной границы диапазона

Листинг 2.33. Оформление верхней и нижней границ диапазона

Sub RangeBorder()

Dim rgRange As Range

Set rgRange = Range("B2:D5")

' Оформление верхней границы диапазона

With rgRange.Borders(xlEdgeTop)

.Weight = xlThick

.LineStyle = xlContinuous

.Color = RGB(0, 0, 255)

End With

' Оформление нижней границы диапазона

With rgRange.Borders(xlEdgeBottom)

.Weight = xlMedium

.LineStyle = xlDash

.Color = RGB(255, 0, 255)

End With

End Sub

Автоматическое определение адреса ячейки

Листинг 2.34. Информация об адресе активной ячейки

Sub Worksheet_SelectionChange(ByVal Target As Range)

' Вывод адреса ячейки в различных форматах

MsgBox Target.Address() & vbCr & _

Target.Address(RowAbsolute:=False) & vbCr & _

Target.Address(ReferenceStyle:=xlR1C1) & vbCr & _

Target.Address(ReferenceStyle:=xlR1C1, _

RowAbsolute:=False, ColumnAbsolute:=False, _

RelativeTo:=Worksheets(1).Cells(2, 2))

End Sub

Автоматизация добавления примечаний в указанном диапазоне

Листинг 2.35. Добавление примечаний в диапазон

Sub CreateComments()

Dim cell As Range

' Производим поиск по всем ячейкам диапазона и добавляем примечания _

ко всем ячейкам, содержащим слово "Выручка"

For Each cell In Range("B1:B100")

If cell.Value Like "*Выручка*" Then

cell.ClearComments

cell.AddComment "Неучтенная наличка"

End If

Next

End Sub

Заливка диапазона

Листинг 2.36. Создание заливки диапазона

Sub FillRange()

' Заливка диапазона

With Range("B1:E10")

' Задаем узор - сетчатый

.Interior.Pattern = xlPatternChecker

' Цвет узора - синий

.Interior.PatternColor = RGB(0, 0, 255)

' Цвет ячейки - красный

.Interior.Color = RGB(255, 0, 0)

End With

End Sub

Ввод строго ограниченных значений в указанный диапазон

Ввод данных с помощью диалогового окна

Листинг 2.37. Настройка ввода данных в диалоговом окне

Sub DialogInputData()

Dim intMin As Integer, intMax As Integer ' Диапазон значений

Dim strInput As String ' Введенная пользователем строка

Dim strMessage As String

Dim intValue As Integer

intMin = 1 ' Минимальное значение

intMax = 50 ' Максимальное значение

strMessage = "Введите значение от " & intMin & " до " & intMax

' Ввод значения (цикл завершается, когда пользователь вводит _

значение из заданного диапазона или отменяет ввод)

Do

strInput = InputBox(strMessage)

If strInput = "" Then Exit Sub ' Отмена ввода

' Проверка, содержит ли введенная пользователем строка число

If IsNumeric(strInput) Then

intValue = CInt(strInput)

' Проверка, удовлетворяет ли значение диапазону

If intValue >= intMin And intValue <= intMax Then

' Все условия выполнены

Exit Do

End If

End If

' Формирование сообщения с текстом ошибки

strMessage = "Вы ввели некорректное значение." & vbNewLine & _

"Введите число от " & intMin & " до " & intMax

Loop

' Внесение данных в ячейку

ActiveSheet.Range("A1").Value = strInput

End Sub

Непосредственный ввод данных

Листинг 2.38. Ограничение возможных значений диапазона

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rgInputRange As Range

Dim cell As Range

Dim strMessage As String

Dim varResult As Variant

' Диапазон, в котором контролируется ввод

Set rgInputRange = Range("A1:E10")

' Просмотр всех измененных ячеек и контроль ввода в тех, которые _

принадлежат заданному диапазону

For Each cell In Target

' Проверка принадлежности диапазону

If Union(cell, rgInputRange).Address = rgInputRange.Address Then

' Контроль правильности ввода

varResult = IsCellDataValid(cell)

If varResult = True Then

' Введено корректное значение

Exit Sub

Else

' Формирование и вывод сообщения об ошибке

strMessage = "Ячейка " & cell.Address(False, False) & ":" _

& vbCrLf & vbCrLf & varResult

MsgBox strMessage, vbCritical, "Неправильное значение"

' Очистка ввода

Application.EnableEvents = False

cell.ClearContents

cell.Activate

Application.EnableEvents = True

End If

End If

Next cell

End Sub

Function IsCellDataValid(cell As Range) As Variant

' Возвращает True, если в ячейку вводится целое число _

в диапазоне от 1 до 12. В противном случае выдается _

соответствующее сообщение

' Проверка, является ли содержимое ячейки числом

If Not WorksheetFunction.IsNumber(cell.Value) Then

IsCellDataValid = "Нечисловое значение"

Exit Function

End If

' Проверка, является ли введенное число целым

If Int(cell.Value) <> cell.Value Then

IsCellDataValid = "Введите целое число"

Exit Function

End If

' Проверка соответствия числа диапазону

If cell.Value < 1 Or cell.Value > 12 Then

IsCellDataValid = "Значение должно быть от 1 до 12"

Exit Function

End If

' В ячейку введено допустимое значение

IsCellDataValid = True

End Function

Последовательный ввод данных

Листинг 2.39. Последовательный ввод данных

Sub StreamInput()

Dim strDate As String

Dim strSum As String

Dim lngRow As Long

' Ввод данных в цикле (повторяется до тех пор, пока пользователь _

не введет пустую строку или не нажмет "Отмена" в окне ввода)

Do

lngRow = Range("A65536").End(xlUp).Row + 1

' Ввод даты

strDate = InputBox("Вводим дату")

If strDate = "" Then Exit Sub

' Ввод выручки

strSum = InputBox("Вводим выручку")

If strSum = "" Then Exit Sub

' Запись данных в ячейки

Cells(lngRow, 1) = strDate

Cells(lngRow, 2) = strSum

Loop

End Sub

Быстрое выделение ячеек с отрицательными значениями

Листинг 2.40. Выделение отрицательных значений

Sub NegSelect()

Dim cell As Range

' Просмотр всех ячеек выделенного диапазона и пометка тех, _

которые содержат отрицательные значения

For Each cell In Selection

If cell.Value < 0 Then

cell.Interior.Color = RGB(255, 0, 0)

Else

cell.Interior.ColorIndex = xlNone

End If

Next cell

End Sub

Получение информации о выделенном диапазоне

Листинг 2.41. Получение информации о выделенном диапазоне

Sub TypeOfSelection()

Dim rgSelUnion As Range ' Объединение выделенных областей

Dim strTitle As String ' Заголовок сообщения

Dim strMessage As String ' Текст сообщения

Dim strSelType As String ' Тип выделения (простой или _

множественный)

Dim intBlockCount As Integer ' Количество блоков в выделении

Dim intCellCount As Long ' Общее количество выделенных ячеек

Dim intColCount As Integer ' Количество выделенных столбцов

Dim intRowCount As Long ' Количество выделенных строк

Dim intAreasCount As Integer ' Количество выделенных областей

Dim strCurSelType As String

Dim rgArea As Range

' Подсчет количества выделенных областей и определение типа выделения: _

простое (одна область) или сложное(несколько областей)

intAreasCount = Selection.Areas.Count

If intAreasCount = 1 Then

strTitle = "Простое выделение"

Else

strTitle = "Множественное выделение"

End If

' Определение типа выделения первой области

strSelType = dhGetAreaType(Selection.Areas(1))

' Создание объединения во избежание повторного учета _

пересекающихся участков выделенных диапазонов

Set rgSelUnion = Selection.Areas(1)

For Each rgArea In Selection.Areas

strCurSelType = dhGetAreaType(rgArea)

' Изменение надписи о типе всего выделения, если _

есть выделения различного типа

If strCurSelType <> strSelType Then

strSelType = "Множественный"

End If

' Определение количества блоков перед их добавлением в объединение

If strCurSelType = "Block" Then

intBlockCount = intBlockCount + 1

End If

' Добавление в объединение

Set rgSelUnion = Union(rgSelUnion, rgArea)

Next rgArea

' Просматриваются элементы созданного объединения

For Each rgArea In rgSelUnion.Areas

Select Case dhGetAreaType(rgArea)

Case "Строка"

intRowCount = intRowCount + rgArea.Rows.Count

Case "Столбец"

intColCount = intColCount + rgArea.Columns.Count

Case "Лист"

intColCount = intColCount + rgArea.Columns.Count

intRowCount = intRowCount + rgArea.Rows.Count

End Select

Next rgArea

' Определение количества неперекрывающихся ячеек

intCellCount = rgSelUnion.Count

' Формирование и вывод итогового сообщения

strMessage = "Тип выделения:" & vbTab & strSelType & vbCrLf & _

"Количество областей: " & vbTab & intAreasCount & vbCrLf & _

"Полных столбцов: " & vbTab & intColCount & vbCrLf & _

"Полных строк: " & vbTab & intRowCount & vbCrLf & _

"Блоков ячеек: " & vbTab & intBlockCount & vbCrLf & _

"Всего ячеек: " & vbTab & Format(intCellCount, "#,###")

MsgBox strMessage, vbInformation, strTitle

End Sub

Function dhGetAreaType(rgRangeArea As Range) As String

' Определение типа диапазона

If rgRangeArea.Count = Cells.Count Then

' Все ячейки рабочего листа

dhGetAreaType = "Лист"

ElseIf rgRangeArea.Cells.Count = 1 Then

' Одна ячейка

dhGetAreaType = "Ячейка"

ElseIf rgRangeArea.Rows.Count = Cells.Rows.Count Then

' Весь столбец

dhGetAreaType = "Столбец"

ElseIf rgRangeArea.Columns.Count = Cells.Columns.Count Then

' Вся строка

dhGetAreaType = "Строка"

Else

' Блок ячеек

dhGetAreaType = "Блок"

End If

End Function

Панель инструментов изменения числового формата ячейки

Листинг 2.42. Код в модуле рабочего листа

Sub Worksheet_Change(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

Листинг 2.43. Код в стандартном модуле

Sub FastChangeNumberFormat()

Dim bar As CommandBar

Dim button As CommandBarButton

' Удаление существующей панели инструментов (если она есть)

On Error Resume Next

CommandBars("Числовой формат").Delete

On Error GoTo 0

' Формирование новой панели

Set bar = CommandBars.Add

With bar

.Name = "Числовой формат"

.Visible = True

End With

' Создание кнопки

Set button = CommandBars("Числовой формат").Controls.Add _

(Type:=msoControlButton)

With button

.Caption = ""

.OnAction = "ChangeNumFormat"

.TooltipText = "Щелкните для изменения числового формата"

.Style = msoButtonCaption

End With

' Обновление созданной панели инструментов

Call UpdateToolbar

End Sub

Sub UpdateToolbar()

' Обновление панели инструментов (если она создана)

On Error Resume Next

' Изменение заголовка кнопки (на название формата выделенной ячейки)

CommandBars("Числовой формат").Controls(1).Caption = _

ActiveCell.NumberFormat

End Sub

Sub ChangeNumFormat()

' Отображение диалогового окна изменения формата ячейки

Application.Dialogs(xlDialogFormatNumber).Show

Call UpdateToolbar

End Sub

Тестирование скорости чтения и записи диапазонов

Листинг 2.44. Тестирование скорости чтения и записи диапазонов

Sub TableSpeedTest()

Dim alngData() As Long ' Массив с числами

Dim lngCount As Long ' Количество элементов в массиве

Dim dtStart As Date ' Хранит время (и даже дату) начала _

тестирования

Dim strArrayToTable As String ' Время записи в таблицу

Dim strTableToArray As String ' Время чтения из таблицы

Dim strMessage As String

Dim i As Long

' Подготовка диапазона ячеек

Range("A:A").ClearContents

' Ввод размера массива, формирование массива заданного размера

lngCount = InputBox("Введите количество элементов")

ReDim alngData(1 To lngCount)

' Заполнение массива данными

For i = 1 To lngCount

alngData(i) = i

Next i

' Перенос массива в таблицу

Application.ScreenUpdating = False

dtStart = Timer

For i = 1 To lngCount

Cells(i, 1) = i

Next i

strArrayToTable = Format(Timer - dtStart, "00:00")

' Чтение данных из таблицы обратно в массив

dtStart = Timer

For i = 1 To lngCount

alngData(i) = Cells(i, 1)

Next i

strTableToArray = Format(Timer - dtStart, "00:00")

Application.ScreenUpdating = True

' Вывод на экран результатов тестирования

strMessage = "Запись: " & strArrayToTable & vbCrLf & _

"Чтение: " & strTableToArray

MsgBox strMessage, , lngCount & " элементов"

End Sub

Разработка и применение полезных пользовательских функций

Объединение данных диапазона

Листинг 2.45. Функция Couple

Function Couple(Diapazon)

' Объединение данных, содержащихся в ячейках диапазона _

Diapazon (разделитель между значениями - пробел)

' iCell - текущая ячейка

For Each iCell In Diapazon

' Сцепляются данные только заполненных ячеек

If IsEmpty(iCell) <> True Then

' Добавление значения ячейки в выходную строку

If Couple = "" Then

Couple = iCell

Else

Couple = Couple & " " & iCell

End If

End If

Next

End Function

Объединение данных с учетом форматов

Листинг 2.46. Функция CoupleFormat

Function CoupleFormat(Diapazon)

' Объединение текстовых данных, содержащихся в ячейках _

диапазона Diapazon (разделитель между значениями - пробел)

' iCell - текущая ячейка

For Each iCell In Diapazon

' Сцепляются данные только заполненных ячеек

If IsEmpty(iCell) <> True Then

' Добавление текста ячейки в выходную строку

If CoupleFormat = "" Then

CoupleFormat = iCell.Text

Else

CoupleFormat = CoupleFormat & " " & iCell.Text

End If

End If

Next

End Function

Эксперименты с датой

Листинг 2.47. Функция ДатаПолная

Function ДатаПолная(Ячейка)

' Получение данных в заданной ячейке в формате _

"dd mmmm yyyy"

Дата = Format(Ячейка, "dd mmmm yyyy")

If IsDate(Ячейка) = True Or IsDate(Дата) = True Then

' Возврат строки с полной датой

ДатаПолная = StrConv(Дата, vbProperCase)

Else

' Данные в ячейке не являются датой

ДатаПолная = "<>"

End If

End Function

Выбор из текста всех чисел

Листинг 2.48. Функция ExtractNumeric

Function ExtractNumeric(iCell)

' Анализируется каждый символ входной строки iCell

For iCount = 1 To Len(iCell)

' Проверка, является ли анализируемый символ числом

If IsNumeric(Mid(iCell, iCount, 1)) = True Then

' Число добавляется в выходную строку

ExtractNumeric = ExtractNumeric & Mid(iCell, iCount, 1)

End If

Next

End Function

Прописная буква только в начале текста

Листинг 2.49. Функция ПрописнНач

Function ПрописнНач(Текст)

' Пустой текст функция не обрабатывает

If Текст = "" Then ПрописнНач = "<>": Exit Function

' Выделение первого символа и перевод его в верхний регистр

ПервыйСимвол = UCase(Left(Текст, 1))

' Выделение остальной части строки и перевод _

ее в нижний регистр

Обрубок = LCase(Mid(Текст, 2))

' Соединение частей строки и возврат значения

ПрописнНач = ПервыйСимвол & Обрубок

End Function

Перевод чисел в «деньги»

Листинг 2.50. Функция RubKop

Function RubKop(Число)

' Пустые ячейки и ячейки, содержащие текст, функция _

не обрабатывает

If IsNumeric(Число) = False Or Число = "" Then RubKop = _

"<>": Exit Function

' Из числа целой части - рубли

ДлинаЧисла = Len(Число)

ЦелаяЧасть = Fix(Число)

ДлинаЦелой = Len(ЦелаяЧасть)

' Вычисление длины дробной части

ДлинаДроби = ДлинаЧисла - ДлинаЦелой

If ДлинаДроби <> 0 Then

ДлинаДроби = ДлинаЧисла - ДлинаЦелой - 1

End If

' Формирование количества копеек в зависимости от длины _

дробной части

If ДлинаДроби = 0 Then

' Ноль копеек

Копейки = "00"

ElseIf ДлинаДроби = 1 Then

' Дробная часть состоит из одного числа - это _

десятки копеек

Копейки = Right(Число, ДлинаДроби) & "0"

ElseIf ДлинаДроби = 2 Then

' Дробная часть полностью соответствует количеству копеек

Копейки = Right(Число, ДлинаДроби)

Else

' Длина дробной части больше двух - округлим _

дробную часть

Копейки = Right(Число, ДлинаДроби)

If Mid(Копейки, 3, 1) > 4 Then

Копейки = Left(Копейки, 2) + 1

Else

Копейки = Left(Копейки, 2)

End If

End If

' Составление полной надписи из количества рублей и копеек

Рубли = ЦелаяЧасть

RubKop = Рубли & " " & "руб." & " " & Копейки & " " & "коп."

End Function

Подсчет количества повторов искомого текста

Листинг 2.51. Функция CoincideCount

Function CoincideCount(Text, Search)

' Проверка правильности входных данных _

(аргумента Search)

If IsArray(Search) = True Then Exit Function

If IsError(Search) = True Then Exit Function

If IsEmpty(Search) = True Then Exit Function

' Просмотр заданного в параметре Text диапазона

For Each iCell In Text

' Анализируются только ячейки, содержащие _

корректные значения

If Not IsError(iCell) Then

' iText - строка для просмотра (в нижнем регистре)

iText = LCase(iCell)

' iSearch - искомое значение (в нижнем регистре)

iSearch = LCase(Search)

' Длина искомой строки

iLen = Len(Search)

' Первый поиск строки iSearch в строке iText _

(этот и последующий поиски производятся без _

учета регистра символов)

iNumber = InStr(iText, iSearch)

While iNumber > 0

' Поиск следующего вхождения строки

iNumber = InStr(iNumber + iLen, iText, iSearch)

' Подсчет количества вхождений

CoincideCount = CoincideCount + vbNull

Wend

End If

Next

End Function

Суммирование данных только видимых ячеек

Листинг 2.52. Функция СуммаВид

Function СуммаВид(Диапазон) As Double

' Просмотр всех ячеек заданного диапазона

For Each Ячейка In Диапазон

' Анализ только видимых ячеек

If Not Ячейка.EntireRow.Hidden And Not _

Ячейка.EntireColumn.Hidden Then

' При расчете учитываются только ячейки _

с численными значениями

If IsNumeric(Ячейка) = True Then

СуммаВид = СуммаВид + Ячейка

End If

End If

Next

End Function

При суммировании — курсор внутри диапазона

Листинг 2.53. Функция Сумма

Function Сумма(Диапазон, АдресЯчейки) As Double

' Просмотр всех ячеек диапазона

For Each Ячейка In Диапазон

' Проверка, чтобы в суммировании не участвовала _

ячейка с формулой

If АдресЯчейки.Address <> Ячейка.Address Then

' В суммировании участвуют только ячейки _

с численными значениями

If IsNumeric(Ячейка) = True Then

Сумма = Сумма + Ячейка

End If

End If

Next

End Function

Начисление процентов в зависимости от суммы

Листинг 2.54. Функция dhCalculatePercent (вариант 1)

Function dhCalculatePercent(lngSum As Long) As Double

' Процентные ставки (декларация констант)

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

' Граничные суммы вкладов (декларация констант)

Const intSum1 As Long = 5000

Const intSum2 As Long = 10000

' Возвращаем сумму, умноженную на соответствующую ставку

If lngSum < intSum1 Then

dhCalculatePercent = lngSum * dblRate1

ElseIf lngSum < intSum2 Then

dhCalculatePercent = lngSum * dblRate2

Else

dhCalculatePercent = lngSum * dblRate3

End If

End Function

Листинг 2.55. Функция dhCalculatePercent (вариант 2)

Function dhCalculatePercent(lngSum As Long) As Double

' Процентные ставки (декларация констант)

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

' Граничные суммы вкладов (декларация констант)

Const intSum1 As Long = 5000

Const intSum2 As Long = 10000

' Возвращаем сумму, умноженную на соответствующую ставку

Select Case lngSum

Case Is < intSum1

dhCalculatePercent = lngSum * dblRate1

Case Is < intSum2

dhCalculatePercent = lngSum * dblRate2

Case Else

dhCalculatePercent = lngSum * dblRate3

End Select

End Function

Еще о расчете процентов

Листинг 2.56. Функция dhCalculatePercent (вариант 3)

Function dhCalculatePercent(Sales As Long, IsTemporal As Boolean) As Double

' Процентные ставки (декларация констант)

Const dblRate1 As Double = 0.09

Const dblRate2 As Double = 0.11

Const dblRate3 As Double = 0.15

Const dblAdd As Double = 1.1

' Граничные суммы

Const lngSum1 As Long = 5000

Const lngSum2 As Long = 10000

' Расчет суммы для выплаты (как обычно)

If Sales < lngSum1 Then

dhCalculatePercent = Sales * dblRate1

ElseIf Sales < lngSum2 Then

dhCalculatePercent = Sales * dblRate2

Else

dhCalculatePercent = Sales * dblRate3

End If

If IsTemporal Then

' Для сторонних вкладчиков - надбавка

dhCalculatePercent = dblAdd * dhCalculatePercent

End If

End Function

Сводный пример расчета комиссионного вознаграждения

Листинг 2.57. Расчет комиссионного вознаграждения

Function dhCalculateCom(dblSales As Double) As Double

Const dblRate1 = 0.09

Const dblRate2 = 0.11

Const dblRate3 = 0.15

' Расчет комиссионных с продаж (без выслуги) в зависимости _

от суммы

Select Case dblSales

Case 0 To 4999.99: dhCalculateCom = dblSales * dblRate1

Case 5000 To 9999.99: dhCalculateCom = dblSales * dblRate2

Case Is >= 10000: dhCalculateCom = dblSales * dblRate3

End Select

End Function

Function dhCalculateCom2(dblSales As Double, intYears As Double) _

As Double

Const dblRate1 = 0.09

Const dblRate2 = 0.11

Const dblRate3 = 0.15

' Расчет комиссионных с продаж (без учета выслуги лет) _

в зависимости от суммы

Select Case dblSales

Case 0 To 4999.99: dhCalculateCom2 = dblSales * dblRate1

Case 5000 To 9999.99: dhCalculateCom2 = dblSales * dblRate2

Case Is >= 10000: dhCalculateCom2 = dblSales * dblRate3

End Select

' Надбавка за выслугу лет

dhCalculateCom2 = dhCalculateCom2 + _

(dhCalculateCom2 * intYears / 100)

End Function

Sub ComCalculator()

Dim strMessage As String

Dim dblSales As Double

Dim ан As Integer

Calc:

' Отображение окна для ввода данных

dblSales = Val(InputBox("Сумма реализации:", _

"Расчет комиссионного вознаграждения"))

' Формирование сообщения (с одновременным расчетом _

вознаграждения)

strMessage = "Объем продаж:" & vbTab & Format(dblSales, "$#,##0") & _

vbCrLf & "Сумма вознаграждения:" & vbTab & _

Format(dhCalculateCom(dblSales), "$#,##0") & _

vbCrLf & vbCrLf & "Считаем дальше?"

' Вывод окна с сообщением (о рассчитанной сумме и вопросом _

о продолжении расчетов)

If MsgBox(strMessage, vbYesNo, _

"Расчет комиссионного вознаграждения") = vbYes Then

' Продолжение расчетов

GoTo Calc

End If

End Sub

Подсчет количества ячеек, содержащих указанные значения

Листинг 2.58. Количество ячеек с определенным значением

Function dhCount(rgn As Range, LowBound As Double, _

UpperBound As Double) As Long

Dim cell As Range

Dim lngCount As Long

' Проходим по всем ячейкам диапазона rgn и подсчитываем значения, _

попадающие в интервал от LowBound до UpperBound

For Each cell In rgn

If cell.Value >= LowBound And cell.Value <= UpperBound Then

' Значение попадает в заданный интервал

lngCount = lngCount + 1

End If

Next

dhCount = lngCount

End Function

Подсчет количества видимых ячеек в диапазоне

Листинг 2.59. Подсчет количества видимых ячеек

Function dhCountVisibleCells(rgRange As Range)

Dim lngCount As Long

Dim cell As Range

' Проходим по всему диапазону и подсчитываем непустые _

видимые ячейки

For Each cell In rgRange

' Проверка, есть ли данные в ячейке

If Not IsEmpty(cell) Then

' Проверка, видима ли ячейка

If Not cell.EntireRow.Hidden And Not _

cell.EntireColumn.Hidden Then

' Еще одна видимая ячейка

lngCount = lngCount + 1

End If

End If

Next cell

dhCountVisibleCells = lngCount

End Function

Поиск ближайшего понедельника

Листинг 2.60. Ближайший день недели по отношению к дате

Function dhGetNextMonday(datDate As Date) As Date

' Определение даты следующего понедельника (функция Weekday _

возвращает номер дня недели, считая от понедельника, если _

в качестве второго аргумента задавать vbMonday)

If Weekday(datDate, vbMonday) = 1 Then

' Заданная дата и есть понедельник

dhGetNextMonday = datDate

Else

' Расчет даты следующего понедельника

dhGetNextMonday = datDate + 8 - Weekday(datDate, vbMonday)

End If

End Function

Подсчет количества полных лет

Листинг 2.61. Функция dhCalculateAge

Function dhCalculateAge(datDate As Date) As Long

Dim lngAge As Long

' Находим разность между текущей датой и указанной (лет)

lngAge = DateDiff("yyyy", datDate, Date)

If DateSerial(Year(datDate) + lngAge, Month(datDate), _

Day(datDate)) > Date Then

' В этом году день рождения еще не наступил

lngAge = lngAge - 1

End If

dhCalculateAge = lngAge

End Function

Проверка, была ли сохранена рабочая книга

Листинг 2.62. Функция dhBookIsSaved

Function dhBookIsSaved() As Boolean

' Если путь файла рабочей книги не задан, то она _

не сохранена (ThisWorkbook.path равняется "")

dhBookIsSaved = ThisWorkbook.path <> ""

End Function

Расчет средневзвешенного значения

Листинг 2.63. Расчет средневзвешенного значения

Function dhAverageWithWeight(rgWeights As Range, rgValues As Range) _

As Double

If (rgWeights.Count <> rgValues.Count) Then

' Количество весов не соответствует количеству аргументов

dhAverageWithWeight = 0

Exit Function

End If

Dim i As Integer

Dim dblSum As Double ' Сумма значений

Dim dblSumWeight As Double ' Взвешенная сумма значений

' Вычисление...

For i = 1 To rgWeights.Count

' Взвешенной суммы значений

dblSumWeight = dblSumWeight + rgWeights(i) * rgValues(i)

' Суммы значений

dblSum = dblSum + rgWeights(i)

Next

' Возвращение средневзвешенного значения

dhAverageWithWeight = dblSumWeight / dblSum

End Function

Преобразование номера месяца в его название

Листинг 2.64. Название месяца

Function dhMonthName(intMonth As Integer) As String

' Возвращение имени месяца по его номеру (intMonth _

является номером элемента в массиве с названиями месяцев)

dhMonthName = Choose(intMonth, "Январь", "Февраль", "Март", _

"Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", _

"Октябрь", "Ноябрь", "Декабрь")

End Function

Расчет суммы первых значений диапазона

Листинг 2.65. Функция dhNSum

Function dhNSum(ByVal intCount As Integer, _

rgValues As Range) As Double

Dim i As Integer

Dim dblSum As Double

If intCount > rgValues.Count Then

' Задано количество элементов большее, чем есть _

в переданном диапазоне

intCount = rgValues.Count

End If

' Расчет суммы первых intCount элементов

For i = 1 To intCount

dblSum = dblSum + rgValues(i)

Next i

' Возврат результата

dhNSum = dblSum

End Function

Поиск последней непустой ячейки диапазона

Листинг 2.66. Функция dhLastUsedCell

Function dhLastUsedCell(rgRange As Range) As Long

Dim lngCell As Long

' Пойдем по диапазону с конца (тогда первая попавшаяся _

заполненная ячейка и будет искомой)

For lngCell = rgRange.Count To 1 Step -1

If Not IsEmpty(rgRange(lngCell)) Then

' Нашли непустую ячейку

dhLastUsedCell = lngCell

Exit Function

End If

Next lngCell

' Непустую ячейку не нашли

dhLastUsedCell = 0

End Function

Поиск последней непустой ячейки столбца

Листинг 2.67. Функция dhLastColUsedCell

Function dhLastColUsedCell(rgColumn As Range) As Variant

' Вывод значения последней непустой ячейки столбца

dhLastColUsedCell = rgColumn.Parent.Cells(Rows.Count, _

rgColumn.Column).End(xlUp).Value

End Function

Поиск последней непустой ячейки строки

Листинг 2.68. Функция dhLastRowUsedCell

Function dhLastRowUsedCell(rgRow As Range) As Variant

' Вывод значения последней непустой ячейки строки

dhLastRowUsedCell = rgRow.Parent.Cells(rgRow.Row, 256). _

End(xlToLeft).Address

End Function

Подсчет количества ячеек в диапазоне, содержащих указанные значения

Листинг 2.69. Функция dhCountSomeCells

Function dhCountSomeCells(rgRange As Range, dblMin As Double, _

dblMax As Double) As Long

' Расчет количества ячеек со значениями от dblMin до dblMax _

с использованием стандартной функции CountIf

With Application.WorksheetFunction

dhCountSomeCells = .CountIf(rgRange, ">=" & dblMin) - _

.CountIf(rgRange, ">" & dblMax)

End With

End Function

Англоязычный текст — заглавными буквами

Листинг 2.70. Английский текст — в верхнем регистре

Function dhFormatEnglish(strText As String) As String

Dim i As Integer

Dim strCurChar As String * 1

' Анализируется каждый символ строки strText. Каждый символ _

латинского алфавита преобразуется в верхний регистр

For i = 1 To Len(strText)

strCurChar = Mid(strText, i, 1)

' Код латинских строчных символов лежит в пределах _

от 97 до 122

If Asc(strCurChar) >= 97 And Asc(strCurChar) <= 122 Then

' Переводим символ в верхний регистр

dhFormatEnglish = dhFormatEnglish & UCase(strCurChar)

Else

' Просто добавляем символ в выходную строку

dhFormatEnglish = dhFormatEnglish & strCurChar

End If

Next i

End Function

Отображение текста «задом наперед»

Листинг 2.71. Преобразование текста в обратном порядке

Function dhReverseText(strText As String) As String

Dim i As Integer

' Переписываем символы из входной строки в выходную _

в обратном порядке

For i = Len(strText) To 1 Step -1

dhReverseText = dhReverseText & Mid(strText, i, 1)

Next i

End Function

Sub ReverseText()

Dim strText As String

' Ввод строки посредством стандартного окна ввода

strText = InputBox("Введите текст:")

' Реверсия строки и вывод результата

MsgBox dhReverseText(strText), , strText

End Sub

Поиск максимального значения на всех листах книги

Листинг 2.72. Поиск максимального значения

Function dhMaxInBook(cell As Range) As Double

Dim sheet As Worksheet

Dim dblMax As Double

Dim dblResult As Double

Dim fFirst As Boolean

fFirst = True

' Расчет максимальных значений на всех листах рабочей книги _

и выбор наибольшего из них

For Each sheet In cell.Parent.Parent.Worksheets

' Расчет максимального значения на листе

dblResult = Application.WorksheetFunction.Max(sheet.UsedRange)

If fFirst Then

' Найдено первое значение - его не с чем сравнивать

dblMax = dblResult

fFirst = False

End If

' Выбираем большее из dblMax и dbmResult

If dblResult > dblMax Then

dblMax = dblResult

End If

Next sheet

' Возврат результата

dhMaxInBook = dblMax

End Function

Использование относительных ссылок

Листинг 2.73. Функция dhSheetOffset

Function dhSheetOffset(offset As Integer, cell As Range) As Variant

' Возврат корректного значения ячейки cell листа, смещение _

которого относительно текущего задано переменной offset

dhSheetOffset = Sheets(Application.Caller.Parent.Index _

+ offset).Range(cell.Address)

End Function

Листинг 2.74. Функция dhSheetOffset2

Function dhSheetOffset2(offset As Integer, cell As Range) As Variant

' Корректировка смещения (чтобы ссылка была на рабочий лист)

Do While TypeName(Sheets(cell.Parent.Index + offset)) _

<> "Worksheet"

If offset > 0 Then

' Пропускаем лист и проходим вперед по книге

offset = offset + 1

Else

' Пропускаем лист и проходим назад по книге

offset = offset - 1

End If

Loop

' Возврат корректного значения ячейки cell листа, смещение _

которого относительно текущего задано переменной offset _

с пропуском листов с диаграммами

dhSheetOffset2 = Sheets(cell.Parent.Index _

+ offset).Range(cell.Address)

End Function

Определение типа данных ячейки

Листинг 2.75. Тип данных, хранящихся в ячейке

Function dhCellType(rgRange As Range) As String

' Переходим к левой верхней ячейке, если rgRange - диапазон, _

а не одна ячейка

Set rgRange = rgRange.Range("A1")

' Определение типа значения в ячейке

Select Case True

Case IsEmpty(rgRange)

' Ячейка пуста

dhCellType = "Пусто"

Case Application.IsText(rgRange)

' В ячейке текст

dhCellType = "Текст"

Case Application.IsLogical(rgRange)

' В ячейке логическое значение (True или False)

dhCellType = "Булево выражение"

Case Application.IsErr(rgRange)

' При вычислении значения в ячейке произошла ошибка

dhCellType = "Ошибка"

Case IsDate(rgRange)

' В ячейке дата

dhCellType = "Дата"

Case InStr(1, rgRange.Text, ":") <> 0

' В ячейке время

dhCellType = "Время"

Case IsNumeric(rgRange)

' В ячейке числовое значение

dhCellType = "Число"

End Select

End Function

Выделение из текста произвольного элемента

Листинг 2.76. Выделение элемента текста

Function dhGetTextItem(ByVal strTextIn As String, intItem As _

Integer, strSeparator As String) As String

Dim intStart As Integer ' Позиция начала текущего элемента

Dim intEnd As Integer ' Позиция конца текущего элемента

Dim i As Integer ' Номер текущего элемента

' Проверка корректности номера элемента

If intItem < 1 Then Exit Function

' Убираются лишние пробелы, если разделитель - пробел

If strSeparator = " " Then strTextIn = Application.Trim(strTextIn)

' Разделитель добавляется в конец строки

If Right(strTextIn, Len(strTextIn)) <> strSeparator Then _

strTextIn = strTextIn & strSeparator

' Поиск всех элементов в строке до нужного

For i = 1 To intItem

' Начало элемента (перемещение вперед по строке)

intStart = intEnd + 1

' Конец элемента

intEnd = InStr(intStart, strTextIn, strSeparator)

If (intEnd = 0) Then

' Дошли до конца строки, но элемент не нашли

Exit Function

End If

Next i

' Выделение текста из входной строки

dhGetTextItem = Mid(strTextIn, intStart, intEnd - intStart)

End Function

Генератор случайных чисел

Листинг 2.77. Функция dhGetRandomValues

Function dhGetRandomValues() As Variant

Dim intRow As Integer ' Номер текущей строки

Dim intCol As Integer ' Номер текущего столбца

Dim aintOut() As Integer ' Выходной массив (двумерный)

Dim aintValues() As Integer ' Массив с возможными значениями

Dim intMax As Integer ' Последний доступный элемент массива _

aintValues

Dim i As Integer

ReDim aintOut(1 To Application.Caller.Rows.Count, 1 To _

Application.Caller.Columns.Count)

' Всего нужно чисел...

intMax = Application.Caller.Rows.Count * _

Application.Caller.Columns.Count

ReDim aintValues(1 To intMax)

' Заполнение массива aintValues значениями от 1 до intMax

For i = 1 To intMax

aintValues(i) = i

Next i

' Занесение значений в выходной массив aintOut, в произвольном _

порядке выбирая их из aintValues

Randomize

For intRow = 1 To Application.Caller.Rows.Count

For intCol = 1 To Application.Caller.Columns.Count

' Определение номера элемента из aintValues

i = Rnd * intMax

If i = 0 Then i = 1

' Занесение этого элемента в выходной массив

aintOut(intRow, intCol) = aintValues(i)

' Уменьшение массива aintValues (то есть еще один его _

элемент выбран) - замена выбранного элемента последним _

в массиве

aintValues(i) = aintValues(intMax)

intMax = intMax - 1

Next intCol

Next intRow

' Возвращение массива значений

dhGetRandomValues = aintOut

End Function

Случайные числа — на основании диапазона

Листинг 2.78. Функция dhGetRandomValues1

Function dhGetRandomValues1(rgSource As Range) As Variant

Dim intRow As Integer ' Номер текущей строки

Dim intCol As Integer ' Номер текущего столбца

Dim avarOut() As Variant ' Выходной массив (двумерный)

Dim avarValues() As Variant ' Массив с возможными значениями

Dim intValCount As Integer ' Количество возможных значений

Dim cell As Range

Dim i As Integer

ReDim avarOut(1 To Application.Caller.Rows.Count, 1 To _

Application.Caller.Columns.Count)

' Всего нужно чисел...

intValCount = rgSource.Rows.Count * rgSource.Columns.Count

ReDim avarValues(1 To intValCount)

' Заполнение массива avarValues значениями из указанного _

диапазона

For Each cell In rgSource

i = i + 1

avarValues(i) = cell.Value

Next cell

' Занесение значений в выходной массив avarOut, в произвольном _

порядке выбирая их из avarValues

Randomize

For intRow = 1 To Application.Caller.Rows.Count

For intCol = 1 To Application.Caller.Columns.Count

' Определение номера элемента из avarValues

i = Rnd * intValCount

If i = 0 Then i = 1

' Занесение этого элемента в выходной массив

avarOut(intRow, intCol) = avarValues(i)

Next intCol

Next intRow

' Возвращение массива значений

dhGetRandomValues1 = avarOut

End Function