Si recién está comenzando a usar VBA, deberá comenzar estudiando Una guía para principiantes sobre VBAPero si eres un experto experimentado en VBA y estás buscando cosas más avanzadas que puedas hacer 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 VBA de Excel y seleccione el objeto Hoja1. En los cuadros de lista desplegable Objeto y Método, elija Hoja de trabajoyActivarEsto hará que el código se ejecute cada vez que abra la hoja de cálculo.
Esto creará una función Subhoja de trabajo_Activar()Pegue el código anterior en esta función.
Esto establece la hoja de trabajo activa en Sheet1Escanee la hoja y conéctese al archivo utilizando la ruta de archivo que especificó en la variable. archivo strEntonces el bucle Con Recorriendo cada línea del archivo y colocando los datos en la hoja comenzando desde la celda A1.
Si ejecuta este código, verá que los datos del archivo CSV se importan a su hoja de cálculo vacía. Sheet1.
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 tabla de datos mencionada anteriormente, seleccione la pestaña Developery seleccione recuadro De un grupo Controla En la cinta, seleccione un control. presionar el botón Seleccione ActiveX en el menú desplegable.
Dibuje el botón en cualquier parte del papel lejos de donde irían los datos.
Haga clic derecho en el botón pulsador y seleccione ViviendasEn la ventana Propiedades, cambie el Título a lo que desea mostrar al usuario. En este caso, podría ser Calcular el impuesto del 5%.
Verás este texto reflejado en el propio botón. Cerrar ventana. PropiedadesHaga doble clic en el mismo botón. Se abrirá una ventana del editor de código y el cursor estará 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, repite el proceso y crea un segundo botón. Etiquétalo. Cálculo del impuesto 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 una lista Desarrolladory seleccione Modo de diseño Desde el grupo de controles de la cinta para deshabilitar Modo de diseñoEsto 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 Hoja de correo electrónico al jefe Usando el mismo procedimiento descrito anteriormente, el código para este botón implicará usar 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 para Biblioteca de Microsoft CDO para Windows 2000Habilítelo y seleccione OK.
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
reemplazar correo electrónico@sitio web.com La contraseña contiene los detalles de su 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
ملاحظةSi ves un error de transferencia al intentar ejecutar este código, es probable que tu cuenta de Google esté bloqueando la ejecución de aplicaciones menos seguras. Deberás visitar [enlace/referencia]. Página de configuración de aplicaciones menos seguras Activa 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.