excel-vba
Excel VBA consejos y trucos

Observaciones

Usando las hojas xlVeryHidden

Las hojas de trabajo en Excel tienen tres opciones para la propiedad Visible . Estas opciones están representadas por constantes en la enumeración xlSheetVisibility y son las siguientes:

1. xlVisible o xlSheetVisible : -1 (el valor predeterminado para las hojas nuevas)
2. xlHidden o xlSheetHidden : 0
3. xlVeryHidden xlSheetVeryHidden valor: 2

Las hojas visibles representan la visibilidad predeterminada para las hojas. Se pueden ver en la barra de pestañas de la hoja y se pueden seleccionar y ver libremente. Las hojas ocultas se ocultan de la barra de pestañas de la hoja y, por lo tanto, no se pueden seleccionar. Sin embargo, las hojas ocultas se pueden ocultar de la ventana de Excel haciendo clic derecho en las pestañas de la hoja y seleccionando "No ocultar"

Las hojas muy ocultas, por otro lado, solo son accesibles a través del Editor de Visual Basic. Esto los convierte en una herramienta increíblemente útil para almacenar datos en instancias de Excel, así como para almacenar datos que deben ocultarse a los usuarios finales. Se puede acceder a las hojas mediante una referencia con nombre dentro del código VBA, lo que permite un uso fácil de los datos almacenados.

Para cambiar manualmente la propiedad .Visible de una hoja de trabajo a xlSheetVeryHidden, abra la ventana de Propiedades de VBE ( F4 ), seleccione la hoja de trabajo que desea cambiar y use el menú desplegable en la fila trece para hacer su selección.

Para cambiar la propiedad .Visible de una hoja de cálculo a xlSheetVeryHidden¹ en el código, acceda de manera similar a la propiedad .Visible y asigne un nuevo valor.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Tanto xlVeryHidden como xlSheetVeryHidden devuelven un valor numérico de 2 (son intercambiables).}

Hoja de trabajo .Nombre, .Index o .CódigoNombre

Sabemos que las "mejores prácticas" dictan que un objeto de rango debe tener su hoja de cálculo principal referenciada explícitamente. Se puede hacer referencia a una hoja de cálculo por su propiedad .Name, su propiedad numérica .Index o su propiedad .CodeName, pero un usuario puede reordenar la cola de la hoja de trabajo simplemente arrastrando una pestaña de nombre o renombrando la hoja de cálculo con un doble clic en la misma pestaña y algunos escribiendo en un libro de trabajo desprotegido.

Considere una hoja de trabajo estándar de tres. Ha cambiado el nombre de las tres hojas de trabajo el lunes, martes y miércoles en ese orden y ha codificado los sub procedimientos de VBA que hacen referencia a estos. Ahora considere que un usuario aparece y decide que el lunes corresponde al final de la cola de la hoja de trabajo, luego aparece otro y decide que los nombres de la hoja de trabajo se ven mejor en francés. Ahora tiene un libro de trabajo con una cola de pestañas con el nombre de la hoja de trabajo que se parece a lo siguiente.

Si ha utilizado alguno de los siguientes métodos de referencia de la hoja de trabajo, su código ahora estaría roto.

'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

Tanto el pedido original como el nombre original de la hoja de trabajo se han comprometido. Sin embargo, si ha usado la propiedad .CodeName de la hoja de trabajo, su sub procedimiento todavía estaría operativo

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

La siguiente imagen muestra la ventana del proyecto VBA ([Ctrl] + R) que enumera las hojas de trabajo por .CodeName luego por .Name (entre paréntesis). El orden en que se muestran no cambia; el .Index ordinal se toma según el orden en que se muestran en la cola de la pestaña de nombre en la ventana de la hoja de trabajo.

Si bien es poco común cambiar el nombre de un .CodeName, no es imposible. Simplemente abra la ventana Propiedades de VBE ([F4]).

La hoja de trabajo .CodeName está en la primera fila. El nombre de la hoja de cálculo está en el décimo. Ambos son editables.

Uso de cadenas con delimitadores en lugar de matrices dinámicas

El uso de arreglos dinámicos en VBA puede ser bastante complejo y requiere mucho tiempo en conjuntos de datos muy grandes. Al almacenar tipos de datos simples en una matriz dinámica (Cadenas, Números, Booleanos, etc.), se pueden evitar las declaraciones ReDim Preserve de las matrices dinámicas en VBA utilizando la función Split() con algunos procedimientos de cadena inteligentes. Por ejemplo, veremos un bucle que agrega una serie de valores de un rango a una cadena en función de algunas condiciones, y luego usamos esa cadena para completar los valores de un 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

La propia cadena Delimiter se puede establecer en cualquier valor, pero es prudente elegir un valor que no se producirá naturalmente dentro del conjunto. Digamos, por ejemplo, que estaban procesando una columna de fechas. En ese caso, utilizando . , - , o / sería imprudente como delimitadores, ya que las fechas se podrían formatear para usar cualquiera de estos, generando más puntos de datos de los que anticipó.

Nota: existen limitaciones para usar este método (es decir, la longitud máxima de las cadenas), por lo que debe usarse con precaución en casos de conjuntos de datos muy grandes. Este no es necesariamente el método más rápido o más eficaz para crear arreglos dinámicos en VBA, pero es una alternativa viable.

Haga doble clic en el evento para formas de Excel

De forma predeterminada, las Formas en Excel no tienen una forma específica de manejar clics simples o dobles, que contienen solo la propiedad "OnAction" para permitirle manejar clics. Sin embargo, puede haber casos en que su código requiera que actúe de manera diferente (o exclusivamente) con un doble clic. La siguiente subrutina se puede agregar a su proyecto de VBA y, cuando se configura como la rutina de OnAction para su forma, le permite actuar en doble clic.

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

Esta rutina hará que la forma ignore funcionalmente el primer clic, solo ejecutando el código deseado en el segundo clic dentro del intervalo de tiempo especificado.

Cuadro de diálogo Abrir archivo - Varios archivos

Esta subrutina es un ejemplo rápido de cómo permitir que un usuario seleccione varios archivos y luego haga algo con esas rutas de archivos, como obtener los nombres de los archivos y enviarlos a la consola a través de 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