» Excel VBA (часть 2)

Обратите внимание, этот топик для помощи в изучении и использовании VBA. Посему запросы типа "Напишите мне такой-то макрос, я VBA не знаю и знать не хочу" не приветствуются.
Древняя мудрость: "Накорми голодного рыбой и он погибнет, научи его ловить рыбу и ты спасешь его."(R)

Предыдущие ветки топика: Часть 1

Информация общего характера:
Список соответствия имен функций в английской и русской версиях Excel
Описание Microsoft Excel File Format (eng.)

Рекомендации:
Если у Вас есть проблема, не решаемая стандартными средствами Excel (об этом можно уточнить здесь) или требующая автоматизации, попробуйте для начала записать макрос самим Excel через меню Сервис (Tools) - Макрос (Macro) - Начать запись (Record New Macro). Подробнее здесь. В большинстве случаев получившийся код (Сервис-Макрос-Макросы-Изменить) Вас не удовлетворит, но подскажет, какие объекты-методы-свойства использовать.
Другой Ваш помощник - Просмотр объектов (Object Browser). Ну и встроення помощь (F1), естественно.

Если Вы в тупике, покажите Ваш код (или часть кода) здесь. Если вылазит ошибка, цитируйте ее полностью. Если код слишком большой, используйте тeг [no][more][/no].
Используйте отладчик - Breakpoints (F9), Watches (Shift-F9), Steps (F8 и др.) Сильно облегчает поиск ошибок.

Рекомендуется к прочтению:
Первые шаги с Excel VBA
Excel VBA: Приёмы программирования
WinApi. Лекция из курса "Основы офисного программирования и язык VBA" (для продвинутых)
Daily Dose of Excel (eng.) - тематический блог: советы по работе с Excel и прочие материалы
Excel Macros & Excel VBA Code Tips, Tricks (eng.) - советы, трюки и уловки
Mr. Excel (forum) (eng.) - весьма оживленный форум по Excel&VBA.
Приемы, хитрости, трюки и нюансы работы в Microsoft Excel - сайт "Планета Excel", целиком посвященный Excel и всему, что с ним связано.
Microsoft Excel: Таблицы и VBA. Справочник. Вопросы и Ответы. Советы. Примеры.

Родственные топики:
Вопросы по работе с MS Excel - Excel FAQ - часть 1, часть 2, часть 3
Технические проблемы с MS Office 2003 или Office XP.
Word VBA все вопросы по Word VBA туда
Access все вопросы по программированию в Access туда
Книжульки по VBA - книги по программированию с использованием VBA

Конкретные вопросы:
Форма-заставка
Как запустить макрос при изменении положения курсора или значения ячейки
Пример 1
Пример 2
Пример 3 (проверка области)
Пример 4
Пример 5
Зацикливание в функции Change или SelectionChange

Ранжирование без пробелов (макрос включает функции сортировки массива и удаления дубликатов, работает и в Excel 2007)
под Office 97

Добавление в главное меню своего пункта, ассоциированного с макросом
Создание ярлыка на рабочем столе
Снятие защиты листа при забытом пароле
Смена раскладки клавиатуры
Скролл формы колесом прокрутки мыши
Оптимизация кода по быстродействию использованием массивов
Найти "чужое" окно и нажать в нем кнопку (вписать текст в текстовое поле)
Работа с UNICODE-символами в VBA: запись, чтение из ячейки, перевод в ASС и обратно
Как программно подключить дополнительные библиотеки (например, "Microsoft Scripting Runtime" или "Microsoft ActiveX Data Objects 2.8 Library) через References

Код: Option Explicit

Sub analiz_vyrobn_rentab()

Const strMonth As String = "04"
Dim lngData As Long, lngfnum As Long
Const strPath As String = "\\hlibprom\fs\Documents\FAU\ANALYTICS\RENTABEL\2007\2007"
Dim strFullPath As String, strT As String

For lngData = 105 To 134
For lngfnum = 1 To 31
strT = Format$(CStr(lngfnum), "00")
strFullPath = "='" & strPath & Month & "\Розсилка\[Рентабельність " & strT & "_" & strMonth & "_07.xls]ХЗ'!"

Sheets("ВХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C4"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C4"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C4"

Sheets("ЛХЗ №5").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C5"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C5"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C5"

Sheets("ГХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C7"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C7"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C7"

Sheets("БХ").Select

Cells(data, 2).FormulaR1C1 = strFullPath & "R5C8"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C8"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C8"

Sheets("МП").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C9"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C9"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C9"

Sheets("ЧГ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C10"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C10"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C10"

Sheets("КХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C11"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C11"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C11"

Sheets("СХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C12"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C12"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C12"

Sheets("РХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C13"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C13"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C13"

Sheets("ЖШ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C14"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C14"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C14"

Sheets("КМХ").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C15"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C15"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C15"

Sheets("ШП").Select
Cells(data, 2).FormulaR1C1 = strFullPath & "R5C16"
Cells(data, 3).FormulaR1C1 = strFullPath & "R34C16"
Cells(data, 4).FormulaR1C1 = strFullPath & "R33C16"
'If lngfnum = 17 Then Exit Sub
Next lngfnum
Next lngdata
End Sub

The okk

Цитата:

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

Так этот код, повидимому, будет раз в месяц собирать сводку из файлов, а результат сохраняться под новым именем месяца.
Вот только стоит ли номер месяца делать константой, может лучше простым InputBox воспользоваться? Да и цикл по номерам дней, контроль на количество не помешал бы, а то в 04 (в апреле) их 30

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

в ячейке формула = Diapozon (С8:В16)

Public Function Diapozon(N As String) As Variant
Dim N1 As Range
Set N1 = Range(N)
Diapozon = Range(N1).Cells.Count
End Function

Не работает, в чем ошибка?

AndVGri

Цитата:

Так этот код, повидимому, будет раз в месяц собирать сводку из файлов, а результат сохраняться под новым именем месяца.

На цикл посмотри В переводе на русский это будет:
1. Взять данные за 1-й день. Записать.
2. Взять данные за 2-й день. Записать.
...
31. Взять данные за 31 день. Записать.
В итоге получится, что 30 предыдущих действий - "в молоко" и результат даст лишь 31-е повторение цикла. Там либо сразу все строки записать (благо, формула в стиле RC это позволяет), либо записывать через Cells(Rows.Count, lngColNum).End(xlUp).Offset(1) в последнюю строку. Но уж никак не в data, которая внутри вложенного цикла меняться не будет (data = data +1 - неправильное решение). А вот lnglastrow = lnglastRow+1, как альтернатива Offset вполне имеет право на жизнь (кстати, дает еще незначительный выигрыш по быстродействию - проверял!).

Цитата:

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

Знаю, но в константу я его запихнул, поскольку в том варианте он и был по сути константой (просто инициировался в каждом цикле). Я ничего не менял - просто доработал код (привел его к удобочитаемому виду), только и всего. Так ошибки искать легче.

Добавлено:
77volt

Цитата:

в ячейке формула = Diapozon (С8:В16)

Public Function Diapozon(N As String) As Variant
Dim N1 As Range
Set N1 = Range(N)
Diapozon = Range(N1).Cells.Count
End Function

Так ты уж что-то одно выбери - либо ты объявляешь N, как строку и тогда передаешь ее, как строку:
= Diapozon ("С8:В16")
Либо ты ее объявляешь, как диапазон и передаешь ее, как диапазон:
= Diapozon (С8:В16)
Public Function Diapozon(N As Range) As Variant

Да и Range(N1) - тоже лишнее.

Код: Public Function Diapozon(N As Range) As Variant
Diapozon = N.Cells.Count
End Function

Спасибо The okk
так работает.
А код только для примера, просто не получалось обратится к диапазону, хотя вроде пробывал раньше объявлять N как Range, буду двигаться дальше.

Привет, друзья! Опять проблема с трансформацией отчётов 1С. Имеется некая таблица, в первом столбце которой перемешаны категории и данные, то есть не перемешаны конечно же, а просто ХЗ - не в том виде представлены, в каком следует. А следует их представить в виде списка, чтобы каждая категория - "Вид продукции", "Подразделение", "Статья затрат", "Вид материала" - разнеслась в соответствующий столбец таблицы и однозначно определяла ТМЦ - то есть каждому товару - электродам, предохранителям и тп - соответствовали бы эти категории. А затем их проанализировать СводнойТаблицей.
У кого будут какие идеи - алгоритмы как распознать в тексте эти категории, и в конечном счёте преобразовать таблицу. Я подумывал одно время что можно было бы сформировать массивы категорий и перебирать их в цикле... Но категории эти часто меняются, дополняются и переименовываются, так что это решение временное и сложное в реализации. Нужен какой-то универсальный алгоритм, который бы разделял категории между собой и выделял бы категории от элементов базы - то есть товаров.

http://www.esnips.com/doc/5877665d-7aa1-4630-96f1-83873f09ed87/form

crotoff
Какая версия 1С? 7? 8?
Выложи файл на какой-нибудь бесплатный сервер.

1С v7
залил form.xls на esnips.com
http://www.esnips.com/nsdoc/5877665d-7aa1-4630-96f1-83873f09ed87

crotoff
Блин, без бутылки не разберёшься. Как бы, для примера, получить ещё, что там и где

Цитата:

"Вид продукции", "Подразделение", "Статья затрат", "Вид материала"

Привет всем, есть вопрос, 28 страниц в теме пролистал не нашёл ответа, ВОПРОС: есть екселевский файл с текстом - форма отчётная - в одной ячейке надо организовать dropdown list - как я понимаю ето хрень с треугольником, нажимаешь и список вываливается - а Ты выбираешь что Тебе нужно и ето остаётся + супер было бы если на печать этот треугольничек не выводился. Пробывал вариации со списком но ексель, зараза строки сокращает, а форму менять нельзя, что делать??! как запрограмировать список таким образом? пытался конструктором пользоваться - он мне VBA выдаёт, а я не парююю... скиньте ссылку, наверняка кто-то уже решал такой вопрос

crotoff
Утро вечера мудреннее
Закономерность прослеживается следующая:
Все категории помечены полужирным шрифтом
Подкатегории определяются числом ведущих пробелов, то есть:
Подразделение 0; Виды продукции 2; Элементы затрат 4; Статьи затрат 6; Виды материалов, наверное 8, не встретил.
Как с этим бороться?
Объявляешь переменные категорий и заполняешь их согласно числу ведущих пробелов. При этом, если встретилась категория, связанная с меньшим числом ведущих пробелов, то все остальные, с большим числом пробелов, сбрасываешь в "". Ну, а как встретилось значение с не полужирным шрифтом, записываешь для него полученные значения категорий. Так где-то

Добавлено:
Вот, приблизительно, ввиде кода:
[more]

Код:
Private Const FirstRow As Long = 3&

Private Function GetOffset(ByVal forCategory As String) As Long
Dim i As Long, Result As Long
For i = 2& To Len(forCategory) Step 2&
If Mid$(forCategory, i, 1&) <> " " Then Exit For
Result = Result + 2&
Next i
GetOffset = Result
End Function

Public Sub ToSimpleTable()
Dim i As Long, pos As Long
Dim Category0 As String, Category2 As String
Dim Category4 As String, Category6 As String
Dim Category8 As String, LastCol As Long
Dim pSource As Worksheet, pDest As Worksheet
Dim sValue As String

Set pSource = ActiveSheet
Set pDest = Worksheets.Add
pos = 1&: LastCol = pSource.UsedRange.Columns.Count
Application.ScreenUpdating = False
pSource.Select

For i = FirstRow To pSource.UsedRange.Rows.Count
sValue = Trim$(CStr(pSource.Cells(i, 1&).Value))
If (pSource.Cells(i, 1&).Font.Bold) And (sValue <> "") Then
Select Case GetOffset(CStr(pSource.Cells(i, 1&).Value))
Case 0:
Category0 = sValue
Category2 = "": Category4 = "": Category6 = "": Category8 = ""
Case 2:
Category2 = sValue
Category4 = "": Category6 = "": Category8 = ""
Case 4:
Category4 = sValue
Category6 = "": Category8 = ""
Case 6:
Category6 = sValue
Category8 = ""
Case 8:
Category8 = sValue
End Select
ElseIf sValue <> "" Then
pos = pos + 1&
pDest.Cells(pos, 1&).Value = Category0
pDest.Cells(pos, 2&).Value = Category2
pDest.Cells(pos, 3&).Value = Category4
pDest.Cells(pos, 4&).Value = Category6
pDest.Cells(pos, 5&).Value = Category8
pSource.Range(Cells(i, 1&), Cells(i, LastCol)).Copy pDest.Cells(pos, 6&)
pDest.Cells(pos, 6&).Value = sValue
End If
Next i
pDest.Select
Application.ScreenUpdating = True
End Sub

Walland
Данные - Проверка.
Ставишь галочку "Список значений".
В поле с формулой списка вводишь свой список для выпадающего меню.
Зачем тебе понадобилось VBA использовать?

AndVGri

Спасибо большущщее! Всё работает!
Были у меня мыслишки использовать эти признаки - жирность и пробелы, да больно мудрёно казалось. Приходилось извращаться - вести справочники для каждой из категорий, а в коде выносить их через функцию VLOOKUP в отдельные колонки, сортировать, удалять строки итогов итп. Теперь житуха класная настала

Такая задача - есть простой код, меняющий стандартную палитру в Excel 2003, выглядит как набор строк вида

Код: ActiveWorkbook.Colors(17) = RGB(0, 97, 146)

skotov

Цитата:

Как можно сделать?

Если я правильно тебя понял,... сделай один раз для одной книги. Кинь эту книгу себе в папку с экселем в \XLstart. Все книги будут создаваться по этому шаблону. Аналогично на других компах.

The okk
Спасибо, но этот способ не будет распространяться на открываемые книги, что тоже необходимо.

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

Короче вопрос открыт.

В чем может быть проблема? Excel 2003
Программно создаю панель инструментов, добавляю CommandBarButton, хочу что б, как написано в документации Office VBA по Shift+F1, вызывалась справка, цепляю

Код:
Dim pComButton As Office.CommandBarButton
Dim pComBar As Office.CommandBar
'...
Set pComButton = pComBar.Controls.Add(msoControlButton)
pComButton.HelpFile = Mid$(ThisWorkbook.FullName, 1&, Len(ThisWorkbook.FullName)-3) & "hlp"
pComButton.HelpContextID = 10&
'...

Народ помогите. Такой вопрос. Нужно на форме связать кнопки OK и Cancel с Enter и Esc соответственно. Никак не въеду как сделать. Событие keypress применительно к форме не работает почему-то.к кнопке чтоли применять?

PhpRu
Для кнопки Ok – свойство Default = True, для кнопки Cancel – Cancel = True

skotov
Т.е. надо, чтобы распространялся на все ранее созданные? Ну тогда действительно только надстройкой.

Цитата:

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

А вот с этого места поподробнее.

Цитата:

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

Вообще должен распространяться везде, где будет подцеплена надстойка.

Как выделить весь столбец, но что бы не копировались пустые поля?
А то у меня получается 20 заполненных полей а потом 2000 строк пустоты.

Gretrick
вот здесь The okk приводит код, как определить первую и последнюю не пустую строку в столбце, а там уж Range(Cells(vRistRow, ColNum), Cells(vLastRow, CollNum)).Copy

Может есть уже готовое решения для моей задачи?
У меня есть файл (который мне присылают каждый день) там около 6000 тысяч наименований.
Таблицы идут в таком виде:

195863 ASF BATTERY ANSM AA NiMh 2x2100mAh (5030472) LUS 3,4 5,49


3я графа это категория товара, 4-фирма производитель, 5-название товара, 6-цена.

Мне надо каждую категорию товаров скопировать в другой файл и сохранить его в csv формате.

Файл можно посмотреть здесь _www.shosting.lv/file.xls

Озадачился таким вопросом:
Как из автофильтра вынуть его лист (т.е. перечень всех его значений)? - Очень удобно было бы использовать для подсчета уникальных значений.

The okk
Создал новую книгу, создал в ней модуль, туда записал код. Сохранил как надстройку, через меню надстроек подцепил ее. В результате ничего не произошло - цвета везде старые..

Может быть надо что-то дополнительно прописать в коде, чтобы он исполнялся? или еще чего?

skotov
все зависит от того, куда ты прописал код. Возможно, стоило его записать в надстройку в событие Workbook_Open.

The okk
В таком случае выдает ошибку Object variable or With block variable not set (Error 91) пи запуске Excel.

Видимо проблема в том, что объект у меня ActiveWorkbook, а надстройка запускается до открытия книги.

Какой можно еще использовать объект? или проблема в чем-то другом?

skotov
Так код на изменение набора цветов в открываемой книге надо навесить на событие Application WorkbookOpen. Создай в надстройке класс, добавь WithEvents pApp As Excel.Application. В процедуре инициализации класса добавь инициализацию pApp. Добавь в классе процедуру pApp_WorkbookOpen(Wb As Workbook), ну а в ней код по изменению набора цветов открываемой книги Wb.Color = ThisWorkbook.Colors. Создай переменную этого класса в надстройке, которая будет инициализироваться в коде надстройки Workbook_Open

AndVGri
Если не сложно, опиши подробнее что и где создавать и что прописывать.