El Investment Return Analyzer es una herramienta en Excel diseñada para evaluar la rentabilidad de proyectos a través del análisis de flujo de caja. Permite ingresar parámetros, registrar ingresos y gastos, y generar un informe financiero automatizado.
Esta herramienta consta de:
Hoja de Datos de Entrada: Donde se ingresan los detalles del proyecto.
Generación de Reportes: Un análisis financiero detallado con indicadores clave y gráficos.
¿Cómo Utilizarlo?
Completa los siguientes campos en la hoja Input Data:
Nombre del proyecto.
Número de períodos a analizar (años o meses).
Tasa de descuento (usada para calcular el Valor Presente Neto).
Tasa de descuento (usada para calcular el Valor Presente Neto).
Ingresos y gastos.
Al hacer clic en el botón «Generate Report», se crea una hoja nueva con un análisis financiero detallado, incluyendo:
Valor Presente Neto (VPN): Evalúa si un proyecto es rentable descontando los flujos de caja futuros.
Tasa Interna de Retorno (TIR): Tasa de retorno que hace que el VPN sea cero.
Retorno de Inversión (ROI): Indica la rentabilidad como porcentaje de la inversión inicial.
Período de Recuperación: Tiempo necesario para recuperar la inversión inicial.
🎥 Video Demostrativo
Mira el siguiente video para ver cómo funciona Investment Return Analyzer en acción:
Bienvenido a Invoice Generator, una herramienta diseñada para simplificar la gestión de facturas en Excel mediante VBA. Con esta plantilla, podrás generar facturas personalizadas, gestionar productos y almacenar un historial de ventas sin necesidad de bases de datos externas. La herramienta cuenta con las siguientes características:
Modelo dinámico y editable de factura, ideal para pequeñas empresas y emprendimientos.
Base de datos con los productos disponibles.
Registro automático de todas las facturas generadas.
Añadir y eliminar productos de forma fácil y rápida.
Generación de factura en formato Excel y .pdf con un solo clic.
🎥 Video Demostrativo
Mira el siguiente video para ver cómo funciona Invoice Generator en acción:
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:
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.
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:
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.
Las fórmulas y funciones en Excel requieren de la designación correcta de sus argumentos. Por ejemplo una suma requiere definir sus elementos, una resta el minuendo y sustraendo.
Si las celdas que van a operar son contiguas, se hace más fácil pues sólo debemos indicar en el argumento el inicio y el final del grupo de celdas. Podemos ver en el ejemplo de la imagen la forma aritmética natural en la que se pueden sumar uno a uno los elementos, y la alternativa de separar con dos puntos “:” la primera y última celda del grupo que necesitas incluir en el arreglo de la suma.
Ejemplo de la función SUMA en Excel:
Esta modalidad puede aplicarse a la mayoría de las fórmulas y funciones de Excel, respetando por supuesto las reglas de cada operación.
Estructura de las funciones y fórmulas de Excel y su comparación en VBA
A continuación te explicamos las estructuras de las funciones y fórmulas más usadas en Excel y su equivalencia en VBA (Visual Basic For Applications).
La palabra “Expresión” en las columnas de las tablas correspondientes a VBA te indicarán que es una función de hoja de cálculo; es decir, para utilizarlas deberías sustituir el término “Expresión” por “Application.WorksheetFunction”. Si tu macro está desarrollada en Excel para operar en esa misma aplicación, no es necesario escribir “Application” (bastaría con sustituir «Expresión» por «WorksheetFunction»), aunque siempre es recomendable hacerlo para asegurar la estabilidad de tu código.
Es importante que conozcas que cualquier fórmula que escribas en una hoja de cálculo también la podrías escribir en el código VBA, sin embargo en algunos casos no habrá una equivalencia directa entre las fórmulas en Excel y VBA; si te encontraras en esa situación solo tendrías que hacer unas pocas cosas para lograr el cálculo que deseas.
Cálculo de operaciones aritméticas y matemáticas
A continuación te presentamos la estructura de algunas de las operaciones matemáticas básicas en Excel, como lo son la Suma, Resta, Multiplicación, División, Potencia y Raíz Cuadrada. También te mostramos cómo escribir estas operaciones en tu código VBA.
Operación
Estructura Excel
Ejemplo Excel
Estructura VBA
Ejemplo VBA
Suma (opción 1)
=SUMA(Arg1 : Arg3)
=SUMA(A1:A3)
Expresión. Sum(Range(Arg1, Arg3))
Expresión. Sum(Range( Cells(1, 1), Cells(3, 1)))
Suma (opción 2)
Arg1+ Arg2 + Arg3
=A1+A2+A3
Arg1 + Arg2 + Arg3
Cells(1,1)+Cells(2,1)+ Cells(2,1)
Resta
Arg1 – Arg2
=A1-A2
Arg1 – Arg2
Cells(1,1)-Cells(2,1)
Multiplicación
Arg1 * Arg2
=A1*A2
Arg1 * Arg2
Cells(1,1)*Cells(2,1)
División
Arg1 / Arg2
=A1/A2
Arg1 / Arg2
Cells(1,1)/Cells(2,1)
Potencia (opción 1)
Arg1^Arg2
=A1^A2
Arg1^Arg2
Cells(1,1)^Cells(2,1)
Potencia (opción 2)
=POTENCIA(Arg1; Arg2)
=POTENCIA(A1; A2)
Arg1^Arg2
Cells(1,1)^Cells(2,1)
Raíz Cuadrada
=RAIZ(Arg1)
=RAIZ(A1)
Sqr(Arg1)
Sqr(Cells(1, 2))
Ejemplo del uso de las fórmulas Suma, Resta, Multiplicación, División, Potencia y Raíz Cuadrada en Excel:
En la siguiente tabla te resumimos la estructura de otras operaciones de uso común en Excel, como el cálculo del Promedio, Desviación Estándar e Intervalo de Confianza. También encontrarás la estructura de las funciones Contar y ContarA, que se usan para contar el número de celdas de un rango que contienen números (Contar) o que no están vacías (ContarA).
Ejemplo del uso de las fórmulas Contar, ContarA, Promedio, Desvest e Intervalo.Confianza.Norm en Excel.
Redondeo
Una función de mucha utilidad en Excel es el redondeo de nuestro cálculo, ya que contribuye a hacer una presentación elegante de los resultados obtenidos.
En la siguiente tabla conocerás las estructuras de los tipos de redondeo y su equivalencia directa en VBA. En la tabla observarás dos argumentos, el primero se refiere al número que deseas redondear y el segundo a la cantidad de decimales o factor de multiplicación que gobernará el redondeo.
Tipo de redondeo
Estructura Excel
Ejemplo VBA
Estructura Excel
Ejemplo VBA
Redondeo
=REDONDEAR( Arg1; Arg2)
=REDONDEAR( 3,141592653589;2)
Expresión.Round(Arg1, Arg2)
Expresión.Round( 3.141592653589, 2)
Hacia arriba
REDONDEAR.MAS( Arg1; Arg2)
=REDONDEAR.MAS( 3,14159265358979;0)
Expresión.RoundUp( Arg1, Arg2)
Expresión.RoundUp(3.141592653589, 0)
Hacia abajo
=REDONDEAR.MENOS( Arg1; Arg2)
=REDONDEAR.MENOS( 3,141592653589;0)
Expresión.RoundDown( Arg1, Arg2)
Expresión.RoundDown(3.141592653589, 0)
Múltiplo
=REDOND.MULT( Arg1; Arg2)
=REDOND.MULT( 3,141592653589;0,2)
(*)
(*)
(*): No tiene equivalencia directa Excel−VBA
Ejemplo del uso de la función Redondear en Excel:
Operaciones Lógicas
Las funciones lógicas son de mucha utilidad en Excel ya que permiten decidir qué acciones o cálculos serán llevados a cabo según el valor de algún(as) celdas. En la siguiente tabla conocerás las estructuras de algunas de estas funciones y su equivalencia directa en VBA.
Prueba Lógica
Estructura Excel
Ejemplo Excel
Estructura VBA
Ejemplo VBA
«Igual que»
= Arg1= Arg2
=B2=10
Arg1= Arg2
Cells(2,2)=10
«Mayor que»
= Arg1> Arg2
=B2>10
Arg1> Arg2
Cells(2,2)>10
«Menor que»
= Arg1< Arg2
=B2<10
Arg1< Arg2
Cells(2,2)<10
«Mayor o igual que»
= Arg1>= Arg2
=B2>=10
Arg1>= Arg2
Cells(2,2)>=10
«Menor o igual que»
= Arg1<= Arg2
=B2<=10
Arg1<= Arg2
Cells(2,2)<=10
«Y»
=Y(Arg1>= Arg2; Arg3>= Arg4)
=Y(B2>=10; 8>=B3; 8>=10)
PruebaLogica1 And PruebaLogica2
Cells(2, 2) >= 10 And Cells(3, 2) >= 10
«O»
=O(Arg1>= Arg2; Arg3>= Arg4)
=O(B3>=10; 8>=B4; 8>=10)
PruebaLogica1 Or PruebaLogica2
Cells(2, 2) >= 10 Or Cells(3, 2) >= 10
Condicional
=O(PruebaLogica; ResVerd; ResFalso)
=SI((B2>=10)= VERDADERO;» Aprobado»; «Reprobado»)
If PruebaLogica Then ResVerd Else ResFalso End If
If Cells(2, 2) > 10 Then Cells(9, 5) = «Aprobado» Else Cells(9, 5) = «Reprobado» End If
Donde:
PruebaLogica: es la evaluación que deseas hacer. Debe ser formulada de manera que su resultado sea VERDADERO o FALSO.
ResVerd: es la acción que se haría en caso de que el resultado de la prueba lógica resulta VERDADERO.
ResFalso: es la acción que se haría en caso de que el resultado de la prueba lógica resulta FALSO.
Ejemplo del uso de las pruebas lógicas en Excel:
Fórmulas con Fecha y Hora
Las funciones con la fecha y hora nos permiten extraer información de tiempo (fecha y hora) del contenido de las celdas de nuestra hoja de cálculo. En la siguiente tabla conocerás las estructuras de las funciones relacionadas con las fechas y el tiempo.
Función
Estructura Excel
Ejemplo Excel
Estructura VBA
Ejemplo VBA
Ahora
=AHORA()
=AHORA()
Now()
Now()
Año
=AÑO(Arg1)
=AÑO(B2)
Year(Arg1)
Year(Cells(2,2))
Mes
=MES(Arg1)
=MES(B2)
Month(Arg1)
Month(Cells(2,2))
Dia
=DIA(Arg1)
=DIA(B2)
Day(Arg1)
Day(Cells(2,2))
Hora
=HORA(Arg1)
=HORA(B2)
Hour(Arg1)
Hour(Cells(2,2))
Minuto
=MINUTO(Arg1)
=MINUTO(B2)
Minute(Arg1)
Minute(Cells(2,2))
Segundo
=SEGUNDO(Arg1)
=SEGUNDO(B2)
Second(Arg1)
Second(Cells(2,2))
Día de la semana
=DIASEM(Arg1)
=DIASEM(B2)
Weekday(Arg1)
Weekday(Cells(2,2))
Fracción de año
=FRAC.AÑO(Arg1; Arg2)
=FRAC.AÑO(B2;B3)
Expresión.YearFrac(Arg1, Arg2
Expresión.YearFrac(Cells(2,2), Cells(3,2))
Ejemplo del uso de las funciones de Año, Mes, Día, Hora, Minuto, Segundo, Día de la Semana y Fracción del año en Excel.
Operaciones con Texto
Existen algunas funciones que nos permiten modificar el texto introducido en las celdas de nuestro libro o extraer información del texto contenido en las celdas. En la siguiente tabla conocerás las estructuras de las funciones para manipulación de texto y su equivalencia directa en VBA.
Función
Estructura
Ejemplo
Estructura
Ejemplo
Concatenar (opción 1)
Arg1&Arg2
B2 & » » & B3
Arg1 & Arg2
Cells(2,2) & “ “ & Cells(3,2)
Concatenar (opción 2)
=CONCATENAR( Arg1, Arg2)
=CONCATENAR( B2; » «;B3)
Arg1 & Arg2
Cells(2,2) & “ “ & Cells(3,2)
Caracteres desde la derecha
=DERECHA(Arg1; NumCaract)
=DERECHA(B2;2)
Right(Arg1, NumCaract)
Right(Cells(2, 2), 2)
Caracteres desde la izquierda
=IZQUIERDA( Arg1; NumCaract)
=IZQUIERDA(B2;2)
Left(Arg1, NumCaract)
Left(Cells(2, 2), 2)
Longitud del texto
=LARGO(Arg1)
=LARGO(B2)
Len(Arg1)
Len(Cells(2, 2))
Extraer caracteres del texto
=EXTRAE(Arg1; PosInic; NumCaract)
=EXTRAE(B2;2;2)
Mid(Arg1, PosInic, NumCaract)
Mid(Cells(2, 2), 2, 2)
Reemplazar
=SUSTITUIR(Arg1; Arg2; Arg3)
=SUSTITUIR(“Hola Mundo”;» «;»»)
Replace(Arg1, Arg2, Arg3)
Replace(Cells(2, 2) & » » & Cells(3, 2), » «, «»)
Minúscula
=MINUSC(Arg1)
=MINUSC(B2)
LCase(Arg1)
LCase(Cells(2, 2))
Mayúscula
=MAYUSC(Arg1)
=MAYUSC(B2)
UCase(Arg1)
UCase(Cells(2, 2))
Donde:
Arg1, Arg2, Arg3: son los argumentos (textos) de la función
PosInic: es la posición inicial de la manipulación del texto
NumCaract: es la cantidad de caracteres del texto que serán modificados o extraídos.
Ejemplo del uso de las funciones de manipulación y extracción de textos en Excel:
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
La selección de casos, o Select-Case, nos permite tomar decisiones basadas en el valor de una variable. Como puedes imaginar, es una alternativa al condicional If Else, y en determinadas situaciones puede resultar más adecuada.
La estructura Select Case se encarga de dirigir el flujo de información en nuestra macro según el valor de la variable seleccionadora. Esta selección resulta en la ejecución de acciones específicas, como se muestra en el siguiente esquema.
La variable seleccionadora puede ser de cualquier tipo. Una de las diferencias más importantes entre Select Case y el condicional If Else es que la evaluación se realiza directamente sobre el resultado de una variable, lo que significa que no podemos utilizar operadores lógicos (And/Or) en esta evaluación.
Estructura del Select Case en VBA
La estructura básica del Select Case es la siguiente:
Select CaseVariableCaseResultado que tomaría la variableAccionesEnd Select
Ejemplo de Select Case en VBA
Si deseamos aplicar un color específico 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) ' Verde
Case "Disapproved"
Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Rojo
End Select
i = i + 1
Loop
Con esta rutina, las celdas que contengan la palabra “Approved” se pintarán de verde, mientras que las que contengan “Disapproved” tendrán color rojo.
Con la práctica, irás adquiriendo destrezas que te permitirán escribir un código de programación más compacto y menos propenso a errores.
¿Te gustaría saber más? Contáctanos para obtener más información.
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:
SetVariable Asignada Al Explorador = CreateObject(«InternetExplorer.Application») WithVariable 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 WhileVariable 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:
WithVariable Asignada Al Explorador SetVariable para Tipo de elemento = Obtener elemento según denominación For EachVariable 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ónSet elemsLocation = .getElementsByClassName("h4 today_nowcard-location")
For Each e In elemsLocation
Ws.Cells(2, 2) = e.innertext
Next'TemperaturaSet elemsTemp = .getElementsByClassName("today_nowcard-temp")
For Each e In elemsTemp
Ws.Cells(3, 2) = e.innertext
Next'Hora de actualizaciónSet elemsUpdateTime = .getElementsByClassName("today_nowcard-timestamp")
For Each e In elemsUpdateTime
Ws.Cells(4, 2) = e.innertext
NextEnd 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 OcultoEnd 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ónSet elemsLocation = .getElementsByClassName("h4 today_nowcard-location")
For Each e In elemsLocation
Ws.Cells(2, 2) = e.innertext
Next'TemperaturaSet elemsTemp = .getElementsByClassName("today_nowcard-temp")
For Each e In elemsTemp
Ws.Cells(3, 2) = e.innertext
Next'Hora de actualizaciónSet elemsUpdateTime = .getElementsByClassName("today_nowcard-timestamp")
For Each e In elemsUpdateTime
Ws.Cells(4, 2) = e.innertext
Next
End With'---------------------------------------------------------------ie.Quit '---> Cierra ExploradorMsgBox "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”)
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:
WithVariable 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.
WithVariable Asignada Al Explorador SetVariable para Tipo de elemento = Obtener elemento según denominación For EachVariable 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 ubicacionSet SearchLocation = .getElementsByClassName("theme__inputElement__4bZUj input__inputElement__1GjGE")
For Each SL In SearchLocation
SL.Value = “Maracaibo" '---> Ingreso de informaciónNext
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.
WithVariable Asignada Al Explorador SetVariable para Tipo de elemento = Obtener elemento según denominación For EachVariable 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 ubicacionSet SearchLocationButton = .getElementsByClassName( "styles__item__sCSPm")input__inputElement__1GjGE")
For Each SLB In SearchLocationButton
SLB.Click '---> Hace click sobre el elementoNext
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.
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.
(*) 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
WithNombre 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.
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.
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:
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:
DimDeclaraciónVariables AsNombre de módulo de claseAbrir Loop que barre la columnaAsigna KeyIf (Revisa si el Key ya esté utilizado) ThenAcción si el Key fue utilizadoElse (Acción si el Key está libre)SetObjetoDeClase = NewNombreDeModuloDeClaseNombreDeDiccionario.AddKey, ObjetoDeClase(Ítems) o arreglo de itemsEnd IfObjetoDeClase.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:
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 diccionarioWithNombreDeDiccionario(key)
AccionesEnd WithCerrar 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 modificarAbrir Loop que busca por cada key del diccionarioRevisar si el key de loop es el que deseamos modificarWithNombreDeDiccionario(key)
Acciones sobre el ítem que seleccionamos en el loopEnd WithCerrar 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 eliminarNombreDeDiccionario.Removekey
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
Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.Ok