Нумерация строк.

[marina]

Здравствуйте форумчане. Долго искала макрос для нумерации строк в Сalc LO и на этом форуме нашла, но этот макрос мне не нравиться. Если бы кто-то, имея свободное время, переделал для меня макрос из Excel для LO, было бы очень здорово. Во вложении файл с макросом с этого форума, а вот макрос из Excel, который желательно переписать для LO.

Код Выделить Развернуть

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Sh As Worksheet, rCell As Range, lCount&
    Application.EnableEvents = False
    For Each Sh In Sheets
    lCount = 0
        For Each rCell In Sh.Range("B6", Sh.Cells(Rows.Count, "B").End(xlUp))
            If Not IsEmpty(rCell) Then lCount = lCount + 1: rCell.Offset(0, -1) = lCount
        Next rCell
    Next Sh
    Application.EnableEvents = True
End Sub

Всем спасибо.

[rami]

Приведённый вами макрос из Excel будет правильно работать если в редакторе макросов в первой строке (перед всеми макросами) записать:

Код Выделить Развернуть

Option VbaSupport 1Это означает поддержку (частичную, но для вашего случая подойдёт) макросов Excel

[marina]

Option VbaSupport 1

про это я знаю, но хотелось бы на родном языке OL.

[JohnSUN]

Ну, на родном языке будет как-то так (можно было записать и короче, и с меньшим количеством переменных, но хотелось показать сам принцип работы с ячейками и их значениями)

[marina]

Ну, на родном языке будет как-то так

. Все хорошо, но при удалении строки, внутри заполненного диапазона, не происходит пересчет нумерации. Можно ли это исправить ?

[rami]

Все хорошо, но при удалении строки, внутри заполненного диапазона, не происходит пересчет нумерации. Можно ли это исправить ?

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

[mikekaganski]

С позволения JohnSun, вот ещё вариант.

Правда, почему-то при первом изменении после смены листа не срабатывает.

[economist]

Почему бы не делать "неубиваемую" нумерацию простой формулой:

=СМЕЩЕНИЕ(B7;-1;0)+1

Данная формула "не портится" из-за того что не ссылается на строку выше, а значит её удаление не приведет к ошибке вида REF#/ССЫЛКА#.

Вставлять строки нужно копированием имеющихся (чтобы не форматировать потом вручную), тогда и нумерация сохранится. 

[bsi]

=СМЕЩЕНИЕ(B7;-1;0)+1

Всем добрый день. Что-то с формулой не получается. Что у меня не так? Файл во вложении.

[rami]

Что-то с формулой не получается.

Формулу надо записывать в ту ячейку, которая указана в формуле (B7) и растягивать вниз. Но это уже совсем другая история...

[JohnSUN]

Правда, почему-то при первом изменении после смены листа не срабатывает.

Вэри найс! Идея повесить событие не на весь лист, а только на колонку, ради нумерации которой вся эта бодяга и затевалась - это хорошо, это душевно. Я-то думал, что marina попросит уточнить что да как и тогда-то в основном обработчике появятся и анализ изменённого диапазона (вторая колонка иди где-то в другом месте), и чтение двух колонок за один раз, и прочее... Не попросила
А вот взглюкивание на первых изменениях, которые наотрез отказывались запускать макрос - озадачило.
Попробовал внести две правки - перецепил .AddModifyListener прямо на вторую колонку, а не на её копию в массиве (мало ли что могло потеряться при копировании) и добавил в onLoaded еще одну строку

Код Выделить Развернуть

ThisComponent.setViewData(ThisComponent.getViewData())
Уж не знаю, что из этого страшного шаманского камлания помогло, но вроде бы заработало.

(ЗЫ. И не скромничай в следующий раз - дописывай себя в первые строки макроса  )

[bsi]

Вэри найс!

. А для начинающих файл с исправлениями можно в студию?

[mikekaganski]

JohnSUN - спасибо.

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

Первоначально я намеревался вообще взять диапазон ячеек (чтобы заодно не реагировать на первые пять строк). И это работало для, например, 10000 ячеек. Но, во-первых, этот подход потребовал бы константы в качестве индекса последней ячейки - а эстетствующий перфекционизмчувство прекрасного протестует, ещё и говорит о маловероятном, но не исключённом изменении числа поддерживаемых строк. А во-вторых, это блин не заработало при последнем индексе 1024*1024-1 (и даже -2 )

Там, конечно, не хватает обработки случая добавления нового листа. - как всегда, JohnSUN the best.

А вообще я никак не могу понять, зачем по событию проходить все листы. - как всегда, JohnSUN the best. Повторенье - мать ученья

[JohnSUN]

Ну, вот так это выглядит:

Код Выделить Развернуть

REM  *****  BASIC  *****
REM © Vladislav Orlov aka JohnSUN, Kiev, Ukraine, 2017 mailto:johnsun@i.ua
REM Edited Mike Kaganski, Khabarovsk mailto:mikekaganski@hotmail.com
REM
Option Explicit ' Все переменные объявляются явно через "Dim".
Option Base 0 ' Индексация каждого массива начинается с нуля

' ----------------------------------------------- XEventListener ------------------------------------------------------
' ------------------------ See https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=10123 ------------------------
Global oXModifyListener As Object

Sub sStartXModifyListener
Dim oSheets As Variant ' Все листы текущей книги
Dim i As Long
Rem Если слушатель события уже существует, отменить слушание
Rem Это может понадобиться, например, при добавлении новых листов в книгу
If Not isNull(oXModifyListener) Then sStopXModifyListener
Rem Пересоздаем слушателя
oXModifyListener=CreateUnoListener("ColumnBChanged_", "com.sun.star.util.XModifyListener")
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1 ' Назначаем колонкам B на всех листах этого слушателя события
oSheets.getByIndex(i).getColumns().getByIndex(1).AddModifyListener(oXModifyListener)
Next i
End Sub

Rem Останавливаем слушание на всех листах
Sub sStopXModifyListener
Dim oSheets As Variant ' Все листы текущей книги
Dim i As Long
On Error Resume Next ' Есть слушатель или нет - обработчик ошибок не даст макросу остановиться
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1
   oSheets.getByIndex(i).getColumns().getByIndex(1).removeModifyListener(oXModifyListener)
Next i
On Error GoTo 0
End Sub

Rem Эту процедуру мы никогда не будем выполнять, но она необходима для самого офиса
Rem https://wiki.openoffice.org/wiki/Documentation/DevGuide/ProUNO/Basic/Listeners
Sub ColumnBChanged_Disposing(oEvent)
  sStopXModifyListener
End Sub

Rem При открытии книги выполним этот макрос и слушатель начнет отслеживать изменения в колонках B
Sub onLoaded(oEvent)
   sStartXModifyListener()
Rem Подтолкнем контроллер к пониманию, что что-то в этой жизни изменилось
   ThisComponent.setViewData(ThisComponent.getViewData())
End Sub

Rem И собственно нумерация
Rem Если эта процедура была вызвана, то одна или несколько ячеек в колонке B были изменены
Rem Мы не станем пересчитывать нумерацию на ВСЕХ листах книги, сделаем это только для текущего листа
Rem Остальные листы будут перенумерованы, когда что-то изменится у них
Sub ColumnBChanged_Modified(oEvent As Variant)
Dim oSheet As Variant ' Текущий лист
Dim oCursor As Variant ' Курсор поможет определить, сколько строк на листе заполнено
Dim nEndRow As Long ' Номер последней не пустой строки
Dim i As Long, cnt As Long
Dim oCellRange As Variant ' Диапазон ячеек - при чтении это будут ячейки колонки B с 6-ой строки и до последней
Rem а при записи - ячейки колонки А (для номеров). Другими словами, макрос не меняет ни одного символа в B
Dim oData As Variant ' Содержимое этого диапазона как массив в памяти - так быстрее, чем обращаться к каждой ячейке
Rem Мы могли бы получить текущий лист прямо из oEvent, но это было бы довольно долго:
Rem cначала нужно было бы определить имеем мы дело с одной ячейкой или несколькими диапазонами...
Rem CurrentController нам сразу скажет "активный лист вот этот"
oSheet = ThisComponent.getCurrentController().getActiveSheet()
Rem Да, метод экселевского диапазона в VBA .End(xlUp) записывается немного лаконичнее
Rem Вот только не всегда возвращает, то что нужно
Rem Здесь эти три строки всегда работают правильно
oCursor = oSheet.createCursor() ' Создали курсор для личта
oCursor.gotoEndOfUsedArea(False) ' Прыгнули в последнюю занятую ячейку
nEndRow = oCursor.getRangeAddress().EndRow ' Узнали номер последней заполненной строки
Rem По условию задачи нумеруемые значения начинаются с 6-ой строки.
Rem Поэтому если ниже 5-ой строки ничего нет, то и делать ничего не нужно
If (nEndRow < 5) Then Exit Sub
oCellRange = oSheet.getCellRangeByPosition(1, 5, 1, nEndRow)
Rem "Грязный хак" - считываем не значения ячеек, а формулы из них
Rem Это гарантированно вернет нам массив строк, а не вариантов, и проверка значений будет очень простой
oData = oCellRange.getFormulaArray()
cnt = 0 ' Номера строк
For i = LBound(oData) To UBound(oData) ' Перебираем сверху вниз массив и проверяем колонку с ФИО на "пустую строку"
If Trim(oData(i)(0))="" Then ' Конечно, если в ячейке записана формула ="", то с одной стороны
Rem она вроде как пустая и нумероваться не должна. А с другой стороны, как же не перенумеровать человека с таким ФИО?
Rem  Для пустых ячеек номера стираем (заменяем текст формулы на пустую строку)
oData(i)(0) = ""
Else ' А не пустые ячейки - нумеруем
cnt = cnt + 1
oData(i)(0) = CStr(cnt) ' Важно! Не сам счетчик cnt, а его текстовое представление! Формула должна быть строкой
EndIf
Next i
Rem Переопределяем диапазон для колонки A и записываем номера
oCellRange = oSheet.getCellRangeByPosition(0, 5, 0, nEndRow)
oCellRange.setFormulaArray(oData)
End Sub


это блин не заработало при последнем индексе 1024*1024-1 (и даже -2 )

Я уж подумал, что мы с тобой баг нарыли... Но, кажется, всё в порядке - ты просто не дождался результата: каким бы быстрым не был перебор массива, 1024*1024 это всё-таки до фига и требует времени...
Ну, тут уже начинается оптимизация по скорости, это отдельный разговор... Будем надеяться, что компания, у которой списки сотрудников занимают целый лист Calc'овской книги, не станут заморачиваться нумерацией с помощью макроса

[mikekaganski]

If isNull(oXModifyListener) Then sStopXModifyListener - мне кажется, опечатка (пропущен Not).