Vba excel ошибка знач - Не ошибается лишь тот, кто ничего не делает!

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

Коллеги, доброго дня!

Не нагуглил решения, пардон, если повтор.
В общем: есть самописная VBA-функция, которая используется на листе. Код корректный, возвращает всё верно.

НО. Периодически в ячейках, где эта функция используется, появляется ошибка #ЗНАЧ! и убрать её можно только одним способом:
просто залезть в формулу ячейки в режиме редактирования и ввести её ещё раз (ничего не меняя!).
И ошибка исчезает.

Что делать? Ячеек-то много.

Юрий М

Модератор

Сообщений: 60762
Регистрация: 14.09.2012

Контакты см. в профиле

Алексей, Вам нужно решить некую задачу или выяснить, почему именно Ваша функция так себя ведёт? Если первое, то сформулируйте кратко саму задачу и предложите новое название темы — модераторы поменяют. В обоих случаях нужен небольшой файл-пример. Прикрепите его к первому сообщению
И не пишите через строку — зачем расятгивать сообщение?

Изменено: Юрий М — 17.12.2021 22:01:03

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

, за строки пардон — привычка. Моя функция не при чём (тут я уверен не 100%, уже не первый год в VBA), это конкретно поведение экселя. Т.е. даже смысла код приводить нет.
При вызове этой функции просто в коде VBA, или при отладке — функция никогда ошибок не даёт.

К слову — у меня есть пара проектов с совершенно разными функциями, но такой эффект (периодически!) проявляется в обоих.
Т.е. некоторые ячейки вдруг почему-то начинают показывать #ЗНАЧ, залезаешь в редактирование формулы ячейки, ничего не меняешь, нажимаешь ввод — формула пересчитывается без проблем.
Похоже на то, что эксель почему-то сам не пересчитывает эту функцию на листе, пока явно не введёшь формулу заново.
Как ещё точнее описать тему — не знаю

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

17.12.2021 22:15:03

Цитата
Алексей написал: Что делать? Ячеек-то много.

… а примера в теме нет ни одного.
Вполне возможно, что Ваши функции написаны Вами не совсем правильно.

Юрий М Модератор Сообщений: 60762 Регистрация: 14.09.2012 Контакты см. в профиле	Алексей, а на какой ответ Вы рассчитываете, не показав пример? Хотите, чтобы форумчание утроили гадание под Новый год? ))

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

17.12.2021 22:21:54

Цитата
написал: … а примера в теме нет ни одного.

Боюсь, прикрепляемый пример ничего не даст — эффект проявляется крайне нерегулярно и бессистемно (я пока отследить не смог).

Могу попытаться прикрепить видео, где в ячейке с формулой, где фигурирует ошибка «#ЗНАЧ», вводим

ту же самую

формулу (точнее просто открываем редактирование формулы и тупо жмём Enter), и ошибка исчезает…

Из интересного:
Поставил брейпоинт на вход функции, нажал «произвести вычисления» на открытом листе. Брейкпоинт не сработал.
Где-то явно какая-то настройка есть, о которой я не знаю.

PS Excel 2016.

Юрий М Модератор Сообщений: 60762 Регистрация: 14.09.2012 Контакты см. в профиле	Ребята, я вчера написал макрос. Так вот он, зараза, то правильно сработает, то ошибку выдаёт. Подскажите, в чём может быть проблема? Алексей, что бы Вы ответили на такой вопрос? Изменено: Юрий М — 17.12.2021 22:28:36

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

Юрий, я написал, что макрос

не выдаёт ошибок

. Он не пересчитывается экселем, т.е. не вызывается при пересчёте листа, хотя, по логике, должен.

Немного почитал теорию пересчёта листов/формул. Похоже, один из вариантов решения — Application.Volatile true, но всё равно странно. Попробую.

PS
На всякий. Похоже проблема в том, что UDF не пересчитываются, пока не изменится значение аргумента.
Отслеживать зависимости функций через VBA движок экселя не умеет.

Вот тема с вариантами решений:

https://superuser.com/questions/1261444/custom-formula-not-updating

Изменено: Алексей — 17.12.2021 22:48:41

Юрий М

Модератор

Сообщений: 60762
Регистрация: 14.09.2012

Контакты см. в профиле

17.12.2021 22:49:08

Цитата
Алексей написал: Юрий, я написал, что макрос не выдаёт ошибок.

Я могу переформулировать своё предыдущее сообщение, но смысл останется тот же.

Алексей Пользователь Сообщений: 10 Регистрация: 17.12.2021	Думаю, тему можно закрыть. Всем спасибо за активное участие

Юрий М Модератор Сообщений: 60762 Регистрация: 14.09.2012 Контакты см. в профиле	Алексей, опять Вы рвёте сообщение пустыми строками!!!

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

#12

17.12.2021 22:56:39

Цитата
написал: Алексей, опять Вы рвёте сообщение пустыми строками!!!

Я по пятницам всё делаю не так Уж простите.

Юрий М Модератор Сообщений: 60762 Регистрация: 14.09.2012 Контакты см. в профиле	А у меня к пятнице накопилась куча банов.

БМВ

Модератор

Сообщений: 21650
Регистрация: 28.12.2016

Excel 2013, 2016

#14

17.12.2021 23:00:12

Цитата
Алексей написал: (тут я уверен не 100%, уже не первый год в VBA)

а Excel c VBA уже десятилетия.

Цитата
Алексей написал: PSНа всякий. Похоже проблема в том, что UDF не пересчитываются, пока не изменится значение аргумента.Отслеживать зависимости функций через VBA движок экселя не умеет.

что за бред. Если не меняется значение аргумента, то и пересчет не нужен. Если вы накривокодили на не первом своем году так что даже боитесь показать ваше чудо творение, это не означает что Excel виноват.

По вопросам из тем форума, личку не читаю.

Юрий М

Модератор

Сообщений: 60762
Регистрация: 14.09.2012

Контакты см. в профиле

#15

17.12.2021 23:06:53

Миш, там есть опечатка, но она по делу:

Цитата
Алексей написал: я уверен не 100%

))

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

#16

17.12.2021 23:10:31

Блин, как в старые добрые времена

Цитата
написал: накривокодили

Дальше модераторы цепляются к разрывам строк и опечаткам. Чувствуется высокий профессионализм участников форума

Можете меня банить, смысла тут находиться не вижу. Ошибся форумам, ошибку признаю
Надувайте пузыри собственной значимости дальше

New Пользователь Сообщений: 4659 Регистрация: 06.01.2013	Покажите свой код, не стесняйтесь ) А то по вашим словам выходит — глючит Excel и ваш код не при чём) Изменено: New — 17.12.2021 23:21:31

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

#18

17.12.2021 23:22:01

Цитата
Алексей написал: Надувайте пузыри собственной значимости

Пока что Вы их надуваете, веря в свою непогрешимость.

Юрий М

Модератор

Сообщений: 60762
Регистрация: 14.09.2012

Контакты см. в профиле

#19

17.12.2021 23:28:16

Цитата
Алексей написал: Ошибся форумам,

Вы уже третий, кто так написал за все эти годы. И ни на одном форуме не привествуется отсутствие адекватной реакции на замечания модераторов.

Цитата
Алексей написал: ошибку признаю

Признали — следует исправить. Вместо «привычка», «по пятницам» следовало просто устранить замечание.

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

#20

17.12.2021 23:44:15

Тема старая, случай аналогичный, как и ещё 61 таких же (см. I have the same question 61)

https://answers.microsoft.com/en-us/msoffice/forum/all/custom-function-incorrectly-returns-value-in-…

Люди чинят чем попало.

— установка Volatile
— вызов CalculateFullRebuild при открытии книги
— вот вообще смешное:

Adding xxxx=now() to the udf worked for me.

Цитата
написал: А то по вашим словам выходит — глючит Excel и ваш код не при чём)

Вы хотите сказать, что у екселя нет глюков? Ой ли…

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

#21

17.12.2021 23:47:28

Цитата
написал: что за бред. Если не меняется значение аргумента, то и пересчет не нужен.

То есть, ви-таки намекаити, что значение UDF (VBA) целиком и полностью определено аргументами? ))
И эти люди запрещают мне ковыряться в носу (с) (т.е. что-то пишут про бред и кривокод. Куда смотрят модераторы?!))

Изменено: Алексей — 17.12.2021 23:47:56

New Пользователь Сообщений: 4659 Регистрация: 06.01.2013	Алексей, они запрещают мне общаться с вами на «ты» Можно? P.s. глюки у Excel бывают, просто они чаще у людей бывают, чем в Excel, но люди свои ошибки часто не замечают Изменено: New — 18.12.2021 00:03:53

Msi2102 Пользователь Сообщений: 3245 Регистрация: 31.03.2014	Алексей, Вы заходили ради того чего? Что бы убедиться в своей гениальности или доказать это нам, а может просто похамить изволите. Удивительно, что ещё бан не прилетел. Всё таки добрые у нас модераторы.

Алексей

Пользователь

Сообщений: 10
Регистрация: 17.12.2021

#24

18.12.2021 00:09:13

Цитата
написал: Можно?

Вообще без проблем.

Цитата
написал: глюки у Excel бывают, просто они чаще у людей бывают, чем в Excel, но люди свои ошибки часто не замечают

Не спорю. Но смысл хором искать глюки в коде, который даже не вызывается? Очевидно же, что это вопрос обработки событий на листе, который, как бы, немного не в нашей власти. Вот о механизмах его работы, собственно, весь вопрос.

Цитата
написал: Вы заходили ради того чего?

Давайте не будем оффтопить? Равно как и обсуждать ваши «догадки» о моих целях.

Msi2102

Пользователь

Сообщений: 3245
Регистрация: 31.03.2014

#25

18.12.2021 00:20:40

Цитата
Алексей написал: ваши «догадки» о моих целях

Значит просто похамить

Юрий М Модератор Сообщений: 60762 Регистрация: 14.09.2012 Контакты см. в профиле	Алексей, а что Вы тут до сих пор делаете? Вы же ошиблись форумом. Да и замечания так и не устранили. Исправляю ситуацию.

БМВ

Модератор

Сообщений: 21650
Регистрация: 28.12.2016

Excel 2013, 2016

#27

18.12.2021 09:01:19

Цитата
Алексей написал: И эти люди запрещают мне ковыряться в носу (с) (т.е. что-то пишут про бред и . Куда смотрят модераторы?!))

Уж лучше ковыряйте в носу, чем тут нести бред.

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

Цитата
Алексей написал: Люди чинят чем попало. — установка Volatile- вызов CalculateFullRebuild при открытии книги- вот вообще смешное: Adding xxxx=now() to the udf worked for me.

Не чинят, а используют. первое от последнего кстати не отличается. По сути пересчет летучей NOW() аналогичен летучести UDF.

Ну и последнее — Если кому то не нравится Excel, напишите свой аналог, более того, включите его в реестр Российского ПО, еще и круто заработаете на импортозамещении.

По вопросам из тем форума, личку не читаю.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14264
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#28

18.12.2021 14:25:05

Цитата
Алексей написал: это вопрос обработки событий на листе, который, как бы, немного не в нашей власти

тут скорее вопрос о незнании механизмов пересчета UDF Без обид, но это реально так. Еще давно написал статью, где кратко описывал этот нюанс с пересчетом и какие есть варианты решений:

Обновление расчетов функции пользователя UDF(автопересчет)

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

sokol92 Пользователь Сообщений: 4456 Регистрация: 10.09.2017	Рекомендую очень внимательно прочитать этот текст разработчика.

БМВ Модератор Сообщений: 21650 Регистрация: 28.12.2016 Excel 2013, 2016	#30 18.12.2021 16:39:40 sokol92, Владимир, приветcтвую. Помню была у меня задача автоматизировать один отчетик и UDF с агументами были организованы запросы к LDAP. Как уже можно понять, аргументы статичны, так как там фактически были указаны части фильтров LDAP запроса, и естественно пересчет был только по F2… Ну от куда функции знать что-что-то изменилось в LDAP с последнего пересчета :-). Добавка летучести приводила б к тормозам, все ж запросы в большом домене не самые быстрые. Это я к тому, что не только события в книге, приложении и прочем типа могут требовать пересчета , но уж точно Excel не предположит что там и как. По вопросам из тем форума, личку не читаю.

Источник

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

Как задается условие «если ошибка» — #ЗНАЧ!

13.03.2018, 10:39. Показов 9376. Ответов 7

Метки нет (Все метки)

Подскажите, пожалуйста, как правильно задается условие «если ошибка»
Мне необходимо указать ошибку: #ЗНАЧ!

Visual Basic

Sub test3()
 
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
        If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
'        If Cells(i, 6).Value = "#ЗНАЧ!" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = Error And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = "#Error" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = "#N/A" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
        
    Next i
    
End Sub

Вложения

If_Error.zip (13.7 Кб, 1 просмотров)

Programming

Эксперт

94731 / 64177 / 26122

Регистрация: 12.04.2006

Сообщений: 116,782

13.03.2018, 10:39

Ответы с готовыми решениями:

Какой функцией задать условие «Если область ячеек пустая» ?
Есть некий макрос, который шагает по рядам и окрашивает их в нужный цвет, или прячет, в зависимости…

Почему ошибка: «Next without for» если For абсолютно точно есть?
На строчку 23 мне тыкает

Sub isColNum()
Dim x As String, r As Long, c As Integer, numCount…

Если str довольно длинная,то выскакивает ошибка «type mismatch»
Делаю ODBC запрос,пишу .commandtext=array(str),где str="……..".Если str довольно длинная,то…

Создать цикл Анализ «что если»-«Подбор параметра.»
Добрый день.
В excel на работе есть классическая задача, которая решается с помощью подбора…

Hugo121

6878 / 2810 / 534

Регистрация: 19.10.2012

Сообщений: 8,573

13.03.2018, 10:41

Visual Basic

1	If Cells(i, 6).Value = CVErr(xlErrNA)

Казанский

15137 / 6411 / 1730

Регистрация: 24.09.2011

Сообщений: 9,999

13.03.2018, 11:52

se_arts, #ЗНАЧ! это CVErr(xlErrValue)
Если любое значение ошибки, то

Visual Basic

1	If iserror(Cells(i, 6).Value) then

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

13.03.2018, 11:54

[ТС]

Hugo121, выдает ошибку
Run-time error ’13’:
Type mismatch

Visual Basic

Sub test3()
   lastRow = Cells(Rows.Count, 1).End(xlUp).Row
   For i = 4 To lastRow
       If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
       If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
       If Cells(i, 6).Value = CVErr(xlErrNA) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
   Next i
End Sub

Данные:

Кликните здесь для просмотра всего текста

5 6

0 0
0 2
3 3
5 4
5
6 6
2 8
2
5 6
4 8
5
5 3
1 1
4 #ЗНАЧ!
6 5
5 6
2 8
4 9
6 8
7 8
7 8
5 2
4 7
4 8
3 4
3 5
3 1
3 2
4 7
4 6
4 8

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

13.03.2018, 12:23

[ТС]

Казанский,

убрал проверку 2-ого условия (And Cells(i, 5).Value <> «»), попробовал CVErr(xlErrValue) и для любой ошибки
тоже выдает ошибку:
Run-time error ’13’:
Type mismatch

Что я неправильно пишу или указываю в коде?
Находит только перове правильное значение с строке 4 и дальше переходит к макросу и показывает ошибку «13».

Visual Basic

Sub test3()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To LastRow
        If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
        If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
'        If IsError(Cells(i, 6).Value) Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = CVErr(xlErrNA) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = CVErr(xlErrValue) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
        If Cells(i, 6).Value = CVErr(xlErrValue) Then Cells(i, 7).Value = 15
    Next i
End Sub

Миниатюры

Вложения

If_Error.zip (13.8 Кб, 4 просмотров)

Vlad999

3836 / 2262 / 753

Регистрация: 02.11.2012

Сообщений: 5,965

13.03.2018, 15:16

IsError(Cells(i, 6).Value) не подошел?
и по моему у вас ЕСЛИ не правильно устроин.

Добавлено через 1 минуту
вариант

Visual Basic

If Cells(i, 5).Value <> "" Then
          If IsError(Cells(i, 6).Value) Then
             Cells(i, 7).Value = 15
          Else
             Select Case Cells(i, 6).Value
                 Case 2: Cells(i, 7).Value = 5
                 Case 5: Cells(i, 7).Value = 10
             End Select
          End If
End If

Hugo121

6878 / 2810 / 534

Регистрация: 19.10.2012

Сообщений: 8,573

13.03.2018, 22:37

Сообщение было отмечено se_arts как решение

Решение

Посмотрел наконец файл — точно xlErrValue нужно:

Visual Basic

Sub test2()
 
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        If Cells(i, 5).Value <> "" Then
            Select Case CStr(Cells(i, 6).Value)
            Case CStr(CVErr(xlErrValue)): Cells(i, 7).Value = 15
            Case "2": Cells(i, 7).Value = 5
            Case "5": Cells(i, 7).Value = 10
            End Select
            End If
        Next i
 
    End Sub

Добавлено через 1 минуту
Или можно конечно искать просто «Error 2015»

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

14.03.2018, 00:09

[ТС]

Hugo121, заработало
Hugo121, Vlad999, Казанский — всем спасибо.

Источник

msm.ru

Нравится ресурс?
Помоги проекту!

Популярные разделы FAQ: Общие вопросы Особенности VBA-кода Оптимизация VBA-кода Полезные ссылки

1. Старайтесь при создании темы указывать в заголовке или теле сообщения название офисного приложения и (желательно при работе с Office 95/97/2000) его версию. Это значительно сократит количество промежуточных вопросов.
2. Формулируйте вопросы как можно конкретнее, вспоминая (хотя бы иногда) о правилах ВЕЛИКОГО И МОГУЧЕГО РУССКОГО ЯЗЫКА, и не забывая, что краткость — сестра таланта.
3. Не забывайте использовать теги [сode=vba] …текст программы… [/code] для выделения текста программы подсветкой!
4. Темы с просьбой выполнить какую-либо работу полностью за автора здесь не обсуждаются и переносятся в раздел ПОМОЩЬ СТУДЕНТАМ.

Пользовательская функция в Excel выдает ошибку #ЗНАЧ!
, не знаю, с какой стороны подступиться

Подписаться на тему
Сообщить другу
Скачать/распечатать тему

Сообщ.
#1

,
29.02.08, 11:19

Здравствуйте.
У меня вопрос по пользовательским функциям в Excel’е. Похоже, мне необходимо сохранить значения моих переменных между вызовами функций, как мне это сделать? Ситуация такая: есть функция, которая выводит значения переменных в зависимости от значения входного параметра. Сами значения переменных рассчитываются в отдельной «вычисляющей процедуре». Все переменные — Public. Работать функция, по замыслу, должна так: когда ее вызывают в первый раз (всего на двух разных листах она вызывается 20 раз), то из функции вызывается «вычисляющая процедура» для расчета значений переменных и функция выводит значение требуемой (только что рассчитанной) переменной. В остальных 19 случаях функция должна просто брать из «памяти» значения рассчитанной переменной и выводить его, не обращаясь к «вычисляющей процедуре». А на следующей итерации все повторяется.
Не знаю, в чем проблема, но при первом вызове функции все работает нормально — функция выдает адекватное значение. При втором обращении (которое без вызова «вычисляющей процедуры») функция чаще всего выдает ошибку #ЗНАЧ!, хотя может и выдать значимый результат.
Подскажите, пожалуйста, как справиться с этой ошибкой.
Заранее большое спасибо.

Krasnaja Shapka

Сообщ.
#2

,
29.02.08, 13:23

Full Member

Рейтинг (т): 18

в пользовательских функциях все зависит от пользователей… какие пользователи — такая и функция..

скажите где в этом абзаце текста информация на основании которой можно найти вашу ошибку? :huh:

Получайник

Сообщ.
#3

,
29.02.08, 15:16

Я так понимаю, что выкладывать сюда три с лишним страницы кода — это не comme il faut?
Не мог бы кто-нибудь мне (для начала) объяснить, как можно добиться в VBA, чтобы по окончании выполнения функции (процедуры) переменные Public продолжали хранить свое значение до следующего запуска функции, а не удалялись?

PS
Под следующим запуском функции имеется в виду запуск пользовательской функции из следующей ячейки таблицы Excel, а не вызов в той же процедуре через пару строк.

Сообщение отредактировано: Получайник — 29.02.08, 15:20

Krasnaja Shapka

Сообщ.
#4

,
29.02.08, 15:41

Full Member

Рейтинг (т): 18

ну.. можно ведь и файл выложить?

что такое следующий запуск функции? (или «вызов процедуры через пару строк»???)
я понимаю с процедурой — она начитает работать в определенный момент времени/при каких-то условиях, а функцию в ячейку вставил — она и пересчитывается…

для того чтоб узнать значение public переменной существуют breakpoint’ы, окно watches (не помню как по-русски — у меня не локализированный офис), пошаговое выполнение процедуры/функции (F8) — очень помогает.

Получайник

Сообщ.
#5

,
29.02.08, 16:00

Попробую объяснить на свой дилетантский манер… Следующий запуск функции — это, как Вы говорите, «в ячейку вставил — она и пересчитывается», а потом в другую ячейку вставил — она там снова пересчитывается, потом в следующую ячейку и т.д. А вызов через пару строк — это (внутри одной функции) примерно так:

Sub s1()

…

End Sub

Function fun1()

…

Call s1

… ‘пара строк

Call s1

…

End Function

pvr

Сообщ.
#6

,
01.03.08, 00:12

Junior

Рейтинг (т): 9

В модуле книги

Public ДолгоживущаяПеременная as Long

Private Sub Workbook_Open()

ДолгоживущаяПеременная = 0

End Sub

В общем модуле

Public Function МояПользовательскаяФункция(r As Range)

ThisWorkbook.ДолгоживущаяПеременная = ThisWorkbook.ДолгоживущаяПеременная + 1

МояПользовательскаяФункция = ThisWorkbook.ДолгоживущаяПеременная + 1

End Function

А теперь на листе в ячеку «А1» вставьте =МояПользовательскаяФункция(A1)
Протащите формулу вправо и насладитесь неожиданным эффектом, эксель пересчитывает формулы в том порядке, каком ему удобнее.

Цитата Krasnaja Shapka @ 29.02.08, 15:41

ну.. можно ведь и файл выложить?

:yes:

Цитата Krasnaja Shapka @ 29.02.08, 15:41

Получайник

Сообщ.
#7

,
01.03.08, 21:00

А что, непосредственно к сообщению файл прикрепить нельзя? Только выложив на другие сайты? А есть ограничения, на какие сайты можно выкладывать?

bi-lya

Сообщ.
#8

,
02.03.08, 00:50

Senior Member

Рейтинг (т): 34

Непосредственно к сообщению файл прикрепить можно. Для этого или перед вводом сообщения нажмите ссылку «Ответить», или это можно сделать на предварительном просмотре вашего сообщения

Получайник

Сообщ.
#9

,
02.03.08, 10:25

Спасибо, bi-lya.
Выкладываю файл. Я слегка поправил макрос за прошедшие дни, но проблема не исчезла: если нажать F9 (1 итерация при ручном пересчете), то на листе Расчеты вместо нулей (единственные черные символы на всем листе) возникнут сообщения об ошибке. Причем на следующей итерации сообщения об ошибке вновь сменятся на нули, но из-за перекрестных ссылок в формулах на эти ячейки ошибка начнет расползаться по листам.
Чтобы было удобней искать нужные ячейки, я все ячейки кроме ячеек, в которых вызывается пользовательская функция dtr(), покрасил белым. Функция вызывается только на листах Агенты и Расчеты.

Прикреплённый файлforum.zip (120.61 Кбайт, скачиваний: 152)

Получайник

Сообщ.
#10

,
13.03.08, 15:24

Неужели, никто не может подсказать, в чем дело?

Krasnaja Shapka

Сообщ.
#11

,
14.03.08, 07:39

Full Member

Рейтинг (т): 18

Цитата Получайник @ 29.02.08, 11:19

Все переменные — Public

ну и хде они???

ваша функция у меня выдает 0, что вполне логично, ибо в начале стоит проверка на used=0 она не выполняется, программа переходит в конец и функция приравнивается переменной равной нулю.

и еще раз повторюсь:

Цитата Krasnaja Shapka @ 29.02.08, 15:41

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

leo

Сообщ.
#12

,
14.03.08, 09:48

Все таки видимо дело не в Public (т.к. сама функция Static), а в том, что как отметил pvr, Excel может вызывать функции не в том порядке, в каком ожидает автор, т.е. сначала вызываются функции с used=1 (в независимых ячейках), и только потом с used = 0 (т.к. эта ячейка зависит от других)

Получайник

Сообщ.
#13

,
16.03.08, 15:47

Krasnaja Shapka, так в том и проблема, что при в режиме отладки (Debugger) значения переменных правильные и процедура выполняется корректно, а на самом листе Excel’я возникают ошибки.

Цитата Krasnaja Shapka @ 14.03.08, 07:39

ну и хде они???

Public, извиняйте, больше нет — я писал о правках, которые были внесены уже после начала данной ветки на форуме. Вместо них я изменил тип функции на Static. Но проблема с тем, что функция выдает ошибку #ЗНАЧ! осталась.

Цитата Krasnaja Shapka @ 14.03.08, 07:39

Да, не спорю. Я же говорил, проблемы начинаются со второй итерации (нажмите 1 раз кнопку F9 и взгляните на результат в оставленных мною ячейках).

leo, а есть какой-то стандартный способ контроля над тем, в какой последовательности Excel вызывает функции?

PS
Кстати, позвольте уточнить. В данном случае, я правильно понимаю, что, поскольку функция Static, Excel будет постоянно хранить последние значения всех внутренних переменных функции, покуда я не закрою Excel? Или при переходе вычислений (во время следующей итерации) на следующую ячейку/лист значения переменных будут обнуляться?

Получайник

Сообщ.
#14

,
24.03.08, 13:36

Ответьте, пожалуйста, хотя бы на вопрос из постскриптума про Static функцию в случае пересчета вручную.

Krasnaja Shapka

Сообщ.
#15

,
24.03.08, 14:12

Full Member

Рейтинг (т): 18

про static, на сколько я понимаю static переменные будут сохранять свои значения между последовательными вызовами процедуры в которой они описаны… пиши public и не прогадаешь…

Цитата Получайник @ 16.03.08, 15:47

в режиме отладки (Debugger) значения переменных правильные и процедура выполняется корректно, а на самом листе Excel’я возникают ошибки.

не верю! (с) станиславский
главное не то что переменные правильные, весь вопрос в результате вычислений самой функции… т.е. какое значение ей присваивается в конечном итоге.

и еще так как результат у тебя зависит от того с какой ячейки excel начинает пересчет значений… то попробуй переписать все используя процедуры например… ибо ты не можешь сказать excel-ю в каком порядке ему все пересчитывать…
например вписать все в стандартные процедуры Workbook_Open и Workbook_SheetCalculate, т.е. при открытии или пересчете документа обновлять значения нужных ячеек…

Сообщение отредактировано: Krasnaja Shapka — 24.03.08, 14:13

0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)

0 пользователей:

Предыдущая тема
VB for Application
Следующая тема

[ Script execution time: 0,0422 ] [ 16 queries used ] [ Generated: 4.06.23, 12:41 GMT ]

Источник

N1K0 Пользователь Сообщений: 70 Регистрация: 01.01.1970	Имеется ячейка с формулой, которая может выдавать значения ошибок (#Н/Д и т.п) требуется если Ячейка не содержит ошибку Then выполнить код или if Ячейка является числом Then выполнить код помогите!

ytk5kyky Пользователь Сообщений: 2410 Регистрация: 01.01.1970	If IsNumeric(ячейка.Value) Then Если известен вид ошибки, то можно проверить значение, например, для #Н/Д: If ячейка.Value <> «#N/A» Then Или так: If Not Application.WorksheetFunction.IsError(ячейка.Value) Then ‘IsError = ЕОШИБКА, IsNA = ЕНД

ZVI Пользователь Сообщений: 4325 Регистрация: 23.12.2012	Или так: If Not IsError(ActiveCell) Then MsgBox «Нет ошибки»

N1K0 Пользователь Сообщений: 70 Регистрация: 01.01.1970	Перепробовал все способы, работает. Спасибо.

Здравствуйте. Решил не создавать новую тему а эту освежить, таким вопросом. Как в коде VBA проверить, что в ячейке находится дата ну например 01.01.2011. Знаю что ексель хранит дату и время в числовом формате, но может есть какой то способ? Спасибо!

{quote}{login=}{date=06.04.2011 06:40}{thema=Как проверить что в ячейке формат даты}{post}Как в коде VBA проверить, что в ячейке находится дата ну например 01.01.2011. {/post}{/quote}

If IsDate(ActiveCell) Then MsgBox «В активной ячейке находится дата!»

Вот только IsDate почему-то распознаёт дату даже в ячейке, где содержится текст 1,2,2011
(Excel не распознаёт этот текст как дату)

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	Игорь, я так понял из вопроса, надо на конкретную дату проверить, что-то вроде: If ActiveCell = #1/1/2011# Then MsgBox «В активной ячейке находится дата!» Но может я и ошибаюсь. Я сам — дурнее всякого примера! …

Guest Гость	#8 06.04.2011 07:29:07 Да Игорь все правильно вы поняли, надо именно на формат проверить, а не на конкретную дату, я думаю если к вашему методу добавить такую проверку: If (IsDate(ActiveCell) And ActiveCell.Value Like «..*» Then MsgBox «В активной ячейке находится дата!» Тогда исключится вариант с запятыми

Q: How do i determine if an error is in any cell in the entire workbook with Excel VBA?

Normally errors will be divide by 0 or #value errors, but this list is not exhaustive (or is it? — i don’t know if more exist)

Is there a way to determine if a cell contains an error then to skip over further processing in my script without spitting out a debug/warning/error message.

something such like

          if value in current.Workbook.cell is error then go to <jump>
           OR
          if value in old.Workbook.cell is error then go to <jump>

where jump is a marker at the end of an if statement but within a loop.

the script compares values between two workbooks and updates the current workbook with colours to show difference.

I have no VBA experience at all. but i get the gist of the script i have been given.

thank you kindly.

asked Nov 22, 2011 at 17:00

MatMat

1,2211 gold badge22 silver badges46 bronze badges

You can skip cells with errors by using the VarType function. For example:

If VarType(ActiveCell.Value) <> vbError Then
    ' do something
End If

The VarType function is also very useful to validate the data type. For example if your code expects a date value but sometimes encounters text, you can use this function to vet the data and handle exceptions gracefully.

answered Nov 22, 2011 at 19:18

Rachel HettingerRachel Hettinger

7,7122 gold badges22 silver badges31 bronze badges

Here’s an snippet of code that records in the Immediate Window, the worksheet name, cell address and formula, where there is a spreadsheet formula error…

Dim ws As Worksheet, r As Range

For Each ws In Worksheets
    For Each r In ws.UsedRange
        If IsError(r.Value) Then
            Debug.Print r.Parent.Name, r.Address, r.Formula
        End If
    Next
Next

answered Nov 22, 2011 at 21:39

Given your initial question was how do you detect errors in any cell of the workbook with VBA then you should be looking for a very efficient approach — looking at each cell is very expensive time wise!

Two options for this are:

Use Excel’s SpecialCells to shortcut the process
use my Mappit! addin which is configured to report on spreadsheet errors

For SpecialCells please see the code below. This takes advantage of the ready-made collection of errors that exist as formulas

Please that that constants also have an errors collection, so if you have copied then run a paste special as value on a formula error then you would need to use Set rng1 = ws.Cells.SpecialCells(xlConstants, xlErrors) to detect these

You can use also detect SpecialCells manually by

Select all cells in the area of interest
Press F5
Click Special
select ‘Errors’ under ‘Formulas’ (or ‘Constants’)

Please be aware that prior to xl2010 there is a limit of 8192 areas that SpecialCells can handle

Sub ErrorList()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim strOut As String
    For Each ws In ActiveWorkbook.Sheets
        Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rng1 Is Nothing Then strOut = strOut & (ws.Name & " has " & rng1.Cells.Count & " errors" & vbNewLine)
    Next ws
    If Len(strOut) > 0 Then
        MsgBox "Error List:" & vbNewLine & strOut
    Else
        MsgBox "No Errors", vbInformation
    End If
End Sub

answered Nov 23, 2011 at 9:35

brettdjbrettdj

54.4k15 gold badges112 silver badges175 bronze badges

There’s another way to do handle this: add On Error Resume Next into your code (usually just put it before the loop).

If a cell is an error, it’ll just skip it and move to the next element in the loop

answered Nov 23, 2011 at 0:33

GaijinhunterGaijinhunter

14.4k4 gold badges50 silver badges57 bronze badges

Q: How do i determine if an error is in any cell in the entire workbook with Excel VBA?

Normally errors will be divide by 0 or #value errors, but this list is not exhaustive (or is it? — i don’t know if more exist)

Is there a way to determine if a cell contains an error then to skip over further processing in my script without spitting out a debug/warning/error message.

something such like

          if value in current.Workbook.cell is error then go to <jump>
           OR
          if value in old.Workbook.cell is error then go to <jump>

where jump is a marker at the end of an if statement but within a loop.

the script compares values between two workbooks and updates the current workbook with colours to show difference.

I have no VBA experience at all. but i get the gist of the script i have been given.

thank you kindly.

asked Nov 22, 2011 at 17:00

MatMat

1,2211 gold badge22 silver badges46 bronze badges

You can skip cells with errors by using the VarType function. For example:

If VarType(ActiveCell.Value) <> vbError Then
    ' do something
End If

answered Nov 22, 2011 at 19:18

Rachel HettingerRachel Hettinger

7,7122 gold badges22 silver badges31 bronze badges

Here’s an snippet of code that records in the Immediate Window, the worksheet name, cell address and formula, where there is a spreadsheet formula error…

Dim ws As Worksheet, r As Range

For Each ws In Worksheets
    For Each r In ws.UsedRange
        If IsError(r.Value) Then
            Debug.Print r.Parent.Name, r.Address, r.Formula
        End If
    Next
Next

answered Nov 22, 2011 at 21:39

Two options for this are:

Use Excel’s SpecialCells to shortcut the process
use my Mappit! addin which is configured to report on spreadsheet errors

For SpecialCells please see the code below. This takes advantage of the ready-made collection of errors that exist as formulas

You can use also detect SpecialCells manually by

Select all cells in the area of interest
Press F5
Click Special
select ‘Errors’ under ‘Formulas’ (or ‘Constants’)

Please be aware that prior to xl2010 there is a limit of 8192 areas that SpecialCells can handle

Sub ErrorList()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim strOut As String
    For Each ws In ActiveWorkbook.Sheets
        Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rng1 Is Nothing Then strOut = strOut & (ws.Name & " has " & rng1.Cells.Count & " errors" & vbNewLine)
    Next ws
    If Len(strOut) > 0 Then
        MsgBox "Error List:" & vbNewLine & strOut
    Else
        MsgBox "No Errors", vbInformation
    End If
End Sub

answered Nov 23, 2011 at 9:35

brettdjbrettdj

54.4k15 gold badges112 silver badges175 bronze badges

There’s another way to do handle this: add On Error Resume Next into your code (usually just put it before the loop).

If a cell is an error, it’ll just skip it and move to the next element in the loop

answered Nov 23, 2011 at 0:33

GaijinhunterGaijinhunter

14.4k4 gold badges50 silver badges57 bronze badges

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

Как задается условие «если ошибка» — #ЗНАЧ!

13.03.2018, 10:39. Показов 8792. Ответов 7

Метки нет (Все метки)

Подскажите, пожалуйста, как правильно задается условие «если ошибка»
Мне необходимо указать ошибку: #ЗНАЧ!

Visual Basic

Sub test3()
 
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
        If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
'        If Cells(i, 6).Value = "#ЗНАЧ!" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = Error And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = "#Error" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = "#N/A" And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
        
    Next i
    
End Sub

Вложения

If_Error.zip (13.7 Кб, 1 просмотров)

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

Programming

Эксперт

94731 / 64177 / 26122

Регистрация: 12.04.2006

Сообщений: 116,782

13.03.2018, 10:39

Ответы с готовыми решениями:

Почему ошибка: «Next without for» если For абсолютно точно есть?
На строчку 23 мне тыкает

Sub isColNum()
Dim x As String, r As Long, c As Integer, numCount…

Hugo121

6874 / 2806 / 533

Регистрация: 19.10.2012

Сообщений: 8,550

13.03.2018, 10:41

Visual Basic

1	If Cells(i, 6).Value = CVErr(xlErrNA)

Казанский

15131 / 6405 / 1730

Регистрация: 24.09.2011

Сообщений: 9,999

13.03.2018, 11:52

se_arts, #ЗНАЧ! это CVErr(xlErrValue)
Если любое значение ошибки, то

Visual Basic

1	If iserror(Cells(i, 6).Value) then

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

13.03.2018, 11:54

[ТС]

Hugo121, выдает ошибку
Run-time error ’13’:
Type mismatch

Visual Basic

Sub test3()
   lastRow = Cells(Rows.Count, 1).End(xlUp).Row
   For i = 4 To lastRow
       If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
       If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
       If Cells(i, 6).Value = CVErr(xlErrNA) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
   Next i
End Sub

Данные:

Кликните здесь для просмотра всего текста

5 6

0 0
0 2
3 3
5 4
5
6 6
2 8
2
5 6
4 8
5
5 3
1 1
4 #ЗНАЧ!
6 5
5 6
2 8
4 9
6 8
7 8
7 8
5 2
4 7
4 8
3 4
3 5
3 1
3 2
4 7
4 6
4 8

se_arts

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

13.03.2018, 12:23

[ТС]

Казанский,

Visual Basic

Sub test3()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To LastRow
        If Cells(i, 6).Value = 2 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 5
        If Cells(i, 6).Value = 5 And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 10
'        If IsError(Cells(i, 6).Value) Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = CVErr(xlErrNA) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
'        If Cells(i, 6).Value = CVErr(xlErrValue) And Cells(i, 5).Value <> "" Then Cells(i, 7).Value = 15
        If Cells(i, 6).Value = CVErr(xlErrValue) Then Cells(i, 7).Value = 15
    Next i
End Sub

Миниатюры

Вложения

If_Error.zip (13.8 Кб, 4 просмотров)

Vlad999

3815 / 2244 / 749

Регистрация: 02.11.2012

Сообщений: 5,894

13.03.2018, 15:16

IsError(Cells(i, 6).Value) не подошел?
и по моему у вас ЕСЛИ не правильно устроин.

Добавлено через 1 минуту
вариант

Visual Basic

If Cells(i, 5).Value <> "" Then
          If IsError(Cells(i, 6).Value) Then
             Cells(i, 7).Value = 15
          Else
             Select Case Cells(i, 6).Value
                 Case 2: Cells(i, 7).Value = 5
                 Case 5: Cells(i, 7).Value = 10
             End Select
          End If
End If

Hugo121

6874 / 2806 / 533

Регистрация: 19.10.2012

Сообщений: 8,550

13.03.2018, 22:37

Сообщение было отмечено se_arts как решение

Решение

Посмотрел наконец файл — точно xlErrValue нужно:

Visual Basic

Sub test2()
 
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        If Cells(i, 5).Value <> "" Then
            Select Case CStr(Cells(i, 6).Value)
            Case CStr(CVErr(xlErrValue)): Cells(i, 7).Value = 15
            Case "2": Cells(i, 7).Value = 5
            Case "5": Cells(i, 7).Value = 10
            End Select
            End If
        Next i
 
    End Sub

Добавлено через 1 минуту
Или можно конечно искать просто «Error 2015»

0 / 0 / 0

Регистрация: 15.10.2016

Сообщений: 91

14.03.2018, 00:09

[ТС]

Hugo121, заработало
Hugo121, Vlad999, Казанский — всем спасибо.

Точно так же, как мы используем ЕСЛИОШИБКА в Excel, чтобы знать, что делать, когда возникает ошибка перед каждой функцией, у нас есть встроенная функция ЕСЛИОШИБКА в VBA, которая используется таким же образом, поскольку это функция рабочего листа, мы используем эту функцию с worksheet.function метод в VBA, а затем мы предоставляем аргументы для функции.

Ожидать, что код будет работать без ошибок, — преступление. Для обработки ошибок в VBA у нас есть несколько способов использования таких операторов, как При ошибке Возобновить следующий VBAОператор VBA On Error Resume — это аспект обработки ошибок, используемый для игнорирования строки кода, из-за которой возникла ошибка, и продолжения со следующей строки сразу после строки кода с ошибкой.читать далее, При ошибке Возобновить Перейти к 0, При ошибке Перейти к метке. Обработчики ошибок VBA могут переходить только к следующей строке кода. Но в случае, если вычисление не происходит, нам нужно заменить ошибку на какое-то другое идентификационное слово. В этой статье мы увидим, как добиться этого с помощью VBA. ЕСЛИОШИБКА Функция в ExcelФункция ЕСЛИОШИБКА в Excel проверяет формулу (или ячейку) на наличие ошибок и возвращает указанное значение вместо ошибки.читать далее.

Вы можете использовать это изображение на своем веб-сайте, в шаблонах и т. д. Пожалуйста, предоставьте нам ссылку на авторствоСсылка на статью должна быть гиперссылкой
Например:
Источник: VBA IFERROR (wallstreetmojo.com)

Как использовать ЕСЛИОШИБКА в VBA?

Здесь следует помнить, что это не Функция VBAФункции VBA служат основной цели для выполнения определенных вычислений и возврата значения. Поэтому в VBA мы используем синтаксис для указания параметров и типа данных при определении функции. Такие функции называются пользовательскими функциями.читать далее скорее просто как функция рабочего листа.

Вы можете скачать этот шаблон Excel VBA IFERROR здесь — Шаблон Excel для VBA ЕСЛИОШИБКА

Для примера возьмем приведенные выше данные только для демонстрации.

Шаг 1: Определите переменную как целое число.

Код:

Sub Iferror_Example1()

  Dim i As Integer

End Sub

Шаг 2: Для выполнения расчета откройте For Следующий цикл.

Код:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6

  Next i

End Sub

Шаг 3: Внутри напишите код как Ячейки(I,3).Значение =

Код:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
     Cells(i,3).Value =
  Next i

End Sub

Шаг 4: Чтобы получить доступ к функции ЕСЛИОШИБКА, мы не можем просто ввести формулу; скорее, нам нужно использовать «Функция рабочего листа» класс.

Код:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 3).Value = WorksheetFunction.If
  Next i

End Sub

Шаг 5: Как вы можете видеть на изображении выше, после вставки класса команды «WorksheetFunction» мы получаем формулу ЕСЛИОШИБКА. Выберите формулу.

Код:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 3).Value = WorksheetFunction.IfError(
  Next i

End Sub

Шаг 6: Одна из проблем в VBA при доступе к функциям рабочего листа: мы не видим аргументы, подобные тому, что мы видели на рабочем листе. Вы должны быть уверены в аргументах, которые мы используем.

По этой причине, прежде чем я покажу вам IFERROR в VBA, я показал вам синтаксис функции рабочего листа.

Первый аргумент здесь — «Значение», т. е. какую ячейку вы хотите проверить? Перед этим примените расчет в Cell.

Теперь в VBA примените приведенные ниже коды.

Код:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 4).Value = WorksheetFunction.IfError(Cells(i, 3).Value, "Not Found")
  Next i

End Sub

Теперь функция ЕСЛИОШИБКА проверяет наличие ошибок в столбце C. Если обнаруживается какая-либо ошибка, в столбце D отображается результат «Не найдено».

Таким образом, используя функцию ЕСЛИОШИБКА, мы можем изменить результаты по своему желанию. В этом случае я изменил результат как «Не обнаружена.» Вы можете изменить это по своему требованию.

Типы ошибок, VBA IFERROR, можно найти

Важно знать виды Эксель ошибкиОшибки в Excel распространены и часто возникают во время применения формул. Список из девяти наиболее распространенных ошибок Excel: #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.читать далее функция ЕСЛИОШИБКА может обработать. Ниже приведены типы ошибок, которые может обработать IFERROR.

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? или #NULL!.

УЗНАТЬ БОЛЬШЕ >>

Post Views: 805

В этом учебном материале вы узнаете, как использовать Excel функцию ЕОШИБКА с синтаксисом и примерами.

Описание

Функцию Microsoft Excel ЕОШИБКА можно использовать для проверки таких значений ошибок, как #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!.
Функция ЕОШИБКА — это встроенная в Excel функция, которая относится к категории информационных функций.
Её можно использовать как функцию рабочего листа (WS) и функцию VBA в Excel.
Как функцию рабочего листа, функция ЕОШИБКА может быть введена как часть формулы в ячейке рабочего листа.
В качестве функции VBA вы можете использовать функцию ISERROR в коде макросов, который вводится через редактор Microsoft Visual Basic.

Синтаксис

Синтаксис функции ЕОШИБКА в Microsoft Excel:

ЕОШИБКА(значение)

Аргументы или параметры

значение: Значение, которое вы хотите проверить.
Если значение является значением ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!), функция ЕОШИБКА вернет ИСТИНА. В противном случае она вернет ЛОЖЬ.

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

Функция ЕОШИБКА возвращает ИСТИНА, если значение является любым значением ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!).
В противном случае функция ЕОШИБКА возвращает ЛОЖЬ.

Применение

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Функция рабочего листа (WS)
Функция VBA

Пример (как функция рабочего листа)

Рассмотрим несколько примеров функции ЕОШИБКА, чтобы понять, как использовать Excel функцию ЕОШИБКА в качестве функции рабочего листа в Microsoft Excel:

На основании, приведенной выше электронной таблицы Excel функция ЕОШИБКА вернет ИСТИНА, поскольку ячейка C2 содержит ошибку #ДЕЛ/0!:

=ЕОШИБКА(C2)

Результат: ИСТИНА

Однако функция ЕОШИБКА в ячейке E3 вернет ЛОЖЬ, потому что C3 содержит значение $0.50 и не является ошибкой:

=ЕОШИБКА(C3)

Результат: ЛОЖЬ

ЕОШИБКА не должна просто смотреть на значение в ячейке, вы также можете использовать функцию ЕОШИБКА для проверки результата формулы, например:

=ЕОШИБКА(9.75/0)

Результат: ИСТИНА

Если вы проверили, было ли деление 9,75 на 0 ошибкой, функция ЕОШИБКА также вернет ИСТИНА, поскольку эта формула вернет ошибку #ДЕЛ/0!.
См. еще примеры функции ЕОШИБКА:
как подсчитать ячейки, содержащие ошибки
как подсчитать ячейки, которые не содержат ошибок.

Затем рассмотрим пример использования функции ISERROR в коде Excel VBA. В нашем примере электронной таблицы мы создали кнопку на Листе2, которая называется «Содержит ли ячейка A2 ошибку?».
iserror
Когда мы нажимаем на эту кнопку, запускается следующий код VBA:

Sub TestIsErrorFunction()

‘Отображение функции IsError для ячейки A2 на Листе2

MsgBox IsError(Лист2.Range(«A2»)), vbOKOnly, «В ячейке A2 есть ошибка?»

End Sub

Этот код VBA отобразит окно сообщения, в котором будет показано, содержит ли значение в ячейке A2 на листе Лист2 ошибку.

Поскольку ячейка A2 на листе Лист2 содержит ошибку #ДЕЛ/0!, в окне сообщения отображается True.

На чтение 8 мин. Просмотров 24k.

Содержание

Объяснение Type Mismatch Error
Использование отладчика
Присвоение строки числу
Недействительная дата
Ошибка ячейки
Неверные данные ячейки
Имя модуля
Различные типы объектов
Коллекция Sheets
Массивы и диапазоны
Заключение

Объяснение Type Mismatch Error

Type Mismatch Error VBA возникает при попытке назначить значение между двумя различными типами переменных.

Ошибка отображается как:
run-time error 13 – Type mismatch

Например, если вы пытаетесь поместить текст в целочисленную переменную Long или пытаетесь поместить число в переменную Date.

Давайте посмотрим на конкретный пример. Представьте, что у нас есть переменная с именем Total, которая является длинным целым числом Long.

Если мы попытаемся поместить текст в переменную, мы получим Type Mismatch Error VBA (т.е. VBA Error 13).

Sub TypeMismatchStroka()

    ' Объявите переменную типа long integer
    Dim total As Long
    
    ' Назначение строки приведет к Type Mismatch Error
    total = "Иван"
    
End Sub

Давайте посмотрим на другой пример. На этот раз у нас есть переменная ReportDate типа Date.

Если мы попытаемся поместить в эту переменную не дату, мы получим Type Mismatch Error VBA.

Sub TypeMismatchData()

    ' Объявите переменную типа Date
    Dim ReportDate As Date
    
    ' Назначение числа вызывает Type Mismatch Error
    ReportDate = "21-22"
    
End Sub

В целом, VBA часто прощает, когда вы назначаете неправильный тип значения переменной, например:

Dim x As Long

' VBA преобразует в целое число 100
x = 99.66

' VBA преобразует в целое число 66
x = "66"

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

Dim x As Long

' Type Mismatch Error
x = "66a"

Простой способ объяснить Type Mismatch Error VBA состоит в том, что элементы по обе стороны от равных оценивают другой тип.

При возникновении Type Mismatch Error это часто не так просто, как в этих примерах. В этих более сложных случаях мы можем использовать средства отладки, чтобы помочь нам устранить ошибку.

Использование отладчика

В VBA есть несколько очень мощных инструментов для поиска ошибок. Инструменты отладки позволяют приостановить выполнение кода и проверить значения в текущих переменных.

Вы можете использовать следующие шаги, чтобы помочь вам устранить любую Type Mismatch Error VBA.

Запустите код, чтобы появилась ошибка.
Нажмите Debug в диалоговом окне ошибки. Это выделит строку с ошибкой.
Выберите View-> Watch из меню, если окно просмотра не видно.
Выделите переменную слева от equals и перетащите ее в окно Watch.
Выделите все справа от равных и перетащите его в окно Watch.
Проверьте значения и типы каждого.
Вы можете сузить ошибку, изучив отдельные части правой стороны.

Следующее видео показывает, как это сделать.

На скриншоте ниже вы можете увидеть типы в окне просмотра.

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

В следующих разделах показаны различные способы возникновения Type Mismatch Error VBA.

Присвоение строки числу

Как мы уже видели, попытка поместить текст в числовую переменную может привести к Type Mismatch Error VBA.

Ниже приведены некоторые примеры, которые могут вызвать ошибку:

Sub TextErrors()

    ' Long - длинное целое число
    Dim l As Long
    l = "a"
    
    ' Double - десятичное число
    Dim d As Double
    d = "a"
    
   ' Валюта - 4-х значное число
    Dim c As Currency
    c = "a"
    
    Dim d As Double
    ' Несоответствие типов, если ячейка содержит текст
    d = Range("A1").Value
    
End Sub

Недействительная дата

VBA очень гибок в назначении даты переменной даты. Если вы поставите месяц в неправильном порядке или пропустите день, VBA все равно сделает все возможное, чтобы удовлетворить вас.

В следующих примерах кода показаны все допустимые способы назначения даты, за которыми следуют случаи, которые могут привести к Type Mismatch Error VBA.

Sub DateMismatch()

    Dim curDate As Date
    
    ' VBA сделает все возможное для вас
    ' - Все они действительны
    curDate = "12/12/2016"
    curDate = "12-12-2016"
    curDate = #12/12/2016#
    curDate = "11/Aug/2016"
    curDate = "11/Augu/2016"
    curDate = "11/Augus/2016"
    curDate = "11/August/2016"
    curDate = "19/11/2016"
    curDate = "11/19/2016"
    curDate = "1/1"
    curDate = "1/2016"
   
    ' Type Mismatch Error
    curDate = "19/19/2016"
    curDate = "19/Au/2016"
    curDate = "19/Augusta/2016"
    curDate = "August"
    curDate = "Какой-то случайный текст"

End Sub

Ошибка ячейки

Тонкая причина Type Mismatch Error VBA — это когда вы читаете из ячейки с ошибкой, например:

Если вы попытаетесь прочитать из этой ячейки, вы получите Type Mismatch Error.

Dim sText As String

' Type Mismatch Error, если ячейка содержит ошибку
sText = Sheet1.Range("A1").Value

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

Dim sText As String
If IsError(Sheet1.Range("A1").Value) = False Then
    sText = Sheet1.Range("A1").Value
End If

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

Вы можете использовать следующую функцию, чтобы сделать это:

Function CheckForErrors(rg As Range) As Long

    On Error Resume Next
    CheckForErrors = rg.SpecialCells(xlCellTypeFormulas, xlErrors).Count

End Function

Ниже приведен пример использования этого кода.

Sub DoStuff()

    If CheckForErrors(Sheet1.Range("A1:Z1000")) > 0 Then
        MsgBox "На листе есть ошибки. Пожалуйста, исправьте и запустите макрос снова."
        Exit Sub
    End If
    
    ' Продолжайте здесь, если нет ошибок

End Sub

Неверные данные ячейки

Как мы видели, размещение неверного типа значения в переменной вызывает Type Mismatch Error VBA. Очень распространенная причина — это когда значение в ячейке имеет неправильный тип.

Пользователь может поместить текст, такой как «Нет», в числовое поле, не осознавая, что это приведет к Type Mismatch Error в коде.

Если мы прочитаем эти данные в числовую переменную, то получим
Type Mismatch Error VBA.

Dim rg As Range
Set rg = Sheet1.Range("B2:B5")

Dim cell As Range, Amount As Long
For Each cell In rg
    ' Ошибка при достижении ячейки с текстом «Нет»
    Amount = cell.Value
Next rg

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

Function CheckForTextCells(rg As Range) As Long

    ' Подсчет числовых ячеек
    If rg.Count = rg.SpecialCells(xlCellTypeConstants, xlNumbers).Count Then
        CheckForTextCells = True
    End If
    
End Function

Вы можете использовать это так:

Sub IspolzovanieCells()

    If CheckForTextCells(Sheet1.Range("B2:B6").Value) = False Then
        MsgBox "Одна из ячеек не числовая. Пожалуйста, исправьте перед запуском макроса"
        Exit Sub
    End If
    
    ' Продолжайте здесь, если нет ошибок

End Sub

Имя модуля

Если вы используете имя модуля в своем коде, это может привести к
Type Mismatch Error VBA. Однако в этом случае причина может быть не очевидной.

Например, допустим, у вас есть модуль с именем «Module1». Выполнение следующего кода приведет к о
Type Mismatch Error VBA.

Sub IspolzovanieImeniModulya()
    
    ' Type Mismatch Error
    Debug.Print module1

End Sub

Различные типы объектов

До сих пор мы рассматривали в основном переменные. Мы обычно называем переменные основными типами данных.

Они используются для хранения одного значения в памяти.

В VBA у нас также есть объекты, которые являются более сложными. Примерами являются объекты Workbook, Worksheet, Range и Chart.

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

Sub IspolzovanieWorksheet()

    Dim wk As Worksheet
    
    ' действительный
    Set wk = ThisWorkbook.Worksheets(1)
    
    ' Type Mismatch Error
    ' Левая сторона - это worksheet - правая сторона - это workbook
    Set wk = Workbooks(1)

End Sub

Коллекция Sheets

В VBA объект рабочей книги имеет две коллекции — Sheets и Worksheets. Есть очень тонкая разница.

Worksheets — сборник рабочих листов в Workbook
Sheets — сборник рабочих листов и диаграммных листов в Workbook

Лист диаграммы создается, когда вы перемещаете диаграмму на собственный лист, щелкая правой кнопкой мыши на диаграмме и выбирая «Переместить».

Если вы читаете коллекцию Sheets с помощью переменной Worksheet, она будет работать нормально, если у вас нет рабочей таблицы.

Если у вас есть лист диаграммы, вы получите
Type Mismatch Error VBA.

В следующем коде Type Mismatch Error появится в строке «Next sh», если рабочая книга содержит лист с диаграммой.

Sub SheetsError()

    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

End Sub

Массивы и диапазоны

Вы можете назначить диапазон массиву и наоборот. На самом деле это очень быстрый способ чтения данных.

Sub IspolzovanieMassiva()

    Dim arr As Variant
    
    ' Присвойте диапазон массиву
    arr = Sheet1.Range("A1:B2").Value
    
    ' Выведите значение в строку 1, столбец 1
    Debug.Print arr(1, 1)

End Sub

Проблема возникает, если ваш диапазон имеет только одну ячейку. В этом случае VBA не преобразует arr в массив.

Если вы попытаетесь использовать его как массив, вы получите
Type Mismatch Error .

Sub OshibkaIspolzovanieMassiva()

    Dim arr As Variant
    
    ' Присвойте диапазон массиву
    arr = Sheet1.Range("A1").Value
    
    ' Здесь будет происходить Type Mismatch Error
    Debug.Print arr(1, 1)

End Sub

В этом сценарии вы можете использовать функцию IsArray, чтобы проверить, является ли arr массивом.

Sub IspolzovanieMassivaIf()

    Dim arr As Variant
    
    ' Присвойте диапазон массиву
    arr = Sheet1.Range("A1").Value
    
    ' Здесь будет происходить Type Mismatch Error
    If IsArray(arr) Then
        Debug.Print arr(1, 1)
    Else
        Debug.Print arr
    End If

End Sub

Заключение

На этом мы завершаем статью об Type Mismatch Error VBA. Если у вас есть ошибка несоответствия, которая не раскрыта, пожалуйста, дайте мне знать в комментариях.

Источник

Модератор:Naeel Maqsudov

DanilK: Сообщения:2; Зарегистрирован:10 июн 2004, 10:00; Откуда:Днепропетровск; Контактная информация:

Приветствую участников форума!
Может кто пояснить мне, как избегать появления ошибки #знач! в Excel?
Ситуация такова: есть сводный файл, в котором собраны ссылки на другие файлы, в основном на сетевых дисках. При его открытии в ячейках стоят числовые значения, как и задумывалось… Однако, если обновить значения исходных файлов, возникает вышеуказанная ошибка… Приходится одновременно со сводным открывать и исходные файлы, тогда все ошибки исчезают, и значения обновляются — то есть смешения форматов ячеек нет!?
Похожая ситуация возникает, если при открытом сводном файле открыть какой либо, даже и не связанный с ним файл Excel — вместо чисел появляются #знач!
Таким образом, процесс работы со сводным файлом «несколько» усложняется, в итоге приходится открывать практически все исходные файлы по цепочке…
Подскажите, если сталкивались с такой ситуацией, как обновлять значения, не открывая все связанные файлы?
Windows 2000 Professional, Excel 2002

Naeel Maqsudov: Сообщения:2551; Зарегистрирован:20 фев 2004, 19:17; Откуда:Moscow, Russia; Контактная информация:

11 июн 2004, 06:58

1. Приведите текст формулы со ссылкой.
2. В Сервис/Параметры проверьте, чтобы были включены флажки «Обновлять удаленные ссылки» и «Сохранять значения внешних связей»; выключен «Игнорировать DDE-запросы от других приложений».

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

DanilK: Сообщения:2; Зарегистрирован:10 июн 2004, 10:00; Откуда:Днепропетровск; Контактная информация:

11 июн 2004, 10:27

Naeel Maqsudov
1. Так ведут себя обычно функции, вроде ВПР, СРЗНАЧ, а иногда просто ссылки на ячейку. Пример:
=СУММЕСЛИ(‘M:Tov_otch 2004Base[June.xls]Fact’!$V$2:$V$34;A26;’M:Tov_otch 2004Base[June.xls]Fact’!$O$2:$O$34)
Если просто обновить, то будет #ЗНАЧ!, единственный выход — одновременное открытие связанных файлов…
Причем, там же есть просто ссылка =’M:Tov_otch 2004Base[June.xls]Fact’!$O$39 так вот она обновляется!

Насчет форматов: по вышеуказанной ссылке формат сравниваемых ячеек общий, а ячеек с данными — числовой с 2 десятичными знаками и разделителем групп разрядов

2. В Сервис/Параметры флажки поставлены именно так, как Вы указали

Насчет перемещений файлов: с ними работает очень ограниченный круг работников, понимающих последствия таких действий, но и, насколько я понимаю, их следствием была бы ошибка #ССЫЛКА!

Naeel Maqsudov: Сообщения:2551; Зарегистрирован:20 фев 2004, 19:17; Откуда:Moscow, Russia; Контактная информация:

16 июн 2004, 02:04

насколько я понимаю, их следствием была бы ошибка #ССЫЛКА!

Нет.
#ССЫЛКА относится к последней части ссылки (за знаком !). Возникает, когда образовалась (в результате модификации формул после удаления/перемещения/добавления диапазонов на листе) ссылка, например, на несуществующую ячейку.
#ЗНАЧ происходит при несоответствии типов (КОРЕНЬ(А1), при текстовом значении в А1) а также при возникновении ошибок несоответствия типов или арифметического переполнения в VBA-функциях. Т.е. связана с невозможностью рассчитать результат выражения. Эта ошибка не связана с внешними ссылками.

По существу проблемы есть следующее предположение:
Так как Excel импользует разные механизмы для доступа к открытым и закрытым книгам, то возможно из-за того, что данная связанная книга имеет слишком большой размер, Excel не может вычислить формулу, сожержащую несколько DDE-запросов, но вычисляет формулу с одним DDE-запросом к ней.
Попробуйте СУММЕСЛИ высчислять три June.xls, т.е. свести все формулы с внешними ссылками к тривиальным ссылкам. Попробуйте уменьшить размер внещних книг. Если эксперименты с уменьшением размера дадут положительный результат, то значит решением проблемы будет только перевоз всего проекта на другую платформу, например, реализовать все в реляционной СУБД.

Источник

Как задается условие «если ошибка» — #ЗНАЧ!

Решение

Как задается условие «если ошибка» — #ЗНАЧ!

Решение

Как использовать ЕСЛИОШИБКА в VBA?

Типы ошибок, VBA IFERROR, можно найти

УЗНАТЬ БОЛЬШЕ >> Post Views: 805

Описание

Синтаксис

Аргументы или параметры

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

Применение

Тип функции

Пример (как функция рабочего листа)

Объяснение Type Mismatch Error

Использование отладчика

Присвоение строки числу

Недействительная дата

Ошибка ячейки

Неверные данные ячейки

Имя модуля

Различные типы объектов

Коллекция Sheets

Массивы и диапазоны

Заключение

УЗНАТЬ БОЛЬШЕ >>

Post Views: 805