» Excel VBA (часть 2)

Столкнулся с проблемой есть ListBox в нем список фамилий (берутся из листа) и нада чтоб в textboxe при наборе по первым символам дополняло фамилию.. , вот только мне нада чтоб еще и в ListBoxe указатель перемещался сам на фамилию.

Товарищи помогите, поиск юзал подобного много, что нужно не нашел, vba пользуюсь первый раз но надо очень.

ol7ca

Цитата:

почему-то возникает ошибка (subscript out of range) в строке Set ws = Sheets(a(i))

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

Цитата:

Вопрос: Когда в коде VBA я ссылаюсь на рабочий лист, возникает сообщение
об ошибке "subscript out of range". Что происходит?
Ответ: Эта ошибка возникает при попытке получить доступ к несуществующему члену коллекции. Например, приведенный ниже оператор создает сообщение об ошибке, если активная рабочая книга не содержит рабочего листа, который называется MySheet:
Set X = ActiveWorkbook.Worksheets("MySheet").

А у Вас случайно нет на этих листах объекта (объектов) с тем же именем, что и имя листа?
P.S. С замечанием nick7inc, что
Цитата:

Resume Next - плохой тон программирования.

в корне не согласен. ИМХО могу привести ряд "красивых" решений с помощью этой команды. Однако, то, что
Цитата:

Надо стараться самому исключать ситуации с ошибками

полностью согласен. Ведь действительно на 100% можно сделать так, чтобы ни в одной ячейке на рабочем листе #N/A не было.

ol7ca

Цитата:

почему-то возникает ошибка (subscript out of range) в строке Set ws = Sheets(a(i))

Вы, батенька, лентяй. Что я вам здесь и здесь писал? Не работает - разбивайте сложные выражения на составляющие, проверяйте тип данных и значения, которое передаёте функции, введите, наконец, промежуточные переменные. Вместо тупого Resume Next используйте Stop в месте обработки ошибки, а при остановке в отладчике смотрите значения и типы данных, которые вы передаёте.
Set ws = Sheets(a(i)) надо разбить на 1) получение имени листа в переменную типа String (НЕ МАССИВ, а именно обычная строковая переменная); 2) присвоение указателя на лист при помощи команды Set.
И определитесь, наконец, в какой книге Sheets() должен искать лист с вашим именем.
Не пишите всё это в одну строчку, разбейте на несколько, чтобы отладчик показал вам конкретное место возникновения ошибки.

Добавлено:
SAS888
Цитата:

Resume Next - плохой тон программирования.

в корне не согласен. ИМХО могу привести ряд "красивых" решений с помощью этой команды. Однако, то, что

Аргументировынное использование - возможно, но только тогда, когда программист точно представляет, как программа с этой командой будет работать. А не механически перекладывать на Resume Next любые ошибочные ситуации в программе.

Цитата:

полностью согласен. Ведь действительно на 100% можно сделать так, чтобы ни в одной ячейке на рабочем листе #N/A не было.

Не совсем так. Бывают случаи, когда нельзя заранее предугадать и on error goto - прекрасный помошник (не забываем, что есть код ошибки и можно получить её текстовое описание). Ячейки с ошибками, безусловно, можно и нужно проверять до вызова функции, использующей их.

Добавлено:
ol7ca
У вас ошибка Out of Range может быть по четырём разным причинам: 1) выход за границы массива; 2) попытка получения указателя на несуществующий лист; 3) вы ищите лист не в той книге; 4) у вас недопустимое имя листа в программе. Всё это можно проверить отладчиком в момент появления ошибки, если следовать моим советам.

nick7inc

Цитата:

Бывают случаи, когда нельзя заранее предугадать и on error goto - прекрасный помошник

Я как раз об этом и говорю. Т.е не допускать появления в ячейке значения #N/A.

блин люди ну помогите, я vba первый раз юзаю, очень нада, на работе полный завал!

SAS888

Цитата:

А у Вас случайно нет на этих листах объекта (объектов) с тем же именем, что и имя листа?

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

nick7inc

не увидел то, что вы вчера добавили для меня, извиняюсь
я воспользуюсь вашим советом: разбить код на составляющие.
может я и отладчиком толком пользоваться не умею
а можно ли посмотреть в каком месте книги (листа) застрял код?

я стараюсь решить задачу сам прежде чем задаю вопрос на форуме
просто уже 3-й день бьюсь и ищу обходные пути.
мне бы за парту сесть да базу по VBA изучить. но нет времени-(
поэтому задаю глупые вопросы. сорри и спасибо за помощ.

ol7ca

Цитата:

можно ли посмотреть в каком месте книги (листа) застрял код

Можно. Код этот? Ссылка

Добавлено:

Цитата:

я стараюсь решить задачу сам прежде чем задаю вопрос на форуме
просто уже 3-й день бьюсь и ищу обходные пути.

Я тоже погорячился. Пишите, какой код, будем править для отладки.

Добавлено:
k0s

Цитата:

блин люди ну помогите, я vba первый раз юзаю, очень нада, на работе полный завал!

Код хоть какой-нибудь есть или всё с нуля надо?

Добавлено:
ListBox где: в UsefForm или ещё где-нибудь?

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

nick7inc

да в UsefForm вот код:


Код:
Private Sub CommandButton1_Click()

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If MsgBox(" отобразить листы ?", _
cbQuestion + vbYesNoCancel) = vbYes Then
UserSheet.Visible = True
UserSheet.Activate
Else
OriginalSheet.Activate
End If
End If
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call CommandButton3_Click
End Sub

Private Sub UserForm_Click()

End Sub

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

ol7ca
Скорее всего что-то всё-таки изменилось. Можно ждать, когда глюк снова проявится, хотя я бы проверял, есть ли лист с заданным именем в книге перед тем, как получать указатель командой Set. Но и самих глюков (в том числе и необъяснимых) в Excel тоже хватает. В любом случае, чтобы исправить ошибку надо сделать так, чтобы её можно было воспроизвести хотя бы у себя.

Добавлено:
k0s
Код не рабочий, не совсем понимаю, что он должен делать. Завтра попробую что-нибудь смастерить сам по вашему описанию.

Добавлено:
ol7ca
Я понял, в чём у вас проблема. Виной всему On error Resume Next. У вас при возникновении ошибки пропускается блок Set X = ..., но не пропускается блок If X Is Nothing Then ... End if. Поэтому в последнем блоке программа работает со старым указателем.

Добавлено:
Ещё мне непонятно, зачем вы в цикле вызываете On Error Resume Next. Это было бы понятно, если происходит в цикле включение обработчика ошибок с последующим его отключением. У вас отключения я не нашёл. Мой вам совет - выкинуть On Error Resume Next, присваивать имя листа переменной символьного типа, вызывать функцию, которая бы проверила, есть ли лист с таким именем в книге или нет, если есть, то проводить требуемые операции с данными, иначе - пропуск (или делать что-нибудь альтернативное).

Добавлено:
Для проверки наличия листа в книге можете воспользоваться [more=этой функцией]

Код: Function Is_sheet_exist(Sheet_name As String, book As Workbook, Worksheet_type As Long) As Boolean
' Worksheet_type.
' Can be one of xlWorksheet, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet.

Dim current_sheet As Worksheet
Is_sheet_exist = True
For Each current_sheet In book.Worksheets
If current_sheet.Name = Sheet_name And current_sheet.Type = Worksheet_type Then Exit Function
Next current_sheet

Is_sheet_exist = False
End Function

nick7inc
спасибо. все это мне очень пригодится. я ту ошибку обошел - избавился от #N/A в искомых ячейках + чуть помудрил - все заработало. но я к этому еще вернусь чуть позже. а сейчас я столкнулся с проблемкой - я написал код, который ищет условие в строках по листам в двух файлах-близнецах (*) и если это так, то возвращает значение. Но для одного листа у меня все работает, а зациклить не получается.
(*) один файл точная копия другого с меньшим количеством листов, с теми же именами листов и столбцами.

Dim i As Integer, j As Integer, As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False

For j = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(j).Select
For i = 2 To Workbooks("map3.xls").Sheets(j).Range("aa65536").End(xlUp).Row
If Not IsEmpty(Cells(i, 27)) And Not IsError(Cells(i, 27)) Then
Workbooks("b3.xls").Sheets(j).Cells(i, 28) = Workbooks("map3.xls").Sheets(j).Cells(i, "AB")
End If
Next i
Next j
End Sub

map3 - урезанный источник
b3 - Это приёмник

ol7ca
У Вас в коде есть ссылка на ActiveWorkbook. А какая рабочая книга у Вас активна в момент запуска макроса? Т.к.
Цитата:

один файл точная копия другого с меньшим количеством листов

, то цикл
Цитата:

For j = 1 To ActiveWorkbook.Sheets.Count

не даст ошибки лишь тогда, когда в ActiveWorkbook листов меньше, чем во второй книге.
Также советую не использовать метод Select. В данном случае это совершенно ни к чему. Если в строку с условиями добавить ссылку на лист книги, то строку
Цитата:

ActiveWorkbook.Sheets(j).Select

можно просто выбросить.

Подскажите как вставить VBScript в модуль написанный на VBA для Excel? Вариант с запуском скрипта как внешней программы не устраивает т.к. юзеры его переодически стирают, считая его нафиг ненужным.

SnowMan80
VBScript - урезанная версия VBA. Поэтому должно все работать и так.

SAS888

активна в момент запуска map3 - урезанный источник
ошибка опять была в зависании Excel. сегодня после перезапуска все заработало без ошибок.

k0s

Цитата:

Столкнулся с проблемой есть ListBox в нем список фамилий...

Смотрите файл.

visual73
надо попробывать.

Но у меня сомнения по поводу того что VBS это урезанный VBA. скорее всего VBS это урезаный VB

visual73
не устраивает, строка должна подниматься, но все равно спасибо
Range(" t ").Activate
Selection.Delete Shift:=xlUp - выделенная строка должна удалиться и все нижеследующие подняться на одну вверх, вот как дать программе понять что t - переменная?

Wukuze
ааа, так Вам строка нужна, пардон, тогда так мож подойдет
Rows(i).Delete Shift:=xlUp
Только я не понял для чего Вы ее хотите непременно выделить?
SnowMan80
А Вы не сомневайтесь, Вы пробуйте
Что наталкивает Вас на такую крамольную догадку?
У меня книжка есть где такое написано, думаю так оно и есть.

visual73
на догатку натолкнуло то, что VBA это заточеный VB для приложений (visio, office, autocad). А VBS это обрезаный VB для написания скриптов. (тоже об этом читал в книжах)

PS. Видимо кто как думает тот так и ишет книги
Практика скажет где есть Истина.

я столкнулся с задачей, когда в книге имеется лист "А" с именами в формате "асс1", "асс2" и т.д. некоторые из ячеек с именами отмечены цветами. Далее в книге имеются ячейки с такими же цветами куда должны быть вставлены формулы сумм ячеек из листа "А" в соответствии с цветами “+”&"асс1" + "асс2"
но почему-то мой код не видит цвет (цифры видит) и как записать формулу суммирования найденных значений (их число Х)

Dim ws As Worksheet, wb As Workbook
Dim i As Integer
Application.ScreenUpdating = False
Set wb = Application.Workbooks("B1.xls")
For Each ws In wb.Worksheets
If ws.Type = xlWorksheet Then
With ws
For i = 1 To .Cells(65536, 28).End(xlUp).Row
If .Cells(i, 28).Interior.ColorIndex = 6 Then -?????????
.Cells(i, 28) = Worksheets(“A”) “+”&"асс1" + "асс2" -?????????
End If
Next
End With
End If
Next
End Sub

Подскажите, пожалуйста.
спасибо.

Если я правильно понял, то нужно в ячейку вставить именно формулу.
Строка Вашего кода
Цитата:

.Cells(i, 28) = Worksheets(“A”) “+”&"асс1" + "асс2"

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

Код: .Cells(i, 28) = "=асс1 + асс2"

SAS888

Цитата:

Если именованные ячейки находятся в другой книге, то ссылку добавлять так:
Код:.Cells(i, 28) = "=" & wb.Name & "!асс1 +" & wb.Name & "!асс2"

Excel с некоторыми именами листов и книг использует одинарные кавычки, например, если в имени книги и/или листа есть пробелы: ='[Книга 2.xls]Лист 2'!$A$3

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

visual73
Спасибо большое, а выделяю я ее потому что на ВБА никогда не кодил и делаю все исключительно только по большим просьбам бухов или манагеров, вот стараюсь на примере записи макросов и разборе их кода что нить написать. Яж просто саппорт а не прогер

Добрый день! Помогите решить проблему!
Имеется книга Excel с (скажем) 10 листами. Нужно в VBA переименовать листы по значению ячейки "A1" в каждом листе. Причем 5 листов имеют значение в ячейке ABC, остальные 5 DEF. Нужно чтобы первые первая группа листов имели имя типа ABC (1), ABC (2) ... ABC (5), а вторая группа DEF (1), DEF (2) ... DEF (5).
У меня получилось добавить порядковый номер листа, но нужна чтобы новая граппа имен начинала нумерацию с 1

Код:
Sub MasterCopies()
For s = 1 To Worksheets.Count
Worksheets(s).Activate
On Error GoTo newname
ActiveSheet.Name = Range("A1")
newname:
ActiveSheet.Name = Range("A1") & s
Next s
End Sub

alfous

Код:
Public Sub MasterCopies()
Dim i As Long, id As Long
Dim prevName As String, curName As String

For i = 1 To Worksheets.Count
curName = CStr(Worksheets(i).Range("A1").Value)
If prevName <> curName Then id = 0
id = id + 1
Worksheets(i).Name = curName & "(" & CStr(id) & ")"
prevName = curName
Next i
End Sub

к сожалению, никак не могу понять, как записать код, который бы находил значения и вставлял формулу в виде X1+X2+...+Xn
подскажите кто знает.
спасибо

visual73

Чистое копирование программы VBS в VBA программу результатов не дало. Первое что тормознуло - это несовпадение функций. Пришлось подбирать аналоги.