Excel vba как задать имя листу, который будет вставлен


Содержание

Excel VBA — создание макроса, который вставляет имя активного листа внутри другой текстовой панели — vba

Я работаю с двумя листами внутри моей книги. «Активный лист» и мой «Верхний лист поставщика». Мой «активный лист» может быть одним из многих дубликатов заметок, которые я готовлю для своих поставщиков, поэтому он может быть одним из многих разных листов внутри книги. Во всех этих заметках о сделке я хотел бы иметь кнопку, которая выполняет макрос, чтобы сделать следующее:

В «Верхнем листе поставщика» в следующей доступной ячейке указанной строки я хочу вставить формулу, которая будет равна ячейке B8 текущего активного листа. Я не смог понять, как получить ссылку VBA на «Активный лист» внутри стандартной формулы excel. Вот мой код:

VBA Проверьте, существует ли имя листа [дубликат]

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

How могу ли я проверить, существует ли этот лист? Что-то вроде:

18 ответов

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

Компактная функция wsExists ( без зависит от обработки ошибок!) [/ ​​g6]

Вот короткий & amp; простую функцию, которая не полагается на обработку ошибок, чтобы определить, существует ли рабочий лист ( и ], [3], [3] hr>

Пример использования:

В следующем примере добавляется новый лист с именем myNewSheet , если он еще не существует:

Дополнительная информация :

  • MSDN: For Each . Next Statement (VBA)
  • MSDN: Exit Statement (VBA)
  • MSDN: Операторы сравнения (VBA)

На самом деле у меня был простой способ проверить, существует ли лист, а затем выполнить некоторую инструкцию:

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

Измените «данные» на любое имя листа, которое вы тестируете для .

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

Я придумал простой способ сделать это, но я не создал для него новую подсистему. Вместо этого я просто «проверил чек» в суб, над которым я работал. Предполагая, что имя листа, которое мы ищем, является «Sheet_Exist», и мы просто хотим его активировать, если он найден:

Я также добавил всплывающее окно, когда лист не существует.

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

НЕ используйте «On Error Resume Next», так как это может конфликтовать с другой частью вашего код.

Поскольку проверка членов коллекции является общей проблемой, вот абстрагированная версия ответа Тима:

Эта функция может использоваться с любой коллекцией, подобной объекту ( Shapes , Range , Names , Workbooks и т. д.).

Чтобы проверить наличие листа, используйте If Contains(Sheets, «SheetName») .

Если вас интересуют только рабочие листы, вы можете использовать простой вызов Evaluate:

Почему бы просто не использовать небольшой цикл, чтобы определить, существует ли именованный лист? Скажите, искали ли вы рабочую таблицу с именем «Sheet1» в текущей книге.

Я сделал другое: удалите лист только в том случае, если он существует — не получить ошибку, если это не так:

Мое решение очень похоже на Tims, но также работает в случае листов без листа — диаграммы

В случае, если кто-то хочет избежать VBA и проверить, существует ли таблица только внутри формулы ячейки, возможно использование функций ISREF и INDIRECT :

Это вернет TRUE , если книга содержит лист, названный SheetName и FALSE в противном случае.

Если вы поклонник WorksheetFunction. или работаете в неанглийской стране с неанглийским Excel, это хорошее решение, которое работает:

Или в функции например:

Много лет спустя, но мне просто нужно было это сделать, и мне не понравилось, что какое-либо из решений было опубликовано . Итак, я сделал все, благодаря магии (жестом в форме радуги SpongeBob) «Evaluate ()» !


Возвращает TRUE, если лист НЕ существует; FALSE, если лист существует. Вы можете заменить любой диапазон, который вам нравится, на «1: 1», но я советую не использовать одну ячейку, потому что если она содержит ошибку (например, # N / A), она вернет True.

Excel VBA, как добавить имя листа источника к скопированной строке в другом листе

Есть ли способ, чтобы добавить имя листа к скопированной строке в другом листе? У меня есть этот код, чтобы переместить строку из одного листа на другой

Допустим, что у меня есть такой сценарий: У меня есть 3 листа с именем: parts1, parts2, журнал Код выше перемещает всю строку из активного листа (который использует только столбец А и В) к лог-листа. но несколько раз на записи в parts1 и parts2 то же самое, и я хочу знать, от которого она была перенесена, добавив имя листа в колонке С вставленного рядом. Является ли это возможным?

Не уверен , что я хотел бы использовать ActiveSheet.Name . Selection.Parent.Name может быть лучше , как ActiveSheet может измениться в то время как код работает, но снова так может Selection .

Кроме того, добавление имени листа источника в клетку может перезаписать значение, как вы копируете всю строку. Может быть, добавить его в качестве комментария?

Примечание: Код на самом деле не выбрать EntireRow , он просто ссылается на это.

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

Кодовое имя листа — что это и зачем нужно? Какие плюсы и минусы?

Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках — зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется «SETS». Тогда код получения данных из ячейки A2 будет выглядеть так:

Dim v v = ThisWorkbook.Sheets(«SETS»).Range(«A2»).Value

Подробнее про обращение к диапазонам можно узнать из статьи: Как обратиться к диапазону из VBA
Все бы ничего, но есть одна ложка дегтя: пользователь может случайно этот лист переименовать. Тогда обращение к листу по заранее заданному имени вызовет ошибку( 9 — Subscript out of range ). Можно пойти другим путем — обратиться к листу по его порядковому номеру. Например, наш лист «SETS» расположен вторым по порядку в книге:

Тогда код получения данных из ячейки A2 будет таким:

Dim v v = ThisWorkbook.Sheets(2).Range(«A2»).Value

Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист «SETS» в другое место. Что опять либо вызовет ошибку 9 — Subscript out of range (если количество листов в книге меньше указанного числа. В нашем примере если в книге будет только один лист) либо запишет нам в переменную v значение из совершенно другого листа и как следствие — это будет не то значение, которое нам нужно.
Как же быть? Есть такое понятие — кодовое имя листа. Т.е. у листа есть два имени: одно отображаемое — это то, которое мы видим на ярлыке(на рисунке выше это Лист1, SETS, Лист2). И есть кодовое имя, которое не видно напрямую. Чтобы увидеть кодовое имя листа необходимо перейти в редактор Visual Basic for Application( Alt + F11 ), отобразить окно проводника проекта —ViewProject explorer(или Ctrl + R ), раскрыть папку Microsoft Excel Objects. Там мы увидим все объекты проекта VBA и их имена. Само название листов в этом окне состоит из двух частей: сначала идет кодовое имя листа, а в скобках — имя, отображаемое на ярлыке:

Где искать разобрались. Что нам это дает? Т.к. это имя просто так не найти — пользователь не сможет уже изменить его имя. А обратиться к такому листу из кода VBA проще простого — как и к любому из компонентов проекта — просто по его кодовому имени(для нашего листа «SETS» это Лист2)

Dim v v = Лист2.Range(«A2»).Value

Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
Удаление листов можно запретить, защитив структуру книги(Рецензирование (Review)Защитить книгу (Protect workbook) )
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится: ActiveWorkbook.Лист2.Range(«A2»).Value . Т.е. по факту нельзя обратиться к листу по кодовому имени из другой книги. Хотя в большинстве случаев это и не надо. Если уж реальная необходимость возникнет — ниже приведена функция поиска листа по его кодовому имени, которую легко можно переделать под получение отображаемого имени листа, найдя его по кодовому имени.
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(ViewProperties Window или F4 ) и просто изменить свойство Name:

Илон Маск рекомендует:  Sqlпростые вложенные подзапросы

Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых — код будет более читаемый и наглядный, если обращение будет к объектам вроде wsSets и wsData , чем к объектам вида Лист1 , Лист2 и т.п.
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.

Порой необходимо проверить — есть ли лист с указанным кодовым именем. Это к слову о том, как избежать ситуации обращения к несуществующему листу, если обращение идет по кодовому имени(и при этом в проектах Вы почему-то не используете Option Explicit). Можно применить такую простенькую функцию:

‘————————————————————————————— ‘ Procedure : RenameSheetCodeName ‘ Purpose : Ищет в указанной книге лист с указанным кодовым именем ‘ wb — Книга, лист в которой необходимо найти ‘ sName — Кодовое имя листа, которое надо проверить на наличие в книге ‘————————————————————————————— Function FindSheetByCodeName(wb As Workbook, sName As String) Dim ws As Worksheet ‘цикл по всем листам For Each ws In wb.Worksheets If StrComp(ws.CodeName, sName, 1) = 0 Then ‘сравнение имени без учета регистра ‘назначаем функции возвращаемое значение FindSheetByCodeName = True Exit Function End If Next End Function

Проверить при этом наличие листа с кодовым именем wsSets можно так:

Sub IsShhetCodeName_Exist() If FindSheetByCodeName(ActiveWorkbook, «wsSets») = False Then MsgBox «Нет такого листа», vbCritical, «www.excel-vba.ru» End If End Sub

И вдогонку код, который поможет быстро переименовать кодовое имя листа по его отображаемому имени. Т.е. все что надо — знать имя листа, отображаемое на ярлыке. И указать новое кодовое имя:

‘————————————————————————————— ‘ Procedure : RenameSheetCodeName ‘ Purpose : Ищет в указанной книге лист с указанным именем ‘ и переименовывает кодовое имя листа на указанное ‘ wb — Объект. Книга, лист в которой необходимо переименовать ‘ sOldName — Текст. Имя листа для переименования ‘ (может быть как отображаемым на ярлыке, так и кодовым) ‘ sNewName — Новое кодовое имя листа ‘ SearchByCodeName — Если True, то лист для переименования ищется ‘ по кодовому имени листа ‘ Если False — лист ищется по отображаемому на ярлыке имени ‘————————————————————————————— Function RenameSheetCodeName(wb As Workbook, sOldName As String, sNewName As String, _ Optional SearchByCodeName As Boolean = True) Dim vbc As Object, ws As Worksheet Dim sn As String ‘проверяем, нет ли уже в книге компонета с таким именем(sNewName) On Error Resume Next Set vbc = wb.VBProject.VBComponents(sNewName) If Not vbc Is Nothing Then MsgBox «The worksheet ‘» & sNewName & «‘ is already exist», vbCritical, «www.excel-vba.ru» ‘MsgBox «Компонент с именем ‘» & sNewName & «‘ уже есть в проекте», vbCritical, «www.excel-vba.ru» Exit Function End If ‘цикл по всем листам и проверка имени For Each ws In wb.Worksheets If SearchByCodeName Then ‘если ищем по кодовому имени sn = ws.CodeName Else sn = ws.Name ‘если ищем по отображаемому имени End If If StrComp(sn, sOldName, 1) = 0 Then ‘сравнение имени без учета регистра ‘переименовываем, если имя совпадает Set vbc = wb.VBProject.VBComponents(ws.CodeName) vbc.Name = sNewName ‘назначаем функции возвращаемое значение RenameSheetCodeName = True ‘выходим из функции(нет смысла продолжать — переименовали) Exit Function End If Next End Function

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

Sub TestRename() ‘RenameSheetCodeName(ActiveWorkbook, «Sheet1», «Лист1») ‘RenameSheetCodeName(ActiveWorkbook, «Лист1», «Sheet1») If RenameSheetCodeName(ActiveWorkbook, «Sheet1», «Лист1») Then MsgBox «Кодовое имя листа переименовано», vbCritical, «www.excel-vba.ru» End If End Sub

Для кодового имени листа программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.

Скачать файл с примером функций:

Кодовое имя листа.xls (59,0 KiB, 173 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

VBA — Добавить листы, основанные на значении и имени, в соответствии с

У меня возникли проблемы с моим кодом. Мне нужно создать новые листы и называть их в соответствии с именем машины. Какой диапазон «B» & (12 * x — 5) . Да изменения диапазона основаны на x , который в этом примере равен от 1 до 33 и находится в правильном заявлении For . Однако мой код создает большое количество листов, а не только 11, в этом примере. Sheets(«Tool Setup»).Range(«C18») = 11 в этом примере. Кроме того, я намерен назвать эти листы в соответствии со значением в диапазоне «B» & (12 * x — 5) .

Если кто-то может помочь мне понять, как создать код, который будет удовлетворять эту проблему, она была бы оценена. У меня есть догадка, что это связано с переменной Tabs .

Создан 21 авг. 17 2020-08-21 16:57:33 A Cohen

вы не определяете х, я полагаю, вы имеете в виду ссылки ‘Tabs’ – sourceCode 21 авг. 17 2020-08-21 17:03:07

вы также не дают вновь добавленный листу имя, если ваши намерения не было, чтобы дать им имена по умолчанию’ Sheet1′, ‘Sheet2’ , и т. д. – sourceCode 21 авг. 17 2020-08-21 17:08:31


‘Dim Sheet as Integer’ является ужасно вводящим в заблуждение именем, чтобы дать переменной, которая не содержит ссылку на объект Worksheet. ‘sheetCount’, возможно? Что находится в » Tool Setup ‘! C18’? FWIW, что бы там ни было, вероятно, будет лучше в качестве именованного диапазона, например. ‘Sheet = ThisWorkbook.Names (« SomeConfigurationValue »). RefersToRange.Value’ – Mat’ s Mug 21 авг. 17 2020-08-21 17:35:26

‘ Tabs’ никогда не назначается (по крайней мере, в отправленном вами коде), поэтому его значение всегда будет ‘0’. О чем это? – Mat’ s Mug 21 авг. 17 2020-08-21 17:36:18

Не думаю, что на ваш вопрос можно ответить как есть. Пожалуйста, отредактируйте, чтобы уточнить. См. [Mcve], если вы не знаете, как это сделать. – Mat’ s Mug 21 авг. 17 2020-08-21 17:37:12

Я считаю, что я отредактировал это лучшее, что мог, для лучшей помощи. Прошу прощения, если я не понимаю, я совершенно новый на VBA – A Cohen 21 авг. 17 2020-08-21 18:53:48

1 ответ

Основываясь на том, что вы предоставили, я придумал код, ниже которого имена 11 листов соответствуют формуле, аналогичной вашей. Чтобы продемонстрировать, что он работает, я поместил последовательные числа в первые 127 строк столбца B активного листа. Затем код вычисляет, какую строку захватывать, используя формулу Range(«B» & (12 * i — 5) . Эта формула дает следующую последовательность: 7 19 31 43 55 67 79 91 103 115 127, и поэтому сделано 11 листов с этими числами.

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

Создан 21 авг. 17 2020-08-21 18:56:31 Tony M

Пожалуйста, обязательно правильно отпечатайте свой код при публикации :). – Brandon Barney 21 авг. 17 2020-08-21 19:09:15

Хорошо, что вы проходите через код по строкам. Также проверьте значения ключевых переменных, наведя на них указатель мыши или, если это не сработает, добавьте строки кода, такие как «s = num» или «s = sh.name» и т. Д. (Где s объявляется как «Dim s as String»). Если он пропускает инструкцию «For», тогда «num» должно быть 1 или меньше. Если он не назовет лист, сначала определите другую переменную «Dim shName as String», а затем замените код, называя лист этими строками «shName = sh.Range (« B »& (12 * i — 5))» а затем «Листы (таблицы) .Name = shName» и проверьте значение shName – Tony M 21 авг. 17 2020-08-21 19:45:08

@Tony Я попытался его, и поскольку ‘num’ изменится с другими данными. Я попытался установить sh = Sheets («Настройка инструмента») и ‘num = sh.Range (« C18 »). Кроме того, поскольку я ранее использовал ‘i’ в качестве переменной, я изменил ее на’ j’. Однако он не будет указывать листы. Он пропускает эту строку кода. Я также переключился на num line с помощью Set – A Cohen 21 авг. 17 2020-08-21 19:45:44

@ACohen Если он пропускает его, тогда проблема, скорее всего, будет ‘i> num’, и если вы имеете в виду, что вы заменили’ num’ на ‘Set’, вам нужно изменить’ Set’ на что-то еще (поскольку ‘Set’ является оператором присваивания). – Brandon Barney 21 авг. 17 2020-08-21 19:47:22

@TonyM Итак, теперь он создает 11 слайдов и дает им имена, но только четыре из них и помещает буферный слайд между ними. » CorrectName1 «‘ — ‘Sheet2’ -‘ Sheet3’ — ‘» CorrectName2 «‘ – A Cohen 21 авг. 17 2020-08-21 20:04:20

@ACohen Возможно, некоторые из имен, которые вы используете, содержат недопустимые символы.Чтобы выяснить, в чем проблема, перейдите по очереди, постоянно проверяя значения переменных непосредственно перед их использованием. Я попытался описать, как это сделать в своем предыдущем комментарии, чтобы вы могли научиться решать такие проблемы. Если вы узнаете, как это сделать, вы найдете VBA очень мощным. При решении вопросов на этом сайте лучше всего решить проблему, потому что в противном случае проблема постоянно меняется, теряя исходный фокус. Надеюсь, вы чувствуете, что вам помогли. – Tony M 21 авг. 17 2020-08-21 20:21:01

@TonyM. Я ценю помощь и помощь в обучении. Тем не менее, они не являются незаконными символами, и имена правильно идут по строкам имен. Это просто добавление имен после двух вновь созданных листов, а не один за другим. Спасибо, тем не менее. – A Cohen 21 авг. 17 2020-08-21 20:28:05

Когда такие странные вещи случаются, я пытаюсь вернуться к точке, где код работал должным образом. Например, в моем исходном коде не было пропусков листов. Затем постепенно вводите строки кода, чтобы вы могли точно определить, где начинается неожиданное поведение. Если по какой-то причине лист не получит имя, если вы считаете, что это нужно, попробуйте называть лист с этим текстом вручную. – Tony M 21 авг. 17 2020-08-21 20:32:22

Excel VBA — создание макроса, который вставляет имя активного листа внутри другой текстовой панели — vba

Я работаю с двумя листами внутри моей книги. «Активный лист» и мой «Верхний лист поставщика». Мой «активный лист» может быть одним из многих дубликатов заметок, которые я готовлю для своих поставщиков, поэтому он может быть одним из многих разных листов внутри книги. Во всех этих заметках о сделке я хотел бы иметь кнопку, которая выполняет макрос, чтобы сделать следующее:

Илон Маск рекомендует:  Таблица «безопасных» цветов

В «Верхнем листе поставщика» в следующей доступной ячейке указанной строки я хочу вставить формулу, которая будет равна ячейке B8 текущего активного листа. Я не смог понять, как получить ссылку VBA на «Активный лист» внутри стандартной формулы excel. Вот мой код:

Excel vba как задать имя листу, который будет вставлен ?

Аэроплан » 23.10.2003 (Чт) 13:59

Макрос создает новый лист.
Неоднократно все время пока файл открыт.
Имена создаваемых файлов меняются Лист1, Лист2.
Как узнать его имя?

.
Я знаю только точное его положение. Он всегда третий слева.

Tony » 23.10.2003 (Чт) 14:29

Но ведь это совершенно не по взрослому ;-)

Аэроплан » 23.10.2003 (Чт) 14:51

А как же тогда пользоваться
Код: Выделить всё Workbook_NewSheet(ByVal Sh As Object)
Ведь это, насколько я понимаю, как раз и есть инструмент для выхватывания «листовых новообразований» в файле.
Я вот только не знаю, как изменить имя листа.

Код типа Sh.Name = «имя_листа» не работает и вообще при компиляции он говорит, что Sh.Name содержит в себе имя ВСЕГО документа, т.е. Имя_файла.xls.

Вот такая петрушка.

Tony » 23.10.2003 (Чт) 14:55

Аэроплан » 23.10.2003 (Чт) 15:54

Да нет же. Не имеет значения, каким образом создается ЛИСТ.
Просто эта процедура как-то реагирует и что-то возвращает при создании нового листа.. Т.е. она точно реагирует и отрабатывается сразу при появлении нового листа. Однако она не возвращает ИМЯ нового созданного листа. Точнее я незнаю как его оттуда выдрать. Вот.
Код: Выделить всё Sub Workbook_NewSheet(ByVal Sh As Object)

‘ тут чтото я незнаю’

Sh.Name = Date
Sheets(newSheetName).Select
Sheets(newSheetName).Move After:=Sheets(3)
Sheets(«Хронология»).Select
Range(«A1:K3»).Select
Selection.Copy

Sheets(newSheetName).Select
ActiveSheet.Paste
Sheets(«Хронология»).Application.CutCopyMode = False

End Sub

Так примерно я наваял. А вот Sh.Name не работает.
Точнее работает, но не так. При компиляции выделенный Name возвращает имя ВСЕГО файла Имя_файла.xls и на этом вылетает. в ошибку.

RayShade » 23.10.2003 (Чт) 16:07


Что то ты мудришь по ходу.

У меня все работает на ура. И с выводом имени листа и с его изменением. Версия офиса какая? Сервиспаки?

Vitaly1 » 23.10.2003 (Чт) 16:41

Если знаешь расположение то так, для активной книги:

Извиняюсь.

Аэроплан » 23.10.2003 (Чт) 17:11

Я извиняюсь. Действительно перемудрил. Код этот работает.
блин, я его собрал, и стал жаловаться на судьбу, а проверить еще не успел.. Проблема в другом.
Совсем в другом.

Если в один день создается несколько листов, то необходимо из
Код: Выделить всё newSheetName = Date

сделать Код: Выделить всё newSheetName = Date + Time

Однако это не работает по причине запрета использования двоеточий (чч:мм:сс) в именах листов. Я тут спешу, короче, сроки поджимают, вот и получается котовасия — проблему решил, тут же перескочил на другую, а решение предыдущей еще не осознал.
Я же не программист ВБ, я программист промышленный — там все проще.

Огромное спасибо за помощь и внимание.

Буду теперь из времени выдергивать двоеточия и менять их на «_».
Снова в поиск.

RayShade » 23.10.2003 (Чт) 17:17

Советую воспользоваться таким водом:

Код: Выделить всё newSheetName = replace(cstr(date+time),»:»,»_». vbtextcompare)

Vitaly1 » 23.10.2003 (Чт) 17:29

там функция есть говорил RayShade но я ее не знаю, поэтому используй мой программный код преобразования:

S = »20:10:3»
s1 =»»
for i=1 to len(s)
if mid(s,i,1) <>«:» then
s1=s1+mid(s,i,1)
else
s1=s1+» »
end if
next i

в s1 символ : замениться на пробел

RayShade » 23.10.2003 (Чт) 18:07

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

Код: Выделить всё s = »20:10:3»
for i=1 to len(s)
if m
next i

Excel vba как задать имя листу, который будет вставлен ?

По материалам эхоконференции RU.EXCEL (за июль-сентябрь 1997 года) Collected by Kirienko Andrew, 2:5020/239.21@fidonet

Cодержание

Как определить последнюю запись в таблице Excel?

Q: Необходимо найти последнюю запись вэлектронной таблице. Какой функцией VB это можно было бы организовать.

A: Первое что вспомнилось: Application.SpecialCells(xlLastCell)

Как отменить выделение диапазона ячеек?

Q: Как управиться с такой болячкой:

После прекращения работы макроса диапазон остается выделенным. Как это выделение убрать?

A: Попробуй вот как: Selection.Cells(1).Select Фокус ввода попадёт после этого на первую ячейку ранее выделенного диапазона.

Как из макроса Excel программно создать таблицу Access?

Q: Подскажите, пожалуйста, как из под Excel программно создать таблицу Access

A: Вот фрагмент кода, который создаёт таблицу «BalanceShifr» базе данных MS Access:

Нint: Не забудьте выставить в Excel ссылки на объекты DAO!
[VBA] Tools/References/Available References/ [x] MicroSoft DAO. Library ‘ Function CreateTable ‘ Create temporary table «BalanceShifr» into temporary database

Public Function CreateTable(ByVal dbTemp As Database) As Boolean

Dim tdfTemр As TableDef
Dim idx As Index
Dim fld As Field

On Error GoTo errhandle

CreateTable = True
‘ CREATE TABLE «BalanceShifr»
Set tdfTemp = dbTemp.CreateTableDef(«BalanceShifr»)
Set fld = tdfTemp.CreateField(«ConditionId», dbLong)
fld.Required = True
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Account», dbText, 4)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SubAcc», dbText, 4)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Shifr», dbLong)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Date», dbDate)
fld.Required = True
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SaldoDeb», dbCurrency)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SaldoKr», dbCurrency)
tdfTemp.Fields.Append fld
dbTemp.TableDefs.Append tdfTemp


‘ CREATE INDEX «BalanceShifr»
Set tdfTemp = dbTemp.TableDefs(«BalanceShifr»)
Set )
Set fld = idx.CreateField(«ConditionId»)
idx.Fields.Append fld
tdfTemp.Indexes.Append idx
Exit Function

errHandle:
MsgBox «Table creating error!», vbExclamation, «Error»
CreateTable = False
End Function

Удаление листов в зависимости от даты

Q: Как удалить рабочие листы листов в зависимости от даты?

A: Вот код функции на Excel VBA, который решает данную проблему:

‘ Function DelSheetByDate
‘ Удаляет рабочий лист sSheetName в активной рабочей книге,
‘ если дата dDelDate уже наступила
‘ В случае успеха возвращает True, иначе — False

Public Function DelSheetByDate(sSheetName As String, _
dDelDate As Date) As Boolean
On Error GoTo errHandle

DelSheetByDate = False
‘ Проверка даты
If dDelDate

Подавление «горячих» клавиш.

Q:Как подавить доступ по «горячим» клавишам, имеется ввиду предопределенные в Excel клавиши типа Ctrl-O и т.д.?

A:Вот малюсенький исходник на Excel VB, который решает такую проблему. :-)

Public Sub Auto_Open()
‘ Overrride standard accelerators
With Application
.OnKey «^o», «Dummy»
.OnKey «^s», «NewAction»
.OnKey «^р», «» ‘ Kill hotkey !
End With
End Sub

‘ ——
Public Sub Dummy()
MsgBox «This hotkey redefined!»
End Sub

‘ ——
Public Sub NewAction()
SendKeys «^n» ‘ Press + for create new file
‘ instead of + !
End Sub

Hint: Отлажено в MS Excel ’97 !

Подсказки к Toolbar

Q: Как сделать к «само нарисованным» кнопочкам на Toolbar’е подсказки? (Ну, те, что после 2-х секунд молчания мышки появляются)

A: Сделать можно вот как: (Пример реализации на Excel’97 VBA )

‘ Cоздаем тулбар
Рublic Sub InitToolBar()
Dim cmdbarSM As CommandBar
Dim ctlNewBtn As CommandBarButton

Set cmdbarSM = CommandBars.Add(Name:=»MyToolBar»,
Position:=msoBarFloating, _
temporary:=True)
With cmdbarSM
‘ 1) Добавляем кнопку
Set ctlNewBtn = .Controls.Add(Type:=msoControlButton)
With ctlNewBtn
. Face > .OnAction = «OnButton1_Click»
.TooltipText = «My tooltip message!»
End With
‘ 2) Добавляем ещё кнопку
Set ctlNewBtn = .Controls.Add(Type:=msoControlButton)
With ctlNewBtn
.Face > .OnAction = «OnButton2_Click»
.TooltipText = «Another tooltip message!»
End With
.Visible = True
End With
End Sub

Hint: На VBA для Excel’95 это делается несколько иначе!

Как определить адрес активной ячейки

Q: Как в макросе узнать и использовать текущее положение курсора (не мышиного, естественно)?

A: Очень просто! :-)
ActiveCell.Row и ActiveCell.Column — покажут координаты активной ячейки.

Подсчет комментариев на рабочем листе

Q: Как узнать есть ли хоть один Notes (комментарий) в рабочем листе, кроме как перебором по всем ячейкам? . Без этого не работает:

A: В Excel’97 эта проблема может быть решена вот как:

‘ Function IsCommentsPresent
‘ Возвращает TRUE, если на активном рабочем листе имеется хотя бы
‘ одна ячейка с комментарием, иначе возвращает FALSE

Public Function IsCommentsPresent() As Boolean
IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 )
End Function

Подсказки к Toolbar (Excel’95)

Q: Как сделать свой собственный Toolbar с tooltip’ами на кнопках в Excel’95?

A: Вот фрагмент кода для Excel’95, который создаёт toolbar с одной кнопкой с пользовательским tooltiр’ом. Нажатие кнопки приводит к выполнению макроса NothingToDo() .


‘ This example creates a new toolbar, adds the Camera button
‘ (button index number 228) to it, and then displays the new toolbar.

Public Sub CreateMyToolBar()
Dim myNewToolbar As Toolbar
On Error GoTo errHandle:

Set myNewToolbar = Toolbars.Add(Name:=»My New Toolbar»)
With myNewToolbar
.ToolbarButtons.Add Button:=228, StatusBar:=»Statusbar help string»
.Visible = True
With .ToolbarButtons(1)
.OnAction = «NothingToDo»
.Name = «My custom tooltiр text!»
End With
End With
Exit Sub
errНandle:
MsgBox «Error number » & Err & «: » & Error(Err)
End Sub


‘ Toolbar button on action code

Рublic Sub NothingToDo()
MsgBox «Nothing to do!», vbInformation, «Macro running»
End Sub

Нint: В Excel’97 этот код тоже работает!

Запуск Excel с поиском ячейки

Q: Как запустить Excel, чтобы оказаться на ячейке содержимое которой известно заранее?


A:Вот как я решил бы твою задачу:

‘ Sub GotoFixedCell:
‘ Делает активной ячейку, содержащую значение vVariant на
‘ рабочем листе sSheetName в активной рабочей книге.

‘ Note: Содержимое ячеек интерпретируется как ‘значение’!

Public Sub GotoFixedCell(vValue As Variant, sSheetName As String)
Dim c As Range, cStart As Range, cForFind As Range
Dim i As Integer

On Error GoTo errhandle:

Set cForFind = Worksheets(sSheetName).Cells ‘ Диапазон поиска
With cForFind
Set c = .Find(What:=vValue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:= xlРart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False)
Set cStart = c
While Not c Is Nothing
Set c = .FindNext(c)
If c.Address = cStart.Address Then
c.Select
Exit Sub
End If
Wend
End With
Exit Sub
errНandle:
MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number
End Sub

Нint: Достаточно выполнить этот код из макроса Auto_Oрen()!

Нint: Протестировано и отлажено в Excel’97.

ThisWorkBook или ActiveWorkBook?

Q: На листе модулей открытой рабочей книги присутствует процедура, которая копирует некий лист из другой (не активной) рабочей книги. В этом листе в некоторых ячейках находятся определенные пользователем формулы. Процедура работает без проблем.
Из workbook, содержащей эту процедуру, я делаю надстройку (.xla) и подключаю ее к Excel 95. При вызове вышеописанной процедуры она выдает сообщение:
Run time error 424 object required
Kак можно избежать это сообщение?

A:Вот что я тебе посоветую:
Посмотри ещё разок код модулей рабочей книги и исправь все ссылки вида ActiveWorkbook.WorkSheets(«.. на ссылки вида ThisWorkBook.WorkSheets(«..

Дело в том, что когда выполняется код надстройки активной книгой в Excel’е является _не_ сама надстройка! Конструкция ThisWorkbook позволяет сослаться на книгу, в которой в настоящий момент выполняется код Excel VBA.

Илон Маск рекомендует:  Уроки MS Excel

Нint: Это общий принцип создание надстроек Excel!

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

Q:Хочy через Excel VBA задать имя листу, который будет вставлен. Но у команды Sheets.Add нет такого параметра ! Как бороться?

A: Очень просто.

‘ Sub CreateSheet
‘ Вставляет активную рабочую книгу в рабочий лист с именем sSName.
‘ Note: Если параметр bVisible имеет значение False, этот лист становится скрытым.

Рublic Sub CreateSheet(sSName As String, bVisible As Boolean)
Dim wsNewSheet As WorkSheet

On Error GoTo errНandle

Set wsNewSheet = ActiveWorkBook.Worksheets.Add
With wsNewSheet
.Name = sSName
.Visible = bVisible
End With
Exit Sub
errНandle:
MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number
End Sub

Как проверить существует ли лист?

Q: А как проверить существует ли лист?

A: Я бы поступил вот как:

‘ Function IsWorkSheetExist
‘ Проверяет, имеется ли в активной рабочей книге лист с именем sSName.
‘ В случае успеха возвращает True, иначе — False

Рublic Function IsWorkSheetExist(sSName As String) As Boolean
Dim c As Object

On Error GoTo errНandle:
Set c = sheets(sName)
‘ Альтернативный вариант :
Worksheets(sSName).Cells(1, 1) = Worksheets(sSName).Cells(1, 1)
IsWorkSheetExist = True
Exit Function
errНandle:
IsWorkSheetExist = False
End Function

Нint: Отлажено и протестировано в Excel’97.

Как обратиться к ячейке по ее имени?

Q: Как обратиться к ячейки по ее имени? Т.е. есть Лист1 и в нем ячейки с именем Дебет и Кредит. Хочy подсчитать Дебет-Кредит средствами Excel VBA. Попробовал Range(Дебет)-Range(Кредит), ругается, что не описаны переменные.

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

‘ Function ValueOfNamedCell
‘ Возвращает значение ячейки с именем sCellName. в активной рабочей книге.
‘ Note: Если ячейка с именем sCellName не существует — функцией возвращается
‘ значение Emрty.

Рublic Function ValueOfNamedCell(sCellName As String) As Variant
On Error GoTo errНandle
ValueOfNamedCell = ActiveWorkbook.Names(sCellName).RefersToRange.Value
Exit Function
errНandle:
ValueOfNamedCell = Emрty
End Function

Нint: Отлажено и протестировано в Excel’97.

Можно ли из программы на Visual Basic создать рабочую книгу Excel?

Q: Можно ли из программы на Visual Basic создать рабочую книгу Excel?

Пример того, как из Visual Basic’a через OLE запустить Excel, и создать рабочую книгу.

‘ CreateXlBook
‘ Вызывает MS Excel, создает рабочую книгу с именем sWbName с одним
‘ единственным рабочим листом. Рабочая книга будет сохранена в каталоге
‘ sDirName. В случае успеха возвращает True, в противном случае — False.

Public Function CreateXlBook(sWbName As String, sDirName) As Boolean

‘ MS Excel hidden instance
Dim objXLApp As Object
Dim objWbNewBook As Object

Set objXLApp = CreateObject(«Excel.Application»)
If objXLApp Is Nothing Then Exit Function

‘ В новой рабочей книге создавать только один рабочий лист
objXLApp.SheetsInNewWorkbook = 1

Set objWbNewBook = objXLApp.Workbooks.Add
If objWbNewBook Is Nothing Then Exit Function

‘ Сохраняем книгу
If vbNullString = Dir(sDirName, vbDirectory) Then Exit Function

objWbNewBook.SaveAs (sDirName + «\» + sWbName + «.xls»)
CreateXlBook = True


‘ Освобождение памяти
Set objWbNewBook = Nothing
objXLApp.Quit
Set objXLApp = Nothing
CreateXlBook = True

Hint: Tested and approved with MS Visual Basic 4.0 Enterprise Edition

Как установить имя листа в VBA?

У меня есть некоторые проблемы с получением этого кода , чтобы установить лист , я могу представить себе его , потому что я не dim его , но я попытался с , dim as workbook что не работает.

Я попытался шаг за шагом идти и «THF» имеют правильную строку в нем.

Повторно в качестве ответа. Вы заявили thfs в книге в следующем операторе Dim thfs As Workbook , а затем пытаетесь SET ей быть рабочим листом. Попробуйте изменить его Dim thfs As Worksheet

Несколько советов по работе с VBA в Excel

Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

Visual Basic

Опции

Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

Так же рекомендуется прописать:

В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

Ещё одним важным оператором является ON ERROR. Привожу варианты:

Возможности языка

Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):

Ускорение работы макросов

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:

Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.

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

Загрузка книги и события

При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

Защита

Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

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

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

Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

Заключение

VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

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

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

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL