Si te estás iniciando en VBA, te recomendamos comenzar estudiando la Guía para principiantes de VBA . Pero si eres un usuario experimentado de VBA y buscas funciones más avanzadas para trabajar con VBA en Excel, sigue leyendo.
La posibilidad de usar el marcado VBA en Excel abre un mundo de posibilidades de automatización. Puedes automatizar cálculos, botones e incluso el envío de correos electrónicos en Excel. Hay más posibilidades de automatizar tu trabajo diario con VBA de las que imaginas.
Guía avanzada de VBA para Microsoft Excel
El propósito principal de escribir código VBA en Excel es permitirle extraer información de una hoja de cálculo, realizar una variedad de cálculos en ella y luego escribir los resultados nuevamente en la hoja de cálculo.
Los siguientes son los usos más comunes de VBA en Excel.
- Importar datos y realizar cálculos
- Cálculo de resultados en función de los clics de los botones del usuario
- Enviar los resultados de los cálculos por correo electrónico a alguien
Usando estos tres ejemplos, deberías poder escribir una variedad de tu propio código VBA avanzado de Excel.
Importar datos y realizar cálculos
Una de las cosas más comunes para las que se usa Excel es para realizar cálculos con datos fuera de Excel. Si no se usa VBA, es necesario importar manualmente los datos, ejecutar los cálculos y exportar esos valores a otra hoja o informe.
Con VBA, puede automatizar todo el proceso. Por ejemplo, si descarga un nuevo archivo CSV en un directorio de su computadora cada lunes, puede configurar su código VBA para que se ejecute al abrir la hoja de cálculo el martes por la mañana.
Se ejecutará el siguiente código de importación y el archivo CSV se importará a su hoja de cálculo de Excel.
Dim ws como hoja de cálculo, strFile como cadena Establecer ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" Con ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh Fin con
Abra el editor de VBA de Excel y seleccione el objeto Sheet1. En los menús desplegables Objeto y Método, elija Hoja de cálculo y active la opción . Esto ejecutará el código cada vez que abra la hoja de cálculo.
Esto creará una función Sub Worksheet_Activate() . Pegue el código anterior en esta función.
Esto establece la hoja de cálculo activa en Hoja1 , escanea la hoja, se conecta al archivo usando la ruta de archivo que especificó con la variable strFile y luego el bucle With itera a través de cada línea en el archivo y coloca los datos en la hoja comenzando desde la celda A1.
Si ejecutas este código, verás que los datos del archivo CSV se importan a tu hoja de cálculo vacía, en la Hoja1.
Importar es solo el primer paso. A continuación, deberá crear un nuevo encabezado de columna que contendrá los resultados de sus cálculos. En este ejemplo, supongamos que desea calcular el 5% de impuestos pagados por la venta de cada artículo.
El orden de acciones que debe realizar su código es:
- Crea una nueva columna de resultados llamada impuestos.
- Repetir a lo largo de una columna unidades vendidas Y calcular el impuesto sobre las ventas.
- Escriba los resultados de los cálculos en la fila correspondiente del papel.
El siguiente código realizará todos estos pasos.
Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range("A1") 'Buscar última fila y columna LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "taxes" Para cada celda En rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Siguiente celda
Este código busca la última fila de la hoja de datos y establece el rango de celdas (la columna que contiene los precios de venta) según la primera y la última fila de datos. El código itera por cada una de estas celdas, calcula el impuesto y escribe los resultados en la nueva columna (columna 5).
Pegue el código VBA anterior debajo del código anterior y ejecute el script. Verá los resultados en la columna E.
Ahora, cada vez que abra su hoja de cálculo de Excel, el programa obtendrá automáticamente la última versión de los datos del archivo CSV. Realizará los cálculos y guardará los resultados en la hoja de cálculo. ¡Ya no tendrá que hacer nada manualmente!
Calcula los resultados presionando el botón
Si prefiere tener control directo sobre cuándo se ejecutan los cálculos, en lugar de ejecutarlos automáticamente cuando se abre la hoja de cálculo, puede utilizar un botón de control.
Los botones de control son útiles para controlar los cálculos. Por ejemplo, en el mismo escenario mencionado, ¿qué sucedería si se utilizara una tasa impositiva del 5% para una región y del 7% para otra?
Puede permitir que el mismo código de importación CSV se ejecute automáticamente, pero dejar que el código de cálculo de impuestos se ejecute cuando se presione el botón correspondiente.
Utilizando la misma hoja de cálculo mencionada anteriormente, seleccione la pestaña Desarrollador y, a continuación, seleccione Insertar en el grupo Controles de la cinta de opciones. Seleccione el control ActiveX Botón pulsador en el menú desplegable.
Dibuje el botón en cualquier parte del papel lejos de donde irían los datos.
Haz clic con el botón derecho en el botón y selecciona Propiedades . En la ventana Propiedades, cambia el título al que quieras mostrar al usuario. En este caso, podría ser Calcular impuesto del 5%.
Verás este texto reflejado en el botón. Cierra la ventana de Propiedades y haz doble clic en el botón. Se abrirá una ventana del editor de código y el cursor se ubicará dentro de la función que se ejecutará al hacer clic en el botón.
Pegue el código de cálculo de impuestos de la sección anterior en esta función, manteniendo el multiplicador de la tasa impositiva en 0.05. Recuerde incluir las dos líneas siguientes para identificar la hoja activa.
Dim ws como hoja de trabajo, strFile como cadena Establecer ws = ActiveWorkbook.Sheets("Sheet1")
Ahora, repita el proceso y cree un segundo botón. Etiquételo como " Cálculo de impuestos del 7%".
Haga doble clic en este botón y pegue el mismo código, pero establezca el multiplicador de impuestos en 0.07.
Ahora, en función del botón que presione, la columna de impuestos se calculará en consecuencia.
Una vez que haya terminado, tendrá ambos botones en su hoja. Cada uno iniciará un cálculo de impuestos diferente y mostrará resultados distintos en la columna de resultados.
Para probar esto, seleccione el menú Desarrollador y, a continuación, seleccione Modo de diseño en el grupo de controles de la cinta de opciones para desactivar el Modo de diseño . Esto activará los botones.
Intente seleccionar cada botón para ver cómo cambia la columna de resultados “Impuestos”.
Enviar por correo electrónico los resultados de la cuenta a alguien
¿Qué pasaría si quisiera enviar los resultados de la hoja de cálculo a alguien por correo electrónico?
Puedes crear otro botón llamado «Enviar hoja de cálculo al jefe» siguiendo el mismo procedimiento descrito anteriormente. El código de este botón utilizará un objeto CDO de Excel para configurar los ajustes de correo electrónico SMTP y enviar los resultados por correo electrónico en un formato fácil de usar.
Para habilitar esta función, debe seleccionar Herramientas y referencias . Desplácese hacia abajo hasta la Biblioteca Microsoft CDO para Windows 2000 , habilítela y seleccione Aceptar.
Hay tres secciones principales de código que debes crear para enviar un correo electrónico e incluir resultados de una hoja de cálculo.
Lo primero es configurar variables para conservar el asunto, las direcciones “para” y “de” y el texto del correo electrónico.
Dim CDO_Mail como objeto Dim CDO_Config como objeto Dim SMTP_Config como variante Dim strSubject como cadena Dim strFrom como cadena Dim strTo como cadena Dim strCc como cadena Dim strBcc como cadena Dim strBody como cadena Dim LastRow como largo Dim StartCell como rango Dim rowCounter como entero Dim rng como rango, celda como rango Dim fltTax como doble
Establecer ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "Impuestos pagados este trimestre" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "El siguiente es el desglose de los impuestos pagados sobre las ventas de este trimestre".
Por supuesto, el texto debe ser dinámico dependiendo de los resultados del artículo, por lo que aquí deberá agregar un bucle que recorra el rango, extraiga los datos y escriba una línea cada vez en el texto.
Establezca StartCell = Range("A1"). vbCrLf Para cada celda En rng strBody = strBody & vbCrLf strBody = strBody & "Vendimos " & Cells(rowCounter, 3).Value & " de " & Cells(rowCounter, 1).Value _ & " para " & Cells(rowCounter, 4).Value & " y pagamos impuestos de " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Siguiente celda
La siguiente sección trata sobre la configuración de SMTP para que puedas enviar correos electrónicos a través de tu servidor SMTP. Si usas Gmail, este suele ser tu dirección de correo electrónico, tu contraseña y el servidor SMTP de Gmail (smtp.gmail.com).
Establecer CDO_Mail = CreateObject("CDO.Message") En caso de error, ir a Manejo de errores. Establecer CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Establecer SMTP_Config = CDO_Config.Fields Con SMTP_Config.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "contraseña" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Establecer .Configuration = CDO_Config Fin con
Sustituye email@website.com y password por los datos de tu cuenta.
Por último, para comenzar a enviar el correo electrónico, ingrese el siguiente código.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Manejo de errores: Si Err.Description <> "" Entonces MsgBox Err.Description
Nota : Si aparece un error de transferencia al intentar ejecutar este código, es probable que tu cuenta de Google esté bloqueando la opción "Aplicaciones menos seguras". Deberás acceder a la configuración de "Aplicaciones menos seguras " y habilitar esta opción.
Tras activar esta opción, se enviará tu correo electrónico. Así lo verá la persona que lo reciba automáticamente.
Como puede ver, hay mucho que puede automatizar con Excel VBA. Pruebe con los fragmentos de código que aprendió en este artículo y cree sus propias automatizaciones de VBA.
Los comentarios están cerrados.