Uso de la Función InputBox en VBA

Los programas desarrollados en VBA pueden interactuar con el usuario a través de los Userforms y los cuadros de dialogo, los cuales no son más que mensajes emergentes con información de interés. Los cuadros de diálogo pueden ser de dos tipos: los informativos conocidos como Message Box (MsgBox) y los cuadros de ingreso de datos, mejor conocidos como InputBox, de los que hablaremos en este tutorial.

Cuadros de ingreso de datos o InputBox

Son ventanas emergentes de mensajes usadas para que el programa solicite el ingreso de alguna información de interés necesaria para continuar con el flujo de trabajo de tu macro.

La información ingresada en los InputBox es normalmente asignada a una variable, así que la estructura básica para mostrar un InputBox es:

Variable = InputBox(Mensaje)

Con el ejemplo mostrado a continuación, el valor introducido en el InputBox será almacenado en la variable “strName”

Sub test()
strName = InputBox("Please enter your Name")
End Sub

El mensaje que aparece en el InputBox puede ser de tipo String (texto), numérico o Booleano (puedes visitar nuestro tutorial de variables para más información), pero VBA lo convertirá automáticamente en un texto (String). Su longitud máxima es de 255 caracteres. Si tu mensaje excede esa longitud, será automáticamente truncado por VBA a los 255 caracteres.

En algunas ocasiones será conveniente almacenar el mensaje que quieres mostrar en una variable para su uso posterior, similar al procedimiento mostrado en el siguiente ejemplo.

Sub test()
If Gender = "Female" Then
  Message = "Hello Mrs."
Else
  Message = "Hello Mr."
End If
strName = InputBox(Message & ", please input your Name")
End Sub

Así, el mensaje que se mostrará en el InputBox podría depender del resultado de algún procedimiento realizado por tu macro en VBA.

Argumentos opcionales de los InputBox

Los InputBox cuentan con parámetros que permiten mejorar la experiencia del usuario y el programador. Para ello te mostramos como utilizar algunos de los argumentos opcionales y sus posibles estructuras de código en VBA.

Los argumentos disponibles son:

  • Arg1: representa el mensaje que va a ser mostrado en el cuadro de diálogo. Es el único argumento obligatorio. A menos que se indique lo contrario, los valores asignados a este argumento serán convertidos automáticamente en texto para ser mostrado en el InputBox: un número o Booleano será interpretado por VBA como un texto.
  • Arg2: es un argumento opcional que corresponde al encabezado del InputBox, normalmente una variable de tipo String. Este argumento puede ser también de tipo numérico o booleano, pero será interpretado por VBA como un texto.
  • Arg3: es un argumento opcional que indica el texto por defecto que aparecerá en el campo de escritura del InputBox. El argumento puede ser una variable capaz de ser interpretada por VBA como un texto.

En el ejemplo de la imagen, el argumento 3 tiene el valor de “Your name here”

  • Arg4 y Arg5: son argumentos opcionales en los que se especifica la posición del InputBox (en puntos) dentro de la pantalla, determinada desde la esquina superior izquierda de la pantalla. Si decides establecer la ubicación de tu InputBox, debes declarar estos dos argumentos en conjunto; en caso de que declares sólo uno, VBA no tomará la información y tu InputBox tendrá su posición por defecto.
  • Arg6: este argumento se usa para especificar el tipo de dato de entrada del InputBox. Por ejemplo, si deseas almacenar una variable de tipo Booleana deberás asignarle a este argumento el valor 4. En la siguiente tabla te describimos la lista completa de opciones para este argumento.
Valor Descripción
0 Fórmula
1 Número
2 Texto (Selección por defecto)
4 Booleano
8 Rango
16 Error
64 Arreglo

Especificación de los argumentos opcionales en el InputBox

Los argumentos pueden ser especificados usando una de estas dos estructuras:

Método 1:

Variable = InputBox (Arg1, Arg2, Arg3, , , Arg6)

Método 2:

Variable = Application.InputBox( _
prompt:= Arg1, Title:= Arg2, _
Default:=Arg3, _
Left:= Arg4, Top:= Arg5, _
Type:= Arg6)

Te recomendamos usar el método 2 por tener algunas ventajas con respecto al método 1. Entre ellas:

  • Permite tener una mejor comprensión del código ya que se indica de manera explícita el nombre del argumento
  • Es una estructura más flexible para ti como programador, pues te permite alterar el orden de los argumentos dentro de la estructura. Por ejemplo, podrías escribir el argumento Type:= Arg6 en la primera posición, antes que prompt:= Arg1.
  • Solo es necesario especificar los argumentos que se quieren usar. Así evitas tener que dejar espacios en blanco separados por comas, como en el caso del método 1.

Ejemplos de uso del inputbox en VBA

InputBox con valor por defecto y encabezado

Método 1:

Sub test()
  Message = "Hello Mrs., please enter your Name"
  Header = "Hello"
  Default_Message = "Your name here"
  strName = InputBox(Message, Header, Default_Message)
End Sub

Método 2:

Sub test()
  Message = "Hello Mrs., please enter your Name"
  Header = "Hello"
  Default_Message = "Your name here"
  strName = Application.InputBox( _
  prompt:=Message, Title:=Header, _
  Default:=Default_Message)
End Sub

InputBox con valor por defecto, encabezado y control de ubicación en la pantalla

Este ejemplo te muestra como ubicar el InputBox en la esquina superior izquierda de la pantalla.

Método 1:

Sub test()
Message = "Hello Mrs., please enter your Name"
Header = "Hello"
Default_Message = "Your name here"
strName = InputBox(Message, Header, Default_Message, 0, 0)
End Sub

InputBox con valor por defecto, encabezado y entrada numérica

Cuando uses este tipo de InputBox, si la información ingresada no es un valor numérico se mostrará un mensaje que indicará un error en el tipo de entrada, pero no detendrá tu macro, sino que volverá a mostrar el InputBox solicitando que introduzcas  de nuevo la información.

Método 2:

Sub test()
Message = "Hello Mrs., please enter your ID number"
Header = "Hello"
Default_Message = "Your Id Number here"
IDNum = Application.InputBox( _
prompt:=Message, Title:=Header, _
Default:=Default_Message, _
Type:=1)
End Sub

InputBox con valor por defecto, encabezado y entrada de texto

La entrada de datos como texto es la opción por defecto de VBA, así que puedes utilizar esta opción o sencillamente omitir la instrucción “Type” en el código.

Debes tener en cuenta que con esta modalidad de InputBox, si ingresas un número, por ejemplo 10, la macro lo reconocerá como un texto dicho número, es decir “10”; Esto significa que no podrás hacer operaciones matemáticas con la información introducida si el tipo de variable seleccionada en el InputBox es un texto.

Método 2:

Sub test()
Message = "Hello Mrs., please enter your ID number"
Header = "Hello"
Default_Message = "Your Id Number here"
IDNum = Application.InputBox( _
prompt:=Message, Title:=Header, _
Default:=Default_Message, _
Type:=2)
End Sub

InputBox con valor por defecto, encabezado y entrada lógica

En este tipo de InputBox, si la información ingresada no es un valor lógico (verdadero o falso) se mostrará un mensaje de error en el tipo de entrada, pero no detendrá tu macro, sino que volverá a mostrar el InputBox solicitando que introduzcas  de nuevo la información.

Método 2:

Sub test()
Message = "Hello Mrs., please input choice"
Header = "Hello"
Default_Message = "Your choice (True or False)"
Answer = Application.InputBox( _
prompt:=Message, Title:=Header, _
Default:=Default_Message, _
Type:=4)
End Sub

Otro detalle de este tipo de InputBox es que la entrada lógica debe ser escrita en el idioma que tengas configurado tu Office, por ejemplo:

  • Si el idioma es español, entonces tus entradas válidas serán “Verdadero” o “Falso”.
  • Si el idioma es Inglés, entonces tus entradas válidas serán “True” o “False”

InputBox con entrada de rangos

Este tipo de InputBox es muy útil cuando deseas hacer la selección de un rango de celdas como información de entrada para tu macro. Cuando se despliegue este InputBox, Excel te permitirá seleccionar las celdas que desees y automáticamente escribirá en el cuadro la información del rango como naturalmente se hace en Excel: InicioDelRango:FinDelRango

Si la información ingresada no proviene de la selección de celdas o no tiene la estructura válida de un rango, se mostrará un mensaje de error en el tipo de entrada, pero no detendrá tu macro, sino que volverá a mostrar el InputBox solicitando que introduzcas  de nuevo la información.

Una particularidad de este tipo de InputBox es que, por referirse a un grupo de celdas, la información del cuadro no se asigna a una variable sino a un objeto, por ello es necesario emplear la declaración “Set” tal como lo observas en el ejemplo.

Método 2:

Sub test()
Message = "Please select your data range"
Header = "Range selection"
Default_Message = "Your range here"
Set SelRange = Application.InputBox( _
prompt:=Message, Title:=Header, _
Default:=Default_Message, _
Type:=8)
End Sub

Ahora bien, si deseas obtener información particular del rango seleccionado en el InputBox, puedes utilizar estos comandos que aquí te explicamos:

Información del rango Estructura Ejemplo
Ubicación Variable = ObjetoInputBox.Address Var = SelRange.Address
Primera Fila Variable = ObjetoInputBox.Row Rgn_Row = SelRange.Row
Primera columna Variable = ObjetoInputBox.Column Rgn_Col = SelRange.Column
Cantidad de celdas Variable = ObjetoInputBox.CurrentRegion.Count Rgn_Count = SelRange.CurrentRegion.Count
Cantidad de filas Variable = ObjetoInputBox.Rows.Count Rgn_RowCount = SelRange.Rows.Count
Cantidad de columnas Variable = ObjetoInputBox.Columns.Count Rgn_ColCount = SelRange.Columns.Count

Con estas herramientas que has aprendido podrás ampliar las posibilidades de tus programas en VBA, así como mejorar tu experiencia y la del usuario. No dejes del practicar estos trucos y veras la diferencia.

Tienes alguna duda?…. Contactanos

Uso de la función Msgbox en VBA

La comunicación con tu computador es de gran importancia para facilitar tu experiencia de uso de algún programa o aplicación. Al igual que los de los Userforms, los cuadros de diálogo son una de las opciones más usadas en la interacción con el usuario , ya que por medio de estos el programa hacer saber, por ejemplo, si existe la necesidad de introducir algún dato, o simplemente que conozcas que los resultados de algún procedimiento fueron satisfactorios o erróneos.

Los cuadros de dialogo en VBA pueden ser de dos tipos: los de ingreso de datos, también conocidos como InputBox, e informativos, conocidos como Message Box (MsgBox), el cual abordaremos en este tutorial.

Sintaxis básica del Msgbox en VBA

La instrucción en VBA para mostrar un MsgBox básico sigue esta estructura:

MsgBox Mensaje

Por ejemplo:

Sub MsgboxBasico()
  MsgBox "Analysis completed"
End Sub

El mensaje que aparece en el cuadro de dialogo puede tener una longitud máxima de 256 caracteres. Si tu mensaje excede esa longitud, será automáticamente truncado por VBA a los 256 caracteres.

En algunas ocasiones será conveniente almacenar el mensaje que quieres mostrar en una variable, como te mostramos en el siguiente ejemplo:

Sub MsgboxBasico() 
  Dim Message As String
  Message = "Analysis completed"
  MsgBox Message
End Sub

Argumentos opcionales de los Msgbox

Los MsgBox tienen algunas utilidades que permiten potenciar su funcionamiento y mejorar la experiencia del usuario del programa. Para ello debemos utilizar algunos argumentos opcionales en la estructura del MsgBox, los cuales describimos a continuación:

MsgBox Arg1, Arg2, Arg3

Donde:

Arg1: representa el mensaje que va a ser mostrado en el cuadro de diálogo. Es el único argumento obligatorio.

Arg2: es un argumento opcional. Es el más rico de información ya que controla tanto la cantidad y tipo de botones como los iconos ilustrativos que aparecerían en el cuadro de dialogo. A continuación te mostramos alguno de los comandos más utilizados para este argumento:

Constante Valor Descripción
vbOKOnly 0 Sólo muestra el botón “OK”
vbOKCancel 1 Sólo muestra el botón “Cancelar”
vbAbortRetryIgnore 2 Muestra los botones de Abortar e ignorar
vbYesNoCancel 3 Muestra los botones “Si”,  “No” y “Cancelar”
vbYesNo 4 Muestra los botones “Si” y “No”
vbRetryCancel 5 Muestra los botones “Reintentar” y “Cancelar”
vbCritical 16 Icono de error o falla crítica
vbQuestion 32 Icono de interrogación
vbExclamation 48 Icono de exclamación
vbInformation 64 Icono de información
vbMsgBoxRight 524288 Alineación del texto a la derecha
* El texto de los botones aparecerá en el idioma del sistema operativo del computador

Un aspecto particular de este argumento es que puedes hacer una combinación de botones e iconos. Para ello debes  colocar el signo “+” entre los valores o constantes del “Arg2”.

Por ejemplo, si quisiéramos que nuestro MsgBox tuviese los botones “Si” y “No” y a la vez mostrara el icono de interrogación, podríamos hacerlo de las siguientes maneras ya que son equivalentes si empleamos los valores o las constantes:

MsgBox “Message”, 4 + 32, “Header” ‘—> Usando valores

MsgBox “Message”, vbYesNo + vbQuestion, “Header” ‘—> Usando constantes

Arg3: es un argumento opcional que corresponde al encabezado del cuadro de dialogo. Podría manejarse igual que el cuerpo del mensaje a través de una variable que cambiaría según el resultado de procedimientos de tu macro. Si deseas incluir un encabezado sin alterar los valores por defecto de “Arg2” deberás dejar un espacio separado por comas en la linea de instrucción del código, de la siguiente forma:

MsgBox “Message”, , “Header”

Valores de respuesta de los botones en los Msgbox

Cuando empleamos botones diferentes al “Aceptar”, tendríamos la opción de almacenar la respuesta del usuario en una variable y con ello poder tomar algún tipo de decisión. Normalmente estas opciones están asociada a condicionales.

Para que puedas hacer alguna operación con la respuesta del Msgbox, debes modificar ligeramente la estructura básica, como te mostramos a continuación:

VariableRespuesta = MsgBox (Arg1, Arg2, Arg3)

La variable de respuesta tomara valores en función a la elección del usuario. En el siguiente cuadro observarás ejemplos para cada caso.

Descripción del Botón Respuesta (Constante) Respuesta (Valor) Ejemplo
 “OK” vbOK 1 answer = MsgBox(Message, vbOKOnly, Header) If answer = vbOK Then     Continúa el código…
 “Cancelar” vbCancel 2 answer = MsgBox(Message, vbOKCancel, Header) If answer = 2 Then     Continúa el código…
Abortar vbAbort 3 answer = MsgBox(Message, vbAbortRetryIgnore, Header) If answer = vbAbort Then     Continúa el código…
Reintentar vbRetry 4 answer = MsgBox(Message, vbAbortRetryIgnore, Header) If answer = vbRetry Then     Continúa el código…
Ignorar vbIgnore 5 answer = MsgBox(Message, vbAbortRetryIgnore, Header) If answer = 5 Then     Continúa el código…
Si vbYes 6 answer = MsgBox(Message, vbYesNoCancel, Header) If answer = vbYes Then     Continúa el código…
No vbNo 7 answer = MsgBox(Message, vbYesNoCancel, Header) If answer = vbNo Then     Continúa el código…

Ejemplos de uso de la función Msgbox de VBA

Como vimos anteriormente, cuando realices la programación de los botones, iconos y textos de los Msgbox, puedes escoger entre el uso de valores o constantes en los argumentos de la función. A continuación te presentamos varios casos de ejemplo para que puedas adaptar los Msgbox a tus necesidades.

Ejemplo de Msgbox con ícono crítico y botones de abortar, reintentar e ignorar

Usando valores:

Sub MsgboxPrueba() 
  Message = "Mensaje de Error"
  Header = "Error"
  Answer=MsgBox (Message, 2 + 16, Header)
  If Answer = 3 Then
   Exit Sub
  End If
End Sub

Usando constantes:

Sub MsgboxPrueba() 
  Message = "Mensaje de Error"
  Header = "Error"
  Answer=MsgBox (Message, vbAbortRetryIgnore + vbCritical, Header)
  If Answer = vbAbort Then
    Exit Sub
  End If
End Sub

Ejemplo de Msgbox con ícono de interrogación y botones de Sí y No

Usando valores:

Sub MsgboxPrueba() 
  Message = "Mensaje de pregunta"
  Header = "Pregunta"
  Answer=MsgBox (Message, 4 + 32, Header)
  If Answer = 6 Then
    MsgBox "Yes"
  End If
End Sub

Usando constantes:

Sub MsgboxPrueba() 
  Message = "Mensaje de pregunta"
  Header = "Pregunta"
  Answer=MsgBox (Message, vbYesNo + vbQuestion, Header)
  If Answer = vbYes Then
    MsgBox "Yes"
  End If
End Sub

Ejemplo de Msgbox con ícono de información y texto alineado a la derecha

Usando valores:

Sub MsgboxPrueba() 
  Message = "Mensaje Alineado a la derecha"
  Header = "Información"
  MsgBox Message, 48 + 524288, Header
End Sub

Usando constantes:

Sub MsgboxPrueba() 
  Message = "Mensaje Alineado a la derecha"
  Header = "Información"
  MsgBox Message, vbExclamation + vbMsgBoxRight, Header
End Sub

Recuerda que puedes combinar los tres tipos de argumentos en un mismo MsgBox según lo requieras!

Con estas herramientas que has aprendido ahora mejoraras la experiencia de los usuarios de tus macros. No dejes del practicar estos trucos y veras la diferencia.

 

Tienes alguna duda?…. Contactanos

Cómo crear un archivo PDF usando VBA?

No son pocas las ocasiones en las que necesitamos exportar el contenido de una o varias hojas de trabajo de nuestro fichero Excel hacia un archivo pdf. En este apartado te enseñaremos como lo puedes hacer de una forma rápida y sencilla, usando la función ExportAsFixedFormat.

Código VBA de ejemplo para exportar a pdf la hoja activa de Excel

A continuación te mostramos un código sencillo para exportar la hoja (worksheet) activa a pdf, usando la función ExportAsFixedFormat. Si sustituyes la palabra ActiveSheet por el nombre de tu hoja de preferencia este código también te funcionará:

Sub ExportAsPDF ()
 
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=”Test”, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2
 
End Sub
 

 

Como puedes observar, la función ExportAsFixedFormat posee varios atributos, la mayoría opcionales, los cuales te describimos en la siguiente tabla:

Nombre Atributo Requerido/Opcional Descripcion
Type Requerido Puede ser xlTypePDF o xlTypeXPS.
Filename Opcional Nombre del archivo. Puedes indicar el Full Path. Si no lo indicas el archivo se guardará en el directorio actual de trabajo.
OpenAfterPublish Opcional Puede tomar valores True o False. Si el valor es True, el pdf se abrirá inmediatamente después de que sea creado.
IncludeDocProperties Opcional Puede tomar valores True o False. Si el valor es True, el pdf tomará las propiedades del archivo Excel.
IgnorePrintAreas Opcional Puede tomar valores True o False. Si el valor es True, el pdf ignorará las áreas de impresión configuradas en Excel, de lo contrario las tomará.
Quality Opcional Se refiere a la calidad de la exportación. Puede ser xlQualityStandard o xlQualityMinimum.
From Opcional Valor entero que indica a partir de cual página se exportará el documento. El valor por defecto es 1.
To Opcional Valor entero que indica la última página a tomar en cuenta para la exportación. Si este valor es omitido, Excel considerará exportar hasta la última página.

Código VBA de ejemplo para exportar a pdf todas las hojas del libro de trabajo

El siguiente código permite exportar todas las hojas de un fichero Excel a pdf. Nótese que en este caso el archivo pdf será guardado en la misma ruta donde tenemos nuestra macro.

Sub ExportActiveWorkbookAsPDF()
 
Dim strFileName As String
strFileName = ThisWorkbook.Path & "\Test.pdf"  'Nombre y ruta del archivo exportado
 
'Exporta el archivo como  PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:= strFileName
End Sub

Código VBA de ejemplo para exportar a pdf un Rango seleccionado

En el caso de que necesitemos exportar solo un rango específico dentro de una worksheet, podemos usar el código a continuación, en donde el rango a exportar es almacenado en la variable strRange:

Sub ExportRangeAsPDF()
 
Dim strFileName As String
Dim stRange as Range
strFileName = ThisWorkbook.Path & "\Test.pdf"  'Nombre y ruta del archive exportado
 
Set strRange = Range("A1:C20") 'Rango a Exportar
 
'Exporta el rango como PDF
strRange.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:= strFileName
 
End Sub

Código VBA de ejemplo para exportar a pdf una gráfica o forma seleccionada

Si lo que necesitas es exportar tus gráficos o shapes a pdf entonces el código que aquí te mostramos te funcionará. El gráfico de interés es en primer lugar almacenado en la variable cht y luego exportado a pdf.

Sub SaveChartAsPDF()
 
Dim strFileName As String
Dim cht As Chart
 
strFileName = ThisWorkbook.Path & "\Test.pdf"  'Nombre y ruta del archivo exportado
 
Set cht = Sheets("Hoja1").ChartObjects("Chart 1").Chart
 
'Exporta la selección a un  PDF
cht.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:= strFileName
End Sub

Código VBA de ejemplo para exportar a pdf varias hojas seleccionadas

En el caso que desees exportar solo algunas hojas seleccionadas de un fichero Excel, te recomendamos en un primer paso crear un Array con las hojas de interés, para poder seleccionarlas y posteriormente usar la instrucción ExportAsFixedFormat, tal y como te mostramos en el siguiente ejemplo:

Sub SelectSheetsAndSaveAsPDF()
  
Dim strFileName As String
Dim sheetArray As Variant
 
strFileName = ThisWorkbook.Path & "\Test.pdf"  'Nombre y ruta del archivo exportado
sheetArray = Array("Hoja1", "Hoja2")
 
'Selecciona varias hojas para exportarlas a PDF
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:= strFileName
End Sub

Quieres saber más? Contáctanos…

 

Uso de Select Case en VBA

La selección de casos “Select-Case” nos permite tomar una decisión en función al valor de una variable. Como te imaginarás es una alternativa al uso del condicional If Else, y dependiendo de la situación puede ser más adecuado su uso.

La estructura Select Case se encarga de hacer que el rumbo de la información en nuestra macro se dirija según el valor de la variable selectora. La selección del caso tendría como resultado tomar acciones, tal y como se muestra en el siguiente esquema.

La variable selectora puede ser de cualquier tipo. Las diferencia más importantes del Select Case con respecto al condicional If Else es que la evaluación se hace sobre el resultado de una variable, por lo cual no podríamos utilizar los operadores lógicos (And / Or) en la evaluación.

Estructura del Select Case en VBA

La estructura básica del Select Case es como se muestra a continuación:

Select Case Variable
    Case Resultado que tomaría la variable
        Acciones
End Select

Ejemplo de Select Case en VBA

Si deseáramos aplicarle un determinado color a las celdas según el resultado de la evaluación “Approved” o “Disapproved”, podríamos usar la siguiente rutina:

i = 1
Do Until Cells(i, 1) = ""
  Select Case Cells(i, 1)
    Case "Approved"
      Cells(i, 1).Interior.Color = RGB(0, 255, 0)
    Case "Disapproved"
      Cells(i, 1).Interior.Color = RGB(255, 0, 0)
  End Select
  i = i + 1
Loop

Con la rutina usada las celdas que contenga la palabra “Approved” se pintaran de color verde, mientras que las que contengan la palabra “Disapproved” tendrán color rojo.

Con la práctica, irás adquiriendo destrezas que te permitirán hacer un código de programación más compacto y a prueba de errores.

¿Deseas conocer más?…. Contáctanos

Web Scraping con VBA

Tienes que añadir un widget, una fila o un diseño preconstruido antes de que puedas ver algo aquí. 🙂

 

El Web Scraping o Data Scraping es una técnica en los lenguajes de programación usada para obtener información de interés de una página web de forma automática. El VBA nos ofrece también  la posibilidad de navegar, leer e ingresar datos en una página o aplicación web de forma automatizada, lo cual permitiría completar en pocas horas un trabajo manual de extracción de información que podía demorar varios días. La herramienta principal del VBA para navegar en la web es el Internet Explorer, aunque recientemente se han popularizado algunas aplicaciones externas que permiten controlar desde VBA la experiencia en la web con navegadores como el Chrome y Mozila Firefox. Sin embargo, en este tutorial hablaremos sobre cómo realizar el web scraping con VBA e Internet Explorer a través de la manipulación de los elementos HTML de la página web.

1. Acciones llevadas a cabo durante la extracción automática de datos Web

La interacción con  la web a través del VBA se consigue principalmente con 3 acciones que se ejecutan sobre el código HTML de la página o aplicación web:

1.1. Lectura de la página:

Una vez que nos disponemos a realizar una extracción automática de datos web, en primer lugar debemos definir cuáles son los datos que necesitamos extraer y cuáles son los botones, opciones o campos de entrada que debemos manipular para obtener estos datos. Los datos y botones están almacenados dentro de unas etiquetas o “Tags” dentro del código HTML de la página web. Estas etiquetas deben ser identificada empleando una técnica que te explicaremos más adelante.

1.2. Ingreso de información:

En similitud con la experiencia del usuario al navegar en la web, en la mayoría de las ocasiones será necesario que escribamos textos en los campos de entrada de datos de la página o aplicación web. Estos campos de entrada ya deben estar previamente identificados durante el proceso de lectura de los elementos HTML de la página.

1.3. “Click”:

Esta actividad simula el acceso a vínculos o botones de las páginas web a través del Clic del mouse.

A lo largo del tutorial iras aprendiendo los códigos de programación para ejecutar estas acciones.

Así como cuando al navegar en la web, haces una revisión de la página para saber dónde está la información que necesitas, también el VBA debe hacerlo, por lo tanto debemos ser bien explícitos y sistemáticos en nuestra navegación automática para asegurarnos de estar obteniendo la información adecuada. ¡Esto es clave para lograr un exitoso Web Scraping!.

2. Ejemplo de  Web Scraping con VBA

Ok, luego de tantas explicaciones es momento de la acción. Hagamos el ejercicio de buscar y escribir en una celda la temperatura de una ciudad cada vez que ejecutemos una macro.

Existen varias páginas web asociadas con el clima y puedes seleccionar cualquiera de ellas; nosotros escogimos para este ejercicio la página https://weather.com/es-VE/ para extraer la temperatura de la ciudad Maracaibo, Venezuela.

Lo primero que debemos hacer es declarar en VBA el explorador web a usar y colocar la url de la página web tu interés, siguiendo la estructura que te mostramos a continuación:

Set Variable Asignada Al Explorador = CreateObject(“InternetExplorer.Application”)
With Variable Asignada Al Explorador  
.MenuBar = 0 o 1(*)
.Toolbar = 0 o 1(*)  
.StatusBar = 0 o 1(*)
.Visible = 0 o 1(*) (**)
End With
(*)Opcional
(**)Si deseas que explorador se ejecute como no visible selecciona 0, de lo contrario selecciona 1.

Ejemplo de llamado de url mediante VBA:

'---> Declara explorador <---
Set ie = CreateObject("InternetExplorer.Application")
With ie
 .MenuBar = 1
 .Toolbar = 1
 .StatusBar = 1
 .navigate “https://weather.com/es-VE/tiempo/hoy/l/fab4b8bb005020eb0f895a19b541c80a8e00641f7f550cf15c74273446aea5b8”
 .Visible = 0
End With

Cuando navegas normalmente en la web y escribes una url en la barra de navegación, usualmente debes esperar unos segundos para que cargue completamente la página; el VBA también debe asegurarse de que la página este cargada previo a ejecutar cualquier acción, por eso no debes olvidar incluir el procedimiento que te mostramos a continuación cada vez que se carga o actualiza una página:

Do While Variable Asignada Al Explorador.Busy Or Variable Asignada Al Explorador.readyState <> 4
DoEvents
Loop
Application.Wait Now + Tiempo

Ejemplo de inclusión de tiempo de espera al cargar una página web en VBA:

Do While ie.Busy Or ie.readyState <> 4
 DoEvents
Loop
Application.Wait Now + #12:00:01 AM# 'Espera 1 segundo

Al final de este paso a paso podrás ver todo el código completo para que puedas usarlo como base en tus propios proyectos de Automatic Web Scraping.

3. ¿Cómo leer la página web?

Ubicar correctamente los campos de la página web con los que deseamos interactuar es el punto clave en el Web Scraping, ya que si hacemos un clic o ingresamos la información en el lugar equivocado, estaríamos corriendo el riesgo de extraer información errada o simplemente obtendríamos un error en nuestro programa.

Una página web es la interfaz de la información contenida en un servidor; esta vista es lo que se conoce como “Front End”. Es aquí donde aparece el lenguaje HTML (en combinación con otros lenguajes como CCS y javascript) que agrupa y clasifica la información en diferentes clases, identificadores o etiquetas. Para encontrar cualquier elemento de la página web debemos conocer cuál es la denominación de éste dentro del código.

La mejor forma de ubicar la denominación de los elementos en la página web es indagando en su código fuente mediante la ventana de “Herramientas de desarrollo” del Internet Explorer, que se muestra a través del menú de configuración. Para ello puedes abrir la página web de tu interés y presionar F12, luego dirígete a la pestaña “Explorador DOM”.

En nuestro ejemplo, el elemento de interés dentro de la página web es el que contiene la información de la temperatura. En el código HTML podríamos tratar de identificar manualmente el Tag de este elemento, pero eso podría llevarnos mucho tiempo, es por ello que la mejor forma de identificar el elemento de interés es emplear el seleccionador de elementos de las herramientas de desarrolladores (flecha ubicada en la parte superior izquierda del explorador DOM). Luego de presionar esa flecha o puntero solo tendrás que seleccionar tu variable de interés directamente en la página web.

Verás que en la ventana del Explorador DOM se resaltará la línea de código con el identificador del elemento que necesitamos.

En este ejemplo los elementos de interés están almacenados en una Clase (“Class”). Cada clase tiene un nombre, así que sólo restaría acceder a las clases cuyos nombres se correspondan con los parámetros de ubicación, temperatura y la hora de actualización de la página. Esta información la almacenaremos en unas celdas de la hoja de Excel, siguiendo la estructura que te mostramos a continuación:

With Variable Asignada Al Explorador
Set Variable para Tipo de elemento = Obtener elemento según denominación
For Each Variable de búsqueda In Variable para Tipo de elemento(*)  
Celda de Excel = Texto de Variable de búsqueda
Next
End With
(*)Repetir la estructura de este Loop por cada elemento de interés.

Ejemplo para extraer la información contenida en elementos de una web:

With ie.document
 'Ubicación
 Set elemsLocation = .getElementsByClassName("h4 today_nowcard-location")
 For Each e In elemsLocation
  Ws.Cells(2, 2) = e.innertext
 Next
 'Temperatura
 Set elemsTemp = .getElementsByClassName("today_nowcard-temp")
 For Each e In elemsTemp
  Ws.Cells(3, 2) = e.innertext
 Next
 'Hora de actualización
 Set elemsUpdateTime = .getElementsByClassName("today_nowcard-timestamp")
 For Each e In elemsUpdateTime
  Ws.Cells(4, 2) = e.innertext
 Next
End With

Listo, ya que logramos obtener la información que deseábamos, ahora no debemos olvidar cerrar el explorador, sobre todo si lo hemos colocado en modo oculto. Para ello debes seguir este código:

Variable Asignada Al Explorador.Quit

Ejemplo de código para cerrar el Internet Explorer con VBA:

ie.Quit

4. Ejemplo de código VBA para extraer datos web de forma automática

A continuación de mostramos el ejemplo del código completo para extraer datos de una pagina web a Excel:

Sub WebScrapingExample()
Dim Ws As Worksheet
Dim ie As Object
 
Set Ws = ActiveSheet
Ws.Range(Ws.Cells(2, 2), Ws.Cells(4, 2)).ClearContents
'---------------------------------------------------------------
'---> Declara explorador <---
Set ie = CreateObject("InternetExplorer.Application")
With ie
    .MenuBar = 1
    .Toolbar = 1
    .StatusBar = 1
     '---> Pagina web <---
    .navigate " https://weather.com/es-VE/tiempo/hoy/l/fab4b8bb005020eb0f895a19b541c80a8e00641f7f550cf15c74273446aea5b8"
    .Visible = 1 '---> Seleccione 1 para Visible, 0 para Oculto
End With
 
'--->Esperar hasta que la página este cargada <---
Do While ie.Busy Or ie.readyState <> 4
   DoEvents 'Para no congelar la pantalla
Loop
 
Application.Wait Now + #12:00:01 AM# 'Espera 1 segundo que cargue la página
'---------------------------------------------------------------
'--- Iniciar el Data Scrape ---
With ie.document
    'Ubicación
    Set elemsLocation = .getElementsByClassName("h4 today_nowcard-location")
    For Each e In elemsLocation
        Ws.Cells(2, 2) = e.innertext
    Next
    'Temperatura
    Set elemsTemp = .getElementsByClassName("today_nowcard-temp")
    For Each e In elemsTemp
        Ws.Cells(3, 2) = e.innertext
    Next
    'Hora de actualización
    Set elemsUpdateTime = .getElementsByClassName("today_nowcard-timestamp")
    For Each e In elemsUpdateTime
        Ws.Cells(4, 2) = e.innertext
    Next 
End With
'---------------------------------------------------------------
ie.Quit '---> Cierra Explorador
MsgBox "Done!"
 
End Sub

5. Tipos de identificación de elementos de página web

Aprendimos que para poder interactuar con los elementos HTML dentro de la página web debemos obtener su denominación única para que no haya ambigüedades. En el ejemplo que vimos los elementos estaban identificados por clases.

Es importante que conozcas otros identificadores que existen y la manera de interactuar con ellos, ya que te podrías encontrar con uno o varios de éstos en tu proyecto de extracción automática de datos web. Aquí te listamos algunos:

Denominación Código en página web Para acceder al elemento
Clase Class Explorador.getElementsByClassName(“Nombre de la clase”)
Identificación Id Explorador.getelementbyid(“Nombre identificación“)
Etiqueta Tag Explorador.getElementsByTagName(“Nombre de la etiqueta“)
Atributo Nombre del atributo Explorador.Elemento.getattribute(“Nombre del atributo“)

Para llegar a nuestro elemento de interés en una página web también es válida una combinación de opciones como esta:

With Variable Asignada Al Explorador
Valor=.getElementsByClassName(“Nombre de la clase”).getElementsByTagName(“Nombre de la etiqueta“)
End With

6. Ingreso de datos y Clic en elementos de una página web usando VBA.

La navegación automática en la web con VBA tiene dos formas de interacción con los elementos de la página: el ingreso de información en campos de entrada y a través de los Clics.

6.1. Ingreso de información en los elementos de una página web con VBA.

La estructura se basa en ubicar el elemento y luego escribir en él la información que deseamos ingresar.

Debes tener en cuenta que si escribes información sobre un elemento que no permita el ingreso de datos podrías tener errores en la rutina o una lectura de datos no adecuada. Por ello es que resaltamos como factor clave la ubicación correcta del elemento de interés en la página.

With Variable Asignada Al Explorador
Set Variable para Tipo de elemento = Obtener elemento según denominación
For Each Variable de búsqueda In Variable para Tipo de elemento
Variable de búsqueda = Valor de información ingresada
Next
End With

Ejemplo de escritura en elementos web usando VBA:

With ie.document
 'Buscar ubicacion
 Set SearchLocation = .getElementsByClassName("theme__inputElement__4bZUj  input__inputElement__1GjGE")
 For Each SL In SearchLocation
  SL.Value = “Maracaibo" '---> Ingreso de información
 Next
End With

6.2. Clic sobre los elementos de una página web usando VBA

Este caso es similar al anterior, con la diferencia de que la acción sobre el elemento seleccionado es el equivalente a hacer un clic con el mouse.

With Variable Asignada Al Explorador
Set Variable para Tipo de elemento = Obtener elemento según denominación
For Each Variable de búsqueda In Variable para Tipo de elemento
Variable de búsqueda.Click
Next
End With

Ejemplo de código VBA para hacer clic en un elemento web:

With ie.document
 'Buscar ubicacion
 Set SearchLocationButton = .getElementsByClassName( "styles__item__sCSPm")input__inputElement__1GjGE")
 For Each SLB In SearchLocationButton
  SLB.Click '---> Hace click sobre el elemento
 Next
End With

7. Las API’s y VBA

Así como algunas páginas web tienen restricciones para el Automatic Data Scraping, otras ofrecen un servicio para facilitar el proceso a través de las denominadas Interfaces de programación, mejor conocidas como API, por sus siglas en inglés (Application Programming Interface). Algunas veces las API’s son gratuitas y otras no.

Según el tipo de información que necesites extraer es recomendable en primera instancia realizar una breve búsqueda de una API gratuita y estable (en el tiempo y en diseño), ya que esto te permitirá realizar el Web Scraping con mayor facilidad.

UserForms en VBA

Un Formulario de Usuario o UserForm es una interfaz que permite ordenar y mostrar de una forma amigable la información que se requiera según sea la necesidad del usuario. De esta manera se evita que el usuario escriba directamente sobre las hojas de trabajo o bases de datos, protegiendo de esta manera la información a la vez que se facilita la entrada de los datos.

Los UserForms son una excelente opción si tus aplicaciones están programadas en Excel/VBA, ya que pueden adecuarse a las necesidades de los usuarios y ser 100% personalizables.

En este tutorial desarrollaremos una interfaz de usuario sencilla, que genera un mensaje de saludo según la información introducida.

1. Cómo crear un Userform en VBA?

Primero busca en la barra de herramientas en el editor de Visual Basic (VBE) la opción “Insertar” luego selecciona  “UserForm”. Se creará el formulario por defecto en el espacio de trabajo.

En la parte inferior derecha del VBE aparecerá el cuadro de propiedades del formulario, es aquí donde podremos controlar todo el Userform y sus elementos internos.

2. Elementos internos del Userform – Botones, TextBox, ComboBox y otros

Son los elementos que permiten la interacción entre el Userform y usuario. Para añadirlos, una vez que has generado el formulario debes mostrar el cuadro de herramientas a través de la opción “Ver” del VBE.

Para agregar un elemento sólo debes seleccionarlo desde el cuadro de herramientas y arrastrarlo al Userform. Con el mouse podrás ubicarlo y ajustarle el tamaño según necesites; verás más adelante que también puedes hacerlo a través de las propiedades de cada elemento.

A continuación te mostramos una lista de los elementos más utilizados en la creación de UserForms. Todos los elementos sin excepción pueden ser configurados o actualizados vía código de programación.

2.1. Imagen (Image)

Es un campo no modificable por el usuario final, usado normalmente para insertar una imagen como la de un logotipo.

2.2. Etiqueta (Label)

Es un campo no modificable por el usuario final, usado normalmente para indicar o explicar la función de otro elemento dentro del Userform.

2.3. Cuadro de texto (TextBox)

Es un elemento modificable por el usuario final, usado para dar ingreso de datos. Todo valor que se ingrese vía cuadro de texto será reconocido por VBA como String. Si se deseas que sea reconocido como otro tipo (número, fecha u otro) deberás hacer la conversión del tipo de variable. Puedes revisar el tutorial de variables para más información.

2.4. Cuadro Combinado (ComboBox)

Este elemento es también conocido como drop-down. Su función es albergar una lista de opciones para que el usuario escoja sólo una de ellas.

2.5. Casilla (CheckBox)

Es un elemento booleano (toma valores True o False) que puede ser modificado por el usuario final.

2.6. Botón de opción (OptionButton)

Similar al CheckBox, es otro elemento booleano (toma valores True o False) que puede ser modificado por el usuario.

2.7. Botón de comando (CommandButton)

Es un elemento sobre el cual se asignan acciones que se quieren realizar en un programa, como ejecutar la macro, cerrar el Userform, abrir un archivo, entre otras.

3. Propiedades del Userform y de sus elementos

Los Userforms y sus elementos poseen propiedades que pueden ser modificadas directamente a través del cuadro de propiedades en el VBA. Algunas de estas propiedades son comunes a todos los elementos, como las relacionadas a la apariencia (ancho, largo, ubicación); otras propiedades son particulares para cada elemento. A continuación te mostramos una lista de las propiedades según el tipo de elemento.

Propiedad Descripción Aplica a:
(Name) Nombre del elemento. Es usado para identificar el elemento dentro del código VBA. Todos los elementos
Back Color Color de fondo Todos los elementos
Caption Texto de titulo Userform, Label, CheckBox, OptionButton, CommandButton
Enabled Activación Todos los elementos
Font Fuente del texto Userform, Label, TextBox, ComboBox, CheckBox, OptionButton, CommandButton
Height Valor de altura de elemento Todos los elementos
Left Ubicación horizontal Todos los elementos
Picture Imagen Userform, Image, Label, CheckBox, OptionButton, CommandButton
Picture-SizeMode Ajuste de tamaño de imagen Userform, Image, CommandButton*
TextAlign Alineación de texto Label, TextBox, ComboBox, Check Box, OptionButton
Top Ubicación vertical Todos los elementos
Visible Visibilidad Image, Label, TextBox, ComboBox, CheckBox, OptionButton, CommandButton
With Ancho Todos los elementos
Word-Wrap Ajuste de línea de texto Label, TextBox, Check Box, OptionButton, CommandButton

(*) La propiedad equivalente para el CommandButton se llama  Picture Position

4. Scripts en los Userforms

Estas son las instrucciones/acciones que se ejecutan cuando ocurra un evento sobre un elemento del Userform. Estos eventos pueden ser un click, el ingreso de datos en un TextBox, el cambio de selección en un ComboBox, entre otros.

Los script pueden ser interdependientes, es decir pueden ejecutarse en un elemento y tener efectos sobre otros elementos; por ejemplo, podemos programar que un TexBox se ha visible o invisible al activar o desactivar un CheckBox.

Te mostraremos algunos scripts típicos para desarrollar una interfaz que nos muestre un mensaje de saludo a partir de la información que ingresemos.

4.1. ComboBox.

Este tipo de elementos requieren que la lista que ellos despliegan sea previamente llenada, lo cual puede hacerse a través de los valores de una hoja de cálculo o indicando directamente los elementos que debe tomar la lista. Las instrucciones para ello serían:

Estructura With Nombre de ComboBox  
.AddItem elemento
End With

Ejemplo:

With ComboBox1
  .AddItem "Sr."
  .AddItem "Sra."
End With

A menos que se desee lo contrario o por requerimiento del código, es buena práctica colocar la instrucción de llenado del ComboBox en el evento de inicialización (Initialize) del UserForm, para que la información esté previamente cargada a la hora de que el usuario desee modificar el ComboBox. El código luciría como se muestra en el siguiente ejemplo:

Private Sub UserForm_Initialize()

With ComboBox1
.AddItem "Sr."
.AddItem "Sra."
End With

End Sub

El ComboBox de nuestro ejemplo tendrá 2 posibles selecciones: Sr. y Sra.

Para escribir un código de programación sobre un evento del Userform principal, basta con hacer doble click en el Userform y elegir el evento sobre el cual desea escribir. En nuestro caso, seleccionamos el evento “Initialize”. Los eventos pueden variar según el tipo de elemento. En la imagen verás la lista para el elemento Userform.

4.2 Checkbox / Option Button

Como te mencionamos anteriormente, este tipo de elementos toma valores True o False al activarlos o desactivarlos, respectivamente. Esta activación / desactivación la puedes aprovechar para generar efectos en otros parámetros o elementos del Userform.

En nuestro ejemplo, cuando el usuario haga click sobre el CheckBox1 para activarlo, el elemento ComboBox1 se habilitará; cuando el  CheckBox1 tenga el valor False (desactivado) el elemento ComboBox1 se deshabilitará, es decir, el usuario no tendrá acceso a los valores de la lista en el ComBobox1.

Estructura Condicional según el valor del elemento  
Nombre de elemento afectado.Enabled = True o False

Ejemplo:

If CheckBox1 = True Then
 ComboBox1.Enabled = True
Else
 ComboBox1.Enabled = False
 ComboBox1 = ""
End If

4.3 CommandButton

Normalmente sobre estos elementos es donde se asignan las rutinas más complejas. En este ejemplo asignaremos las instrucciones de generar le mensaje de saludo, que estará compuesto por la selección realizada en el elemento ComboBox1 y la palabra escrita en el elemento TextBox1.

Ejemplo:

MsgBox "Hola " & ComboBox1 & " " & TextBox1 & "." & vbCrLf & "Feliz Día!"

5. Mostrar y Ocultar Userforms

El llamado de un Userform se hace a través de la instrucción “Show” (mostrar).

Estructura NombreDeUserform.Show

Ejemplo:

UserForm1.Show

Para cerrar el Userform utilizaremos la instrucción “Unload”.

Estructura Unload NombreDeUserform

Ejemplo:

Unload UserForm1

Normalmente esta instrucción la colocamos en un CommandButton que ejecute la “Salida” del programa

6. Resultado

Una vez escritas todas las instrucciones que has aprendido en este tutorial, el resultado que obtendrás será un Userform como el siguiente, el cual muestra un mensaje de bienvenida luego de presionar el botón “Run!”.

Como puedes ver, la generación de interfaz de usuario es una actividad relativamente sencilla. Su complejidad variará dependiendo del funcionamiento de tu programa y de la interacción entre los elementos.

No tenemos dudas de que con lo aprendido en este tutorial podrás armar tu propia interfaz de usuario.

 

 

¿Quieres saber más? Contáctanos

Diccionarios (Dictionaries) en VBA

Al igual que las Colecciones, los diccionarios son un tipo de arreglo que nos permite almacenar todo tipo de variables como números, textos, objetos y combinación de ellas. En este tutorial verás por qué consideramos esta estructura muy útil.

Su forma de trabajo hace que el Dictionary sea muy similar al Collection; sin embargo tienen algunas diferencias en sus estructuras. Aquí te mostramos un resumen con las características de los Diccionarios, Colecciones y Arreglos:

  Arreglos Colecciones Diccionarios
Facilidad de programación X    
Ordenamiento de elementos internos X X  
Edición de elementos internos X   X
Eliminación de elementos intermedios   X X
Buen desempeño en grandes bases de datos   X X
Manejo de información a través de claves (Keys)   X X

En este tutorial te explicaremos las formas más generales de trabajar con los diccionarios para la cantidad de variables que tú decidas. Existen unos pasos que podríamos omitir cuando tu diccionario almacene sólo una variable, pero nuestra intención es enseñarte todo!.

1. Declaración de diccionarios

Microsoft nos provee dos modalidades para la declaración: Early Binding y Late Binding.

La modalidad Early Binding necesita que actives la referencia “Microsoft Scripting Runtime”, lo cual podría no ser del agrado para algunos usuarios finales de nuestras herramientas, debido a que muchos no estarán familiarizados con VBA.

El Late Binding resulta ventajoso en el sentido de que la declaración la harás directamente en el código, sin labores extras para el usuario final, por ello trabajaremos bajo esta modalidad. La única desventaja de esta opción es que perderás la ayuda de escritura que el código muestra en los procedimientos disponibles, sin embargo con la explicación que te daremos, esa ayuda no sería necesaria 🙂

En este tutorial iremos desarrollando un ejemplo de creación de un diccionario que almacene la información de la nómina de una empresa.

 

1.2. Declaración del diccionario

La declaración de nuestro diccionario se hace bajo la siguiente estructura:

Set NombreDeDiccionario = CreateObject(“Scripting.Dictionary”)

Ejemplo de declaración de un diccionario en VBA:

Set Nomina = CreateObject("Scripting.Dictionary")

 

 

 

2. Añadir elementos al Diccionario

En muchas ocasiones necesitaremos añadir elementos al diccionario a partir de la información de una hoja Excel. Al tomar los datos de la hoja de cálculo, debemos definir cuál columna representará nuestro identificador único o Key y cuáles serán las variables agregadas o Ítems que pertenecerán a dicho Key.

Debemos recordar que el Key es un identificador único, así que en este ejemplo el nombre de la persona no sería un buen identificador, ya que en algún momento podría repetirse (en nuestra tabla existen dos personas distintas llamadas Ana). Te sugerimos usar el número de identificación (Columna A en este caso) como Key.

Debemos entonces hacer un recorrido por todas las filas llenas, para lo cual recurrimos a un Loop.

Para evitar errores en nuestra rutina, es conveniente revisar si el Key ha sido previamente utilizado en el diccionario, es decir, debemos verificar si la persona fue previamente registrada. Esto lo podremos hacer siguiendo la estructura:

Dim DeclaraciónVariables As Nombre de módulo de clase
Abrir Loop que barre la columna
Asigna Key  
If (Revisa si el Key ya esté utilizado) Then
  Acción si el Key fue utilizado
Else  (Acción si el Key está libre)  
  Set ObjetoDeClase = New NombreDeModuloDeClase           NombreDeDiccionario.Add Key, ObjetoDeClase(Ítems) o arreglo de items  
End If
ObjetoDeClase.NombreDeItem = CeldaQueContieneEseItem.Value (subItem) 
Cerrar el Loop

2.1. Ejemplo para añadir elementos al Diccionario sin usar módulo de clases

En lugar de un módulo de clases, esta modalidad hace uso de un Array auxiliar para almacenar la información de los ítems. Bajo este concepto, cuando queramos acceder a los elementos del Dictionary debemos hacerlo a través de la posición que cada ítem ocupa en el mismo. En este ejemplo, la primera posición la ocupa el ítem “Person”, la segunda el ítem “Age” y la tercera el elemento “Gen”.

'Declaración de diccionario
Dim key As Variant, listItem() As Variant
Set WorkerList = CreateObject("Scripting.Dictionary")
'-----------------------------------------------------------------------------
'--- Añadir elementos al Diccionario -
i = 2
Do While IsEmpty(ws.Cells(i, 1)) = False '--->Loop through "A" Column
 IDPerson = ws.Cells(i, 1).Value        '--->Key
 '---> Revisa si el key fue añadido al diccionario
 If WorkerList.Exists(IDPerson) = True Then
  MsgBox ("Key " & IDPerson & "is already used")
 Else
 '---> Asigna Items
    ReDim listItem(i - 1, 3)
    listItem(i - 1, 1) = ws.Cells(i, 2).Value '-->Person
    listItem(i - 1, 2) = ws.Cells(i, 3).Value '---> Age
    listItem(i - 1, 3) = ws.Cells(i, 4).Value '---> Gen
    WorkerList.Add IDPerson, listItem
 End If
 i = i + 1
Loop

2.2. Ejemplo para añadir elementos al diccionario con módulo de clases

2.2.1. Creación de módulo de clases

Aunque este paso no es obligatorio, es altamente recomendado para poder tener un mejor tratamiento y manejo de la información en el diccionario. Te recomendamos crear un módulo de clases donde almacenaremos todas las variables que estarán en el diccionario. Para ello haz clic sobre el menú “Insertar” de VBE y selecciona la opción “Módulo de clase”.

Si decides no usar el módulo de clases, tendrás que tener ciertas consideraciones (que aprenderás más adelante) a la hora de acceder al diccionario.

Es buena práctica renombrar el módulo de clases para relacionarlo con el diccionario que crearemos. Para ello debes dirigirte al nombre de la clase y escribir el nombre que desees.  Dado que haremos un diccionario de nómina, le colocaremos “clsNom” (las tres primeras letras para diferenciar nemotécnicamente que es un módulo de clases).

Dentro del módulo de clases declararemos los ítems o variables (según su tipo) con las que trabajaremos en nuestro diccionario, bajo la siguiente estructura:

Option Explicit
Public NombreDeVariable As TipodeVariable

Ejemplo de creación de módulo de clase para un diccionario VBA:

Option Explicit
Public Name As String   'Worker’s name
Public Age As Long      'Worker’s age
Public Gen As String    'Worker’s genre

2.2.2. Añadir elementos al diccionario VBA

Para implementar esta opción, deberás haber creado previamente el módulo de clases. La ventaja de este método es que posteriormente podrás acceder a cada elemento del diccionario solo con conocer su nombre, sin necesidad de conocer su posición.

La información que continua en el tutorial es basada en la estructura del módulo de clases, sin embargo, si tienes dudas con la otra modalidad, no dejes de contactarnos.

Dim key As Variant
Dim oNom As clsNom
'-----------------------------------------------------------------------------
'Declaración de diccionario
Set WorkerList = CreateObject("Scripting.Dictionary")
'-----------------------------------------------------------------------------
'--- Añadir elementos al Diccionario -
i = 2
Do While IsEmpty(ws.Cells(i, 1)) = False '--->Loop through "A" Column
 IDPerson = ws.Cells(i, 1).Value        '--->Key
'---> Revisa si el key fue añadido al diccionario
 If WorkerList.Exists(IDPerson) = True Then
  MsgBox ("Key " & IDPerson & "is already used")
 Else '---> Crea el objeto de clase (ClsNom)
  Set oNom = New clsNom
  WorkerList.Add IDPerson, oNom
 End If
 '---> Asigna Items
 oNom.Name = ws.Cells(i, 2).Value
 oNom.Age = ws.Cells(i, 3).Value
 oNom.Gen = ws.Cells(i, 4).Value
 i = i + 1
Loop

3. Acceder a elementos del diccionario

Al igual que las Colecciones, los diccionarios tienen un excelente desempeño para manejar grandes cantidades de datos que se relacionan con el identificador único (Key). Así, cuando necesitamos acceder a alguna información para luego actualizarla, eliminarla o sencillamente visualizarla, las instrucciones de VBA hacen una búsqueda en el diccionario por Key  o palabra clave.

Para acceder a los elementos del diccionario debemos seguir la siguiente estructura:

Abrir Loop que busca por cada key del diccionario  
With
NombreDeDiccionario(key)   
Acciones  
End With
Cerrar el Loop

Ejemplo para acceder a elementos de un diccionario VBA:

'--- Acceder al diccionario ---
For Each key In WorkerList.Keys '---> Busca en cada elemento del diccionario
With Nomina(key)            '---> Con el Key de objeto de clase: Acciones
  Debug.Print key, .Name, .Age, .Gen'---> Acción: Imprimir Key e ítems
 End With
Next key

4. Modificar el Diccionario

Sabiendo ya como acceder a los elementos del diccionario, para la modificación sólo restaría encontrar la entrada que deseamos modificar y hacer las respectivas acciones. Veamos ejemplos con las dos modificaciones más comunes: Actualización y Eliminación de entradas.

4.1. Actualizar entradas del diccionario

Identificar el key que deseamos modificar
Abrir Loop que busca por cada key del diccionario
Revisar si el key de loop es el que deseamos modificar  
With NombreDeDiccionario(key)   
Acciones sobre el ítem que seleccionamos en el loop  
End With
Cerrar el Loop

Ejemplo para actualizar datos en un diccionario VBA:

Dim ws As Worksheet
Dim i As Long
Dim oNom As clsNom
Dim key As Variant
 
Set ws = DictionaryWS
'-----------------------------------------------------------------------------
'Declaración de diccionario
Set WorkerList = CreateObject("Scripting.Dictionary")
'-----------------------------------------------------------------------------
'--- Añadir elementos al Diccionario -
i = 2
Do While IsEmpty(ws.Cells(i, 1)) = False '--->Recorrido por toda la columna A
 IDPerson = ws.Cells(i, 1).Value        '--->Key
'---> Revisa si el key fue añadido al diccionario
 If WorkerList.Exists(IDPerson) = True Then
  MsgBox ("Key " & IDPerson & "is already used")
 Else                                    '---> Crea Objeto de clase (ClsNom)
  Set oNom = New clsNom
  WorkerList.Add IDPerson, oNom
 End If
' ---> Asigna valores a los Items
 oNom.Name = ws.Cells(i, 2).Value
 oNom.Age = ws.Cells(i, 3).Value
 oNom.Gen = ws.Cells(i, 4).Value
 i = i + 1
Loop
'-----------------------------------------------------------------------------
'--- Actualización de diccionario ---
Query = "ID 09"              '---> Key a modificar
For Each key In WorkerList.Keys
    If key = Query Then     '---> Modifica si ek key es Query
      '---> Modificación de la entrada <---
      With WorkerList(key)
       .Age = .Age + 1 '---> Actualiza la Edad
      End With
    End If
Next key

Si deseamos limpiar o borrar la información de un ítem, debemos asignarle la el valor “Clear” a la entrada. Aquí te mostramos el ejemplo con el elemento “Age” (edad):

.Age = Clear    '---> Actualiza la Edad

4.2. Borrar Entradas del diccionario

Cuando necesitemos eliminar la información de una entrada del diccionario (por ejemplo, cuando la persona deje de prestar servicio en la empresa),  sería una acción similar a borrar una fila de la hoja de Excel:

La estructura es:

Identificar el key que deseamos eliminar 
NombreDeDiccionario.Remove key 

Ejemplo para eliminar entradas de un diccionario VBA:

'--- Borrar key & Items seleccionado ---
Query = "ID 09"              '---> Key a eliminar
For Each key In WorkerList.Keys
    If key = Query Then     '---> Elimina si el key es Query
       WorkerList.Remove key
    End If
Next key

Ahora bien, si lo que deseas es limpiar toda la información del diccionario, puedes utilizar la siguiente instrucción:

NombreDeDiccionario.RemoveAll

Ejemplo para limpiar información de un diccionario VBA:

'--- Borrar todo el diccionario --- 
WorkerList.RemoveAll

Quieres saber mas? Contáctanos…

 

Colecciones (Collections) en VBA

Las colecciones (Collections) son básicamente grupos de variables almacenadas en un solo lugar.  Las colecciones podrían llegar a confundirse con los Arrays (ver tutorial de Arreglos para mayor información), pero la realidad es que las Colecciones, por sus características, permiten el manejo y acceso de mayor cantidad de datos. Bajo este enfoque es importante que aprendamos a identificar cuando recurrir a un arreglo o a una colección.

La ventaja principal de las Collections respecto a los Arrays es el manejo de datos en redimensionamiento. Haciendo un ejemplo ilustrativo, si tenemos una colección de cuadros de colores como los de la imagen, y deseamos eliminar un cuadro intermedio e incluir otro, las acciones serian así:

En contraparte, a diferencia de los Arrays, las variables de las colecciones no son actualizables, es decir, solo podemos añadir y eliminar elementos, pero no modificar. Esta característica hace que los Collections sean muy útiles cuando necesitamos agrupar elementos sin valores duplicados.

A continuación te describimos algunas instrucciones para operar los elementos tipo colección desde VBA.

1. Declaración de la colección

Existen varias formas de declarar una colección, pero te recomendamos hacerlo similar a una variable, de la siguiente forma

Estructura Dim  As Nombre de la colección As Collection
Set Nombre de la colección = New Collection

Ejemplo:

Dim Square As Collection 
Set Square = New Collection

2. Añadir elementos en la colección

Añadir elementos en una colección es algo sencillo e intuitivo. Básicamente debemos asignarle a la variable previamente declarada la acción que se desea hacer, en este caso añadir (add). Las variables se añadirán una tras otra con valor de índice consecutivo creciente partiendo de número 1, es decir 1, 2, 3… y así sucesivamente. También podrías añadir (opcionalmente) una etiqueta única o Key a cada variable para su rápida identificación. En nuestro ejemplo, crearemos una colección que almacenará 3 nombres de colores: Negros, Gris y Verde, y el color Gris será identificado (Key) como “Favorite”.  Esta identificación debe estar separada por una “,”.

Estructura Nombre de la colección.Acción Valor

Ejemplo:

Square.Add "Black"            'Índice 1            
Square.Add "Grey", “Favorite” 'Índice 2
Square.Add "Green"            'Índice 3                            

Si deseamos añadir un valor intermedio, por ejemplo, antes del color Gris, deberíamos conocer La identificación de referencia (índice o Key) y utilizar una estructura como la mostrada en el siguiente ejemplo:

Estructura Nombre de la colección.Add Valor a añadir , before: IdentificaciónReferencia

Ejemplo 1:

Square.Add "Red1", before:=2

Ejemplo 2:

Square.Add "Red1", before:= “Favorite”

Para añadir valores a una colección, puedes repetir las operaciones descritas arriba tantas veces como variables necesites en tu arreglo o también puedes usar un lazo o Loop (puedes ver nuestro tutorial de Loop para mayor información). Una estructura típica sería:

Estructura Abrir Loop que barre la celda o columna        
Nombre de la colección Celda
Cerrar el Loop

Ejemplo:

Dim Square As Collection
Dim i as long
Dim ws as Worksheet
 
Set ws=Sheet1 'Nombre de la hoja
Set Square = New Collection
i = 2
Do While IsEmpty(ws.Cells(i, 1)) = False
    Square.Add ws.Cells(i, 2)
    i = i + 1
Loop

3. Acceder a los elementos de una colección

Para conocer los valores de las variables dentro de una colección, sólo debemos conocer la identificación de referencia y emplear la siguiente instrucción:

Estructura NombreColección(IdentificaciónReferencia)

Ejemplo 1:

Debug.Print Square(2)

Ejemplo 2:

Debug.Print Square("Favorite")

4. Eliminar elementos de la colección

Similar al caso anterior, para eliminar un elemento de la colección sólo es necesario conocer la identificación de referencia (índice o Key). Las instrucciones serian:

Estructura Nombre de la colección.Remove Índice referencia

Ejemplo:

Square.Remove 3

Análogamente, si deseas eliminar varios elementos de la colección podrías removerlos uno a uno o hacer un Loop, según sea tu preferencia.

5. Barrido por los elementos de la colección

Si deseamos recorrer todos los elementos de una colección, para revisar o usar sus valores, tenemos las siguientes opciones:

5.1 Modo For-Next /Do Loop

La base de esta modalidad es el conteo de elementos en la colección “Count”.

Estructura For Contador= i = 1 To NombreColección.Count    
Acción
Next

Ejemplo:

For i = 1 To Square.Count
  Debug.Print Square (i) 
Next

Ahora bien, si te sientes más a gusto empleando el Do-Loop, podrías hacer algo muy similar, así como se muestra a continuación:

Estructura Contador =1
Do While(Contador<=NombreColección.Count)    
Acción    
Contador= Contador+1 Loop

   Ejemplo:

i = 1
Do While (i <= Square.Count)
    Debug.Print Square (i)
    i = i + 1
Loop

5.2 Modo For- Each

Básicamente esta forma es la misma de For-Next, la diferencia es que esta busca por “Cada” elemento del arreglo en lugar de usar un contador. Esta opción ha mostrado mejores resultados de velocidad de respuesta. La estructura seria la siguiente:

Estructura For Each Elemento de la colección In NombreColección     Acción
Next

Ejemplo:

For Each Elem In Square 
  Debug.Print Elem 
Next
 

 

Quieres saber más? Contáctanos…

Arreglos (Arrays) en VBA

Un arreglo (Array) es un grupo de ítems “empaquetados” en una única variable; en VBA y en muchos otros lenguajes de programación encontraremos los Arrays como estructuras de uso común.

Definir un Array en VBA

Los meses del año son un ejemplo ilustrativo. Si necesitáramos guardar la información de cada periodo sin usar los Arrays deberíamos recurrir a doce (12) variables, una por mes. Para facilitar el manejo, podríamos generar un arreglo con 12 argumentos, uno por cada mes del año. Tenemos varias formas para declarar estos arreglos:

Opción 1 Dim Mes(1 To 12) As Integer
Opción 2 Dim Mes(1,12)
Opción 3 Dim Mes(11)
Opción 4 Option Base 1
Dim Mes(12)

Las dos primeras opciones son las más intuitivas: la primera genera un arreglo de 12 elementos, desde el elemento 1 hasta el 12. La segunda opción tiene una estructura matricial, por ello puede que nos resulte familiar. Puedes revisar el tutorial de Loops en VBA, así como los Tips y atajos en VBA de Seleccionar una celda o Encontrar última celda ocupada.

Los arreglos en VBA comparten en cierta forma la sintaxis con otros lenguajes en donde se maneja por defecto el cero como numero  base, por lo tanto el arreglo como se describe en la opción 3, va desde el número 0 hasta el 11, es decir, 12 elementos. Sin embargo, para hacer la actividad más intuitiva, VBA nos permite cambiar la base por defecto a través de la declaración Option Base 1, así como lo vemos en la opción 4. Es recomendable que la declaración Option Base 1 se coloque al inicio de cada módulo.

Dimensionamiento del Arrays

A diferencia de las variables cuya declaración era opcional (puedes revisar un poco más en el tutorial de variables en VBA), la dimensión del arreglo debe ser previamente declarada antes de comenzar a introducir elementos en él.

Los ejemplos hasta ahora mostrados son unidimensionales, para mayor facilidad de comprensión, sin embargo es importante tener en cuenta que los Arrays pueden tener hasta más de 60 dimensiones.

Hagamos un ejemplo tridimensional: si en tu macro deseas almacenar la información mensual de las horas trabajadas por cada trabajador respecto a su turno de rotación, una opción inteligente sería mediante la declaración de un arreglo. Para dimensionar el arreglo debemos conocer los máximos valores que cada variable podría tomar. Para los meses su máximo valor será de 12; el número total de empleados depende de la plantilla de la empresa, en este caso supondremos un valor de 10. Partiendo de que los turnos de trabajo son de 8 horas por día, el número máximo de turnos será de 3. La declaración debería lucir como esta:

Ejemplo:

Option Base 1
Dim Horas (12, 10, 3)

Considerando que la base es 1, entonces 12 representa el número de meses, 10 el número de trabajadores y 3 el número de turnos.

Por lo tanto, para guardar la información se deberán llenar los espacios correspondientes a cada mes, empleado y turno. Algunos ejemplos serian:

Ejemplo a): Si la persona llamada “Trabajador #3” en el mes de Julio estuvo en el 2do turno y trabajó 125 horas, la asignación de valores en el Array sería:

Horas(7, “Trabajador#3”, 2)= 125

Ejemplo b): Si la persona llamada “Trabajador #8” en el mes de Abril estuvo en el 1er turno y trabajó 150 horas,  la asignación de valores en el Array sería:

Horas(4, “Trabajador#8”, 1)= 150

Ejemplo c): Si la persona llamada “Trabajador #1” en el mes de Diciembre estuvo en el 3er turno y trabajó 200 horas, la asignación de valores en el Array sería:

Horas(12, “Trabajador#1”, 3)= 200

Extraer o leer información del Array

Afortunadamente para nosotros, VBA es muy intuitivo, así que si deseamos escribir en una celda o una variable el valor de determinado elemento de un arreglo, solo debes hacer referencia a la posición del arreglo, tal y como se muestra a continuación en un arreglo tridimensional:

Ejemplo en una celda:

Range("A2") = Horas(7, “Trabajador#3”, 2)

Ejemplo en una variable:

ResultadoHP= Horas(7, “Trabajador#3”, 2)

Bajo este enfoque, debes estar seguro de que en el Array existen los elementos que estas consultando para escribir en la celda, ya que en caso contrario VBA te mostrara un error de dimensionamiento. Para evitar ese tipo de errores, puedes emplear las funciones Lbound y Ubound, las cuales te permiten conocer los límites inferior y superior de tu arreglo, respectivamente.

Dimensionamiento dinámico del Array

Un uso avanzado de los arreglos refiere al dimensionamiento dinámico. Si retomamos el ejemplo anterior, el número de empleados podría cambiar respecto del tiempo.

Para lidiar con este caso, debemos inicializar el arreglo en vacío, siendo estrictamente necesario asignarle  las dimensiones antes de almacenar alguna información en el arreglo mediante el comando de redimensionamiento Redim, tal y como se describe a continuación:

Option Base 1
Dim Horas() As Integer
Redim Horas(12, 10, 3)

Los valores para redimensionamiento pueden ser una constante como en el caso anterior, o también pueden ser variables previamente definidas como lo vemos en el siguiente ejemplo:

Option Base 1
Dim Horas() As Integer
Ntrab=10
Redim Horas(12, Ntrab, 3)

El redimensionamiento puede expandir o reducir el tamaño del arreglo, sin embargo, debemos tener en cuenta que el comando Redim borra toda la información previamente almacenada en el arreglo.  Si deseamos expandir el arreglo y preservar la información contenida en él, debemos usar la instrucción Redim Preserve. En el ejemplo vemos los “Espacios vacíos” añadidos al arreglo, representados por los elementos que tienen el fondo verde.

Un código de redimensionamiento de número de trabajadores en el ejemplo ilustrado seria así:

Option Base 1
Dim Horas(12, 10, 3) As Integer
Ntrab=12
ReDim Preserve Horas(12, Ntrab, 3)

No debemos olvidar que en el redimensionamiento por reducción perderemos la información de los elementos que quedan “Excluidos” del Array. Por ejemplo, si pasamos del arreglo original de 3×3 a uno de 2×2 , perderíamos la información de los elementos que están en el fondo blanco.

Al igual que en el anterior, si deseamos preservar la información de los elementos del fondo azul, debemos utilizar la instrucción ReDim Preserve.

 

Quieres saber más? Contáctanos…

Referenciar hojas de cálculo (Worksheets)

Cuando desarrollamos códigos en VBA, en muchas ocasiones necesitamos referenciar distintas hojas (worksheets) de un mismo libro (workbook) o de un libro distinto al que contiene las macros para realizar operaciones entre ellas. A continuación te mostramos distintos métodos que podemos usar para hacer dichas referencias. Cada uno de estos métodos puede ser más o menos útil dependiendo de la naturaleza del desarrollo.

Referenciar hojas por su nombre visible

La referencia de las hojas por su nombre visible es muy usada, ya que a todos los que estamos acostumbrados al ambiente Excel nos resulta muy familiar. La debilidad de este método es que el nombre visible de la hoja debe permanecer inalterable para que no se genere un error en tu macro o código VBA.

Suponiendo que queremos referenciar una hoja para su activación, la secuencia de comandos para hacerlo es:

Sheets(“Nombre de pestaña”).Activate

Ejemplo:

Sheets(“Hoja 1”).Activate

Referenciar hojas por su índice o lugar en el libro

Este método aplica cuando se busca referenciar la hoja según el orden de la pestaña que ocupe en el libro de Excel. Así, la primera pestaña tendrá un índice de 1, la segunda un índice de 2 y así sucesivamente. Con este método podrás cambiar el nombre visible de la hoja sin ningún problema, pero el orden o índice de las pestaña u hoja referenciada debe ser siempre el mismo para que el código VBA haga lo que necesitamos.

Siguiendo el mismo ejemplo de activar una determinada hoja, la secuencia de comandos para hacerlo utilizando índices es:

Sheets(“Índice de  la pestaña”).Select

Ejemplo:

Sheets(3).Select

Nótese que en este caso utilizamos la opción .Select, en lugar de .Activate para activar la hoja, pero ambas opciones generan el efecto deseado.

Referenciar hojas por su nombre interno o nombre de código

El nombre visible de las pestañas en Excel no es necesariamente el mismo nombre interno o de código con el que Excel se refiere a las hojas  de trabajo. Para ver y modificar este nombre de código es necesario hacerlo desde el editor de VBA. Como ya te puedes imaginar, es más difícil que usuarios sin conocimientos de VBA tengan acceso a este nombre para modificaarlo, por lo que referencias las hojas de trabajo bajo este método ofrece mayor seguridad y estabilidad del código de nuestra macro, independientemente de si se modifica el nombre visible de la pestaña o su orden en el libro. Por esta razón es nuestra SmartCode Recomendation.

La secuencia de comandos para activar una hoja usando su nombre de código es:

Nombre Interno de la hoja.Activate

Ejemplo:

Hoja1.Activate

Quieres saber más? Contáctanos…