Ошибка при вызове метода контекста specialcells

26.02.10 — 14:53

1 — 26.02.10 — 14:55

С чего это такая уверенность, что «Нашел Код номенклатуры»?

2 — 26.02.10 — 14:56

А кто будет проверять нашелся товар по коду или нет?

3 — 26.02.10 — 15:07

(2) а как?

4 — 26.02.10 — 15:09

Товар.ПустаяССылка()

5 — 26.02.10 — 15:09

СП:

Пример:

СтрокаКода = «840»;

Валюты = Справочники.Валюты;

НайденнаяСсылка = Валюты.НайтиПоКоду(СтрокаКода);

Если НайденнаяСсылка = Валюты.ПустаяСсылка() Тогда

   Сообщить(«Валюты «»» + СтрокаКода + «»» еще нет»);

КонецЕсли;

6 — 26.02.10 — 15:10

(5)полный пипец

7 — 26.02.10 — 15:11

(6) Это пример из СП.

Полный пипец — это (4).

Если уж так, как ты предлагаешь проверять, то: Товар.Пустая()

8 — 26.02.10 — 15:11

(4), (6) а как надо правильно, применительно к моему коду в (0)???

9 — 26.02.10 — 15:12

(8) Ну-ну…

10 — 26.02.10 — 15:14

(7)ну да согласен … затупил с сылкой

11 — 26.02.10 — 15:16

12 — 26.02.10 — 15:22

сойдет

13 — 26.02.10 — 15:36

14 — 26.02.10 — 15:41

15 — 26.02.10 — 15:57

up!

16 — 26.02.10 — 16:00

SpecialCells(11)

17 — 26.02.10 — 16:01

18 — 26.02.10 — 16:01

хотя …

нафига Excel.Cells(2,1).SpecialCells(21).Row

просто 1

19 — 26.02.10 — 16:05

20 — 26.02.10 — 16:08

(19) это не колонка

21 — 26.02.10 — 16:08

(20) а что?

22 — 26.02.10 — 16:09

открой для себя справку по VBA )

23 — 26.02.10 — 16:11

24 — 26.02.10 — 16:11

может надо было указать 12 ?
xlCellTypeVisible. All visible cells    12

25 — 26.02.10 — 16:11

или 4 ?

26 — 26.02.10 — 16:12

а вообще (14) сильно корявый код хоть и рабочий частично

Книга = Excel.Workbooks.Open(ИмяФайла);

Лист = Книга.Sheets(НомерЛиста);

НовыйРеквизит = СокрЛП(Лист.Cells(а,НомерКолонкиНовогоРеквизита).Value);

ФайлКолонок = Лист.SpecialCells(11).Column;

и т.д. и т.п.

27 — 26.02.10 — 16:12

(22) я смотрю ты открывать то умеешь, вот только пользоваться не очень

28 — 26.02.10 — 16:13

(23) XlCellType <> номер колонки? ))

29 — 26.02.10 — 16:15

))

30 — 26.02.10 — 16:18

(28) да я вообще VBA в глаза не видел ….
раза 2 за 10 лет открывал, откуда мне знать? я просто предположил..

31 — 26.02.10 — 16:18

и кстати угадал

тебе виднее )

Здравствуйте! Подскажите, пожалуйста! Загружаю из Excel данные, хочу обратиться к именованной области, выдает следующую ошибку: «Ошибка при вызове метода контекста (Cells): Произошла исключительная ситуация (0x800a03ec)
ФайлСтрок = Excel.Cells(2,1).SpecialCells(21).Row;
по причине:
Произошла исключительная ситуация (0x800a03ec)»

Процедура ОсновныеДействияФормыЗагрузить(Кнопка)

НомерКолонкиАртикул = ЭлементыФормы.ТабличныйДокумент.Область(«R2C1»;
НомерКолонкиНаименованияТовара = ЭлементыФормы.ТабличныйДокумент.Область(«R2C2»;
НомерКолонкиЕдиницаИзмерения = ЭлементыФормы.ТабличныйДокумент.Область(«R2C3»;
НомерКолонкиСтрана = ЭлементыФормы.ТабличныйДокумент.Область(«R2C4»;

//В разных версиях Excel получаются по-разному, поэтому сначала определим версию Excel
Excel = новый COMОбъект(«Excel.Application»;

Версия = Лев(Excel.Version,Найти(Excel.Version,».»-1);
Если Версия = «8» тогда
ФайлСтрок = Excel.Cells.CurrentRegion.Rows.Count;
ФайлКолонок = Макс(Excel.Cells.CurrentRegion.Columns.Count, 13);
Иначе
ФайлСтрок = Excel.Cells(2,1).SpecialCells(21).Row;
ФайлКолонок = Excel.Cells(2,1).SpecialCells(21).Column;
Конецесли;

// Выбираем данные из файла
Для а = Excel.Cells(2,1).SpecialCells(21).Row по ФайлСтрок Цикл

//Полуим данные из соответсвующих ячеек
Артикул = СокрЛП(Excel.Cells(а,Артикул).Value);
НаименованиеТовара = СокрЛП(Excel.Cells(а,НомерКолонкиНаименованияТовара).Value);
ЕдиницаИзмерения = СокрЛП(Excel.Cells(а,НомерКолонкиЕдиницаИзмерения).Value);

Товар = Справочники.Номенклатура.ПустаяСсылка();

// Ищем товар в справочнике по коду
Товар = Справочники.Номенклатура.НайтиПоКоду.Артикул;

// Если не нашли по коду, то ищем по наименованию
Если Товар.Пустая() Тогда
Товар = Справочники.Номенклатура.НайтиПоНаименованию.Наименование;
Конецесли;

//Если не нашли создаем новый
Если Товар.Пустая() Тогда
Товар = Справочники.Номенклатура.СоздатьЭлемент();
Товар.Наименование = НаименованиеТовара;
Товар.Артикул = Артикул;
Товар.БазоваяЕдиницаИзмерения = ЕдиницаИзмерения;
Товар.СтранаПроисхождения = НомерКолонкиСтрана;
Товар.Записать();
Конецесли;
КонецЦикла;

КонецПроцедуры

Похожие темы (5)

Если ваш макрос выдаёт ошибку при использовании метода SpecialCells — возможно, причина в установленной защите листа Excel.

Почему разработчики Microsoft отключили работу этой функции на защищённых листах — не совсем понятно, но мы попробуем обойти это ограничение.

Итак, нам надо получить все заполненные ячейки из некого диапазона листа Excel. 

Обычно для этого используется вызов метода SpecialCells — например,

msgbox Range("a2:d8").SpecialCells(xlCellTypeConstants).Address

Но на защищенном листе такой код выдаст ошибку 1004.

Чтобы избавиться от ошибки, мы используем функцию SpecialCells_TypeConstants — замену встроенному методу SpecialCells(xlCellTypeConstants)

Function SpecialCells_TypeConstants(ByRef ra As Range) As Range
    ' возвращает диапазон, содержащий все заполненные ячейки диапазона ra
    On Error Resume Next: en& = Err.Number
    If ra.Worksheet.ProtectContents Then    ' если лист защищён
        Dim cell As Range
        ' перебираем все ячейки в диапазоне
        For Each cell In Intersect(ra, ra.Worksheet.UsedRange).Cells
            If Trim(cell.Value) <> "" Then    ' если ячейка непустая
                ' то добавляем её в результат
                If SpecialCells_TypeConstants Is Nothing Then
                    Set SpecialCells_TypeConstants = cell
                Else
                    Set SpecialCells_TypeConstants = Union(SpecialCells_TypeConstants, cell)
                End If
            End If
        Next cell
 
    Else    ' если защита листа не установлена - используем штатные средства Excel
        Set SpecialCells_TypeConstants = ra.SpecialCells(xlCellTypeConstants)
    End If
    If en& = 0 Then Err.Clear
End Function

Теперь наш код (работающий в т.ч. и на защищённых листах) будет выглядеть так:

msgbox SpecialCells_TypeConstants(Range("a2:d8")).Address

Аналогичная функция, если нам надо получить диапазон видимых (нескрытых) строк на листе Excel: 

(замена для SpecialCells(xlCellTypeVisible))

Function SpecialCells_VisibleRows(ByRef ra As Range) As Range
    On Error Resume Next: en& = Err.Number
    If ra.Worksheet.ProtectContents Then
        Dim ro As Range
        For Each ro In Intersect(ra, ra.Worksheet.UsedRange.EntireRow).Rows
            If ro.EntireRow.Hidden = False Then
                If SpecialCells_VisibleRows Is Nothing Then
                    Set SpecialCells_VisibleRows = ro
                Else
                    Set SpecialCells_VisibleRows = Union(SpecialCells_VisibleRows, ro)
                End If
            End If
        Next ro
    Else
        Set SpecialCells_VisibleRows = ra.SpecialCells(xlCellTypeVisible)
    End If
    If en& = 0 Then Err.Clear
End Function

Хитрости »

3 Январь 2019              2851 просмотров

Прежде чем читать далее необходимо знать что такое функция пользователя(UDF) и как её создать. Узнать про это можно из статьи: Что такое функция пользователя(UDF)?

Если кратко, то UDF это Ваша собственная функция для вызова её с листа(как и остальные функции Excel). Пишется UDF на встроенном в Excel языке программирования Visual Basic for Applications. UDF способны дополнить и расширить и без того немалый перечень встроенных функций Excel, но есть у UDF и ограничения. Например, они не могут изменять значения других ячеек, форматы ячеек(с некоторыми недокументированными отступлениями), а так же выделять ячейки(методами Select, Application.GoTo и т.п.). Если с изменением значений и форматов ячеек и выделением все более-менее понятно, то некоторые ограничения кажутся больше невменяемыми, чем интуитивно понятными. О них и пойдет речь в статье.
И для детального разбора мы возьмем указанные в заголовке методы, как наиболее часто используемые и многим понятные.

SpecialCells

Для определения последней заполненной ячейки на листе часто используется метод SpecialCells(читать подробнее про определение последней строки). Но он может быть использован и для определения только тех ячеек, которые содержат примечания, проверки данных, только пустые ячейки или только видимые и т.д. И это предоставляет разработчику VBA очень неплохой инструмент для быстрого отбора нужных ячеек. Но у этого метода есть свои недостатки. Например, он не работает на защищенных листах(если конечно, мы не применили трюк с защитой только от пользователя, но не от макроса). Хотя в случае защищенного листа VBA честно скажет сообщением об ошибке в момент выполнения. Однако, при использовании метода SpecialCells именно из UDF — VBA не выдаст никакой ошибки, а вернет результат. Правда, не тот, который ожидался. Возьмем код ниже:

Function UDF_SpecCells_LastCell()
    Dim rr As Range
    Set rr = Cells.SpecialCells(xlCellTypeLastCell)
    UDF_SpecCells_LastCell = rr.Address
End Function

Если выполнить эту функцию напрямую из VBA, то UDF_SpecCells_LastCell вернет корректный адрес одной конкретной ячейки — последней(путь это будет $X$34). Но если выполнить эту функцию, записав в любую ячейку листа =UDF_SpecCells_LastCell(), то функция вернет адрес ячеек всего листа — $1:$1048576. При этом даже защита листа в этом случае не будет помехой. Все потому, что сам метод SpecialCells по факту даже не выполняется, а просто игнорируется и итогом будет адрес родительского объекта — Cells.
Как же из UDF получить адрес последней ячейки?
Адрес последней ячейки можно узнать и другим способом, который точно не даст осечек — можно использовать объект UsedRange:

Function UDF_LastCell()
    Dim lr As Long, lc As Long
    With Application.Caller.Parent 'обращаемся к листу, с которого вызвана функция
        'номер последней строки
        lr = .UsedRange.Row + .UsedRange.Rows.Count - 1
        'номер последнего столбца
        lc = .UsedRange.Column + .UsedRange.Columns.Count - 1
    End With
    'собираем из номера строки и столбца адрес
    UDF_LastCell = Cells(lr, lc).Address
End Function

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

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: UDF_GetCommentCells
'          Функция возвращает адрес ячеек, содержащих комментарии
'          rr - необязательный. Ссылка на диапазон, в котором надо найти примечания
'               если не указан - берутся все ячейки листа
'---------------------------------------------------------------------------------------
Function UDF_GetCommentCells(Optional rr As Range)
    Dim rAll As Range, rc As Range, rCmnts As Range
    If rr Is Nothing Then
        Set rAll = Application.Caller.Parent.UsedRange
    Else
        Set rAll = rr
    End If
    For Each rc In rAll
        'если в ячейке есть примечание
        If Not rc.Comment Is Nothing Then
            'собираем все ячейки в один диапазон
            If rCmnts Is Nothing Then
                Set rCmnts = rc
            Else
                Set rCmnts = Union(rCmnts, rc)
            End If
        End If
    Next
    UDF_GetCommentCells = rCmnts.Address
End Function

Собственно, такой подход можно применять для поиска и других типов ячеек. Для только видимых надо будет применять проверку каждой на Rows.Hidden и Columns.Hidden, для получения только ячеек с формулами — hasFormula. Для получения ошибочных — IsError(Cell) и т.д. Но в любом случае это будет в разы медленнее, чем SpecialCells.

FindNExt

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

Function FindAllValues()
    Dim s As String, firstAddress As String, c As Range
    With Range("E:E")
        Set c = .Find(2, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                s = s & c.Address & "; "
                Set c = .FindNext(c)
                If Not c Is Nothing Then
                    If firstAddress = c.Address Then
                        Exit Do
                    End If
                End If
            Loop While Not c Is Nothing
        End If
    End With
    FindAllValues = s
End Function

Чтобы проверить работу этой функции запишите в столбец E(начиная с ячейки E1) ряд значений(в каждую ячейку по одной цифре): 1, 2, 3, 2, 5, 4, 7.
И опять — если выполнить эту функцию напрямую из VBA, то FindAllValues вернет адреса всех ячеек с указанным значением(2) — $E$2; $E$4; . Но если выполнить эту функцию, записав в любую ячейку листа =FindAllValues(), то функция вернет адрес только первой найденной ячейки — $E$2; . Проблема в том, что метод FindNext не выполняется и возвращает Nothing.
Так же как и в случае со SpecialCells заменить такой поиск можно только собственными усилиями. Я для примера могу приложить такой вот не оптимальный, но рабочий код:

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: UDF_FindValCells
'          Функция возвращает адрес ячеек, содержащих искомое значение
'          v  - искомое значение
'          rr - необязательный. Ссылка на диапазон, в котором надо найти значения
'               если не указан - берутся все ячейки листа
'---------------------------------------------------------------------------------------
Function UDF_FindValCells(v, Optional rr As Range)
    Dim rAll As Range, rc As Range, rVals As Range
     If rr Is Nothing Then
        Set rAll = Application.Caller.Parent.UsedRange
    Else
        Set rAll = rr
    End If
    For Each rc In rAll
        'если значение в ячейке равно искомому
        If rc.Value = v Then
            'собираем все ячейки в один диапазон
            If rVals Is Nothing Then
                Set rVals = rc
            Else
                Set rVals = Union(rVals, rc)
            End If
        End If
    Next
    UDF_FindValCells = rVals.Address
End Function

Быстрее поиск будет работать на массивах, но это уже другая история.

Еще несколько коварных методов

Так же проблемы возникнут с использованием таких методов как:

CurrentRegion

(определение всей прилегающей к активной ячейке таблицы. На листе можно вызывать нажатием сочетания клавиш

Ctrl

+

A

)),

CurrentArray

(определение всей области применения формулы массива),

ShowPrecedents

и

ShowDependents

(выделение зависимостей ячеек).

• CurrentRegion и CurrentArray при вызове из UDF всегда будут возвращать адрес активной ячейки
• ShowPrecedents и ShowDependents при вызове из UDF просто ничего не покажут

Аналогично игнорируются и все так называемые объекты окружения самого Excel. Например, методы изменения способа вычисления формул(Application.Calculation), изменение стиля ссылок(Application.ReferenceStyle), вид курсора(Application.Cursor) и многие другие. Т.е. получить текущее значение этих параметров можно, но вот изменить уже не получится.

Не баг, но все же — бяка в каком-то смысле.
В 2010 Excel у объекта Range появился новый метод:

DisplayFormat

. У него есть такие свойства как

Interior

(заливка ячейки),

Font

(шрифт ячейки),

Borders

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

Sub GetTrueCellColor()
    MsgBox ActiveCell.DisplayFormat.Interior.Color
End Sub

Стандартно, не применяя DisplayFormat, получить заливку или шрифт ячеек, окрашенных при помощи условного форматирования нельзя без танцев с бубном.
Так вот этот самый DisplayFormat вообще не работает при вызове из UDF листа. Мы просто получим в итоге ошибку #ЗНАЧ!(#Value!):

Function GetTrueCellColor(rcell As Range)
GetTrueCellColor = rcell.DisplayFormat.Interior.Color
End Function

Но здесь нет смысла обижаться — это документированная особенность метода DisplayFormat.

Зато функцией UDF можно добавить в ячейку примечание. Функция ниже прекрасно отработает как при вызове непосредственно из VBA, так и при вызове с листа при помощи записи в ячейку функции

=UDF_AddNewComment()

:

Function UDF_AddNewComment()
    Dim rr As Range
    Set rr = ActiveCell 'Application.Caller 'если надо добавить в ячейку с самой UDF
    rr.AddComment ("Привет от excel-vba.ru!")
End Function

Если Вы так же обнаружите методы, которые некорректно работают при вызове из UDF — делитесь в комментариях, соберем коллекцию 🙂

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

I have an Excel VBA macro that I run once a week. I have a piece of code that filters out for different data and then copies the remaining cells to a different worksheet

Here is the portion of effected code:

dim data as worksheet
dim sku vp as worksheet
Set skuvp = Workbooks("weekly Brand snapshot report.xlsx").Sheets("SKU VP")
set data = Workbooks("weekly Brand snapshot report.xlsx").Sheets("SKU Data")

data.Range("A1").AutoFilter Field:=4, Criteria1:="Foods", Operator:=xlFilterValues
data.Range("Onsales[[Product]]").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("B2")
skuvp.Range("foods").Sort key1:=skuvp.Range("C1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=4, Criteria1:="Treats", Operator:=xlFilterValues
data.Range("Onsales[[Product]]").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("H2")
skuvp.Range("treats").Sort key1:=skuvp.Range("I1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=3, Criteria1:="Hardgoods", Operator:=xlFilterValues
data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("N2")
skuvp.Range("hard").Sort key1:=skuvp.Range("O1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=3, Criteria1:="Specialty", Operator:=xlFilterValues
data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("T2")
skuvp.Range("spcl").Sort key1:=skuvp.Range("U1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

Data and skuvp are set as worksheets.

This code ran fine the very first time I ran it. However, it began having an error after that. The error appears on this line:

data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("N2")

The error it gives is «Unable to get the Specialcells property of the range class.»

I originally had the range in that code set the table column «Onsales[[Product]]» as the range like the previous 2 times I used the code but changed it to a set range to see if that would fix the issue.

Why is this code having an error on that line when the same basic code works a few lines earlier?

I’ve searched stackoverflow and other online sources for a solution without success.

I have an Excel VBA macro that I run once a week. I have a piece of code that filters out for different data and then copies the remaining cells to a different worksheet

Here is the portion of effected code:

dim data as worksheet
dim sku vp as worksheet
Set skuvp = Workbooks("weekly Brand snapshot report.xlsx").Sheets("SKU VP")
set data = Workbooks("weekly Brand snapshot report.xlsx").Sheets("SKU Data")

data.Range("A1").AutoFilter Field:=4, Criteria1:="Foods", Operator:=xlFilterValues
data.Range("Onsales[[Product]]").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("B2")
skuvp.Range("foods").Sort key1:=skuvp.Range("C1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=4, Criteria1:="Treats", Operator:=xlFilterValues
data.Range("Onsales[[Product]]").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("H2")
skuvp.Range("treats").Sort key1:=skuvp.Range("I1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=3, Criteria1:="Hardgoods", Operator:=xlFilterValues
data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("N2")
skuvp.Range("hard").Sort key1:=skuvp.Range("O1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

data.Range("A1").AutoFilter Field:=3, Criteria1:="Specialty", Operator:=xlFilterValues
data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("T2")
skuvp.Range("spcl").Sort key1:=skuvp.Range("U1"), order1:=xlDescending, Header:=xlYes
data.ShowAllData

Data and skuvp are set as worksheets.

This code ran fine the very first time I ran it. However, it began having an error after that. The error appears on this line:

data.Range("B2:B16354").SpecialCells(xlCellTypeVisible).Copy Destination:=skuvp.Range("N2")

The error it gives is «Unable to get the Specialcells property of the range class.»

I originally had the range in that code set the table column «Onsales[[Product]]» as the range like the previous 2 times I used the code but changed it to a set range to see if that would fix the issue.

Why is this code having an error on that line when the same basic code works a few lines earlier?

I’ve searched stackoverflow and other online sources for a solution without success.