Страницы: 1
RSS
Макросс VBA, для генерации инструций, Мне нужно написать макрос VBA, который позволит упростить написание инструкций для программистов.
 
Добрый день коллеги!

Мне нужно написать макрос VBA, который позволит упростить написание инструкций для программистов, которые переносят калькулятор (сделанный изначально в excel) на сайт.
Подобный макрос нужен, потому что написание инструкций в ручном режиме очень трудоемкий процесс из-за объемов калькулятора в excel и часто вносимыми изменениями.

Примеры, в приложении к теме:
-.1: пример расположения переменных в интерфейсе калькулятора на EXCEL;
-.2: пример инструкции для программистов (это наиболее удобный способ передачи алгоритма программистам);

Примерно описание калькулятора:
-в калькуляторе около 270 переменных;
-каждая переменная имеет наименование находящаяся во второй ячейке слева;
-переменные распределены в разных таблицах не всегда последовательно расположенными друг за другом, по 4 листам одной книги;

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

Файлы удалены - превышен дпустимый размер вложения [МОДЕРАТОР]
 
Это ТЗ для платного рздела. Перенести тему?
 
Я бы хотел сам написать, мне нужно только разобраться в каком направлении копать.
 
Доброе время суток
Цитата
Kanitele написал: Я бы хотел сам написать
Но пример нужен в любом случае, хотя бы для понимания - лучше один раз увидеть, чем сто раз прочитать. Ограничьтесь 10 переменными калькулятора. Главное указать взаимосвязи.
 
Цитата
Андрей VG написал:  Главное указать взаимосвязи.
 
Kanitele,  увы, не понял :(
Изменено: Андрей VG - 27.06.2020 08:42:23
 
Цитата
Андрей VG написал:
увы, не понял
Экий вы Андрей не понятливый, нужна кнопка - "Написать ТЗ"
По вопросам из тем форума, личку не читаю.
 
На примере ячейки W5 моего файла

в ней лежит формула: "=H8"
слева, в ячейке P5 ее наименование: "Монтаж межэтажного перекрытия"
нужно, как минимум, что бы алгоритм передал мне формулу указанной ячейки: "=площадь межэтажного перекрытия", в другую указанную ячейку

Желательно, что бы алгоритм мог построить цепочку из всех ячеек участвующих в расчете значения W5, т.е.:
Скрытый текст

И так далее. Т.е. мне как минимум нужно получит формулу с наименованиями переменных из одной ячейки.
 
проименуйте ваши ячейки и используйте имена.
По вопросам из тем форума, личку не читаю.
 
видимо, я избалован прослойкой в виде бизнес-аналитиков.Так и не понял, что должно быть результатом :(
 
Андрей, я примерно понял, но так как это требует разбора формулы поиска в них адреса, извлечение значения левее этого адреса, то овчинка на золотое руно становится похожа. Если использовать имена, как я описал выше, то все становится достаточно примитивно. написать процедуру которая  рекурсивно по именам пройдет и выдаст нужные строки не сложно. правда смущает тот факт что I8 заменяется на _ , но видимо есть тайный смысл :-)
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал: я примерно понял
Вот что значит быть админом, всё время на острие удовлетворения клиентских потребностей :excl:  С полувзгляда и полуслова понимает, что надо, а что нет. Но я бы так не выдержал бы. Поубивал бы всех нафиг.
 
Off
Андрей, на работе я едва сдерживаюсь. Вот на днях пишу рассылку, мол так и та для смены пароля тем кто работает через RDP нужно использовать Ctrl+Alt+End остальное все также. Один умник пишет- Михаил, у Вас опечатка, вы наверно Ctrl+Alt+Del имели в виду, но у меня эта эта комбинация вызывет меню на локальном ПК.... и ничего не получается... .  И так каждый день.
По вопросам из тем форума, личку не читаю.
 
Привет!

Kanitele, не Вы первый, не Вы последний.
"Перевести «программы», написанные в Excel на какой-либо язык программирования — практически нереально. Это займет уйму времени, а проблема постоянного обновления и проверки корректности делает такую задачу и вовсе нерешаемой."
Сравнение прайсов, таблиц - без настроек
 
Inexsu, "Перевести «программы», написанные в Excel на какой-либо язык программирования..." - Но мне это и не требуется.

Цитата
БМВ написал: I8 заменяется на _
У I8 нет имени, это ячейка для подмены значения пользователем взамен рассчитанной алгоритмом - т.е. она не нужна, да и если будет алгоритм, который проверяет имя на заданное количество ячеек левее, то имени не обнаружит.

Цитата
написать процедуру которая  рекурсивно по именам пройдет и выдаст нужные строки
Можете подсказать, как написать, есть аналоги или можете накидать примерный путь, что почитать, что изучить?

Цитата
извлечение значения левее этого адреса
ДА! Я не пользовался ранее функционалом наименования ячеек, с этим, что то можно придумывать, но: у меня почти 300 переменных и каждую переименовывать не хочется - можно ли это автоматизировать и нужно ли это если можно написать алгоритм проверки имени слева?

Цитата
Андрей VG написал: ...всё время на острие удовлетворения клиентских потребностей
Я благодаря planete хотя бы, что-то могу в excel, иначе жизнь была бы куда сложнее - здесь сидят гуру невероятного для меня уровня!

Цитата
я бы так не выдержал бы. Поубивал бы всех нафиг.
Неужели настолько тяжело написано?  
 
Kanitele, понимаете, тут ведь как, прелюдия - залог успеха. В данном случае, предварительная работа по наименованию областей и коррекции в формулах - потом делает жизнь в разы проще. и тут надо сравнивать затраты на то, что  до или  потом обработку писать с анализом а I2 это адрес в формуле или ....
Автоматизировать можно все, но если автоматизировать бардак, то получается автоматизированный бардак. Пробежаться по областям и присвоить имена  - не проблема - сразу бы можно и замену делать, но тут подвох, есть например I2 и i20  надо предусматривать что после I2 для замены нужно отсечь разделитель, скобки операторы ... и скатываеся к тому же анализу формулы, с определением адреса.  Короче  - все это может оказаться эффективнее руками сделать, чем писать умный макрос.
По вопросам из тем форума, личку не читаю.
 
Действуйте поэтапно.
1. Присвойте подходящие имена ячейкам. Разработчик даже предусмотрел для этого специальный механизм (которым нечасто пользуются). На примере #9:

  • На листе Калькулятор выделите ячейки K4:L5
  • Далее в главном меню Формулы/Создать из выделенного, птичка "в столбце слева" и OK. Теперь ячейки L4 и L5 должны быть правильно поименованы.
Повторите указанный прием для всех необходимых ячеек.
Изменено: sokol92 - 27.06.2020 19:58:28
Владимир
 
Цитата
sokol92 написал: Повторите указанный прием для всех необходимых ячеек.
Уже попробовал получается неплохо, то что надо, но хочется уточнить сложность автоматизации процесса.

Цитата
БМВ написал: прелюдия - залог успеха
Если бы.

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

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

Цитата
отсечь разделитель, скобки операторы
Зачем их отсекать? мне и нужно было все оставить, только ссылки на наименования заменить.

Цитата
все это может оказаться эффективнее руками сделать
Вот тут самое важное, я не могу оценить сложность предстоящей работы, поэтому очень прошу в этом помочь мне. На разработку инструкции по калькулятору, для программистов, мне требуется от 2 до 7 рабочих дней, при этом такое ТЗ должно быть максимально разжёванным, ну кому я это рассказываю, думаю вы сами все знаете. И я уже написал 6 инструкцию, что порядком тратит мои силы и время. Хотелось бы облегчить и ускорить работу, для чего наименование ячеек и диапазонов уже поможет, но тему на форуме я открывал для того, что бы раскрыть ее и понять стоит ли вообще игра свеч, т.к. инструкций еще будет.
 
После того, как закончите именовать ячейки, выложите файл - попробуем сочинить макрос для замены в формулах ссылок на адреса ячеек на имена ячеек.
Владимир
 
Все переименовал, только с W5 проблемы, наименование работает при вызове ячейки, но в графе наименования ячейки по прежнему стоит W5.
 
Цитата
Kanitele написал: неужели это плохо?
Цитирование - не бездумное копирование.

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

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

Код
Option Explicit
' Меняет в формуле ячейки ссылки на адреса ячеек на имена диапазонов
Sub CellChgFormula(ByVal Cell)
    Dim rg As Range, bChange As Boolean, formula As String, sFrom, sTo, i As Long, j As Long
    Dim matches As Object, match As Object
    Static regex As Object

    If regex Is Nothing Then
        Set regex = CreateObject("Vbscript.Regexp")
    End If
    regex.IgnoreCase = True: regex.Global = True
    regex.Pattern = "[!]?[$]?[A-Z]{1,3}[$]?[1-9][0-9]{0,6}([:][$]?[A-Z]{1,3}[$]?[1-9][0-9]{0,6})?" ' адрес диапазона ячеек (после имени листа)
  
    If IsObject(Cell) Then
        Set rg = Cell
    Else
        Set rg = Range(Cell)
    End If
    If Not rg.HasFormula Or rg.Cells.Count > 1 Then Exit Sub
    formula = rg.formula

    bChange = True
    Do While bChange
        bChange = False
        Set matches = regex.Execute(formula)   ' Execute search.
        If matches Is Nothing Then
            Exit Sub
        End If
        
        For Each match In matches
            sFrom = match.Value
            i = 1 + match.FirstIndex  ' номер первого символа найденного текста
            j = i + Len(sFrom) - 1    ' номер последнего символа найденного текста
     
            If Left(sFrom, 1) = "!" Then ' необходимо слева найти имя листа
                If Mid(formula, i - 1, 1) = "'" Then ' имя листа заключено в апострофы
                    i = InStrRev(formula, "'", i - 2)
                    If i = 0 Then
                        sFrom = ""
                    End If
                Else  ' в имени листа только буквы, цифры, знак подчеркивания
                    While i > 2 And (UCase(Mid(formula, i - 1, 1)) <> LCase(Mid(formula, i - 1, 1)) Or Mid(formula, i - 1, 1) Like "[0-9_]")
                        i = i - 1
                    Wend
                End If
                sFrom = Mid(formula, i, j - i + 1)
            End If
     
            ' sFrom  - адрес ячейки
            ' слева от найденного адреса не должно быть буквы, цифры, знака подчеркивания
            If sFrom <> "" Then
                If UCase(Mid(formula, i - 1, 1)) <> LCase(Mid(formula, i - 1, 1)) Or Mid(formula, i - 1, 1) Like "[0-9_]" Then sFrom = ""
            End If
    
            ' слева от найденного текста должно быть четное число двойных кавычек
            If sFrom <> "" Then
                If UBound(Split(Left(formula, i - 1), """")) Mod 2 <> 0 Then sFrom = ""
            End If
     
            If sFrom <> "" Then
                On Error Resume Next
                sTo = Range(sFrom).Name.Name
                If Err.Number = 0 Then
                    rg.formula = Left(formula, i - 1) & sTo & Mid(formula, j + 1)
                    bChange = (Err.Number = 0)
                    On Error GoTo 0
                    If bChange Then
                        formula = rg.formula
                        Exit For
                    End If
                End If
                On Error GoTo 0
            End If
        Next match
    Loop
End Sub

' Меняет в ячейках диапазон ссылки на адреса ячеек на имена диапазонов
Sub RangeChgFormula(ByVal r)
    Dim rg As Range, c As Range
    If IsObject(r) Then
        Set rg = r
    Else
        Set rg = Range(r)
    End If
    Application.ScreenUpdating = False
    For Each c In rg.SpecialCells(xlCellTypeFormulas).Cells
        CellChgFormula c
    Next c
    Application.ScreenUpdating = True
End Sub

Sub TestSel()
  RangeChgFormula Selection
End Sub

Владимир
 
Я разместил алгоритм в редакторе VBA:
применил, но вроде без эффекта, может что не так делаю?
 
Выложите пример (с макросом) в файле и опишите свои действия: выделил такой-то диапазон, запустил макрос TestSel, не получил желаемого эффекта (адрес именованной ячейки такой-то не заменился на имя ячейки).
Кроме того, макросы необходимо поместить в стандартный модуль, а не в модуль листа (как в предыдущей картинке).
Изменено: sokol92 - 03.07.2020 13:05:58
Владимир
 
Зачем тупо копировать предыдущее сообщение?! Прочитайте еще раз сообщение №21 [МОДЕРАТОР]

Добрый день!

Внес алгоритм в конструктор - возможно неправильно, в файле увидите.
Применил макросс к выделенным диапазонам (H4:H8) и (L4:L8) - никакого эффекта.
 
Вы не провели работу над ошибками. Необходимо:
  • присвоить имена необходимым ячейкам - см. #17 и #19. Я присвоил в прилагаемом к данному сообщению файле  имена ячейкам L4:L5
  • занести код макроса в стандартный модуль - см. #24. У Вас код - в модуле листа.  Я переставил
  • выделить ячейки и применить макрос. Я выделил ячейки H4:H8 и применил макрос. Формула в ячейке H8 изменилась в части ссылки на ячейку L5.
Владимир
Страницы: 1
Наверх