Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Office Новый топик    Ответить
 Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
Здравствуйте.

Есть защищенный лист. Данных очень много. Надо получить область со всеми непустыми ячейками. Значения ячеек доступны макросу, но функция SpecialCells вылетает с ошибкой (из-за защиты). Как быть? Снять защиту не предлагать :)

Интересно даже, почему SpecialCells не работает? Ведь обычным циклом я могу собрать эти ячейки. Но конечно хочется побыстрее.
1 сен 17, 15:57    [20765023]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Cursky
Member

Откуда:
Сообщений: 153
Сын вождя,
ActiveSheet.UsedRange
пробовал?
1 сен 17, 16:11    [20765046]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
Cursky
Сын вождя,
ActiveSheet.UsedRange
пробовал?

Попробуй мне быстро найти непустые ячейки здесь:
Dim SH As Worksheet
    Set SH = Excel.Workbooks.Add.Sheets(1)
    SH.Cells(1, 1).Value = 1
    SH.Cells(SH.Rows.Count, SH.Columns.Count).Value = 2
    SH.UsedRange.Select ' :))))))))))))
1 сен 17, 16:37    [20765170]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
Сын вождя,

Не знаю на сколько быстро/ресурсозатратно, но что если весь лист в массив и уже проверять массив...ну или сначало UsedRange, а потом массив...как-то так.
1 сен 17, 18:28    [20765480]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
iMrTidy
Не знаю на сколько быстро/ресурсозатратно, но что если весь лист в массив и уже проверять массив...

Да, руками можно что-то сделать. Но функции Excel работают очень быстро. Вот и хочу найти подходящие.

Та же сортировка массива, в несколько раз быстрее через лист Excel, чем тасованием массива макросом.

CountA и CountBlank выполняются мгновенно, независимо от объема данных. Значит проблема в SpecialCells.
1 сен 17, 20:03    [20765671]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
Сын вождя,

A1 = 1, A1048576 = 2

Option Explicit

Sub Test()

Dim wb As Workbook
Dim sh As Worksheet
Dim rn As Range
Dim arr() As Variant
Dim k As Long

Set wb = ThisWorkbook
Set sh = wb.Sheets(1)
Set rn = sh.UsedRange

arr = rn

For k = 1 To UBound(arr)
    If arr(k, 1) <> "" Then
        Debug.Print arr(k, 1)
    End If
        
Next

End Sub


Работает очень быстро.
1 сен 17, 23:57    [20766236]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
iMrTidy
...Работает очень быстро.

Быстро, но не работает :)

При копировании области в массив, Excel имеет ограничение в 65536 ячеек. В последней версии Excel, ячеек 16 777 216.

Нужны не только значения, а еще и область с ячейками. Нужна альтернатива, сравнимая по скорости с SpecialCells(xlCellTypeConstants).
2 сен 17, 07:45    [20766319]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
Сын вождя,

Когда речь заходит о работе софта на пределе его возможностей, невольно приходит в голову мысль, а правильно ли выбран инструмент...
2 сен 17, 11:36    [20766528]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
iMrTidy
...а правильно ли выбран инструмент...

Инструмент устраивает. Не вижу подходящего инструмента этого инструмента :)

Тестирую на словаре в 200 000 слов. Ничего особенного.
2 сен 17, 12:09    [20766574]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
Сын вождя
iMrTidy
...а правильно ли выбран инструмент...

Инструмент устраивает. Не вижу подходящего инструмента этого инструмента :)

Тестирую на словаре в 200 000 слов. Ничего особенного.


SQL?
2 сен 17, 12:32    [20766612]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
ZVI
Member

Откуда: Sevastopol
Сообщений: 428
Сын вождя
Есть защищенный лист... функция SpecialCells вылетает с ошибкой (из-за защиты). Как быть? Снять защиту не предлагать

Достаточно (пере)защитить с UserInterfaceOnly:=True
Sub Test()
  With ActiveSheet
    [b].Protect UserInterfaceOnly:=True[/b] ', Password:="123"
    Debug.Print .UsedRange.SpecialCells(xlCellTypeConstants).Count
  End With
End Sub
3 сен 17, 01:14    [20767647]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
ZVI
Member

Откуда: Sevastopol
Сообщений: 428
Тэги и в предыдущем сообщении лишние:
Sub Test()
  With ActiveSheet
    .Protect UserInterfaceOnly:=True ', Password:="123"
    Debug.Print .UsedRange.SpecialCells(xlCellTypeConstants).Count
  End With
End Sub
3 сен 17, 01:16    [20767651]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
ZVI
...(пере)защитить...

Это снятие защиты, а там может быть пароль. Пишется макрос для общего пользования.

Для работы, макросу требуются значения ячеек и их область. Все это макросу доступно при любой защите (поправьте, если есть защита, когда макросом ничего не считать). Но некоторые функции Excel, которые ничего не меняют на листе, почему-то не работают :(

Может кто-то составлял список, что работает в защите, а что нет? Логики я не вижу. Проверить можно только опытным путем :(
3 сен 17, 14:24    [20768094]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
Сын вождя,

Учитывая, что любая защита снимается в течение секунды, можно делать временную копию исходника, снимать на нем все защиты, делать все что нужно, а затем уничтожать.
3 сен 17, 18:14    [20768254]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
ZVI
Member

Откуда: Sevastopol
Сообщений: 428
Сын вождя
Это снятие защиты, а там может быть пароль. Пишется макрос для общего пользования.

В предложенном варианте нет снятия защиты ни на мгновение: .Protect UserInterfaceOnly:=True ', Password:="123"
Предполагается, что лист уже был защищен, а этот код повторной защиты открывает полный доступ лищь для VBA, но не для ручных действий пользователя.
Понятно, что если лист защищен паролем, а пароль коду неизвестен, то предложенный вариант не "прокатит", что вообще-то и правильно.
5 сен 17, 00:43    [20771354]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
Сын вождя
Member

Откуда: mtdmacro.ru
Сообщений: 155
[quot ZVI]
Сын вождя
...В предложенном варианте нет снятия защиты ни на мгновение...

Ага, а книгу почему-то предлагает сохранить, как измененную :)

Народ, речь не о снятии защиты вообще. Нужен алгоритм быстрой обработки листа под защитой. Без извращений. Стандартными средствами. Как я понял, таких в Excel нет. Остается оптимизировать костыли.
5 сен 17, 11:34    [20772067]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
iMrTidy
Member

Откуда:
Сообщений: 565
[quot Сын вождя]
ZVI
пропущено...

Ага, а книгу почему-то предлагает сохранить, как измененную :)

Народ, речь не о снятии защиты вообще. Нужен алгоритм быстрой обработки листа под защитой. Без извращений. Стандартными средствами. Как я понял, таких в Excel нет. Остается оптимизировать костыли.


Сохранить предлагает, потому что раньше защита распространялась еще и на доступ к ячейкам через VBA, а теперь только через интерфейс. После того, как данные обработаны, поменяйте флаг на исходный.
5 сен 17, 13:04    [20772511]     Ответить | Цитировать Сообщить модератору
 Re: Альтернатива SpecialCells для защищенного листа  [new]
ZVI
Member

Откуда: Sevastopol
Сообщений: 428
Сын вождя
Ага, а книгу почему-то предлагает сохранить, как измененную :)

Это не причем. Много чего меняет свойство Saved книги.
Например, любая формула динамического диапазона типа =A1:ИНДЕКС(A:A;СЧЁТЗ(A:A)) пересчитывается при загрузке книги и Excel спросит о сохранении книги с такой формулой. И это никак не связано с защитой.
Если смущает, то ActiveWorkbook.Saved = True
Sub Test1()
  Dim IsSaved As Boolean
  ' Запомнить статус Saved книги
  IsSaved = ActiveWorkbook.Saved
  ' Защитить с доступом из VBA
  With ActiveSheet
    .Protect UserInterfaceOnly:=True ', Password:="123"
    Debug.Print .UsedRange.SpecialCells(xlCellTypeConstants).Count
  End With
  ' Восстановить статус Saved книги
  ActiveWorkbook.Saved = IsSaved
End Sub
6 сен 17, 01:29    [20774208]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Office Ответить