Una guía avanzada de VBA para MS Excel

Si recién está comenzando con VBA, entonces querrá comenzar a estudiar nuestra guía de VBA para principiantes. Pero si eres un experto experimentado en VBA y estás buscando cosas más avanzadas que puedes hacer con VBA en Excel, entonces sigue leyendo.

La capacidad de usar la codificación de VBA en Excel abre un mundo entero de automatización. Puede automatizar los cálculos en Excel, Pushbutton e incluso enviar correo electrónico. Hay más posibilidades para automatizar su trabajo diario con VBA de lo que puede darse cuenta de.

Esto establece la hoja de trabajo activa en Sábana, borra la hoja, se conecta al archivo utilizando la ruta del archivo que definió con el strfile variable, y luego el Con El bucle cicla a través de cada línea del archivo y coloca los datos en la hoja a partir de 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 Sábana.

Importar es solo el primer paso. A continuación, desea crear un nuevo encabezado para la columna que contendrá los resultados de sus cálculos. En este ejemplo, supongamos que desea calcular los impuestos del 5% pagados en la venta de cada artículo.

El orden de las acciones que su código debe tomar es:

1. Crear una columna de nuevos resultados llamado impuestos.
2. Recorrer el unidades vendidas columna y calcule el impuesto sobre las ventas.
3. Escriba los resultados de calcular a la fila apropiada en la hoja.

El siguiente código logrará todos estos pasos.

Dim Lastrow tanto tiempo
Dim StartCell como rango
Dim Rowcounter como entero
Dim rng como rango, celda como rango
Dim flttax como doble
Establezca startcell = range ("a1")
'Encuentra la última fila y columna
LASTROW = WS.Células (WS.Hilera.Cuenta, StartCell.Columna).Fin (xlup).Fila
Establecer rng = ws.Rango (WS.Células (2, 4), WS.Celdas (Lastrow, 4))
rowcounter = 2
Células (1, 5) = "impuestos"
Para cada celda en RNG
flttax = celda.Valor * 0.05
Celdas (Rowcounter, 5) = Flttax
RowCounter = RowCounter + 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 última fila de datos. Luego, el código boje a través de 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á los resultados que se muestran en la columna E.

Ahora, cada vez que abra su hoja de trabajo de Excel, se apagará automáticamente y obtendrá la copia más fresca 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!

Calcule los resultados del botón Presione

Si prefiere tener más control directo sobre cuándo se ejecutan los cálculos, en lugar de ejecutar 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é pasa 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 deje que el código de cálculo de impuestos se ejecute cuando presione el botón apropiado.

Usando la misma hoja de cálculo que se indicó, seleccione el Desarrollador pestaña y seleccionar Insertar desde el Control S Grupo en la cinta. Selecciona el presionar el botón Control ActiveX desde el menú desplegable.

Dibuje el botón de empuje en cualquier parte de la hoja lejos de donde irá cualquier dato.

Haga clic con el botón derecho en el botón Push y seleccione Propiedades. En la ventana Propiedades, cambie el título a lo que desea mostrar al usuario. En este caso podría ser Calcular el 5% de impuestos.

Verá este texto reflejado en el botón Push en sí. Cierra el propiedades ventana, y haga doble clic en el botón en sí mismo. Esto abrirá la ventana del editor de códigos y su cursor estará dentro de la función que se ejecutará cuando el usuario presione el botón de empuje.

Pegue el código de cálculo de impuestos de la sección anterior en esta función, manteniendo el multiplicador de tasa impositiva 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.Hojas ("Hoja1")

Ahora, repita el proceso nuevamente, creando un segundo botón Push. Hacer la leyenda Calcular el 7% de impuestos.

Haga doble clic en ese botón y pegue el mismo código, pero haga el multiplicador de impuestos 0.07.

Ahora, dependiendo 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 de ellos iniciará un cálculo fiscal diferente y escribirá diferentes resultados en la columna de resultados.

Para enviar un mensaje de texto con esto, seleccione el Desarrollador menú y seleccionar Modo de diseño formar el grupo de controles en la cinta para deshabilitar Modo de diseño. Esto activará los botones de presión.

Intente seleccionar cada botón Push para ver cómo cambia la columna del resultado de los "impuestos".

Resultados de cálculo de correo electrónico a alguien

¿Qué pasa si desea enviar los resultados en la hoja de cálculo a alguien por correo electrónico??

Podrías crear otro botón llamado Hoja de correo electrónico al jefe Usando el mismo procedimiento anterior. El código para este botón implicará usar el objeto CDO de Excel para configurar la configuración de correo electrónico SMTP y enviar un correo electrónico a los resultados en un formato legible por el usuario.

Para habilitar esta función, debe seleccionar Herramientas y referencias. Desplazarse hacia abajo a Microsoft CDO para la biblioteca de Windows 2000, Habilitarlo y seleccionar DE ACUERDO.

Hay tres secciones principales al código que necesita crear para enviar un correo electrónico e incrustar resultados de hoja de cálculo.

El primero es configurar variables para mantener el tema, hacia y desde las direcciones, y el cuerpo de 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 tanto tiempo
Dim StartCell como rango
Dim Rowcounter como entero
Dim rng como rango, celda como rango
Dim flttax como doble

Establecer ws = ActiveWorkBook.Hojas ("Hoja1")
strSubject = "Impuestos pagados este trimestre"
strfrom = "[email protected] "
strto = "[email protected] "
strcc = ""
strbcc = ""
strby = "El siguiente es el desglose de los impuestos pagados en las ventas este trimestre."

Por supuesto, el cuerpo debe ser dinámico dependiendo de qué resultados hay en la hoja, por lo que aquí deberá agregar un bucle que atraviese el rango, extrae los datos y escriba una línea a la vez para el cuerpo.

Establecer startcell = range ("a1") 'Buscar la última fila y columna lasstrow = ws.Células (WS.Hilera.Cuenta, StartCell.Columna).Fin (xlup).Fila set rng = ws.Rango (WS.Células (2, 4), WS.Celdas (LASTROW, 4)) ROWCOUNTER = 2 STRBOY = STRBOY & VBCRLF para cada celda en rng strBody = strBody & vbcrlf strby = strbody & "vendimos" y celdas (RowCounter, 3).Valor y "de" y celdas (Rowcounter, 1).Valor _ & "para" y celdas (Rowcounter, 4).Valor & "y impuestos pagados de" y celdas (Rowcounter, 5).Valor & "."RowCounter = Rowcounter + 1 Siguiente celda

La siguiente sección implica configurar la configuración SMTP para que pueda enviar un correo electrónico a través de su servidor SMTP. Si usa Gmail, esta es típicamente su dirección de correo electrónico de Gmail, su contraseña de Gmail y el servidor Gmail SMTP (SMTP.gmail.com).

Establecer cdo_mail = createObject ("CDO.Mensaje ") En Error GOTO ERROR_HANDLING SET CDO_CONFIG = createObject (" CDO.Configuración ") CDO_Config.Cargar -1 establecer SMTP_CONFIG = CDO_CONFIG.Campos con SMTP_Config .Artículo ("http: // esquemas.Microsoft.com/cdo/configuración/sendusing ") = 2 .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/smtpserver ") =" smtp.gmail.com " .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/smtpauthenticate ") = 1 .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/sendUsername ") =" correo electrónico@sitio web.com " .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/sendpassword ") =" contraseña " .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/smtpserverport ") = 465 .Artículo ("http: // esquemas.Microsoft.com/cdo/configuration/smtpusessl ") = true .Actualizar Fin con CDO_mail set .Configuración = CDO_CONFIG END con

Reemplace el correo electrónico@sitio web.com y contraseña con los detalles de su propia cuenta.

Finalmente, para iniciar el envío de correo electrónico, inserte el siguiente código.

CDO_MAIL.Sujeto = strsubject
CDO_MAIL.De = strFrom
CDO_MAIL.A = strto
CDO_MAIL.Textbody = strbody
CDO_MAIL.CC = STRCC
CDO_MAIL.BCC = STRBCC
CDO_MAIL.Enviar
Manejo de errores:
Si err.Descripción "" Entonces msgbox errar.Descripción

Nota: Si ve un error de transporte al intentar ejecutar este código, es probable porque su cuenta de Google está bloqueando las "aplicaciones menos seguras" para ejecutar. Deberá visitar la página de configuración de aplicaciones menos seguras y activar esta función.

Después de que eso esté habilitado, su correo electrónico se enviará. Así es lo que parece para la persona que recibe su correo electrónico de resultados generados automáticamente.

Como puede ver, hay muchas cosas que realmente puede automatizar con Excel VBA. Intente jugar con los fragmentos de código sobre los que ha aprendido en este artículo y cree sus propias automataciones VBA únicas.