Решения, советы, используемые механизмы, инструменты

[Naeel Maqsudov]

Идея организации этой темы простая - обмен опытом, у кого что есть - решения, советы, используемые механизмы, инструменты и т.д.,

Чтобы увековечить свое имя полезным советом, пришлите его Модератору как личное сообщение.

Т.е. эта тема является ПРЕМОДЕРИРУЕМОЙ (типа как в FIDONet).

[Naeel Maqsudov]

[1] Очень полезное свойство механизма отладки программ VBA, которое узнал сравнительно недавно -
это возможность перетаскивать переменные, массивы, объекты в окно Watches. Например, выполнение кода остановилось
на команде Stop, теперь выделяем мышкой переменную (объект, массив и т.д.), например, "iCell" и тащим в ниже расположенное и заранее открытое окно Watches, в котором можно рассматривать структуру объекта и текущие значения его свойств.

Код: Выделить всё

For Each iCell In Range
    If iCell.Address = "$C$4" Then abc = 1
      Stop
Next

[Naeel Maqsudov]

Календарь в Excel с изменяемым годом, кто желает ...

http://avsha.narod.ru/Date2006.rar

Примечание:
в справке по функции КОНМЕСЯЦА написано ...

Если эта функция недоступна, следует установить надстройку «Пакет Анализа», а затем подключить его с помощью команды Надстройки меню Сервис.

[Naeel Maqsudov]

Книга Excel для сравнения тарифов при оплате по времени, например для модемного подключения к Internet.

http://avsha.narod.ru/ExcelTarif.rar

используются встроенные возможности Excel -
меню Данные\Проверка\Список
меню Формат\Условное Форматирование...

[Naeel Maqsudov]

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

У UserForm1 свойство StartUpPosition должно быть = 0 (Manual)

Код: Выделить всё

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  Dim b As CommandBar
  dx = 0: dy = 0
  For Each b In Application.CommandBars
    If b.Visible Then
      Select Case b.Position
       Case msoBarLeft: dx = dx + b.Width
       Case msoBarMenuBar, msoBarTop: dy = dy + b.Height
      End Select
    End If
  Next
  With Target.Application.ActiveWindow
    UserForm1.Left = (Target.Left - .VisibleRange.Left) * .Zoom / 100 + .Application.Left + dx
    UserForm1.Top = (Target.Top - .VisibleRange.Top) * .Zoom / 100 + .Application.Top + dy
    UserForm1.Show
  End With
End Sub

Тут сначала вычисляется размер области, занимаемой тулбарами (DX,DY)
Ну а дальше дело техники:
1) минус .VisibleRange.Top\Left - убирает то что получилось в результате прокрутки листа
2) * .Zoom / 100 - учитывает масштаб листа (как оказалось это тоже надо учитывать)
3) и прибавить сдвиг самого окна, а затем размеры тулбаров

Надо конечно еще чуть-чуть добавить, так как от края окна до тулбаров есть еще толщина границы окна, но ее точное значение надо доставать функциями WinAPI (GetSystemMetrics, или что-то в этом роде, но это уже мелочи - можно пренебречь)

[Naeel Maqsudov]

Часто используемые шаблоны перечислений листов и ячеек для работы с листами по принципу: данные собираются из n-1 листов на лист n (сводный), либо обратные операции:

Код: Выделить всё

Option Explicit

' Перечисление рабочих листов книги
Private Sub EnumSheets()
    Dim wsh As Worksheet
    Dim i As Long
    Dim j As Long

    For i = 1 To ThisWorkbook.Worksheets.Count - 1
        Set wsh = ThisWorkbook.Worksheets(i + 1)
        wsh.Activate
        ' Ввод данных в множество листов
        ' wsh.Cells(15, 3).Value = Worksheets(i).Cells(i, 2).Value
        ' Вывод данных, собранных из разных листов
        ' Worksheets(i).Cells(i, 2).Value = wsh.Cells(15, 3).Value

        ' Поиск # строки, начиная с 6, во 2 столбце
        j = FindDown(wsh, "Итого", 6, 2)
        If j > 0 Then
            ' Работа со строкой, содержащей искомую строку
            ' wsh.Cells(j, 4).Value = 0.58
        End If
    Next i

    Set wsh = Nothing
End Sub

' Поиск значения Вниз
Private Function FindDown(Sheet As Worksheet, Value As Variant, Optional Start As Long = 1, Optional Column As Long = 1) As Long
    Dim j As Long
    FindDown = 0
    j = Start
    Do While Not Sheet.Cells(j, Column).Value = Empty
        If Sheet.Cells(j, Column).Value = Value Then
            FindDown = j
            Exit Function
        End If
        j = j + 1
    Loop
End Function

[Naeel Maqsudov]

Как сделать кнопку переключения стиля отображения ссылок A1 / R1C1?

Делается все очень просто. Описание по шагам:
1. Выбираем меню Сервис - Макрос - Начать запись... Появится окно "Запись макроса".
2. Пишем осмысленное имя макроса "ChangeReferenceStyle", выбираем в списке "Сохранить в:" пункт "Личная книга макросов", жмем "Ok", потом на "Stop" (кнопка в виде квадратика, останавливаем запись макроса).
3. Нажимаем Alt+F8, в окне "Макрос" выбираем "PERSONAL.XLS!ChangeReferenceStyle" и кнопкой "Войти" попадаем в редактор VBA, там нажимаем "Stop" (кнопка-квадратик в панели инструментов), чтобы прекратить отладку. Убераем из тела процедуры макроса ChangeReferenceStyle все-все и вставляем код:

Код: Выделить всё

If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If

После жмем "Сохранить" и закрываем редактор VBA.
4. Правой кнопкой мыши щелкаем над панелью инструментов и выбираем пункт "Настройка..." всплывающего меню. Как вариант: меню Сервис - Настройка...
5. В окне "Настройка" на вкладке "Команды" в списке выбираем пункт "Макросы", во втором списке справа появится пункт "Настраиваемая кнопка" с иконкой в виде смайлика - его и перетаскиваем себе на любую панель, куда нам нравится, где щелкать кнопку удобней.
6. Правой кнопкой мыши на кнопке со смайликом на выбранной панели и выбираем пункт всплывающего меню "Назначить макрос" - выбираем наш макрос, затем "Ok", закрываем и окно "Настройка".
7. Для пущей красоты можно изменить иконку или нарисовать свою собственную иконку (как это сделал автор) - для этого в Excel 2003 есть даже редактор иконок! Пока открыто окно "Настройка" щелкаем правой кнопкой мыши на нашей кнопке и выбираем "Изменить значок на кнопке", после чего попадаем в окно "Редактор кнопок".

Вот так мы сделали удобную кнопку для быстрой работы. Кстати, макросу для быстроты можно еще и горячую клавишу назначить.