Si recién está comenzando con VBA , querrá comenzar a estudiar nuestra guía de VBA para principiantes^{(VBA guide for beginners)} . 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 ^(VBA)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^{(Advanced VBA Guide For Microsoft Excel)}

El objetivo principal de escribir código VBA en ^(VBA)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^(Import) datos y realizar cálculos
• Calcular^(Calculate) los resultados de un usuario presionando un botón
• Enviar^(Email) 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.^{(Excel VBA)}

Importación de datos y realización de cálculos^{(Importing Data and Performing Calculations)}

Una de las cosas más comunes para las que la gente usa Excel es para 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^(Monday) , puede configurar su código VBA para que se ejecute cuando abra su hoja de cálculo por primera vez el ^(VBA)martes^(Tuesday) 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 As Worksheet, strFile As String

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

strFile = “c:\temp\purchases.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 de Excel VBA y seleccione el objeto ^{(Excel VBA)}Sheet1 . En los cuadros desplegables de objetos y métodos, seleccione Hoja^(Worksheet) de trabajo y Activar^(Activate) . 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 ^(CSV)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:

1. Crea una nueva columna de resultados llamada impuestos^(taxes) .
2. Recorra la columna de unidades vendidas^{(units sold)} y calcule el impuesto a las ventas.
3. Escriba los resultados del cálculo en la fila correspondiente de la hoja.

El siguiente código cumplirá con 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 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^{(Calculate Results From Button Press)}

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^(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^(Developer) y seleccione Insertar^(Insert) del grupo Controles^(Controls) en la cinta. Seleccione el botón ^{(push button)} 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^(Properties) . En la ventana Propiedades^(Properties) , cambie el título a lo que le gustaría mostrar al usuario. En este caso, podría ser Calculate 5% Tax .

Verá este texto reflejado en el propio botón pulsador. Cierre la ventana de propiedades^(properties) 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 As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Ahora, repita el proceso nuevamente, creando un segundo botón pulsador. Haga el título Calculate 7% Tax .

Haga doble clic en^{(Double-click)} 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 ^(Developer)Modo de diseño^{(Design Mode)} en el grupo Controles^(Controls) en la cinta para deshabilitar el Modo de diseño^{(Design Mode)} . 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^{(Email Calculation Results to Someone)}

¿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^{(Email Sheet to Boss)} utilizando el mismo procedimiento anterior. El código para este botón implicará el uso del objeto CDO de Excel^{(Excel CDO)} 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^{(Tools and References)} . Desplácese hacia abajo hasta Microsoft CDO para Windows 2000 Library^{(Microsoft CDO for Windows 2000 Library)} , habilítelo y seleccione Aceptar^(OK) .

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^(From) origen y destino 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 según los resultados que se encuentren 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.

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 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 ^(Gmail)SMTP^{(Gmail 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 del 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^(Note) : 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^{(less secure apps settings page)} 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 .

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then you’ll want to start out studying oυr VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

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

strFile = “c:\temp\purchases.csv”

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

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

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

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

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."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

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

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

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

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• La mejor guía de VBA (para principiantes) que necesitará

• Cómo crear una macro o secuencia de comandos de VBA en Excel

• Una guía para todas las extensiones de archivos de Excel y lo que significan

• Cómo eliminar líneas en blanco en Excel

• Arreglar el mensaje de error "No se puede verificar la licencia" de MS Office

• Use nombres de rango dinámico en Excel para menús desplegables flexibles

• Cómo combinar datos en varios archivos de Excel

• Cómo compartir un archivo de Excel para facilitar la colaboración

• Cómo usar referencias absolutas en Excel

• Cómo quitar líneas de cuadrícula en Excel

• Cómo crear una tabla dinámica simple en Excel

• Verifique todos los hipervínculos en un documento de MS Word en segundos

• ¿Qué es una matriz VBA en Excel y cómo programarla?

• Use el teclado para cambiar la altura de fila y el ancho de columna en Excel

• Cómo insertar CSV o TSV en una hoja de cálculo de Excel

• Cómo insertar rápidamente varias filas en Excel

• Agrupar filas y columnas en una hoja de cálculo de Excel

• Genere texto aleatorio o texto de Lorem Ipsum en MS Word

• Hojas de cálculo de Google vs Microsoft Excel: ¿cuáles son las diferencias?

• 4 formas de convertir Excel a Google Sheets