Здравствуйте! Данная проблема вытекает из прошлой моей темы: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=133191&...= Однако суть другая. Сегодня использовал написанную (не без Вашей помощи, за что еще раз спасибо) формулу для сортировки текстовых данных "снизу-вверх" и замене в них направлений сторон света на противоположные. Думаю, стоит еще раз ее воспроизвести:
Код
Function Переворачиватель_для_описания(массив, последняя, текущая)
Переворачиватель_для_описания = IIf(Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения.") = WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " южном ", " северном "), " юго-западном ", " северо-восточном "), " западном ", " восточном "), " северо-западном ", " юго-восточном "), " против течения.", " по течению."), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения."))
End Function
Аргументы "массив", "последняя" и "текущая" я беру из другого файла exel (поскольку Exel позволяет это делать, ну и чтобы оба файла обновлялись сразу). Но есть... Не то, чтобы проблема... Скорее неприятный баг: Для условности назову файл-источник, из которого беру значения аргументов для формулы - "X", а файл, в который вбиваю формулу - "У". Короче: выбирать значения аргументов в формулу для файла "У" из файла "Х" я могу, только когда файл "Х" непосредственно открыт. После ввода аргументов я получаю ожидаемый результат в виде замененного текста файла "Х". Если я потом закрываю файл "Х", а файл "У" оставляю открытым - содержимое ячеек с формулой в файле "У" не меняется. Если же я закрою файл "У", а потом снова открою его - в ячейках с формулой вместо значений выдается ошибка #ЗНАЧ. Проверяю аргументы - все в них правильно, даже путь указан к файлу. Если же я открываю дальше файл "Х" - в файл "У" возвращаются значения! То есть: для комфортной работы и вывода результатов расчета формулы, мне ВСЕГДА необходимо держать открытыми ОБА файла? Просто при использований "внутриэкселевских" функций (тот же ИНДЕКС, который я использую и в своей формуле) при тех же условиях данная ошибка не возникает вовсе.
С чем эта ошибка связана? Можно-ли это как-то исправить?
Дмитрий Величко, формулы, которые работают с закрытыми внешними файлами делают это при помощи связей, которые сохраняются в книге. А как написана ваша UDF из вашего сообщения не видно. Судя по всему, она эти связи не использует.
PooHkrd, у меня в этой вкладке ситуация с 1 скрина. В строках как раз результаты из формулы. Сейчас оба документа открыты. А вот, когда я закрыл документ Х и обновил связь, получилось, как на скрине 2
Function Переворачиватель_для_описания(массив_исх, последняя, текущая)
Dim массив
Select Case TypeName(массив_исх)
Case "Range"
массив = массив_исх.Value
Case "Variant()"
массив = массив_исх
End Select
Переворачиватель_для_описания = IIf(Replace(Replace(Replace(Replace(Replace( _
WorksheetFunction.Index(массив, последняя - текущая + 1, 1) _
, " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения.") = WorksheetFunction.Index(массив, последняя - текущая + 1, 1), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя - текущая + 1, 1), " южном ", " северном "), " юго-западном ", " северо-восточном "), " западном ", " восточном "), " северо-западном ", " юго-восточном "), " против течения.", " по течению."), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя - текущая + 1, 1), " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения."))
End Function
последняя и текущая заменил в самой формуле - незачем для этих аргументов ссылаться на другую книгу, т.к. они представляют собой номер строки. Поэтому записывайте формулу в ячейку так:
Дмитрий(The_Prist) Щербаков, Спасибо! Все работает! =) Можно несколько вопросов, касаемо кода? Я в этом чайник, но хочется знать: что все-таки в нем написано? 1 -Dim - это оператор для создания нового аргумента "массив"? Можно ли без этого "массив" обойтись, оставив только аргумент "массив_исх" в основной формуле? 2 - что делает часть "SelectCase - End Select"? Ну и все, что внутри) 3 - а для чего в самой формуле абзацы и нижние подчеркивания на концах первой и второй строки? Просто пробежался по содержанию - не нашел отличий от своей, кроме как в этом. Однако с моей прошлой формулой команда не работает... А, ну еще аргумент "1" в конце Индекса. Это ведь необязательный аргумент, насколько я понимаю?
Дмитрий(The_Prist) Щербаков написал: последняя и текущая заменил в самой формуле - незачем для этих аргументов ссылаться на другую книгу, т.к. они представляют собой номер строки. Поэтому записывайте формулу в ячейку так:
В моем случае лучше оставить оператора ".Row" внутри кода, чтобы было удобнее вбивать аргументы коллегам, которые не сильно в Екселе шарят =) Спасибо за знакомство с оператором Dim. Теперь смог немного "сжать" код =)
Код
Function Переворачиватель_для_описания(массив_исх, последняя, текущая)
Dim индекс
Dim массив
Dim С
Dim СВ
Dim В
Dim ЮВ
Dim Ю
Dim ЮЗ
Dim З
Dim СЗ
Dim ПО
Dim ПР
Select Case TypeName(массив_исх)
Case "Range"
массив = массив_исх.Value
Case "Variant()"
массив = массив_исх
End Select
индекс = WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1, 1)
С = " северном "
СВ = " северо-восточном "
В = " восточном "
ЮВ = " юго-восточном "
Ю = " южном "
ЮЗ = " юго-западном "
З = " западном "
СЗ = " северо-западном "
ПО = " по течению."
ПР = " против течения."
Переворачиватель_для_описания = IIf(Replace(Replace(Replace(Replace(Replace(индекс, С, Ю), СВ, ЮЗ), В, З), ЮВ, СЗ), ПО, ПР) = индекс, Replace(Replace(Replace(Replace(Replace(индекс, Ю, С), ЮЗ, СВ), З, В), СЗ, ЮВ), ПР, ПО), Replace(Replace(Replace(Replace(Replace(индекс, С, Ю), СВ, ЮЗ), В, З), ЮВ, СЗ), ПО, ПР))
End Function
В принципе, остался вопрос только касаемо "Select Case" и аргумента "1" в индексе. Он вроде и необязательный, но без него формула не работает...
Дмитрий Величко: Данная проблема вытекает из прошлой моей темы
… в которой вам несколько помогающих, в том числе и я, несколько раз сказали, что ваше "решение" крайне неустойчивое и громоздкое, а также предложили помочь сделать нормально, если вы объясните все условия… Но вам же виднее… Удачи в решениях
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Option Explicit
'====================================================================================================
' Диапазон.Rows.Count - общее количество строк в диапазоне
' Application.Caller.Row - аналог функции листа =СТРОКА(), то есть возвращает номер текущей строки с функцией
' Диапазон.Cells(1, 1).Row - номер первой строки диапазона (ведь он не всегда может начинаться со сторой строки листа)
' Диапазон(N) - Nый элемент диапазона (напрямую вместо индекса)
' дальше чистая математика и логика
'====================================================================================================
Function Перевернуть(Диапазон As Range)
Перевернуть = Диапазон(Диапазон.Rows.Count - Application.Caller.Row + Диапазон.Cells(1, 1).Row)
End Function
'====================================================================================================
'====================================================================================================
Function Поменять(Ячейка)
Dim x, r&, n&
Static arrFR
If Not IsArray(arrFR) Then ' если массив "найти заменить" ещё не был определён, то определяем (чтобы не вычислять для какждого вхождения функции)
ReDim arrFR(1 To 4, 1 To 2)
arrFR(1, 1) = "северо-": arrFR(1, 2) = "юго-" ' 1я строка массива: 1ый столбец = "северо-", 2ой столбец = "юго-"
arrFR(2, 1) = "северном": arrFR(2, 2) = "южном" ' 2я строка массива …
arrFR(3, 1) = "восточном": arrFR(3, 2) = "западном" ' 3я строка массива …
arrFR(4, 1) = "по течению": arrFR(4, 2) = "против течения" ' 4я строка массива …
End If
x = Ячейка ' запоминаем значение ячейки в переменную
For r = 1 To UBound(arrFR, 1) ' цикл по всем строкам массива "найти и заменить" (в данном случае: от 1 до 4ёх)
For n = 0 To 1
If InStr(x, arrFR(r, n + 1)) Then ' если в ячейке найдено значение из первого столбца, то меняем на значение второго столбца и наоборот
x = Replace(x, arrFR(r, n + 1), arrFR(r, 2 - n))
Exit For
End If
Next n
Next r
Поменять = x ' функция возвращает заменённое значение
End Function
'====================================================================================================
Скрин
Название темы (и той и этой - всё тоже самое):UDF. Перевернуть диапазон и заменить фразы по списку
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: Дмитрий Величко , проверяйте Я бы делал так
Для меня это слишком заумно пока . Я в кодинге человек непросвещенный. А хотелось бы не просто готовые результаты спрашивать с других, а самому чему-то научиться (собственно, уже немного почерпнул ). Но спасибо! Проверю и буду анализировать сие творение)
HATEfield: хотелось бы не просто готовые результаты спрашивать с других, а самому чему-то научиться
добавил комментарии к кодам из #14 — изучайте
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Дмитрий Величко написал: Можно ли без этого "массив" обойтись, оставив только аргумент "массив_исх" в основной формуле?
Можно. Но текст функции раздуется вдвое.
Цитата
Дмитрий Величко написал: В моем случае лучше оставить оператора ".Row" внутри кода
можете и так сделать. Только ссылку на диапазон надо делать на диапазон из книги с функцией, а не на внешнюю книгу. Иначе определить номер строки будет очень сложно при закрытой книге и код будет еще сложнее.
Цитата
Дмитрий Величко написал: для чего в самой формуле абзацы и нижние подчеркивания на концах первой и второй строки?
лучше справку открыть или поисковик, т.к. все Ваши вопросы - это азы программирования в VBA и неплохо находятся поиском. Нет смысла разъяснять каждый пункт.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...