» Excel VBA (часть 3)

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

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

Информация общего характера:
Список соответствия имен функций в английской и русской версиях 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

[more=Перечень основных ColorIndex'ов из MSDN] [/more]

Ну первым буду


Код: Sub Макрос1()
'вычисляем последнюю использованную строку данных
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A5:A" & iLastRow).Select
Range("A5:A" & iLastRow).NumberFormat = "dd/mm/yy h:mm:ss;@"
'вычисляем начало и конец периода
repDateSt = WorksheetFunction.Min(iRng)
repDateEnd = WorksheetFunction.Max(iRng)
Range("F4") = "Промежуток с " & Format(repDateSt, "dd.mm.yyyy") & " по " & Format(repDateEnd, "dd.mm.yyyy")
End Sub

JekG, какой кошмар... А почему бы не использовать такой подход?
Ячейка A2: "Промежуток с"
Ячейка B2: =Min()
Ячейка C2: "по"
Ячейка D2: = Max()

Добавлено:
Ну или если уж совсем хочется в одну ячейку забубенить, то использовать формулу вида

Код: ="Промежуток с " & Text(Min(A:A),"дд.мм.гггг") & " по " & Text(Max(A:A),"дд.мм.гггг")

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

JekG, да, в этом случае придется сначала конвертить, а потом только высчитывать. Рекомендую все же сделать это не макросом, а штатными средствами Ёкселя (т.е. завести колонку для конвертации, можно скрытую, можно на отдельном листе - если конечно нет возможности отконвертить данные ДО попадания их на лист) - обычно штатные решения работают хоть немного, но быстрее реализованных на VBA.

Кстати, а как вообще лист наполняется? Копи-паст или из другой системы?

Excel 2003, пытаюсь сбросить горячие клавиши через код:

Application.OnKey "{F12}", ""
Application.OnKey "+{F12}", ""

Не сбрасываются.

Я что-то упустил?

Помогите плз с задачкой.
В массиве С(n) подсчитать количество отрицательных элементов, количество положительных элементов, сумму отрицательных элементов, сумму положительных элементов.

randomar, контрольная горит?

randomar
Ты че решил всю свою методичку сюда скормить?

Пример по первой части вопроса. В массиве С(n) подсчитать количество отрицательных элементов, количество положительных элементов


Код: Dim P As Integer 'количество положительных элементов
Dim O As Integer 'количество отрицательных элементов
Dim Sum 'сумма
Dim Pr 'произведение
Sum = 0
Pr = 1
For i = LBound(C) To UBound(C)
If С(i) < 0 Then
O = O + 1
Pr = Pr * C(i)
Else
P = P + 1
Sum = Sum + C(i)
End If
Next i

randomar
Можно эту задачу без VBA решить с помощью функций листа, например СУММЕСЛИ

JekG
да нет всего лишь одну задачку проверить хотел)) да и спасибо тебе, я нашёл свою ошибку)))

PrWork1, не получится. СуммЕсли умеет считать по жесткозаданной критерии, больше/меньше ей не катит. В этом случае можно действительно обойтись без VBA, но со вспомогательной колонкой - признаком положительного/отрицательного числа. После этого уже можно задействовать СуммЕсли

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

Dim Arr(3,3) as Double

Создаст двумерный массив (4 столбца/строки, индекс первого - 0), каждая ячейка будет типа Double. Более подробно - читай хелп по функциям Dim/ReDim [Preserve].

Добавлено:
Принадлежность проверять надо исключительно циклом вида


Код: For I = Lbound(Arr, 1) To Ubound(Arr)
For J = Lbound(Arr, 2) To Ubound(Arr)
If Arr(I, J) = CheckValue Then
{Рапортуем о нахождении, делаем свои дела}
Exit For ' Чтобы дальше без толку по массиву не бегать
Next J
If Arr(I, I) = CheckValue Then Exit For ' Выходим из цикла проверки для второго цикла
Next I

Цитата:

Excel 2003, пытаюсь сбросить горячие клавиши через код:

Application.OnKey "{F12}", ""
Application.OnKey "+{F12}", ""

Не сбрасываются.

Я что-то упустил?

кто-то может что-то сказать по сабжу?

prestigo, ага, пункт п. 2.8.3. главы VIII Соглашения по использованию

Добавлено:
А если совсем по сабжу - код

Код: Application.OnKey "{F12}", ""

Цитата:

А если совсем по сабжу - код

Код:
Application.OnKey "{F12}", ""


Прекрасно отключил у меня обработчик события по F12

а какой у вас офис?

2003 En SP2 (11.8146.8132)

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

Добрый вечер
Помогите пожалуйста сделать следующую вещь:
На листе экселя например по хоткею копировать текст из ячеек D12-D15 в ячейку А18, в ячейку В18 вставить дату и время когда это произошло, а в ячейку С18 вставить линк на нужный документ.
Заранее спасибо

bugmeneverplzthanku

Сервис/Макрос/Начать запись.
Проделай все вышеперечисленные действия останови запись и в списке макросов увидишь нужный тебе код.

bugmeneverplzthanku, добавлю, что для получения текущей даты/времени можно воспользоваться функцией Now()

Большое спасибо, буду тренироваться.

Доброго времени суток!!!



1. Как сделать средствами VBA так что бы была привязка файла к данному компьютеру. Т.е., к примеру, к сведениям активации: пользователь, или id; но лучше, мне кажется, к имени компьютера: полное имя. Привязывать к компьютеру хорошо бы по нескольким этим параметрам. Мне это нужно для защиты от копирования, чтобы при открытии на другом пк все сведения внутри листов обнулялись (это я уже нашел как сделать см. код ниже), а вот привязать к одному компьютеру файл xls не смог.

' Замена формул на значения во всех ячейках
Public Sub Formul_0()
With ActiveSheet.UsedRange
.Value = .Value ' здесь можно поставить и так: .Value = 0 тогда будут нули...
End With
End Sub

2. Так же необходимо что бы не было видно формул забитых в ячейки, ни при редактировании ячейки, ни в строке формул средствами VBA... Но самое главное оставить доступ к редактированию ячеек пользователем!!!

Способ: "Войти в формат ячейки (ячеек) и в "защите" установить "скрыть формулы". Затем защитить лист, разрешив, например, все действия." ... Не подходит, хотя если это единственный, то.... Хотя (размышления) если макросы будут отключены, то формулы все равно ведь будут видны..... . Тогда, наверное, стандартными, а не программными (VBA) лучше скрыть формулы в ячейках...

Пока остановился на следующем:

ActiveSheet.Protect 'защита листа, пароль вот только не смог посатвить
Application.DisplayFormulaBar = False 'показ строку ввода формул
Application.ActiveSheet.Range("A1:IV65536").FormulaHidden = True 'скрыть формулы в выбранном диапазоне, но при этом должно быть прописано ActiveSheet.Protect

По каким-то причинам выдает ошибку:

ActiveSheet.Protect Password:="Protection" 'защита листа и установка пароля

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

ActiveWorkbook.Saved = True

demeterz

Цитата:

Как сделать средствами VBA так что бы была привязка файла к данному компьютеру

Не стоит оно таких усилий, ибо от дурака можно только сделать (в Excell до версии 2000 включительно). Умный возьмёт и отключит макросы при открытии файла, либо воспользуется какой-нибудь утилитой для снятия защиты с файла.

Как неплохой вариант (но опять же, от дурака):
сделать макросы, которые выполняют основную работу в книге не в самом XLS файле, а в его допополнении XLA. Тогда экселевский документ будет работать только на той машине, где установлено дополнение.

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

daMIR, примерно так:


Код: SourceSelection.Copy
DestinationRange.PasteSpecial xlPasteValues,,,True

daMIR
А задача разовая?
Может проще копировать дапазон в буфер и специальная вставка-транспонировать?

Цитата:

А макрос можно в студию?

[more=Макрос]
Sub Макрос4()
'
' Макрос4 Макрос
'

'
Range("B3:B22").Select
Selection.Copy
Sheets("Рез. Свойства & Способности").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
End Sub
[/more]

Вот я не понимаю как в нем отдельно указать лист откуда брать и лист куда вставить.


Цитата:

А задача разовая?

Нет, в том то и дело. Чуть подробнее опишу задачу. Нужно делать определенный расчет таблиц. Три листа, три таблицы. В каждой таблице используется либо один столбик либо одна строка для другой таблицы в качестве заголовка. Т.е. один раз заголовок забиваем, а далее он переносится автоматом на другой лист. Каждый макрос я хочу повесть на кнопку на каждом листе. Т.е. диапазон ячеек и лист откуда/куда брать/вставлять известны и жестко заданы.

Цитата:

Т.е. диапазон ячеек и лист откуда/куда брать/вставлять известны и жестко заданы.

Тогда присвойте этим диапазонам имя (Вставка/Имя/Прсвоить)
Далее даже не нужно будет переходить на листы имена уникальны во всей книге
Просто
Range("Исходный_диапазон").Select
Selection.Copy
Range("Конечный_диапазон").Select
вставка....