COLECCIONES

¿Qué es una colección?

Una colección es un objeto utilizado para agrupar y administrar objetos relacionados. Las colecciones son un buen método para hacer un seguimiento de los objetos que una aplicación puede necesitar crear o destruir dinámicamente.

Una colección se parece a una matriz, en el sentido que podemos acceder a sus elementos mediante un índice.

Ventajas de utilizar colecciones frente a las matrices:

• Utilizan menos memoria.
• El acceso a los elementos de la colección es mas flexibe.
• Las colecciones tienen métodos para añadir nuevos elementos y eliminar otros.
• El tamaño de la colección se ajusta automáticamente.
• Pueden tener simultáneamente elementos de diferentes tipos.

La palabra clave New en la declaración de la variable produce un objeto Collection que se va a crear cuando el control pasa a la instrucción de declaración.

Dim loteria As New Collection

El objeto Collection tiene los siguientes métodos:

• Add: Agrega un elemento a un objeto Collection de VBA.
• Remove: Quita un elemento de un objeto Collection de VBA.

Y las siguientes propiedades:

• Count: Devuelve un valor Integer que contiene el número de elementos en una colección.
• Item: Devuelve un elemento concreto de un objeto Collection de VBA por posición o por clave.

El siguiente procedimiento creamos la colección lotería con cinco elementos (números  aleatorios de 0 a 9) que presentamos concatenados dentro de una variable string en un mensaje.

Sub loteriaNiño()

Dim loteria As New Collection

Dim numero As Integer, n As Integer

' Llenamos el objeto colección de números enteros aleatorios.

    For n = 1 To 5

        numero = Int(rnd * 9)

        loteria.Add numero

    Next n

' Recorremos el objeto de colección para presentar el numero.

Dim premio As String

    For n = 1 To loteria.Count

        premio = premio & loteria.Item(n)

    Next n

MsgBox "El numero premiado es... " & Chr(13) & premio

End Sub

DATOS DUPLICADOS

En esta publicación vamos a explicar cómo podemos eliminar la fila donde nos encontramos dos datos iguales, es decir, tenemos por ejemplo en la columna "A" referencias de nuestros productos y en la columna "D" tenemos referencias del proveedor, se nos da el caso de que alguna de las referencias son coincidentes y las necesitamos eliminar.

Al mismo tiempo vamos a provechar y vamos a incluir la aplicación StatusBar para ver la evolución de nuestro procedimiento.

Option Explicit

Sub EliminarFilas()   

Dim Rango As Range, Fila As Long, ÚltimaFila As Long   

Application.ScreenUpdating = False

Range("A1").Select

ÚltimaFila = Range("A:A").End(xlDown).Row   

For Fila = 1 To ÚltimaFila

    Application.StatusBar = "Procesando fila " & Fila & " / " & ÚltimaFila

        If Range("A" & Fila).Value = Range("D" & Fila).Value Then

            If Rango Is Nothing = True Then

                Set Rango = Rows(Fila)

            Else

                Set Rango = Application.Union(Rango, Rows(Fila))

        End If

    End If

Next Fila

If Rango Is Nothing = False Then

    Rango.Select

    Selection.Delete

    Range(1, 1).Select

End If  

Application.StatusBar = "Proceso finalizado"

Application.ScreenUpdating = True

End Sub

COMPRIMIR ARCHIVOS

Los archivos en formato ZIP probablemente sean los tipos de archivos mas utilizados, este ejemplo muestra como crear un archivo ZIP de un grupo de archivos seleccionados por el usuario con el método (Application.GetOpenFilename). Después crea un archivo llamado PruebaComprimir.zip en el directorio donde este guardado el Excel que contenga el siguiente procedimiento.

Sub ComprimirArchivos()

    Dim ShellApp As Object

    Dim FileNameZip As Variant

    Dim FileNames As Variant

    Dim i As Long, FileCount As Long

'   Obtener los nombres de los archivo

    FileNames = Application.GetOpenFilename _

        (FileFilter:="Todos los archivos. (*.*),*.*", _

         FilterIndex:=1, _

         Title:=" Seleccione los archivos para comprimir. ", _

         MultiSelect:=True)

'   Salir si se cancela el cuadro de diálogo

    If Not IsArray(FileNames) Then Exit Sub

  

    FileCount = UBound(FileNames)

    FileNameZip = Application.DefaultFilePath & "\PruebaComprimir.zip"

   

  

'   Crear archivo Zip vacío...

    Open FileNameZip For Output As #1

    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)

    Close #1

    Set ShellApp = CreateObject("Shell.Application")

'   Copia los archivos a la carpeta comprimida

    For i = LBound(FileNames) To UBound(FileNames)

        ShellApp.Namespace(FileNameZip).CopyHere FileNames(i)

    Next i

    On Error Resume Next

    Do Until ShellApp.Namespace(FileNameZip).items.Count = FileCount

        Application.Wait (Now + TimeValue("0:00:01"))

    Loop

   

    If MsgBox(FileCount & " Los archivos se han comprimido en:" & _

       vbNewLine & FileNameZip & vbNewLine & vbNewLine & _

       " Quieres ver el archivo zip?", vbQuestion + vbYesNo) = vbYes Then _

       Shell "Explorer.exe /e," & FileNameZip, vbNormalFocus

End Sub

EL protocolo DDE

El protocolo de intercambio dinámico de datos DDE (Dynamic Data Exchange) es uno de los métodos de comunicación entre procesos que permite intercambiar datos entre aplicaciones de Windows. El protocolo DDE está basado en el sistema de mensajería construido por Windows.

EL vínculo DDE comprende tres fases principales:

1º.- Inicialización: la aplicación destino busca la aplicación fuente y establece con ella un canal de comunicación (vinculo).

La función DDEinitiate: Permite inicia la comunicación entre dos aplicaciones.

2º.- Conversación: se intercambian los datos en el canal.

La función DDE: Permite establecer la conversación.

La función DDESend: Envía un elemento de información a la aplicación.

La función DDEPoke: Envía información a la aplicación fuente por un canal abierto.

La función DDERequest: pide información a la aplicación fuente.

La función DDEExcute: envía una cadena de comandos a la aplicación vinculada.

3º.- Clausura: cierra el canal de comunicación.

La función DDETerminate: cierra el canal de información DDE.

La función DDETerminateAll: cierra todos los canales de comunicación DDE.

El siguiente ejemplo muestra como copiar celdas Excel al principio de un documento Word.

Sub transferirInforme()

Dim canal As Long

'Inicializacion

    canal = Application.DDEInitiate("WinWord", "C:\Users\Jose Luis\Desktop\PruebaDDE\presupuesto.docx")

    Worksheets(1).Range("D8:E12").Select

'Conversacion

    Application.DDEPoke canal, "\StartOfDoc", Selection

'Clausura

    Application.DDETerminate canal

End Sub

“\ StartOfDoc” es un marcador predefinido en Word.

Invalidar la X del UserForm

Alguna vez nos hemos preguntado como podría impedir que el usuario pulse el botón de cerrar el formulario.

Para eliminar el botón de cerrar de una barra de titulo de un UserForm, se necesita complejas funciones API como las que vimos en el post anterior. Una solución mucho mas sencilla consiste en interceptar todos los intentos de cerrar el UserForm por medio de un procedimiento de eventos “UserForm_QueryClose” en el modulo de código del UserForm.

El siguiente ejemplo impide al usuario cerrar el formulario pulsando el botón de Cerrar:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then

        Cancel = True

    End If

End Sub

Cuando se intenta cerrar el formulario, utilizamos el procedimiento de evento QueryClose para establecer la propiedad Cancel a True, deteniendo el proceso de cierre.

Truco para UserForm

Excel no ofrece de ninguna manera mostrar directamente un formulario sin su barra de titulo, pero esto lo podemos hacer realidad gracias a la utilización de funciones API.

Que es API??

API es un acrónimo de Interfaz de programación de aplicaciones, el conjunto de comandos que utiliza una aplicación para solicitar y realizar servicios de nivel inferior del sistema operativo del PC.

Cuando necesites capacidades que vayan más allá del lenguaje y los controles comunes proporcionados por Microsoft Visual Basic, puedes llamar directamente a los procedimientos incluidos en las bibliotecas de vínculos dinámicos (DLL, Dinamyc Link Libraries). Al llamar a estos procedimientos, podrás tener acceso a los miles de procedimientos que constituyen la espina dorsal del sistema operativo Microsoft Windows, así como a rutinas creadas en otros lenguajes.

Para mostrar un UserForm sin barra de titulo necesitamos cuatro funciones API:

• Function GetWindowLong
• Function SetWindowLong
• Function DrawMenuBar
• Function FindWindowA 

Que declararemos de forma Privada dentro de nuestro formulario, luego las haremos llamar desde el procedimiento de iniciación del formulario (UserForm_Initialize).

El código quedaría más o menos de la siguiente forma,

Option Explicit

Const GWL_STYLE = -16

Const WS_CAPTION = &HC00000

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Sub UserForm_Initialize()

    Dim lngWindow As Long, lFrmHdl As Long

    lFrmHdl = FindWindowA(vbNullString, Me.Caption)

    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)

    lngWindow = lngWindow And (Not WS_CAPTION)

    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)

    Call DrawMenuBar(lFrmHdl)

End Sub

Un problema que se nos puede presentar al utilizar un formulario sin barra de titulo es que no tenemos manera de cerrarlo si no creamos un botón de cerrar o cambiar su tamaño.

Si quieres aprender mas sobre API pincha aquí.

Formas

Excel utiliza el grupo Formas o “Shapes”, al que se accede seleccionando insertar>>Formas para insertar una forma “Shape” a una hoja de trabajo.

Shapes (objeto)  es la colección de todos los objetos Shape de la hoja especificada, cada objeto Shape representa un objeto de la capa de dibujo, como una autoforma, una forma libre, un objeto OLE o una imagen.

El número del nombre de la forma varía, dependiendo de cuantas formas iguales se haya insertado, por ejemplo si se han incrustado dos rectángulos, el nombre del segundo seria “2 Rectangle”.

Usaremos Shapes(índice), donde índice es el nombre o número de índice de la forma, para obtener un solo objeto Shape.

El siguiente procedimiento crea una forma con una textura y aplica algunos efectos y sombras.

Sub ObShapes()

Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddShape(Type:=msoShapeMathMultiply, _

Left:=100, Top:=10, Width:=200, Height:=200)

    With shp

        .Line.Visible = False

        .ThreeD.Visible = True

        .ThreeD.Depth = 60

        .ThreeD.RotationY = 10

        .Shadow.Visible = True

        .Shadow.Transparency = 0.8

        .Shadow.Blur = 12

        .Fill.ForeColor.RGB = RGB(255, 0, 0)

    End With

End Sub

Al trabajar con formas es preferible que el código realice varias acciones, por lo tanto es eficiente crear una variable de objeto.

Nuestro ejemplo quedaría más o menos como la siguiente imagen:

Trabajar con colores

Tratar con los colores en Excel no es un asunto trivial, admito que es algo complicado, grabar una macro mientras se cambia el color en una celda u objeto solo genera confusión.

Ahora podemos acceder en un libro a una cantidad de colores prácticamente ilimitada  (16.777.216 colores), nadie puede conocer el valor de casi 17 millones de colores.

Podemos cambiar el color de la celda activa a verde utilizando la siguiente instrucción.

Sub colorVerde()

ActiveCell.Interior.color = 62280

End Sub

Una buena manera de cambiar los colores es especificar el color de acuerdo con sus componentes  rojo, verde y azul, el sistema de colores RGB.

El rango de cada uno de estos colores va desde el 0 al 255, por lo tanto el número total de colores posibles seria 256x256x256= 16.777.216 colores.

Para especificar los colores en VB mediante RGB utilizamos la función RGB que nos devuelve un número  que representa un valor de color.

Para representar el color del ejemplo anterior con RGB lo haríamos de la siguiente forma.

Sub colorVerde()

ActiveCell.Interior.color = RGB(0, 255, 0)

End Sub

La siguiente tabla muestra algunos colores estándar y sus valores de rojo, verde y azul:

Color	Valor de rojo	Valor de verde	Valor de azul
Negro	0	0	0
Azul	0	0	255
Verde	0	255	0
Cián	0	255	255
Rojo	255	0	0
Magenta	255	0	255
Amarillo	255	255	0
Blanco	255	255	255

El siguiente procedimiento nos muestra las 60 variantes de los colores de la paleta de colores (Colores del Tema).

Quedaría más, menos así:

Sub TemaColores()

  Dim r As Long, c As Long

  For r = 1 To 6

    For c = 1 To 10

        With Cells(r, c).Interior

        .ThemeColor = c

        Select Case c

            Case 1

            Select Case r

                Case 1: .TintAndShade = 0

                Case 2: .TintAndShade = -0.05

                Case 3: .TintAndShade = -0.15

                Case 4: .TintAndShade = -0.25

                Case 5: .TintAndShade = -0.35

                Case 6: .TintAndShade = -0.5

            End Select

        Case 2

            Select Case r

                Case 1: .TintAndShade = 0

                Case 2: .TintAndShade = 0.5

                Case 3: .TintAndShade = 0.35

                Case 4: .TintAndShade = 0.25

                Case 5: .TintAndShade = 0.15

                Case 6: .TintAndShade = 0.05

            End Select

        Case 3

            Select Case r

                Case 1: .TintAndShade = 0

                Case 2: .TintAndShade = -0.1

                Case 3: .TintAndShade = -0.25

                Case 4: .TintAndShade = -0.5

                Case 5: .TintAndShade = -0.75

                Case 6: .TintAndShade = -0.9

            End Select

        Case Else

            Select Case r

                Case 1: .TintAndShade = 0

                Case 2: .TintAndShade = 0.8

                Case 3: .TintAndShade = 0.6

                Case 4: .TintAndShade = 0.4

                Case 5: .TintAndShade = -0.25

                Case 6: .TintAndShade = -0.5

            End Select

        End Select

        Cells(r, c) = .TintAndShade

        End With

    Next c

  Next r

End Sub

Cells(x,x).Interior.TintAndShade

Devuelve o establece un valor que aclara u oscurece un color se escribir un número comprendido entre -1 (más oscuro) y 1 (más claro) para la propiedad TintAndShade; el cero (0) corresponde al valor neutro.