Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 08.09.2011, 21:11   #1
bdfy
Форумчанин
 
Регистрация: 12.11.2009
Сообщений: 258
По умолчанию Application.ConvertFormula - как заставить обрабатывать длинные формулы ?

столкнулся с проблемой. есть формула вот такого вида.
"=(($B$4+$B$5+$B$6+$B$10)*(B24*$B$7 +(B18+B20+B22+B24)*$B$10))/(($B$8+$B$7+$B$10)*($B$4+$B$5+$B$6+ $B$10)-$B$10^2)-($B$10*(B18*$B$5+(B18+B20)*$B$6+(B1 8+B20+B22+B24)*$B$10))/(($B$8+$B$7+$B$10)*($B$4+$B$5+$B$6+ $B$10)-$B$10^2)"
220 знаков
надо применить команду
Цитата:
r0 = Application.ConvertFormula(x, xlA1, xlA1, True)
Debug.Print r0
сыпется - Error 2015. а ведь даже не 255 символов. как обойти проблему ? напрашивается решение: разделить строку на несколько более мелких, потом обработать отдельно и сложить.
проблема в том что определить корректное место разрыва не вижу возможности а если разорвать в произвольном месте без учета скобок, т.е получить выражение вида
s = "(($B$4+$B$5+$B$6+$B$10)*(B24*$B$7+ (B18+B20+B22+B24)*$B$10))/(($B$8+$B$7+$B$10)*($B$4+$B$5+$B$6+ "
то оно тоже не обработается. что делать ? как выйти из ситуации ?

погуглил. проблема попадается - решение пока не попалось ((
http://www.mrexcel.com/forum/showthread.php?t=208317

Последний раз редактировалось bdfy; 08.09.2011 в 21:18.
bdfy вне форума Ответить с цитированием
Старый 08.09.2011, 21:18   #2
EducatedFool
Программист VBA
СуперМодератор
 
Аватар для EducatedFool
 
Регистрация: 13.07.2008
Сообщений: 6,858
По умолчанию

А зачем вы конвертируете формулу???
Формула изначально имеется в виде текстовой строки, или хранится в ячейке?
EducatedFool вне форума Ответить с цитированием
Старый 09.09.2011, 11:56   #3
bdfy
Форумчанин
 
Регистрация: 12.11.2009
Сообщений: 258
По умолчанию

я весь код написал эдак год назад... помню далеко не все если верить комментариям: преобразование это делается чтобы все ссылки сделать абсолютными. после этого со строкой можно удобно работать используя регулярные выражения - ссылки очевиднее становятся.
сейчас макрос посыпался - на длинной формуле. проблема именно в этом преобразовании. в макрос формула передается в виде текстовой строки.
bdfy вне форума Ответить с цитированием
Старый 09.09.2011, 13:00   #4
Казанский
Старожил
 
Аватар для Казанский
 
Регистрация: 31.12.2010
Сообщений: 2,133
По умолчанию

Там результирующая формула, наверно, получается длинее 255.
Можно с помощью регулярных находить выражения в скобках, конвертить и сохранять, для перехода к следующему уровню заменять на уникальное случайное число.
А лучше ИМХО оптимизировать формулы.
exceleved@yandex.ru Яндекс.Деньги: 410011500007619

Последний раз редактировалось Казанский; 09.09.2011 в 13:03.
Казанский вне форума Ответить с цитированием
Старый 09.09.2011, 13:21   #5
Казанский
Старожил
 
Аватар для Казанский
 
Регистрация: 31.12.2010
Сообщений: 2,133
По умолчанию

Ан нет, не длиннее 255. Попробуйте:
Код:
Sub bb()
Dim x, r0, i&, y
x = "=(($B$4+$B$5+$B$6+$B$10)*(B24*$B$7+(B18+B20+B22+B24)*$B$10))/(($B$8+$B$7+$B$10)*($B$4+$B$5+$B$6+$B$10)-$B$10^2)-($B$10*(B18*$B$5+(B18+B20)*$B$6+(B18+B20+B22+B24)*$B$10))/(($B$8+$B$7+$B$10)*($B$4+$B$5+$B$6+$B$10)-$B$10^2)"
For i = Len(x) / 2 To 1 Step -1
    r0 = Application.ConvertFormula(Left$(x, i), xlA1, xlA1, True)
    If Not IsError(r0) Then Exit For
Next
If i = 0 Then Stop 'не удалось

y = Application.ConvertFormula("1" & Mid$(x, i + 1), xlA1, xlA1, True)
If IsError(y) Then Stop 'не удалось
r0 = r0 & Mid(y, 2)
Debug.Print r0
ActiveCell.Formula = r0
End Sub
exceleved@yandex.ru Яндекс.Деньги: 410011500007619
Казанский вне форума Ответить с цитированием
Старый 10.09.2011, 21:25   #6
bdfy
Форумчанин
 
Регистрация: 12.11.2009
Сообщений: 258
По умолчанию

спасибо. работает ваш код. до сих пор прадва не пойму как ((
bdfy вне форума Ответить с цитированием
Старый 11.09.2011, 16:17   #7
Казанский
Старожил
 
Аватар для Казанский
 
Регистрация: 31.12.2010
Сообщений: 2,133
По умолчанию

Оформил в виде функции, с комментариями. Пример использования в файле.
Код:
Function ConvertLongFormula(sFrml, FromReferenceStyle As XlReferenceStyle, Optional ToReferenceStyle As XlReferenceStyle, Optional ToAbsolute As XlReferenceType, Optional RelativeTo)

'функция предназначена для замены метода Application.ConvertFormula,
'позволяет работать с более длинными формулами.
'аргументы такие же, как у метода Application.ConvertFormula
                                    
' http://programmersforum.ru/showthread.php?t=165108
' Алексей, exceleved@yandex.ru
'======================================================================
                                    
Dim i&, x, y
                                    'сначала пытаемся конвертировать формулу как есть
ConvertLongFormula = Application.ConvertFormula(sFrml, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
If IsError(ConvertLongFormula) Then 'если не получилось
    For i = Len(sFrml) / 2 To 3 Step -1 'берем левую половину формулы и уменьшаем длину, пока не получится формула без ошибки
        x = Application.ConvertFormula(Left$(sFrml, i), FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
        If Not IsError(x) Then Exit For
    Next
    If Not IsError(x) Then          'найдена левая часть формулы
                                    'конвертируем правую часть, подставляя "1" вместо левой части
        y = Application.ConvertFormula("1" & Mid$(sFrml, i + 1), FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
        If Not IsError(y) Then      'если нет ошибки, склеиваем части и выходим
            ConvertLongFormula = x & Mid$(y, 2)
            Exit Function
        Else
            GoTo try_right
        End If
    Else                            'с левой частью не получилось
try_right:
        For i = Len(sFrml) / 2 To Len(sFrml) - 2 'берем правую половину формулы и уменьшаем длину, пока не получится формула без ошибки
            x = Application.ConvertFormula(Mid$(sFrml, i), FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
        If Not IsError(x) Then Exit For
        Next
        If Not IsError(x) Then          'найдена правая часть формулы
                                        'конвертируем левую часть, подставляя "1" вместо правой части
            y = Application.ConvertFormula(Left$(sFrml, i - 1) & "1", FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
            If Not IsError(y) Then      'если нет ошибки, склеиваем части и выходим
                ConvertLongFormula = Left$(y, Len(y) - 1) & x
                Exit Function
            End If
        End If          'п-ц не лечится, возвращается ошибка
    End If
End If
End Function
Вложения
Тип файла: rar ConvertLongFormula.rar (10.0 Кб, 13 просмотров)
exceleved@yandex.ru Яндекс.Деньги: 410011500007619
Казанский вне форума Ответить с цитированием
Старый 11.09.2011, 21:40   #8
bdfy
Форумчанин
 
Регистрация: 12.11.2009
Сообщений: 258
По умолчанию

в который раз убеждаюсь какое же все таки VBA зло... сколько трудов чтобы элементарную вещь заставить работать правильно. спасибо буду использовать ваш код. хотя возможно лучше на RegEx'ах переписать функцию конвертации формул в перспективе
bdfy вне форума Ответить с цитированием
Старый 11.09.2011, 21:55   #9
EducatedFool
Программист VBA
СуперМодератор
 
Аватар для EducatedFool
 
Регистрация: 13.07.2008
Сообщений: 6,858
По умолчанию

Цитата:
в который раз убеждаюсь какое же все таки VBA зло... сколько трудов чтобы элементарную вещь заставить работать правильно
О да, ещё какое зло... особенно, если пытаться делать всё через ж..у

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

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

За эти годы я использовал Application.ConvertFormula всего один раз, и RegExp около 10 раз.

PS: Если формула нормально составлена - никакие RegExp не нужны (и тем более, преобразования типа ConvertFormula).
Вы пытаетесь бороться со следствием проблемы, а не с её причиной.

У нас на форуме много спецов по формулам - давно бы привели ваши формулы к нормальному виду (если бы вы показали свой файл, и описали, что надо получить в результате)

PPS: Список ссылок на ячейки можно и без RegExp вытащить - у объекта Range есть свойства Dependents и Precedents (о которых вы, видимо, не знали)

Последний раз редактировалось EducatedFool; 11.09.2011 в 21:57.
EducatedFool вне форума Ответить с цитированием
Старый 11.09.2011, 21:55   #10
Казанский
Старожил
 
Аватар для Казанский
 
Регистрация: 31.12.2010
Сообщений: 2,133
По умолчанию

Вы бы описали задачу целиком, а не отдельными частями. Наверняка можно реорганизовать данные, изменить алгоритм, чтобы уйти от монструозных формул.
exceleved@yandex.ru Яндекс.Деньги: 410011500007619
Казанский вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц



Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Как обрабатывать события с задержкой? gramp Microsoft Office Excel 9 12.06.2011 21:36
Excel. Длинные формулы. v00d00 Microsoft Office Excel 4 14.01.2010 18:31
Как обрабатывать данную ситуацию? Arkuz БД в Delphi 2 08.12.2008 02:51
Как заставить компонент TTrackBar обрабатывать события мыши? Никки Общие вопросы Delphi 5 29.08.2008 14:17
как обрабатывать события с клавы? proglamer Паскаль, Turbo Pascal, PascalABC.NET 13 25.10.2007 11:41