Una guía avanzada de VBA para MS Excel

Hola leyente ! Aquí Gabriel con un nuevo tutorial para ustedes !
Espero que les sirva y nos vemos en la próxima

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

La capacidad de utilizar la codificación VBA en Excel abre todo un mundo de automatización. Puede automatizar cálculos en Excel, botones e incluso enviar correos electrónicos. Hay más posibilidades de automatizar su trabajo diario con VBA de las que imagina.

Guía avanzada de VBA para Microsoft Excel

El objetivo principal de escribir código VBA en Excel es que pueda 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
  • Envíe por correo electrónico los resultados del cálculo a alguien

Con estos tres ejemplos, debería poder escribir una variedad de su propio código avanzado de Excel VBA.

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 sobre 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 en su computadora todos los lunes, puede configurar su código VBA para que se ejecute cuando abra la 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 en su hoja de cálculo de Excel.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:temppurchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Abra la herramienta de edición Excel VBA y seleccione el objeto Sheet1. En los cuadros desplegables de objeto y método, elija Hoja de cálculo y Activar. Esto ejecutará el código cada vez que abra la hoja de cálculo.

Esto creará un Sub Worksheet_Activate () función. Pegue el código anterior en esa función.

Esto establece la hoja de trabajo activa en Hoja1, borra la hoja, se conecta al archivo utilizando la ruta de archivo que definió con el strFile variable, y luego la Con El ciclo 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 Hoja1.

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, digamos 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:

  1. Crear una nueva columna de resultados llamada impuestos.
  2. Recorre el unidades vendidas columna y calcular el impuesto sobre las ventas.
  3. Escriba los resultados del cálculo en la fila correspondiente de la hoja.

El siguiente código completará 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")

'Find Last Row and Column
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"

For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell

Este código busca 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 se muestran 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 resultados presionando el 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.

Los botones de control son útiles si desea controlar qué cálculos se utilizan. Por ejemplo, en el mismo caso 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 anterior, seleccione el Desarrollador pestaña y seleccione Insertar desde el Control S grupo en la cinta. Selecciona el presionar el botón Control ActiveX en el 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 pulsador 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 Calcule el 5% de impuestos.

Verá este texto reflejado en el botón pulsador. Cierra el propiedades ventana y haga doble clic en el botón pulsador. 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 As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Ahora, repita el proceso nuevamente, creando un segundo pulsador. Haz la leyenda Calcular el 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, 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 de impuestos diferente y escribirá resultados diferentes en la columna de resultados.

Para enviar un mensaje de texto, seleccione el Desarrollador menú y seleccione Modo de diseño Forme el grupo Controles en la cinta para deshabilitar 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 del cálculo a alguien

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

Podrías crear otro botón llamado Enviar hoja por correo electrónico al jefe 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 Biblioteca de Microsoft CDO para Windows 2000, habilítelo y seleccione Okay.

Hay tres secciones principales del código que debe crear para enviar un correo electrónico e incrustar los resultados de la hoja de cálculo.

La primera es configurar variables para contener el asunto, las direcciones Para y De, y el cuerpo del correo electrónico.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

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

Set StartCell = Range("A1")

'Find Last Row and Column
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
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

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

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With 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") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Reemplace [email protected] y la 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.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

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

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ás visitar el página de configuración de aplicaciones menos seguras y active esta función.

Una vez que esté habilitado, se enviará su correo electrónico. Así lo verá la persona que recibe el correo electrónico de resultados generado automáticamente.

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

Deja un comentario