Vba excel ошибка знач - Решение и исправление самых разных ошибок на TopOshibok.ru

se_arts

0 / 0 / 0

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

Сообщений: 91

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

13.03.2018, 10:39. Показов 9610. Ответов 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

6919 / 2829 / 543

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

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

13.03.2018, 10:41

Visual Basic

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

Казанский

15139 / 6413 / 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

3866 / 2282 / 765

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

Сообщений: 6,023

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

6919 / 2829 / 543

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

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

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, Казанский — всем спасибо.

Источник

Алексей

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

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

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

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

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

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

Юрий М

Модератор

Сообщений: 60912
Регистрация: 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

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

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

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

Алексей

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

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

17.12.2021 22:21:54

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

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

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

ту же самую

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

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

PS Excel 2016.

Юрий М Модератор Сообщений: 60912 Регистрация: 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

Юрий М

Модератор

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

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

17.12.2021 22:49:08

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

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

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

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

Алексей

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

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

#12

17.12.2021 22:56:39

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

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

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

БМВ

Модератор

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

Excel 2013, 2016

#14

17.12.2021 23:00:12

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

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

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

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

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

Юрий М

Модератор

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

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

#15

17.12.2021 23:06:53

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

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

))

Алексей

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

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

#16

17.12.2021 23:10:31

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

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

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

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

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

vikttur

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

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

#18

17.12.2021 23:22:01

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

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

Юрий М

Модератор

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

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

Алексей

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

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

#24

18.12.2021 00:09:13

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

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

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

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

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

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

Msi2102

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

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

#25

18.12.2021 00:20:40

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

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

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

БМВ

Модератор

Сообщений: 21914
Регистрация: 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) Щербаков

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

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

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

#28

18.12.2021 14:25:05

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

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

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

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

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

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

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

Источник

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,0527 ] [ 16 queries used ] [ Generated: 21.09.23, 18:58 GMT ]

Источник

Как обойти значение ячейки #ЗНАЧ!

SergejSor

Дата: Пятница, 09.11.2012, 09:45 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 49

Репутация:
0
±

Замечаний:
0% ±

Всем доброго дня. Проблема в следующем, при переборе в столбце ячеек, как только появляется ячейка со значением #ЗНАЧ! то есть с ошибкой, макрос останавливается. Вопрос как можно игнорировать это значение, пропускать например или же чтоб макрос не останавливался. Сам макрос проверяет первый столбец, если значения одинаковые, то суммирует значения из другого столбца, до тех пор пока значение первого столбца не изменится на большее. Ошибки в формулах приводящие к #ЗНАЧ!, можно конечно убрать, но пока это проблематично. Спасибо.
Пример кода:
[vba]

Code

Sub raschet()
Dim xl As Long
Dim st, zn, sum As Variant
st = 5
zn = 5
Do
sum = 0
zn = st
              Do
                  sum = Range(«BS» & st).Value + sum
                  st = st + 1
              Loop Until Range(«A» & st).Value > Range(«A» & st — 1).Value
Range(«BT» & zn).Value = sum
Loop Until Range(«A» & st + 1) = «»

End Sub

[/vba]

Сообщение отредактировал SergejSor — Пятница, 09.11.2012, 09:48

Ответить

Саня

Дата: Пятница, 09.11.2012, 10:45 |
Сообщение № 2

Группа: Друзья

Ранг: Ветеран

Сообщений: 1067

Репутация:
560
±

Замечаний:
0% ±

XL 2016

напиши в начале процедуры:
[vba][/vba]

вариант «дикий», но чтобы написать грамотный обработчик ошибок, нужно понимать нюансы…

Ответить

Kuzzka

Дата: Пятница, 09.11.2012, 10:51 |
Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 30

Репутация:
0
±

Замечаний:
0% ±

На 2007-10 есть формула ЕСЛИОШИБКА, её нельзя использовать?

Как говорит в таких случаях мой психолог:
Да пошли они на… твари долбаные…

Ответить

SergejSor

Дата: Пятница, 09.11.2012, 11:42 |
Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 49

Репутация:
0
±

Замечаний:
0% ±

Quote (Саня)

on error resume next

вариант «дикий», но чтобы написать грамотный обработчик ошибок, нужно понимать нюансы

Такой вариант подходит, спасибо огромное!

Ответить

Саня

Дата: Пятница, 09.11.2012, 12:45 |
Сообщение № 5

Группа: Друзья

Ранг: Ветеран

Сообщений: 1067

Репутация:
560
±

Замечаний:
0% ±

XL 2016

Quote (Kuzzka)

На 2007-10 есть формула ЕСЛИОШИБКА, её нельзя использовать?

для отлавливания ошибок использовать функции рабочего листа не советую.

более тонкий подход:
[vba]

Code

dim v
v=Range(«BS» & st).Value
if v<>»Error 2015″ then                 ‘  #ЗНАЧ!
‘ if TypeName(v)<>»Error» then   ‘  любая ошибка
‘ if TypeName(v) = «Double» then   ‘ если там число, или так   IsNumeric(v)
    sum = sum + v
end if

[/vba]

Ответить

nerv

Дата: Пятница, 09.11.2012, 21:56 |
Сообщение № 6

Группа: Редакторы

Ранг: Обитатель

Сообщений: 431

[vba][/vba]

Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина — самый громкий звук

YM 41001156540584 / WM WMR R21924176233

https://github.com/nervgh/vba

Ответить

Leojse

Дата: Четверг, 08.08.2013, 12:19 |
Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 148

Репутация:
1
±

Замечаний:
0% ±

2010/2013

Здравствуйте. Есть 2 столбца. В ячейке A1322 и B1322 суммируются числа синих ячеек (используется макрос) по столбцам. Но проблема в том, что в ячейке В1322 постоянно выдается #знач. Подскажите, в чем может быть проблема? Причем если в формуле изменить диапазон суммирования на B8:B198, то макрос начинает считать, но если добавить до 199, то выдает ошибку. Менял форматы, чистил содержимое, ничего не помогает…

Ответить

Leojse

Дата: Четверг, 08.08.2013, 12:24 |
Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 148

Репутация:
1
±

Замечаний:
0% ±

2010/2013

Приклепляю файл (почему-то сразу не приложился…)

К сообщению приложен файл:

1099458.xls
(99.0 Kb)

Ответить

Источник

Модератор: 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 2004\Base\[June.xls]Fact’!$V$2:$V$34;A26;’M:\Tov_otch 2004\Base\[June.xls]Fact’!$O$2:$O$34)
Если просто обновить, то будет #ЗНАЧ!, единственный выход — одновременное открытие связанных файлов…
Причем, там же есть просто ссылка =’M:\Tov_otch 2004\Base\[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, т.е. свести все формулы с внешними ссылками к тривиальным ссылкам. Попробуйте уменьшить размер внещних книг. Если эксперименты с уменьшением размера дадут положительный результат, то значит решением проблемы будет только перевоз всего проекта на другую платформу, например, реализовать все в реляционной СУБД.

Источник