Si recién está comenzando con VBA, querrá comenzar a estudiar nuestra guía de VBA para principiantes. Pero si es un experto experimentado en VBA y está buscando cosas más avanzadas que pueda hacer con VBA en Excel, siga leyendo.
La capacidad de usar la codificación VBA en Excel abre todo un mundo de automatización. Puede automatizar cálculos en Excel, pulsadores e incluso enviar correo electrónico. Hay más posibilidades de automatizar su trabajo diario con VBA de lo que cree.
Guía avanzada de VBA para Microsoft Excel
El objetivo principal de escribir código VBA en Excel es poder extraer información de una hoja de cálculo, realizar una variedad de cálculos en ella y luego volver a escribir los resultados en la hoja de cálculo.
Los siguientes son los usos más comunes de VBA en Excel.
- Importar datos y realizar cálculos
- Calcular los resultados de un usuario presionando un botón
- Enviar por correo electrónico los resultados de los cálculos a alguien
Con estos tres ejemplos, debería poder escribir una variedad de su propio código Excel VBA avanzado.
Importación de datos y realización de cálculos
Una de las cosas más comunes para las que la gente usa Excel es realizar cálculos en datos que existen fuera de Excel. Si no usa VBA, eso significa que debe importar manualmente los datos, ejecutar los cálculos y enviar esos valores a otra hoja o informe.
Con VBA, puede automatizar todo el proceso. Por ejemplo, si tiene un nuevo archivo CSV descargado en un directorio de su computadora todos los lunes, puede configurar su código VBA para que se ejecute cuando abra su hoja de cálculo por primera vez el martes por la mañana.
El siguiente código de importación se ejecutará e importará el archivo CSV a su hoja de cálculo de Excel.
Dim ws como hoja de trabajo, strFile como StringSet ws = ActiveWorkbook.Sheets("Sheet1")Cells.ClearContentsstrFile = c:temppurchases.csvWith ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws .Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .RefreshEnd With
Abra la herramienta de edición de Excel VBA y seleccione el objeto Sheet1. En los cuadros desplegables de objetos y métodos, elija Hoja de trabajo y Activar . 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 esa función.
Esto establece la hoja de trabajo activa en Sheet1, borra la hoja, se conecta al archivo usando la ruta del archivo que definió con la variable strFile y luego el bucle With recorre cada línea del archivo y coloca los datos en la hoja comenzando en la celda A1 .
Si ejecuta este código, verá que los datos del archivo CSV se importan a su hoja de cálculo en blanco, en Sheet1.
Importar es solo el primer paso. A continuación, desea crear un nuevo encabezado para la columna que contendrá los resultados de su cálculo. En este ejemplo, supongamos que desea calcular el 5% de impuestos pagados por la venta de cada artículo.
El orden de las acciones que debe realizar su código es:
- Crea una nueva columna de resultados llamada impuestos.
- Recorra la columna de unidades vendidas y calcule el impuesto a las ventas.
- Escriba los resultados del cálculo en la fila correspondiente de la hoja.
El siguiente código cumplirá con todos estos pasos.
Atenuar la última fila mientras dure
Dim StartCell como rango
Atenuar contador de filas como entero
Dim rng como rango, celda como rango
Dim fltTax como doble
Establecer StartCell = Rango ("A1")
'Buscar última fila y columna
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Establecer rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
Contador de filas = 2
Celdas(1, 5) = "impuestos"
Para cada celda en rng
FltTax = celda.Valor * 0.05
Celdas (contador de filas, 5) = impuesto flt
Contadorfilas = contadorfilas + 1
Siguiente celda
Este código encuentra la última fila en su hoja de datos y luego establece el rango de celdas (la columna con los precios de venta) de acuerdo con la primera y la última fila de datos. Luego, el código recorre cada una de esas celdas, realiza el cálculo de impuestos y escribe los resultados en su nueva columna (columna 5).
Pegue el código VBA anterior debajo del código anterior y ejecute el script. Verá que los resultados aparecen en la columna E.
Ahora, cada vez que abra su hoja de cálculo de Excel, saldrá automáticamente y obtendrá la copia más reciente de los datos del archivo CSV. Luego, realizará los cálculos y escribirá los resultados en la hoja. ¡Ya no tienes que hacer nada manualmente!
Calcular los resultados de la pulsación de un botón
Si prefiere tener un control más directo sobre cuándo se ejecutan los cálculos, en lugar de ejecutarse automáticamente cuando se abre la hoja, puede usar un botón de control en su lugar.
Los botones de control son útiles si desea controlar qué cálculos se utilizan. Por ejemplo, en este mismo caso que el anterior, ¿qué sucede si desea utilizar una tasa impositiva del 5 % para una región y una tasa impositiva del 7 % para otra?
Puede permitir que el mismo código de importación CSV se ejecute automáticamente, pero dejar que se ejecute el código de cálculo de impuestos cuando presione el botón correspondiente.
Usando la misma hoja de cálculo que la anterior, seleccione la pestaña Desarrollador y seleccione Insertar del grupo Controles en la cinta. Seleccione el botón ActiveX Control del menú desplegable.
Dibuje el botón en cualquier parte de la hoja lejos de donde irán los datos.
Haga clic con el botón derecho en el botón y seleccione Propiedades. En la ventana propiedades, cambie el título a lo que le gustaría mostrar al usuario. En este caso, podría ser Calcular 5% de impuestos.
Verá este texto reflejado en el propio pulsador. Cierre la ventana de propiedades y haga doble clic en el propio botón. Esto abrirá la ventana del editor de código y su cursor estará dentro de la función que se ejecutará cuando el usuario presione 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 de impuestos en 0,05. Recuerde incluir las siguientes 2 líneas para definir la hoja activa.
Dim ws como hoja de trabajo, strFile como cadena
Establecer ws = ActiveWorkbook.Sheets("Hoja1")
Ahora, repita el proceso nuevamente, creando un segundo botón pulsador. Haga el título calcular 7% de impuestos.
Haga doble clic en ese botón y pegue el mismo código, pero haga que el multiplicador de impuestos sea 0.07.
Ahora, según el 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 de ellos iniciará un cálculo de impuestos diferente y escribirá resultados diferentes en la columna de resultados.
Para enviar esto por mensaje de texto, seleccione el menú desarrollador y seleccione modo de diseño en el grupo controles en la cinta para deshabilitar el modo de diseño. Esto activará los botones pulsadores.
Intente seleccionar cada botón para ver cómo cambia la columna de resultados de impuestos.
Envíe por correo electrónico los resultados de los cálculos a alguien
¿Qué sucede si desea enviar los resultados de la hoja de cálculo a alguien por correo electrónico?
Puede crear otro botón llamado Hoja de correo electrónico para Boss utilizando el mismo procedimiento anterior. El código para este botón implicará el uso del objeto CDO de Excel para configurar los ajustes de correo electrónico SMTP y enviar los resultados por correo electrónico en un formato legible por el usuario.
Para habilitar esta función, debe seleccionar Herramientas y referencias. Desplácese hacia abajo hasta Microsoft CDO para Windows 2000 Library, habilítelo y seleccione Aceptar.
Hay tres secciones principales en el código que necesita crear para enviar un correo electrónico e incrustar los resultados de la hoja de cálculo.
El primero es configurar variables para contener el asunto, las direcciones de origen y destino y el cuerpo 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
Atenuar la última fila mientras dure
Dim StartCell como rango
Atenuar contador de filas como entero
Dim rng como rango, celda como rango
Dim fltTax como doble
Establecer ws = ActiveWorkbook.Sheets("Hoja1")
StrSubject = "Impuestos pagados este trimestre"
StrFrom = "[email protected]"
StrTo = "[email protected]"
StrCC = ""
StrBcc = ""
StrBody = "El siguiente es el desglose de los impuestos pagados sobre las ventas este trimestre".
Por supuesto, el cuerpo debe ser dinámico según los resultados que haya en la hoja, por lo que aquí deberá agregar un ciclo que recorra el rango, extraiga los datos y escriba una línea a la vez en el cuerpo.
Establezca StartCell = Range("A1")'Find Last Row y ColumnLastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).RowSet rng = ws.Range(ws.Cells(2, 4) ), ws.Cells(LastRow, 4))rowCounter = 2strBody = strBody & vbCrLfFor Cada celda en rng strBody = strBody & vbCrLf strBody = strBody & "Vendimos" & Cells(rowCounter, 3).Value & " of " & Cells (rowCounter, 1).Valor _ & " para " & Cells(rowCounter, 4).Value & " e impuestos pagados de " & Cells(rowCounter, 5).Value & "." contadorfilas = contadorfilas + 1Siguiente celda
La siguiente sección involucra la configuración de SMTP para que pueda enviar correos electrónicos a través de su servidor SMTP. Si usa Gmail, normalmente es su dirección de correo electrónico de Gmail, su contraseña de Gmail y el servidor SMTP de Gmail (smtp.gmail.com).
Establecer CDO_Mail = CreateObject("CDO.Message") En caso de error Ir a Error_HandlingSet CDO_Config = CreateObject("CDO.Configuration")CDO_Config.Load -1Establecer SMTP_Config = CDO_Config.FieldsWith 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 protected]".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 .UpdateEnd WithWith CDO_Mail Set .Configuration = CDO_ConfigEnd With
Reemplace [email protected] y la contraseña con los detalles de su propia cuenta.
Finalmente, para iniciar el envío del correo electrónico, inserte el siguiente código.
CDO_Mail.Asunto = strAsunto
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Correo.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Enviar
Manejo de errores:
Si Err.Descripción <> "" Entonces MsgBox Err.Descripción
Nota: si ve un error de transporte al intentar ejecutar este código, es probable que su cuenta de Google esté bloqueando la ejecución de aplicaciones menos seguras. Deberá visitar la página de configuración de aplicaciones menos seguras y activar esta función.
Una vez habilitado, se enviará su correo electrónico. Esto es lo que le parece a la persona que recibe su correo electrónico de resultados generado automáticamente.
Como puede ver, hay muchas cosas que puede automatizar con Excel VBA. Intente jugar con los fragmentos de código que aprendió en este artículo y cree sus propias automatizaciones únicas de VBA.
