excel-vba
Советы и подсказки Excel VBA

замечания

Использование xlVeryHidden Sheets

Рабочие листы в excel имеют три варианта свойства Visible . Эти параметры представлены константами в перечислении xlSheetVisibility и следующие:

1. xlVisible или xlSheetVisible значение: -1 (по умолчанию для новых листов)
2. xlHidden или xlSheetHidden : 0
3. xlVeryHidden xlSheetVeryHidden : 2

Видимые листы представляют видимость по умолчанию для листов. Они видны на панели вкладок листов и могут быть свободно выбраны и просмотрены. Скрытые листы скрыты от панели вкладок листа и поэтому не могут быть выбраны. Однако скрытые листы могут быть скрыты из окна Excel, щелкнув правой кнопкой мыши на вкладках листа и выбрав «Unhide»,

Скрытые листы, с другой стороны, доступны только через редактор Visual Basic. Это делает их невероятно полезным инструментом для хранения данных через экземпляры excel, а также для хранения данных, которые должны быть скрыты от конечных пользователей. Листы могут быть доступны по именованной ссылке в коде VBA, что позволяет легко использовать сохраненные данные.

Чтобы вручную изменить свойство .Visible для xlSheetVeryHidden, откройте окно свойств VBE ( F4 ), выберите рабочий лист, который вы хотите изменить, и используйте раскрывающийся список в тринадцатой строке, чтобы сделать свой выбор.

Чтобы изменить свойство .Visible рабочего листа на xlSheetVeryHidden¹ в коде, аналогичным образом получите доступ к свойству .Visible и назначьте новое значение.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Оба xlVeryHidden и xlSheetVeryHidden возвращают числовое значение 2 (они взаимозаменяемы).}

Рабочий лист .Name, .Index или .CodeName

Мы знаем, что «лучшая практика» диктует, что объект диапазона должен иметь свой родительский лист, на который явно ссылаются. Рабочий лист может ссылаться на его свойство .Name, численное свойство .Index или его свойство .CodeName, но пользователь может изменить порядок очереди на рабочий лист, просто перетащив вкладку имен или переименуйте рабочий лист с двойным щелчком на той же вкладке, а некоторые набрав незащищенную книгу.

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

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

'reference worksheet by .Name
with worksheets("Monday")
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

'reference worksheet by ordinal .Index
with worksheets(1)
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

И первоначальный заказ, и имя исходного листа были скомпрометированы. Однако, если вы использовали свойство .CodeName рабочего листа, ваша вспомогательная процедура все равно будет работать

with Sheet1
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

На следующем рисунке показано окно проекта VBA ([Ctrl] + R), в котором перечислены рабочие листы по .CodeName, затем по .Name (в скобках). Приказ, который они отображают, не изменяется; ординал .Index берется по порядку, который они отображаются в очереди вкладок имен в окне рабочего листа.

Хотя это редко можно переименовать .CodeName, это не невозможно. Просто откройте окно свойств VBE ([F4]).

Рабочий лист .CodeName находится в первой строке. Имя рабочего листа находится в десятой части. Оба доступны для редактирования.

Использование строк с разделителями на месте динамических массивов

Использование динамических массивов в VBA может быть довольно неуклюжим и трудоемким по сравнению с очень большими наборами данных. При хранении простых типов данных в динамическом массиве (Strings, Numbers, Booleans и т. Д.) Можно избежать операторов ReDim Preserve необходимых для динамических массивов в VBA, используя функцию Split() с некоторыми умными строковыми процедурами. Например, мы рассмотрим цикл, который добавляет ряд значений из диапазона в строку на основе некоторых условий, а затем использует эту строку для заполнения значений ListBox.

Private Sub UserForm_Initialize()

Dim Count As Long, DataString As String, Delimiter As String

For Count = 1 To ActiveSheet.UsedRows.Count
    If ActiveSheet.Range("A" & Count).Value <> "Your Condition" Then
        RowString = RowString & Delimiter & ActiveSheet.Range("A" & Count).Value
        Delimiter = "><" 'By setting the delimiter here in the loop, you prevent an extra occurance of the delimiter within the string
    End If
Next Count

ListBox1.List = Split(DataString, Delimiter)

End Sub

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

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

Событие Double Click для форм Excel

По умолчанию в образцах Excel нет определенного способа обработки одиночных или двойных кликов, содержащих только свойство «OnAction», позволяющее обрабатывать клики. Однако могут быть случаи, когда ваш код требует от вас действовать по-разному (или исключительно) при двойном щелчке. Следующая подпрограмма может быть добавлена ​​в ваш проект VBA и, когда она установлена ​​в качестве процедуры OnAction для вашей фигуры, позволяет действовать двойным щелчком.

Public Const DOUBLECLICK_WAIT as Double = 0.25 'Modify to adjust click delay
Public LastClickObj As String, LastClickTime As Date

Sub ShapeDoubleClick()
    
    If LastClickObj = "" Then
        LastClickObj = Application.Caller
        LastClickTime = CDbl(Timer)
    Else
        If CDbl(Timer) - LastClickTime > DOUBLECLICK_WAIT Then
            LastClickObj = Application.Caller
            LastClickTime = CDbl(Timer)
        Else
            If LastClickObj = Application.Caller Then
                'Your desired Double Click code here
                LastClickObj = ""
            Else
                LastClickObj = Application.Caller
                LastClickTime = CDbl(Timer)
            End If
        End If
    End If
    
End Sub

Эта процедура заставит форму функционально игнорировать первый клик, только запуск вашего желаемого кода на второй клик в течение указанного промежутка времени.

Открыть диалоговое окно «Файл» - несколько файлов

Эта подпрограмма - это быстрый пример того, как разрешить пользователю выбирать несколько файлов, а затем делать что-то с этими файловыми путями, например получить имена файлов и отправить их на консоль через debug.print.

Option Explicit

Sub OpenMultipleFiles()
    Dim fd As FileDialog
    Dim fileChosen As Integer
    Dim i As Integer
    Dim basename As String
    Dim fso As Variant
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    basename = fso.getBaseName(ActiveWorkbook.Name)
    fd.InitialFileName = ActiveWorkbook.Path ' Set Default Location to the Active Workbook Path
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True
    
    fileChosen = fd.Show
    If fileChosen = -1 Then
        'open each of the files chosen
        For i = 1 To fd.SelectedItems.Count
            Debug.Print (fd.SelectedItems(i))
            Dim fileName As String
            ' do something with the files.
            fileName = fso.getFileName(fd.SelectedItems(i))
            Debug.Print (fileName)
        Next i
    End If

End Sub