Introducción a VBA de Excel VBA (Visual Basic for Applications) es un lenguaje de macros utilizado para programar pequeñas aplicaciones dentro de un documento Excel, permitiendo la ampliación de la funcionalidad del mismo. ¡Aprende a programar macros Excel! VBA es una modalidad adaptada del conocido lenguaje de programación Visual Basic, incluida en la mayoría de las aplicaciones MS Office como Excel, Word y Access. Una diferencia entre VBA y Visual Basic es que el programa VBA no se puede separar del documento Excel, sino queda totalmente integrado dentro del libro de trabajo. Esta introducción a Excel VBA te enseñará como empezar a desarrollar tus propias macros Excel. VBA Excel y sus macros constituyen una ayuda potente para particulares y pequeñas y medianas empresas. Excel, y sobre todo la aplicación de macros VBA, mejora prácticamente cualquier proceso de trabajo, desde sencillos cálculos sobre la economía familiar hasta complejos modelos empresariales de Excel para crear informes, presupuestos y demás documentos financieros. ¿Todo el mundo puede aprender a programar macros? Si, sin duda. Mucha gente empieza por la grabación de macros, es decir grabar una secuencia de comandos, para luego reutilizarla. Un buen ejemplo es "Pegar - Valores", uno de los comandos VBA más utilizados. Luego se pasa a una programación VBA mas compleja para buscar soluciones más íntegras. ¿Qué es VBA? VBA significa Visual Basic for Applications. Es un lenguage de programación que surge de Visual Basic (VB). Se podría decir que es un dialecto de VB. VBA de Excel está adaptado a Excel, para trabajar con celdas, hojas, autofiltro etc (es decir, los objetos de la aplicación Excel). ¿Puedo acceder a VBA? Sí. VBA está incluido en el Excel 'normal y corriente'. Se instala con Excel por defecto. ¿Porqué VBA? VBA sirve para muchas cosas. Imagínate todas las tareas repetitivas que tienes que ejecutar todos los días. Un ejemplo - siempre vas aplicando el mismo formato a un grupo de celdas de un libro que te mandan todos los días. En vez de hacer un monton de clicks para obtener este formato, puedes automatizar el proceso, a un boton, o un atajo de teclado. También puedes crear aplicaciones que importan datos desde el libro mayor, reorganizan los datos, y crean informes personalizados para cada departamento. Casi no hay límites. VBA de Excel te permite interactuar no solo con otros libros Excel, sino con todos los programas Office, como Access, Word etc (menos InfoPath que forma parte del Office 2003). Origen de VBA Es de Microsoft, y surge de BASIC (Beginner´s All-purpose Symbolic Instruction Code, o Código de Instrucciones Simbólicas de Uso General para Principiantes), el cual en su turno es un lenguage que tiene muchos años ya (desde los años sesenta). ¿Existen varios VBAs? VBA existe para todas las aplicaciones de Microsoft Office, y estas se llaman 'host aplicacions'. Así que hay VBA para Excel, otro para Word etc. Cada VBA se parece al resto, pero también tienen diferencias, por servir distintos 'host aplications'. Excel utiliza, por supuesto, VBA para Excel. El editor VBA de Excel El editor VBA sirve para controlar y manipular tu código VBA, tanto de macros como de completas aplicaciones VBA y add-ins. Todo parece mucho a Visual Basic, con la diferencia de que VBA tiene otros objetos (hoja, celda etc)/métodos/eventos. Contenido de un proyecto En la columna de izquierda tenemos las piezas que forman parte del proyecto. En este ejemplo tenemos dos libros abiertos: Libro1.xls y Personal add-ins.xla (este segundo es un Add-in/Complemento). Este Libro y las hojas del libro Excel Objetos Contenedores para el código del proyecto Módulos Tus propios formularios, con controles (botones, menús... + Formularios código A la derecha vemos el marcado del Objetos/Módulos/Formularios. Qué es una macro Definiciones La definición más común de una macro es que se trata de un pequeño programa que sirve para automatizar tareas repetitivas. Normalmente se guarda en el mismo libro Excel en cual se va a ejecutar, pero se puede guardar en otros libros Exel, tanto como en libros Add-in. Cuando ese programa crezca, podrá empezar a llamarse programa. Pero si hablamos de un programita que sirve para ejecutar sencillas, repetitivas tareas, debemos llamarlo macro. El nombre macro vendrá de 'macro-instrucciones' que así se llamaban antes. Supongo que se referirían a 'instrucciones que iban más allá de los menús'. ¿Para qué sirven las macros? Para cualquier trabajo realizado en Excel, prácticamente. Puedes automatizar procesos, importaciones de Access, dar un formato predefinido (por ti) a las celdas seleccionadas, atajo para mandar el último Pívot de Excel a Outlook... Cómo grabar una macro Excel VBA Para aprender a programar VBA de Excel la mejor manera de empezar es probablemente la grabación de macros en Excel. Es fácil grabar tus propias macros. Y con un ligero retoque, salen todavía mejor. Preparar la grabación Entra Herramientas - Macro - Grabar nueva macro. Nombre de la macro Método abreviado (atajo) Guardar macro en Pon un nombre descriptivo, por ejemplo DosDecimales. Si quieres espacio entre palabras, pon Dos_Decimales. Si quieres que la macro se active desde el teclado, pon la combinación, por ejemplo CTRL + L. Presionando MAYUSCULA te darán todavía más combinaciones. O se guarda en Este Libro, o en Libro de Macros Personales. Si eliges Libro de Macros Personales, la macro va a estar disponible desde cualquier libro de Excel. Descripción Si quieres puedes poner un texto explicativo el cual se incluirá en el código de la macro. Presiona OK. Ahora la macro grabará todo lo que pase a tu libro de Excel. Si seleccionas celda H1, esa misma acción se grabará. Después, al ejecutar la macro, esa celda se seleccionará (¡sorpresa...!). Entonces, mientras grabas, selecciona celda H1, cambia el formato a Número - 2 decimales. Paramos la grabación. Editar la macro grabada Entra el VBA editor (Herramientas- Macro - Visual Basic Editor). Entra Módulos - Módulo 1. A la derecha se ve lo que VBA te ha grabado. El código hace que Excel selecione celda H1, y que luego aplique un formato de número de dos decimales. Es decir, la macro solo actuará sobre la celda H1. Al cambiar el código un poco podremos hacer que VBA cambie el formato a cualquier celda que tengas seleccionada. Limpia el código para que quede el siguiente marcado. Selection.NumberFormat = "0.00" Ejecutar la macro Ahora, vuelve a la hoja, y prueba tú nueva macro. Selecciona un rango de celdas, aplica el atajo (CTRL+L). También puedes ejecutar la macro desde Herramientas - Macro - Macros. Macros personales en Excel ¿Qué son las macros personales? Son macros que están accesibles desde cualquier libro Excel. Estas macros se guardan en un libro especial llamado Personal.xls (Libro de Macros Personales). Al abrir Excel, este libro se cargará, ocultamente. De este modo, las macros que grabes allí siempre estarán accesibles. Ideal para tus propios atajos, cómo por ejemplo Separador de miles, Pegar valor etc. Sobre el libro Personal.xls Es un libro Excel normal y corriente, pero con unas características especiales. » Tiene el nombre de Personal.xls » Contiene código VBA para tus macros, funciones etc. » Se carga automáticamente al iniciar Excel » Tiene el atributo Hidden (escondido) activado (Ventana - Mostrar) » Se guarda en uno de estos sitios (normalmente) c:\Program Files\Microsoft Office\Office\InicioXL (XLStart) c:\Documents and Settings\ -user name\Application Data\Microsoft\Excel\InicioXL ¿Cómo se crea el libro Personal.xls? Hay dos maneras de crear este libro. 1. Por tu cuenta: Crea un libro nuevo, guardalo en la carpeta InicioXL indicado arriba, vuelves a Excel para luego ocultarlo (Ventana - Ocultar). Luego, para colocar macros dentro de tu nuevo libro, puedes introducirlas manualmente (editor VBA) o grabar una macro nuevo y guardarla dentro del Libro de Macros personales. Para grabar una macro ver Grabar una macro. 2. Dejar que Excel te ayude: Al grabar una macro indicas donde guardarla. Si eliges Libro de macros personales, Excel creará el libro Personal.xls en la carpeta InicioXL. Para grabar una macro ver Grabar una macro. Alternativas a macros personales En vez de colocar tus macros en el Libro de Macros Personales, puedes crear un Add-in (Complemento). Este es un libro [.xla] que se carga cada vez que abres Excel (igual que el Libro de Macros Personales), que no está ocultado pero tampoco visible. VBA Variables Enlace Descripción Tipos de Referencia de variables variables Declarar variables La importancia de declarar las variables. Tipos de variables Abajo presentamos los más frecuentes tipos de variable de Excel VBA. Tipo Bytes Descripción Comentario 1 0-255 Integrales Byte positivos 1 True/False Valores discretos Boolean 2 -32.768 hasta + ... Integrales Integer 4 -2.147.483.648 hasta + ... Integrales Long (long int.) 4 -3,402823 E38 hasta + ... Decimales Single 8 -1,79769313486232 E308 hasta + Decimales Double ... 8 15 díg. + 4 decimales Número, 4 dec. Currency 8 1-ene-100 hasta 31-dic-9999 Fechas Date 4 referencia a objetos Ej. "Workbook" Object 10+ carácteres Ascii (texto) Texto String 1+ carácteres Ascii, longitud predef. Texto String (long. fija) 16+ cualquier tipo de datos Cubre la mayoría Variant Declarar variables ¿Porqué declarar variables? El código saldrá más estructurado Si no declaras un variable, no sabrás que tipo de datos contendrá. Es más seguro - evitarás errores tipográficos VBA te ayudará a poner los nombres correctos. Si no, un error tipográfico puede parar el programa. El código trabajará más eficaz Variables no declaradas serán tratatos como Variants, las cuales ocupan mucha más memoria. Te ayudará a programar VBA te ayuda a elegir propiedades/métodos que corresponden a esa variable. Declarar variables Una variable se declara empleando el comando DIM. DIM significa Dimension, y viene del antiguo BASIC. Al declarar, puedes indicar el número de dimensiones que la variable va a tener (ej. guardar números dentro de una variable, en 3 dimensiones). Para que sea más fácil leer el código, pon un indicador en el nombre de la variable. Así basta con leer el nombre de la variable para saber de que tipo es. Puede ser str para String, int para Integer etc. Una alternativa al DIM es Public. La variable será accesible desde todas partes de tú proyecto VBA. Poner nombres explicativos Intenta poner nombres explicativos a las variables. Dim strCodigoPostal as String Dim datFechaRecogida as Date El nombre puede tener hasta 254 carácteres (por supuesto demasiado...). No puede empezar con una cifra. Algunos nombres son reservados para VBA/Excel, la cual te notificará al ejecutar. ¿Donde poner las declaraciones? VBA te deja declarar casi en cualquier sitio del código. No obstante, la posición puede dar resultados distintos. Por eso es recomendable seguir unas normas. Tipo de Ubicación Accesible declaración Encima del procedimiento Todos los procedimientos del Dim (antes del primer Sub) módulo. Antes de un procedimiento Ese procedimiento . Dim específico. Dentro de un procedimiento. Resto de ese procedimiento. Dim Encima del procedimiento Todos los procedimientos (de Public (antes del primer Sub) todos los módulos). VBA bucles Intro Para qué sirven los bucles Los bucles sirven para repetir instrucciones varias veces. A lo mejor tienes una columna en Excel con 25.000 nombres, y quieres sacar las personas cuyos apellidos empieza con "Lo". En este caso se puede emplear un bucle que evalua todos estos nombres según el criterio "Lo", uno por uno. Hay dos tipos generales de bucles: Bucles Do... Loop Repite las instrucciones mientras/hasta etc. una condición es TRUE/VERDADERO. Do while... Loop Repite las instrucciones mientras una condición es TRUE/VERDADERO. Do until... Loop Repite las instrucciones hasta que una condición se convierta en TRUE/VERDADERO. While... Wend Igual al bucle Do while... Loop. Bucles For... Next Repite las instrucciones un número especificado de veces. For i... Next Repite las instrucciones i veces. Do While... Loop /While... Wend Instrucción que repite las instrucciones mientras una condición es TRUE/VERDADERO. Ejemplo Excel tiene valores en las celdas B1:B100. Quieres buscar la primera celda que tenga un valor más alto/igual que 1,50. i = 1 Do While Cells(i, 2) "" f Cells(i, 1) >= 1.5 Then Exit Do i = i + 1 Loop MsgBox "El valor se encontró en fila no. " & i i = la línea donde empezamos el bucle. Ponemos i = 1. Cells(i, 2) significará línea i (=1), columna 2. Es decir celda B1. Al final del bucle, i nos dará la línea que buscamos. Do While Cells(i, 2) "" significa que queremos que un bucle siga hasta que no hayan más celdas con valores en esta columna. Aplicamos la condición a cada celda, para luego salir del bucle (Exit Do) si encuentra un valor igual a ó más alto que 1,50 (VBA utiliza punto en vez de coma para decimales). Al final devolvemos un Messagebox para presentar el resultado. Do until... Loop Instrucción que repite las instrucciones hasta que una condición se convierta en TRUE/VERDADERO. Ejemplo Una hoja Excel tiene datos (en este caso nombres) en las celdas A1:A5. Queremos que el bucle pare en "Alexis". A B 1 Alberto 2 Alejandro 3 Alex 4 Alexis 5 Allain 6 i = 1 Do Until Cells(i, 1) = "Alexis" i = i + 1 Loop MsgBox "El nombre Alexis se encontró en la línea " & i i = la línea donde empezamos el bucle. Ponemos i = 1. Cells(i, 1) significará línea i (=1), columna 1. Es decir celda A1. Al final del bucle, i nos dará la línea que buscamos. Do Until Cells(i, 1) = "Alexis" significa que queremos que un bucle que siga hasta que se encuentre el texto "Alexis". Al final presentamos una caja de diálogo para presentar el resultado, cual en este caso sería 4. For i... Next Esta instrucción se repite el número de veces (i) que tú indicas. Ejemplo Creamos un bucle sencillo. Queremos que se repita 4 veces, y que la variable intValor (al empezar = 1) se incremente con 2 cada vuelta. Este nos da el resultado intValor = 9 (1+2+2+2+2). Observa que el bucle tiene step 1. Esto significa que i se incrementa con 1 cada vuelta. Esto es, que si queremos un bucle que vaya para atrás, pondríamos step -1. intValor = 1 For i = 1 to 4 step 1 intValor = intValor + 2 Next i Inicio VBA Bucles For Each... Next For Each... Next • • • • Instrucción que repite las instrucciones según el número de objetos especificados. Por ejemplo, For each Cell de un rango en Excel. Ejemplo En este ejemplo vamos a construir un bucle que evalua cada celda de un rango. El rango será celdas A1:A5, que se escribe como Range(Cells(1, 1), Cells(5, 1). Con el Exit For salimos del bucle al cumplir la condición. Dim rngArea rngArea = Range(Cells(1, 1), Cells(5, 1)) For Each Cell In rngArea If Cell = "Alexis" Then MsgBox "¡Encontró Alexis" Exit For End If Next VBA Excel - ejemplos prácticos de macros Una de las utilidades principales de VBA es la de automatizar tareas cotidianas, como por ejemplo crear un atajo (del teclado) para la función de 'Pegar como valor' en Excel. Estas pequeñas aplicaciones suelen denominarse 'macros', pero Excel VBA también sirve para la programación de aplicaciones más complejas, como servicios de bases de datos y la manipulación de archivos. Ejemplos de macros VBA de Excel Esta lista de ejemplos VBA de Excel recopila algunas pequeñas aplicaciones de código VBA. Recuerda - haz una copia de seguridad antes de aplicar código VBA a tus archivos. Excel y otros programas Método para abrir-copiar-modificar-cerrar Excel desde VB. Marcador de teléfono Cómo marcar el teléfono desde Excel (API). Importar texto de Word a Excel Aplicamos la función 'Create Object' de VBA para importar textos desde Word. Macro para pegar celdas Excel Mandar celdas Excel a una tabla nuevo de en tabla Word Word. Importar celdas de Excel a ADO de Excel VBA nos deja conectar con otro Excel libro Excel para importar datos. Excel y bases de datos Access a Excel (ADO) Crear/modificar archivo de texto Importar dBase IV a Excel Abrir archivo de texto en Excel Importar (ADO) una tabla Access a Excel. Código VBA - archivo de texto como base de datos. Importar una base de datos dBase IV a una hoja Excel. Ejemplo VBA cómo abrir archivos planos en Excel. Excel y Outlook Excel por e-mail Importar datos de Outlook a Excel Mandar hoja Excel por e-mail. Importar datos de Outlook a Excel. (VBA) Abrir Excel desde VB Libros y hojas Excel Libros Hojas Formatos Filas Columnas Libros, hojas etc. Código para insertar y ordenar las hojas Excel . Formatear celdas en Excel (VBA). VBA: Trabajar con filas. VBA: Trabajar con columnas. Repasar archivos/carpeta Método VBA Excel para repasar varios archivos de una carpeta. Exportar Excel a Visual Basic Excel permite la exportación de datos a un montón de aplicaciones como de Visual Basic, bases de datos, XML etc. Es este ejemplo Excel presentamos una solución para pasar datos desde celdas Excel a una variable Visual Basic. En concreto vamos a hacer lo siguiente: • • • • Abrimos un archivo Excel desde VB Leemos el contenido del archivo Excel Pasamos el contenido Excel a una variable Visual Basic Por último Cerramos la hoja de cálculo Configuraciones Excel/Visual Basic Para que esto funcione, Visual Basic necesitará cargar los objetos de Excel. Por eso, no olvides marcar Microsoft Excel x.xx Object Library en tu Visual Basic - Proyecto/Referencias Código Visual Basic para importar datos Excel Private Sub LeerExcel() 'dimensiones Dim xlApp As Excel.Application Dim xlLibro As Excel.Workbook Dim xlHoja As Excel.Worksheet Dim varMatriz As Variant Dim lngUltimaFila As Long 'abrir programa Excel Set xlApp = New Excel.Application 'xl.Visible = True 'abrir el archivo Excel (archivo en otra carpeta) Set xlLibro = xlApp.Workbooks.Open _ ("c:\Fax2.xls", True, True, , "") 'abrir el archivo Excel (archivo en la misma carpeta) Set xlLibro = xlApp.Workbooks.Open(App.Path & _ "\Fax2.xls", True, True, , "") Set xlHoja = xlApp.Worksheets("Hoja1") '1. Si conoces el rango a leer 'varMatriz = xlHoja.Range("A1:C10").Value '2. Si no conoces el rango lngUltimaFila = _ Columns("A:A").Range("A65536").End(xlUp).Row varMatriz = xlHoja.Range(Cells(1, 1), _ Cells(lngUltimaFila, 1)) 'utilizamos los datos... Text1.Text = varMatriz(27, 1) 'cerramos el archivo Excel xlLibro.Close SaveChanges:=False xlApp.Quit 'reset variables de los objetos Set xlHoja = Nothing Set xlLibro = Nothing Set xlApp = Nothing End Sub Marcar el teléfono desde Excel Resumen Si tu pc está equipado con módem podrás realizar llamadas desde Excel, teniendo el número en una lista. A través de una API (instrucción Windows) podemos hacer que Excel arranque el Marcador Telefónico. Para usar tienes que marcar la celda con el nº, y luego dar marcar. Configuraciones Hay una parte del código, que se puede configurar, por si alguien quiere llamar desde un lugar donde se trabaja con una central telefonica. Generalmente para tomar una linea te pide que marques el (0). PhoneCall "0" + strNumero, strNombre Si tienes linea diecta, debes quitar "0". PhoneCall strNumero, strNombre El código ' Option Explicit Private Declare Function tapiRequestMakeCall _ Lib "TAPI32.DLL" _ (ByVal Dest As String, _ ByVal AppName As String, _ ByVal CalledParty As String, _ ByVal Comment As String) As Long Private Sub PhoneCall(sNumber As String, sName As String) Dim lRetVal As Long lRetVal = tapiRequestMakeCall(Trim$(sNumber), _ "Marcando", Trim$(sName), "") If lRetVal 0 Then End If End Sub Public Sub MarcarTelefono() Dim strNumero As String Dim strNombre As String strNombre = ActiveCell.Value strNumero = ActiveCell.Offset(0, 1).Value PhoneCall "0" + strNumero, strNombre End Sub Objecto Application de Excel VBA El objeto Application de VBA nos da información del usuario/máquina. En este artículo presentaremos unas aplicaciones del un objeto muy básico de Excel/VBA: Application. Este representa todo el conjunto del programa Excel y sus libros de trabajo. Unos ejemplos de propiedades del objeto Application El objeto Application tiene muchas propiedades. Hay unas que pueden ser interesantes para el programador de macros VBA de Excel. Objeto Propiedad Application. UserName OrganizationName OperatingSystem Version ProductCode StandardFont StandardFontSize DecimalSeparator ActivePrinter DefaultFilePath UserLibraryPath Sobre la versión de MS Excel arriba: Nº Versión 12 Excel 2007 11 Excel 2003 10 Excel 2002 ("XP") 9 Excel 2000 8 Excel 97 7 Excel 95 Devuelve Nombre del usuario Nombre de la empresa Sistema operativo Versión de MS Excel Código de MS Excel Fuente por defecto Tamaño fuente por defecto Carácter separador de miles Impresora por defecto Ruta de acceso por defecto Ruta a carpeta Add-ins Determinar idioma del usuario desde Excel VBA [a partir de Excel 2000] Este marcado sirve para averiguar el idioma de Excel del usuario. La función Application.International de Excel VBA nos ayuda. Lo único es que hay que saber los marcadores de telefonía de los países para descodificar la respuesta de la función. Ver por ejemplo esta lista aquí. El código Sub DeterminarPais() CodigoPais = Application.International(xlCountryCode) If CodigoPais = 1 Then MsgBox ("Hello") ElseIf CodigoPais = 34 Then MsgBox ("Hola") End If End Sub VBA y libros Excel Cerrar libro Excel (guardar cambios) ActiveWorkbook.Close ActiveWorkbook.Close Savechanges:=True ActiveWorkbook.Close(True) Cerrar libro Excel (sin guardar cambios) ActiveWorkbook.Close(False) ActiveWorkbook.Close Savechanges:=False Cerrar libro Excel (variable, sin guardar cambios) Application.DisplayAlerts = False Windows(Libro_mayor).Close Application.DisplayAlerts = True Abrir libro Excel (ruta fija) Workbooks.Open FileName:="C:\Trabajo\Informe.xls" Abrir libro Excel (diálogo) Msg = MsgBox("Elija archivo para abrir.", vbOKOnly, ("")) strArchivo = Application.GetOpenFilename On Error GoTo 99 Workbooks.OpenText Filename:=strArchivo If strArchivo = "" Then Exit Sub strArchivo = ActiveWindow.Caption 99: Exit sub Devolver nombre del libro Excel strNombre = ActiveSheet.Parent.FullName MsgBox ActiveWorkbook.FullName VBA y hojas Excel Nombre de la hoja (variable) 'asigna nombre variable a la hoja a variable strHoja = ActiveWindow.Caption Windows(strHoja).Activate 'para activar el libro del nombre asignado Insertar hoja nueva (elegir posición) ActiveWorkbook.Sheets.Add Before:=Worksheets("Informe1") Insertar hoja nueva (primera posición) Sheets("Informe1").Copy After:=Worksheets(Worksheets.Count) Mover hoja Worksheets("informe5").Move After:=Worksheets("Informe4") Ordenar hojas (orden alfabético) intNumeroHojas = ActiveWorkbook.Worksheets.Count For i = 1 To intNumeroHojas For j = i To intNumeroHojas If LCase(Worksheets(j).Name) < LCase(Worksheets(i).Name) Then Worksheets(j).Move Before:=Worksheets(i) End If Next j Next i Suprimir una hoja 'Informe' Application.DisplayAlerts = False For i = 1 To Sheets.Count Sheets(i).Activate xxx = ActiveCell.Worksheet.Name If xxx = "Informe" Then ActiveWindow.SelectedSheets.Delete End If Next Application.DisplayAlerts = True Seleccionar primera hoja ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Seleccionar última hoja ActiveWindow.ScrollWorkbookTabs Position:=xlLast Scrollrow - Imagen fija a un costado de la pantalla Si queremos que el usuario vea una imagen en el costado superior izquierdo de la pantalla, esté donde esté en la hoja, podemos aplicar el siguiente código. Trata de utilizar la propiedad SCROLLROW, que nos da la celda superior izquierdo de la pantalla visible. Y SCROLLCOLUMN de la columna por supuesto. Luego insertamos un comentario, en la cual ponemos una imagen y un poco de texto. El resultado será algo como El código Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim strRuta As String Dim intRowActual, intColumnActual As Double Dim intRow, intColumn As Double 'ruta a imagen strRuta = "C:\imagen.jpg" 'fila/columna actual intRowActual = ActiveCell.Row intColumnActual = ActiveCell.Column 'fila/columna de scrollRow intRow = ActiveWindow.ScrollRow + 1 intColumn = ActiveWindow.ScrollColumn 'insertamos comentario Cells.ClearComments With Cells(intRow, intColumn) .AddComment .Comment.Text Text:="Hola" .Comment.Visible = True End With 'añadimos imagen al comentario Cells(intRow, intColumn).Comment.Shape.Select True Selection.ShapeRange.Fill.UserPicture strRuta 'aparcamos en celda actual Cells(intRowActual, intColumnActual).Select End Sub VBA para proteger ciertas hojas de un libro Excel Con la ayuda de un truco de VBA de Excel puedes permitir que el usuario solo pueda abrir por ejemplo la primera hoja (sin palabra de paso) - pero no las otras (cuales requieren palabra de paso). Esta función no está integrada en Excel (sorprendentemente), por eso hay que recurrir a VBA. Eventos empleados para la protección Para esto vamos a utilizar el evento Workbook_SheetActivate, un evento que salta cada vez que alguna hoja se active. Es decir, el evento se activará cuando el usuario hace click sobre una etiqueta de una hoja. Si la hoja forma parte de las restringidas, Excel pedirá palabra de paso. Al introducir una palabra de paso erronea, se queda en la hoja anterior. Grado de seguridad de la macro para proteger hojas Cualquier persona puede entrar al código VBA para ver la palabra de paso. Por eso debes proteger el codigo (VBA editor - Click derecho sobre EsteLibro - Propiedades). Este método no da 100% de seguridad. Si activamos la protección de los módulos VBA tenemos bastante seguridad, pero hay que tener en cuenta que existen varios programas comerciales para resolver el tema de palabras de paso de Excel. Los procedimientos Pon este código en el contenedor 'EsteLibro' del editor VBA (Herramientas - Macro - Editor VBA). Luego tienes que poner que hojas/palabra de paso (ver 'Preparar modelo' del código). Código VBA para proteger hojas Excel Dim strStartHoja As String Dim strSegundaHoja As String Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim z As Integer Dim i As Integer Dim x As Boolean Dim varHoja As Variant Dim varPaso As Variant Dim varInput As Variant 'preparar modelo [admin. input] varHoja = Array("Sheet2", "Sheet3") 'las hojas a proteger... varPaso = "gofio" 'palabra de paso... [letras/números] 'desconectar otros Events (evitar un tipo de bucle) Application.EnableEvents = False 'comprobar hojas strSegundaHoja = Sh.Name For i = LBound(varHoja) To UBound(varHoja) If varHoja(i) = strSegundaHoja Then x = True Next i If x = False Then GoTo 99 'ocultar la hoja temporalmente z = ActiveWindow.Index Windows(z).Visible = False 'comparar palabra de paso varInput = InputBox("Palabra de paso:") If varInput varPaso Then Sheets(strStartHoja).Select 'volver a mostrar la hoja Windows(z).Visible = True 99: 'conectar Events Application.EnableEvents = True End Sub '************************************************* Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 'recordar hoja inicial strStartHoja = Sh.Name End Sub • • • Inicio VBA Ejemplos Crear enlaces a las hojas Excel Si nuestro libro Excel contiene muchas hojas (hasta 256) convendría hacer una lista de enlaces a cada hoja. Esto puede ayudar mucho a los 'navegantes' de nuestros libros. Y como siempre, esta lista se puede crear manualmente. O se puede recurrir a una macro de VBA, cual tardará al máximo un par de segundos para concluir el trabajo. Procedimiento Empezamos con un libro Excel cualquier, pero que tenga por lo menos dos hojas. Luego vamos a crear un módulo dentro de este libro. En este módulo escribimos el código. Crear el módulo Entra a Herramientas - Macros - Editor VBA. A la izquierda ves (si no lo ves CTRL+R) algo como "ProyectoVBA (Tu libro). Marca ese proyecto. Insertar - Módulo. Doble click en el módulo nuevo, y a la derecha sale un espacio blanco, es para el código. Herramientas - Referencias. Marca Microsoft ActiveX DataObjects x.x Library. Ahora cierras el editor VBA, vuelves a Excel. Guardamos el libro. El código Sub Links_hojas() Dim wrbLibro As Workbook Dim wrsHojaActiva As Worksheet, wsHoja As Worksheet Dim intFila, intColumna As Integer Set wrbLibro = ActiveWorkbook Set wrsHojaActiva = ActiveSheet 'en que fila/columna empezar la lista intFila = 4 intColumna = 1 'el bucle repasa todas las hojas For Each wsHoja In wrbLibro.Worksheets 'para excluir hoja de los links If wsHoja.Name = "Hoja4" Then GoTo ProxHoja 'crear links If wsHoja.Name wrsHojaActiva.Name Then wrsHojaActiva.HyperLinks.Add wrsHojaActiva.Cells(intFila, intColumna), _ "", SubAddress:="'" & wsHoja.Name & "'!A1", _> TextToDisplay:=wsHoja.Name intFila = intFila + 1 End If ProxHoja: Next wsHoja End Sub Ejecutar macro Para ejecutar esta macro entras a Herramientas - Macros. Allí encontrarás la macro "Conectar_Excel_ADO". Marca esta, y "Ejecutar". Macro VBA para eliminar vínculos Excel Con esta macro podrás eliminar vínculos a otras hojas/libros. » Elimina vínculos a otras hojas/libros » Referencias dentro de la hoja se mantienen. » Los vínculos se convierten en valores. » Antes de todo, ¡recuerda hacer una copia de seguridad! Copia de seguridad Guarda una copia de seguridad de tu libro. Hazlo porque nunca sabes. Crear el módulo Entra a Herramientas - Macros - Editor VBA. A la izquierda ves (si no lo ves CTRL+R) algo como "ProyectoVBA (Tu libro). Marca ese proyecto. Insertar - Módulo. Doble click en el módulo nuevo, y a la derecha sale un espacio blanco, es para el código. Herramientas - Referencias. Marca Microsoft ActiveX DataObjects x.x Library. Ahora cierras el editor VBA, vuelves a Excel. Guardamos el libro. El código Sub QuitarVinculos() Dim Dim Dim Dim varVinculo As Variant wrsHoja As Worksheet objCelda As Object varMsg As Variant '(0) Un pequeño control varMsg = MsgBox("¿Ha guardado una copia de seguridad?", vbYesNo) If varMsg = 7 Then Exit Sub '(1) Quitar vínculos externos (a otros libros) varLink = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(varLink) Then i = 1 Do Until IsEmpty(varLink) On Error GoTo 9 ActiveWorkbook.BreakLink Name:=varLink(i), _ Type:=xlLinkTypeExcelLinks i = i + 1 Loop End If 9: '(2) Quitar vínculos internos (a otras hojas) On Error Resume Next For Each wrsHoja In ActiveWorkbook.Worksheets For Each objCelda In wrsHoja.UsedRange.SpecialCells(xlCellTypeFormulas, 23) If InStr(objCelda.Formula, "!") Then objCelda.Value = objCelda.Value Next Next End Sub Ejecutar macro Para ejecutar esta macro entras a Herramientas - Macros. Allí encontrarás la macro "Conectar_Excel_ADO". Marca esta, y "Ejecutar". Macro VBA para crear archivos de cada hoja Excel Con un mínimo de código vamos a crear archivos independientes de las hojas de un libro. Un ejemplo sería un libro con 124 hojas, una hoja por empleado, y ahora queremos separar las hojas de cada colega y crear libros independientes. Procedimiento Empezamos con el libro de una o varias hojas. Luego vamos a crear un módulo dentro de este libro. En este módulo escribimos el código. Crear el módulo Entra a Herramientas - Macros - Editor VBA. A la izquierda ves (si no lo ves CTRL+R) algo como "ProyectoVBA (Tu libro). Marca ese proyecto. Insertar - Módulo. Doble click en el módulo nuevo, y a la derecha sale un espacio blanco, es para el código. Herramientas - Referencias. Marca Microsoft ActiveX DataObjects x.x Library. Ahora cierras el editor VBA, vuelves a Excel. Guardamos el libro. Código Sub Crear_archivos_de_hojas() Dim strHoja, strStartHoja, strRuta As String Dim i As Integer Application.ScreenUpdating = False strStartHoja = ActiveCell.Worksheet.Name 'bucle todas hojas For i = 1 To Sheets.Count 'copia la hoja a libro nuevo Sheets(i).Activate strHoja = ActiveCell.Worksheet.Name Sheets(strHoja).Copy 'donde guardar los archivos creados strRuta = "C:\excel\vba\ejemplos" 'guarda el libro nuevo ActiveWorkbook.SaveAs Filename:=strRuta & "\" & strHoja, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close Savechanges:=True 'repetir bucle' Next Sheets(strStartHoja).Activate Application.ScreenUpdating = True End Sub Ejecutar macro Para ejecutar esta macro entras a Herramientas - Macros. Allí encontrarás la macro "Conectar_Excel_ADO". Marca esta, y "Ejecutar". Columnas de Excel y VBA Presentamos unas macros para trabajar con columnas Excel desde Excel VBA. Encontrar última columna (buscar al revés) Sub EncontrarUltimaColumna() Dim intUltimaCol As Integer If WorksheetFunction.CountA(Cells) > 0 Then intUltimaCol = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column MsgBox intUltimaCol End If End Sub Encontrar última columna (en fila especificada) Sub EncontrarUltimaColumna() Dim intUltimaCol As Range If WorksheetFunction.CountA(Rows(1)) > 0 Then Set intUltimaCol = Range("IV1").End(xlToLeft) MsgBox intUltimaCol.Address End If End Sub Suprimir columnas vacías en el rango Sub SuprimirColumnas() Dim cCount As Integer, c As Integer Dim DeleteRange as Range DeleteRange =("A1:B10") If DeleteRange Is Nothing Then Exit Sub 'Ejemplo: DeleteEmptyColumns Range("A1:Z1") If DeleteRange.Areas.Count > 1 Then Exit Sub With DeleteRange cCount = .Columns.Count For c = cCount To 1 Step -1 If Application.CountA(.Columns(c)) = 0 _ Then .Columns(c).EntireColumn.Delete Next c End With End Sub Suprimir cada n-columnas Sub SuprimirColumnas() Dim rCount As Long, r As Long Dim DeleteRange as range DeleteRange =("A1:B10") If DeleteRange Is Nothing Then Exit Sub If DeleteRange.Areas.Count > 1 Then Exit Sub If n < 2 Then Exit Sub With DeleteRange cCount = .Columns.Count For c = n To cCount Step n - 1 .Columns(c).EntireColumn.Delete Next c End With End Sub Filtrar con colores en Excel Marcar encabezado de columnas filtradas Este ejemplo Excel VBA surge en base a que si uno filtra con mucho criterios, pasa que a veces nos perdemos que columnas tenemos filtrado. En este caso se utiliza una macro, para que resalte el encabezado de las columnas filtradas. El usuario tiene la posibilidad de elegir el color que prefiere. En este caso tiene unos ejemplos pero se puede agregar más colores. Contenedor del código El código debe ir en el módulo VBA correspondiente a la hoja para filtrar, por utilizar el suceso Worksheet_Calculate(). Ver ejemplo. El código Option Explicit ' 'ayudaexcel@ yahoo.com.ar Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer If ActiveSheet.AutoFilterMode Then Set af = ActiveSheet.AutoFilter iFilterCount = 1 For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = Range("color") Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = xlNone End If iFilterCount = iFilterCount + 1 Next fFilter Else Rows(1).EntireRow.Interior.ColorIndex = xlNone End If End Sub Filas Excel - macros VBA Encontrar última fila intUltimaFila = _ Columns("A:A").Range("A65536").End(xlUp).Row Encontrar última fila intUltimaFila = _ ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Encontrar última fila MaxRow = Cells.SpecialCells(xlLastCell).Row MaxCol = Cells.SpecialCells(xlLastCell).Column Encontrar última celda (buscar al revés) Dim lngUltimaCelda As Long If WorksheetFunction.CountA(Cells) > 0 Then lngUltimaCelda = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row MsgBox lngUltimaCelda End If Encontrar última fila (en columna especificada) Dim intUltimaFila As Range If WorksheetFunction.CountA(Columns(1)) > 0 Then Set intUltimaFila = Range("65536").End(xlUp) MsgBox intUltimaFila.Address End If Suprimir filas vacías intLastRow = Columns("A:A").Range("A65536").End(xlUp).Row For r = intLastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r Suprimir filas vacías Dim intNumDeFilas As Long Selection.SpecialCells(xlCellTypeLastCell).Select intNumDeFilas = Selection.Row For i = 1 To intNumDeFilas If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete End If Next Suprimir filas vacías intUltimaFila = _ ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For r = intUltimaFila To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r Suprimir filas por condición Dim rngString As Range Do Set rngString = Cells.Find("Aglis", MatchCase:=False, _ LookAt:=xlPart, LookIn:=xlValues) If Not rngString Is Nothing Then rngString.EntireRow.Delete End If Loop Until rngString Is Nothing Suprimir filas vacías por dos condiciónes X, Y For i = intUltimaFila To 1 Step -1 Let strTest= Application.Cells(i, 2) If strTest "X" And strTest "Y" Then Rows(i).Delete Next i El temporizador de Excel VBA Si queremos que una instrucción se haga automáticamente, a intervalos fijos, podemos recurrir al método Application.OnTime. Procedimientos Vamos a necesitar tres sencillas macros. 1. StartTemporizador - Iniciar el temporizador 2. Tu_Sub - Cualquier instrucción 3. StopTemporizador - Cerrar el temporizador Luego hacen falta dos botones en la hoja, uno para la macro StartTemporizador, y otro para la StopTemporizador. Declaraciones Por encima de las macros descritas abajo, hacemos las declaraciones. Public datHora As Date Public Const conIntervalo = 60 'un minuto Public Const conRunMacro = "Tu_Sub" 'tu proced. Iniciar temporizador La primera macro inicia el temporizador. Sub StartTemporizador() datHora = Now + TimeSerial(0, 0, conIntervalo) 'iniciar el temporizador Application.OnTime _ Earliesttime:=datHora, _ Procedure:=conRunMacro, _ Schedule:=True End Sub Tu procedimiento Aquí pones lo que quieres que Excel haga periodicamente. Sub Tu_Sub() MsgBox datHora 'o cualquier instrucción 'reiniciar el temporizador StartTemporizador End Sub Cerrar temporizador El temporizador hay que cerrarlo 'manualmente'. Sub StopTemporizador() On Error Resume Next 'desactivar el temporizador Application.OnTime _ Earliesttime:=datHora, _ Procedure:=conRunMacro, _ Schedule:=False End Sub Todo el código Ahora, el módulo de las macros descritas arriba debe tener el siguiente aspecto. Public datHora As Date Public Const conIntervalo = 60 'un minuto Public Const conRunMacro = "Tu_Sub" 'tu proced. Sub StartTemporizador() datHora = Now + TimeSerial(0, 0, conIntervalo) 'iniciar el temporizador Application.OnTime _ Earliesttime:=datHora, _ Procedure:=conRunMacro, _ Schedule:=True End Sub Sub Tu_Sub() MsgBox datHora 'o cualquier instrucción 'reiniciar el temporizador StartTemporizador End Sub Sub StopTemporizador() On Error Resume Next 'desactivar el temporizador Application.OnTime _ Earliesttime:=datHora, _ Procedure:=conRunMacro, _ Schedule:=False End Sub WAIT - Mostrar un formulario VBA durante un tiempo predeterminado Con el método WAIT puedes hacer que un formulario se cierre después de un tiempo determinado. Esto puede ser útil para presentar información al usuario ('La importación ha terminado con éxito', 'El archivo está guardado' etc). El truco está en utilizar el método WAIT de VBA. Procedimiento Crea un formulario 'frmMensaje' con el mensaje que quieres que aparezca. Añade este código al formulario. El ejemplo nos dice que la rutina se va a esperar ('Wait') hasta la hora Now + TimeValue("00:00:04") Es decir la hora actual más 4 segundos. Sub Importar_Access() Private Sub UserForm_Activate() Application.Wait Now + TimeValue("00:00:04") frmMensaje.Hide End Sub Luego, para mostrar el formulario en cualquier parte de tu programa, aplicas este código. frmMensaje.Show En el ejemplo de abajo, el formulario se mostrará al abrir el libro (ponemos el código en el contenedor de código VBA 'EsteLibro'. Private Sub Workbook_Open() frmMensaje.Show End Sub Crear una barra de progreso en Excel Un ejemplo de una macro VBA de como crear una barra de progreso (progress bar) para incluir en nuestras aplicaciones y plantillas Excel. De esta forma podemos añadir un medidor de progreso gráfico a la barra de estado de Excel. El código Option Explicit Sub BarraDeProgreso() Dim R As Integer Dim MT As Double For R = 1 To 180 MT = Timer Do Loop While Timer - MT < 0.05 Application.StatusBar = "Progress: " & R & " de 180: " & _ Format(R / 180, "Percent") & " --- " & "Cumplimiento" DoEvents Next R Application.StatusBar = False End Sub Como armar un reloj en Excel (método OnTime). Sobre el método de Excel VBA "OnTime" El método OnTime de Excel ejecuta una macro en una hora designada o en intervalos fijos. Código VBA empleado para el método OnTime Sub Reloj() Range("A1").Formula = "=NOW()" Application.OnTime Now + TimeValue("00:00:01"), "reloj" End Sub Sub auto_Open() Call Reloj End Sub Imprimir la hora en la hoja Excel Según el código VBA descrito arriba, Excel pondrá el tiempo en la celda A1. Para devolver fragmentos (hora/minuto/segundo), emplea formulas como = HORA("A1") = MINUTO("A1") = SEGUNDO("A1") Formatos Excel y VBA Redondear celdas Set Area = Selection For Each Cell In Area z = Round(Cell, 2) Cell.Value = z Cell.NumberFormat = "#,##0.00" Next Cell Formatear fuente Cells.Select With Selection.Font .Name = "MS Sans Serif" .Size = 10 End With Líneas de división ActiveWindow.DisplayGridlines = False Indice de colores ActiveWorkbook.Colors(44) = RGB(236, 235, 194) 'verde Colorear rango Range("A1:B10").Interior.ColorIndex = 44 Cambiar entre estilos A1 / RC Application.ReferenceStyle = xlA1Application.ReferenceStyle = xlR1C1 Excel VBA y la paleta de colores Excel emplea una paleta de 56 colores predefinidos. Puedes cambiar estos colores desde Herramientas - Opciones - Color o desde código VBA. Abajo presentamos unos ejemplos de código VBA para modificar la paleta de colores del libro Excel. Por desgracia, a muchos de nosotros los colores de la hoja Excel predefinidos por Microsoft parecen bastante fuertes, y a veces hacen que la hoja sea difícil de leer. Los colores forman parte del diseño del libro, son importantes para una buena presentación de un informe. Pero por regla general tampoco se debe pasar utilizando demasiado color. Excel y Colores RGB Excel expresa colores del formato RGB (Red, Green, Blue). Red, Green, Blue son variables cuales expresan el grado de estos colores, valores entre 1 y 255. Tambien podemos expresar el color en formato HEX (hexadecimal). KfmDesign presenta una buena página de Colores RGB y HEX. Para cambiar entre RGB y HEX necesitamos un Conversor RGB HEX. Cambiar colores de la paleta Excel Entonces, ¿qué podemos hacer para que Excel siempre tenga los colores definidos por ti? Una solución sería crear una plantilla con nuestros formatos. Excel llamará esta plantilla al crear un libro nuevo. Otra solución es crear una macro cual nosotros podemos llamar cuando nos convenga. Esto nos da un poco más de control. En este ejemplo cambiaremos los colores 40 y 41 (de los 56) de la paleta. ActiveWorkbook.Colors(40) = RGB(234, 234, 234) ActiveWorkbook.Colors(41) = RGB(236, 235, 194) Código VBA para cambiar colores de celdas Excel Para cambiar los colores aplicados a una celda ejecutamos una de las siguientes instrucciones. Selection.Interior.ColorIndex = 40 Selection.Interior.ColorIndex = _ xlNone/xlColorIndexAutomatic/xlColorIndexNone Selection.Interior.Color = RGB(234, 234, 234) Selection.Interior.Color = ?000066? 'hexadecimal Resetear paleta de colores Para resetear los colores a los predefinidos de Excel, aplicamos el método ResetColors. ActiveWorkbook.ResetColors Copiar colores de otro libro Excel También se puede copiar (importar) la paleta de otro libro Excel. Para esto cambiamos la propiedad Colors del libro. ActiveWorkbook.Colors = Workbooks("C:\MiLibroDeColores.xls").Colors Devolver colores del libro Excel actual A lo mejor nos interesa saber que color tiene una celda. La propiedad ColorIndex nos ayuda. i = Cells(1, 1).Interior.ColorIndex MsgBox i Aplicar colores por condiciones en Excel VBA Podemos colorear celdas por condiciones, evaluando una cadena de texto, por ejemplo. For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells) If Item.Text = "ColaCao" Then Item.Interior.ColorIndex = 44 End If Next Buscar fragmento de texto en una lista La función INSTR nos permite buscar un fragmento de texto en una lista de cadenas de texto. Nos devuelve la posición dentro de la cadena. Esta cifra es clave: 0 = no coincide, >0 = sí que coincide. En este ejemplo buscaremos en una lista de apellidos. En el ejemplo abajo, "Aban" nos devuelve dos apellidos. Otro ejemplo sería 'queiro", c ual nos devolvería Abanquiero. Preparamos la hoja En celdas C5:C12 tenemos la lista en que buscar (celdas D5:D12 también pertenecen a la matriz. En celda G2 introducimos el criterio. En celdas G5:H5 el programa pondrá la lista "filtrada", allí no tienes que introducir nada. Crear el módulo Entra a Herramientas - Macros - Editor VBA. A la izquierda ves (si no lo ves CTRL+R) algo como "ProyectoVBA (Tu libro). Marca ese proyecto. Insertar - Módulo. Doble click en el módulo nuevo, y a la derecha sale un espacio blanco, es para el código. Ejecutar macro Para ejecutar esta macro entras a Herramientas - Macros. Allí encontrarás la macro "Buscar_Texto_En_Lista()". Marca esta, y "Ejecutar". Código Sub Buscar_Texto_En_Lista() 'dimensiones Dim Dim Dim Dim Dim Dim lngUltimaFila As Long strObjetoBuscar As String lngResultado As Long lngColumna As Long, lngFila As Long lngPegarColumna As Long, lngPegarFila As Long x As Integer, n As Integer 'quitar resultados anteriores Range("G5:H4000").ClearContents 'columna + fila donde empezar/terminar búsqueda lngColumna = 2 lngFila = 5 lngUltimaFila = Columns(lngColumna). _ Range("A65536").End(xlUp).Row 'columna + fila donde empezar a pegar resultados lngPegarColumna = 6 lngPegarFila = 5 'objeto a buscar strObjetoBuscar = Range("G2").Text If strObjetoBuscar = "" Then GoTo 99 'minúsculas strObjetoBuscar = LCase(strObjetoBuscar) 'bucle: realizar búsqueda For n = lngFila To lngUltimaFila 'evaluación lngResultado = InStr(1, Cells(n, 3), _ strObjetoBuscar, vbTextCompare) 'copiar/pegar If lngResultado > 0 Then Range(Cells(n, 2), Cells(n, 4)).Copy Range( _ Cells(lngPegarFila, lngPegarColumna), _ Cells(lngPegarFila, lngPegarColumna + 2)) _ .Select ActiveSheet.Paste lngPegarFila = lngPegarFila + 1 End If Next n 'aparcar Application.CutCopyMode = False Range("G2").Select 99: End Sub Sumar rango variable con VBA Excel Sumar un rango en Excel es fácil. Sumar un rango expresado por una variable en VBA es un poco más complicado (pero sigue siendo fácil). No siempre se sabe de antemano que celdas formarán parte del rango a sumar. Entonces tenemos que expresar el rango de forma variable. En Excel es fácil sumar este rango mediante una sencilla fórmula. Pero VBA no contiene ninguna función igual. Entonces hay que hacer que VBA utilice las funciones de Excel. Utilizar funciones Excel en VBA Tenemos un rango varSuma, el rango a sumar. Para sumar las celdas de este rango tenemos que llamar a la función SUM de Excel. Application.WorksheetFunction.Sum(varSuma) De esta manera puedes aplicar cualquier fórmula de Excel en VBA, con tal de que empieces la línea de código con Application.WorksheetFunction... Nuestro ejemplo En este ejemplo el rango que nos interesa sumar son los valores correspondientes a "BB", es decir C8:C13. Escribir la suma (en celda) 'el rango a sumar varSuma = Range(Cells(8, 3), Cells(13, 3)) 'sumar el rango Cells(1, 1) = Application.WorksheetFunction.Sum(varSuma) Escribir la suma (variable) 'el rango a sumar varSuma = Range(Cells(8, 3), Cells(13, 3)) 'sumar el rango SUMA = Application.WorksheetFunction.Sum(varSuma) Messagebox y Excel VBA Las messagebox son muy útiles (y fáciles de usar), y crea una interfaz entre el usuario y el programa. Sirven para Construcción sintáctica de la messagebox Msgbox "Mensaje", Botones/íconos, "Título" • • • Mostrar información al usuario Recibir información del usuario Devolver información del usuario o en el caso de devolver información Respuesta = Msgbox("Mensaje", _ Botones/íconos, "Título") Nota los paréntesis en la segunda construcción. Mensaje Cualquier tipo de texto. Para crear un salto de línea empleamos el carácter vbCrLf. Actualización terminada:" & vbCrLf & _ "- Importación de datos de venta. Botones Si quieres puedes añadir cualquier de estos cuatro botones (si no pones nada Excel te pondrá vbOkOnly por defecto). vbOkOnly vbOkCancel vbYesNoCancel vbAbortRetryIgnore Íconos Puedes elegir entre los siguientes. vbCritical vbQuestion vbExclamation vbInformation Título Cualquier texto. Devolver información Si quieres que el programa utilice la respuesta del usuario, estas son las cifras que te devuelve. Ok = 1 Cancel = 2 Abort = 3 Retry = 4 Ignore = 5 Yes = 6 No = 7 Ejemplos Te ponemos unos ejemplos módelo para que te vayas acostumbrando a las diferentes messagebox. Sub MessageBox() msgbox "Actualización terminada:", _ vbOKOnly, "Información" End Sub Sub MessageBox() msgbox "¿Quieres seguir?", vbYesNo, _ "Información importante" End Sub Sub MessageBox() Dim intRespuesta As Integer intRespuesta = msgbox("¿Quieres seguir?", _ vbQuestion + vbYesNo, "Información importante") If intRespuesta = 6 Then msgbox "Seguimos" Else msgbox "Terminamos" End If End Sub Sub MessageBox() msgbox "Actualización terminada:" & _ vbCrLf & vbCrLf & _ "- Importación de datos de venta." & vbCrLf & _ "- Cálculos de impuestos." & vbCrLf & _ "- Venta por proveedor." & vbCrLf _ , vbOKOnly, "Actualización terminada." End Sub Sub MessageBox() msgbox "Actualización terminada:" & vbCrLf & _ vbCrLf & _ "- Importación de datos de venta." & vbCrLf & _ "- Cálculos de impuestos." & vbCrLf & _ "- Venta por proveedor." & vbCrLf _ , vbExclamation + vbOKOnly, _ "Actualización terminada." End Sub Calcular los Dígitos de control desde Excel VBA En este ejemplo presentamos un ejemplo de marcado VBA para calcular los dígitos de control de una cuenta bancaria española ¿Qué son los dígitos de control? Las cuentas bancarias españolas consisten de 4 grupos de dígitos. Los dígitos de control verifican los códigos de Entidad y Oficina (el primer dígito), y el Número de Cuenta (el segundo dígito). El algoritmo para calcular los dígitos de control se define en Norma bancaria 34 de AEB (Asociación Española de Banca). Tarea Queremos un programa que nos calcule los dígitos de control de una cuenta. Un ejemplo de cuenta 0123 - 4567 - xx - 0123456789 Entidad 0123 Oficina 4567 Dígitos de control Xx Cuenta 0123456789 En el ejemplo arriba, los dígitos de control serían 81. Empezamos Abrimos un libro nuevo. Formateamos las celdas B3:E3 a TEXTO. Crear el módulo Entra a Herramientas - Macros - Editor VBA. A la izquierda ves (si no lo ves CTRL+R) algo como "ProyectoVBA (Tu libro). Marca ese proyecto. Insertar - Módulo. Doble click en el módulo nuevo, y a la derecha sale un espacio blanco, es para el código. Herramientas - Referencias. Marca Microsoft ActiveX DataObjects x.x Library. Ahora cierras el editor VBA, vuelves a Excel. Guardamos el libro. El código Sub DC_check() Dim Dim Dim Dim Dim Dim Bank As String Office As String DC As String Account As String BankOffice As String Valor1, Valor2, Valor3, Valor4, Valor5 DIm Valor6, Valor7, Valor8, Valor9, Valor10 Dim TestValue1 As Integer Dim TestValue2 As Integer Dim TestValueDC As Integer '--------------------------------------------------'recoger el número de cuenta Bank = Range("B3").Text Office = Range("C3").Text DC = Range("D3").Text Account = Range("E3").Text BankOffice = Bank & Office '--------------------------------------------------'evaluar DC (1) Valor1 = (Mid(BankOffice, 1, 1) * 4) Valor2 = (Mid(BankOffice, 2, 1) * 8) Valor3 = (Mid(BankOffice, 3, 1) * 5) Valor4 = (Mid(BankOffice, 4, 1) * 10) Valor5 = (Mid(BankOffice, 5, 1) * 9) Valor6 = (Mid(BankOffice, 6, 1) * 7) Valor7 = (Mid(BankOffice, 7, 1) * 3) Valor8 = (Mid(BankOffice, 8, 1) * 6) TestValue1 = Valor1 + Valor2 + Valor3 + Valor4 + _ Valor5 + Valor6 + Valor7 + Valor8 TestValue1 = (TestValue1 Mod 11) TestValue1 = 11 - TestValue1 If TestValue1 = 10 Then TestValue1 = 1 If TestValue1 = 11 Then TestValue1 = 0 '--------------------------------------------------'evaluar DC (2) Valor1 = (Mid(Account, 1, 1) * 1) Valor2 = (Mid(Account, 2, 1) * 2) Valor3 = (Mid(Account, 3, 1) * 4) Valor4 = (Mid(Account, 4, 1) * 8) Valor5 = (Mid(Account, 5, 1) * 5) Valor6 = (Mid(Account, 6, 1) * 10) Valor7 = (Mid(Account, 7, 1) * 9) Valor8 = (Mid(Account, 8, 1) * 7) Valor9 = (Mid(Account, 9, 1) * 3) Valor10 = (Mid(Account, 10, 1) * 6) TestValue2 = Valor1 + Valor2 + Valor3 + Valor4 + Valor5 + _ Valor6 + Valor7 + Valor8 + Valor9 + Valor10 TestValue2 = (TestValue2 Mod 11) TestValue2 = 11 - TestValue2 If TestValue2 = 10 Then TestValue2 = 1 If TestValue2 = 11 Then TestValue2 = 0 '--------------------------------------------------'reconstituir las dos cifras del DC TestValueDC = TestValue1 & TestValue2 'evaluar DC completo If TestValueDC = DC Then Test = True MsgBox "Correcto." Else Test = False MsgBox "DC no corresponde a esta cuenta." & vbCrLf & _ "[en este caso DC sería " & TestValueDC & ".]", vbOKOnly, vbInformation End If '--------------------------------------------------- End Sub Ejecutar macro Para ejecutar esta macro entras a Herramientas - Macros. Allí encontrarás la macro "Conectar_Excel_ADO". Marca esta, y "Ejecutar". Casilla de Verificación (evento Change) Resumen Se usa el evento change para simular una casilla de verificación, en la columna A, y luego combinamos con una suma condicional, que que solamente SUMA los meses que se encuentran tildados. Un ejemplo real Al hacer clic sobre la columna A, tilda la celda. Luego se puede aplicar una fórmula matricial y condicional para sumar los meses tildados. {=SUMA(SI($A$2:$A$13"";$C$2:$C$13;0))} El código El código debe escribirse en la misma hoja Excel, ver imagen. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim aOffset As Integer On Error GoTo err_handler Application.EnableEvents = False If Not Application.Intersect _ (Target, Columns("A")) Is Nothing Then If Target.Column = 4 Then aOffset = 3 Else aOffset = 2 End If If IsEmpty(Target.Value) Then With Target .Font.Name = "Wingdings" .Value = Chr(252) End With Target.Offset(0, iOffset).Select Else Target.Value = "" Target.Offset(0, iOffset).Select End If End If err_handler: Application.EnableEvents = True End Sub Comentarios Para evitar confusiones (sobre todo si estamos preparando una plantilla para otras colegas), podríamos optar por la fórmula SUMAPRODUCTO, que además permite aplicar varias condiciones de una manera fácil. / Anders J. =SUMAPRODUCTO((A2:A13"")*C2:C13) Explorador de informes Resumen de este ejemplo de "Explorador de informes" Este ejemplo nos nuestra, de cómo armar un explorador de informes en Excel, manejándonos desde un Panel Principal. Esto nos permite dar una mejor estética a cualquier aplicación desarrollada en Excel. La importancia del formato Excel de presentación Como hemos ido comentando en otros ejemplos de Excel y VBA de este sitio, la presentación de los libros Excel es algo muy importante, sobre todo si trata de informes para distribuir. Captura de pantalla de esta plantilla Excel Instrucciones Encontrarás un ejemplo real del Explorador de archivos Excel en la carpeta de este curso en el servidor de UGA Para ver el código utilizado, combine las siguientes Teclas:ALT + F11. Importar tabla Access a Excel Aquí explicamos cómo importar una tabla de una base de datos Access, a una hoja Excel. Esta maniobra también puede realizarse desde los menús del propio programa Access. ¿Pero qué pasa si tenemos que acceder a un archivo Access, y no tenemos el programa Access instalado? Entonces podemos recurrir al código VBA, y programar una macro. Además, esta importación tarda poquísimo - muy interesante si importamos muchas tablas al día. El código Sub Importar_Access() 'dimensiones Dim datConnection As ADODB.Connection Dim recSet As ADODB.Recordset Dim strDB, strSQL As String Dim strTabla As String Dim lngTablas As Long Dim i As Long 'elegir uno de estas dos rutas al archivo Access strDB = ThisWorkbook.Path & "\" & "db.mdb" 'strDB = "C:\vba\db.mdb" 'si en otra carpeta 'nombre de la tabla del archivo Access strTabla = "salarios_2003" 'crear la conexión Set datConnection = New ADODB.Connection Set recSet = New ADODB.Recordset datConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source =" & strDB & ";" 'consulta SQL strSQL = "SELECT * FROM " & strTabla & "" recSet.Open strSQL, datConnection 'copiar datos a la hoja ActiveSheet.Cells(2, 1).CopyFromRecordset recSet 'copiar rótulos lngCampos = recSet.Fields.Count For i = 0 To lngCampos - 1 ActiveSheet.Cells(1, i + 1).Value = recSet.Fields(i).Name Next 'desconectar recSet.Close: Set recSet = Nothing datConnection.Close: Set datConnection = Nothing End Sub Importar datos de Outlook a Excel Con VBA de Excel podemos contactar con Outlook para devolver datos. En este ejemplo vamos a importar algunos datos de los Contactos. Objetos utilizados Para contactar con Outlook desde Excel podemos utilizar el objeto GetNameSpace de la MAPI Messaging Application Programming Interface. Luego, el método GetDefaultFolder nos deja acceder a las carpetas de Outlook, por ejemplo el de los Contactos. Parámetro Carpeta 3 Deleted Items (Elementos elimindos) 4 Outbox (Bandeja de salida) 5 Sent Items (Elementos enviados) 6 Inbox (Bandeja de entrada) 9 Calendar (Calendario) 10 Contacts (Contactos) 11 Journal (Diario) 12 Notes (Notas) 13 Tasks (Tareas) 16 Drafts (Borrador) El código Sub ImportarContactos() Dim Dim Dim Dim olApp As Outlook.Application olContacts As Outlook.MAPIFolder olContact As Outlook.ContactItem i As Integer Set olApp = New Outlook.Application Set olContacts = olApp.GetNamespace("MAPI").GetDefaultFolder(olFolderContacts) 'rotulos Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, Cells(1, 1) = "Nombre" 2) = "E-mail" 3) = "Título" 4) = "Empresa" 5) = "Tel (casa)" 6) = "Tel (móbil)" 7) = "Tel (trabajo)" 8) = "Fax (trabajo)" 9) = "Dir. (empresa)" 10) = "Postal (empresa)" 11) = "Ciudad (empresa)" 12) = "País (empresa)" 13) = "Dir. (casa)" 14) = "Postal (casa)" 15) = "Ciudad (casa)" 16) = "País (Casa)" 'importar contact items For i = 2 To olContacts.Items.Count If TypeOf olContacts.Items.Item(i) Is _Outlook.ContactItem Then Set olContact = olContacts.Items.Item(i) Cells(i, 1) = olContact.FullName Cells(i, 2) = olContact.Email1Address Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, Cells(i, End If Next 3) = olContact.JobTitle 4) = olContact.CompanyName 5) = olContact.HomeTelephoneNumber 6) = olContact.MobileTelephoneNumber 7) = olContact.BusinessTelephoneNumber 8) = olContact.BusinessFaxNumber 9) = olContact.BusinessAddressStreet 10) = olContact.BusinessAddressPostalCode 11) = olContact.BusinessAddressCity 12) = olContact.BusinessAddressCountry 13) = olContact.HomeAddressStreet 14) = olContact.HomeAddressPostalCode 15) = olContact.HomeAddressCity 16) = olContact.HomeAddressCountry 'eliminar variables de los objetos Set olContact = Nothing Set olContacts = Nothing Set olApp = Nothing 'ordenar lista por Nombre Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess End Sub Enviar hoja Excel por correo electrónico (Outlook etc.) Resumen Este truco sirve para enviar una hoja Excel por mail, utilizando Outlook u otro sistema de correo instalado en el ordenador del usuario. El código ' Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim stEmail As String strdate = Format(Now, "dd-mm-yy h-mm-ss") strEmail = "
[email protected]" Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name & " " & strdate & ".xls" .SendMail strEmail, "Archivo Adjunto" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub Configuraciones Hay que indicar la dirección a utilizar. strEmail = "
[email protected]"