Samples

Repetición de tareas'!A1

Archivo de ejemplos de Microsoft Excel Contenido

Funciones de hoja de cálculo Ejemplos de fórmulas para realizar las tareas más frecuentes de una hoja de cálculo.

Formato condicional Explicación sobre la manera de modificar el formato aplicado a una celda (fuente, color, etc.) dependiendo de su valor.

Validación de datos Se muestra cómo establecer restricciones a los valores que se pueden introducir en una celda.

Rótulos de gráficos Una macro que permite insertar rótulos automáticamente en un gráfico XY (Dispersión).

Repetición de tareas Ejemplos de código de bucles y una explicación sobre la manera de modificar el código grabado para repetir tareas en un rango de celdas o en un rango seleccionado. Microsoft proporciona ejemplos de procedimientos de Visual Basic for Applications (Visual Basic para Aplicaciones) sólo con propósito ilustrativo, sin ningún tipo de garantía, explícita o implícita, incluidas entre otras las garantías implícitas de comercialización o de uso para un fin en particular. Los procedimientos de Visual Basic en este libro de ejercicios se entregan tal cual aparecen ("as is"); Microsoft no garantiza que se puedan utilizar en todo tipo de situaciones. A pesar de que los ingenieros del Servicio Técnico de Microsoft podrían ayudar a explicar el funcionamiento de una macro en particular, no modificarán los ejemplos para agregar nuevas funciones, ni tampoco crearán macros como respuesta a necesidades específicas. Si usted tiene una experiencia limitada en programación, póngase en contacto con algún Microsoft Solution Provider.

mplos de Microsoft Excel Contenido Matrices Ejemplo de código de macros para mostrar la manera de transferir el contenido de una matriz a una hoja de cálculo.

Ejemplos de API Cómo utilizar llamadas API (Interfaz de programación de aplicaciones) desde el entorno de programación de Microsoft Excel.

Programar con eventos Ejemplos que muestran cómo algunos eventos pueden hacer que se inicie la ejecución de codigo de macros.

Automatización Ejemplos de código de macro para mostrar cómo se pueden automatizar tareas de Microsoft Excel y otras aplicaciones de Microsoft Office.

Objetos de datos ActiveX (ADO) Ejemplos que muestran cómo realizar algunas de las tareas más frecuentes de bases de datos.

sual Basic for Applications (Visual Basic para Aplicaciones) sólo con propósito cluidas entre otras las garantías implícitas de comercialización o de uso para un fin ro de ejercicios se entregan tal cual aparecen ("as is"); Microsoft no garantiza que que los ingenieros del Servicio Técnico de Microsoft podrían ayudar a explicar el n los ejemplos para agregar nuevas funciones, ni tampoco crearán macros como experiencia limitada en programación, póngase en contacto con algún Microsoft Solution Provider.

Funciones de hoja de cálculo Esta hoja de cálculo contiene ejemplos de fórmulas que puede usar para completar algunas tareas usuales en hojas de cálculo. Las celdas con fórmulas se muestran en azul. Si desea ver un ejemplo de fórmula, sitúe el cursor del mouse (ratón) sobre la celda para que aparezca el comentario o presione ALT+º para cambiar entre presentar valores o fórmulas en la hoja de cálculo. Para obtener más información acerca de una función, seleccione la celda con la función y, a continuación, haga clic en el botón Modificar fórmula (=) en la barra de fórmulas.

Suprimir la presentación de valores de error Con frecuencia, las fórmulas de la hoja de cálculo devuelven valores de error (#¡DIV/0!, #N/A, #¡VALOR!, #¡REF!, y #¡NUM!) si están basadas en valores no esperados. En la fórmula de división del ejemplo mostrado a continuación, si la celda de origen es 0, devolverá #DIV/0! como error. Origen A Origen B 25 0

Fórmula original #DIV/0!

Nueva fórmula

Otra forma de expresarlo es: =SI(ESERROR();"";) donde es la fórmula en la cual se desea suprimir el valor que produce el error. Si devuelve un error, el enunciado devolverá "" (vacío); de lo contrario devolverá el resultado de . Otro método para suprimir la presentación de valores de error es usar la opción Formato condicional. Esta opción permite que la celda muestre distintos formatos según el contenido de la misma. En el caso de valores de error, el procedimiento sería: 1. 2. 3. 4. 5.

Seleccione la celda para dar formato. En este caso es $E$41. Haga clic en Formato condicional en el menú Formato. En el primer cuadro desplegable, seleccione Fórmula. En el cuadro de edición, escriba: =ESERROR($E$41). Haga clic en el botón Formato y seleccione un formato, en este caso se eligió azul para el fondo y la fuente. 6. Haga clic en Aceptar.

Como resultado, en los valores de error la fuente y el fondo presentarán el mismo color, por lo que no se mostrará nada. Origen A Origen B 25 0

Formato originales #DIV/0!

Nuevo formato #DIV/0!

Numerar, hacer coincidir y buscar valores Una de las tareas más comunes que se realizan con las listas es encontrar el valor del punto de intersección entre una columna y una fila en un rango rectangular de celdas en una hoja de cálculo. El Asistente para búsquedas le ayudará a escribir fórmulas para realizar esta tarea. Para usar esta opción, seleccione una celda dentro de la lista que desea resumir. En el menú Herramientas seleccione Asistente y, a continuación, Suma condicional. Siga las instrucciones del asistente Si estas opciones de menú no están presentes y para obtener información adicional sobre la

la utilización o la instalación del Asistente para búsquedas, utilice el Ayudante de Office y realice una búsqueda utilizando las palabras "Asistente para búsquedas".

Fórmulas de suma condicional Otra tarea frecuente en Excel es calcular la suma de los valores que cumplen las condiciones especificadas. El Asistente para suma condicional le ayudará a escribir fórmulas para completar esta tarea. Para usar esta opción, seleccione una celda dentro de la lista que desea resumir. En el menú Herramientas seleccione Asistente y, a continuación, Suma condicional. Siga las instrucciones del asistente. Si desea obtener información adicional, use el Ayudante de Office. Si los elementos de menú no están presentes y para obtener información adicional sobre la utilización y la instalación del Asistente para suma condicional, use el Ayudante de Office y realice una búsqueda de la frase "Asistente para suma condicional". Si estas opciones de menú no están presentes y, para obtener información adicional sobre la la utilización o la instalación del Asistente para suma condicional, utilice el Ayudante de Office y realice un búsqueda utilizando las palabras "Asistente para suma condicional".

Formato condicional El formato condicional permitie modificar el formato aplicado a las celdas en función de sus valores. Así se pueden examinar hojas de cálculo de gran tamaño mucho más rápidamente resaltando las excepciones. El formato condicional permite aplicar hasta tres condiciones distintas a una celda. El formato condicional también permite modificar el estilo de la fuente, los bordes y las tramas de las celdas.

Formato condicional. Ejemplo 1

Las celdas H10:H14 contienen tres reglas de formato condicional que cambiarán el formato de estas celdas dependiendo de los valores que se escriban en ellas. Para ver la condición que se ha aplicado a las celdas, seleccione la celda H10 y haga clic en Formato condicional, en el menú Formato.

4 7 texto 1 15

Formato condicional. Ejemplo 2: ocultar valores de error En este ejemplo se muestra cómo utilizar el Formato condicional para ocultar valores de error devueltos por las fórmulas. En este ejemplo, las celdas H21 y H23 deberían mostrar el código de error #DIV/0!. El formato condicional aplicado establece el color de la fuente de las celdas que contengan un valor de error como el color de fondo de la hoja de cálculo. Para ver las condiciones aplicadas a las celdas, seleccione la celda H21 y haga clic en Formato condicional, en el menú Formato.

2.5 4 #DIV/0! 5 #DIV/0!

10 20 30 40 50

4 5 8

Validación de datos La Validación de datos permite establecer restricciones a los valores que se pueden introducir en una celda. En los siguientes ejemplos se muestran varias situaciones típicas en las que se puede utilizar la validación de datos. Validación de datos. Ejemplo 1: limitación a valores numéricos A las celdas H9:H13 se les ha aplicado un formato con un criterio de validación que limita el valor de la celda a valores numéricos. En este ejemplo se utiliza el estilo Límite para el mensaje de error que impide introducir datos no válidos en la celda seleccionada. Para examinar los valores de Validación de este ejemplo, seleccione la celda H9 y, a continuación, haga clic en Validación, en el menú Datos. Validación de datos. Ejemplo 2: limitación de la longitud del texto A las celdas H19:H23 se les ha aplicado un formato con un criterio de validación que limita la longitud de las entradas de texto a siete caracteres. En este ejemplo se utiliza el estilo Advertencia para el mensaje de error, que permite cancelar la introducción de datos o introducir el valor no válido en la celda seleccionada. Para ver los valores de validación de este ejemplo, seleccione la celda H19 y, a continuación, haga clic en Validación, en el menú Datos. Validación de datos. Ejemplo 3: limitación a un rango de números enteros A las celdas H30:H34 se les ha aplicado un formato con un criterio de validación que avisará al usuario si no se escribe un número entero que esté entre 1 y 10. En este ejemplo se utiliza el estilo Información para el mensaje de error, que comunicará al usuario que se han introducido datos no válidos pero permitiendo mantener los valores actuales. Para examinar los valores de validación de este ejemplo, seleccione la celda H30 y haga clic en Validación, en el menú Datos. Validación de datos. Ejemplo #4: limitación a una lista de valores A las celdas H41:H45 se les ha aplicado un formato con un criterio de validación que limita los datos válidos a una lista de valores. La lista de valores válidos se encuentra en las celdas M41:M49. Cuando se selecciona una de las celdas del rango H41:H45, aparece una lista desplegable. Al hacer clic en la flecha, aparecerá la lista de valores validos. Sólo tiene que hacer clic en el valor que desee introducir. En este ejemplo se utiliza el estilo Límite para el mensaje de error, lo que impide introducir datos no válidos en la celda. Para examinar las opciones de Validación de este ejemplo, seleccione la celda H41 y, a continuación, haga clic en Validación, en el menú Datos.

valor que desee introducir. En este ejemplo se utiliza el estilo Límite para el mensaje de error, lo que impide introducir datos no válidos en la celda. Para examinar las opciones de Validación de este ejemplo, seleccione la celda H41 y, a continuación, haga clic en Validación, en el menú Datos.

Andrés Bárbara Carlos Juan Martín Miguel Nicolás Patricia Sofía

Adjuntar rótulos a un gráfico XY (Dispersión) Rótulos Punto de datos 1 Punto de datos 2 Punto de datos 3 Punto de datos 4

Valores X 2 9 5 4

Valores Y 5 7 3 8

En Microsoft Excel no existe un método para agregar rótulos automáticamente a un gráfico XY (Dispersión). Sin embargo, puede usar una macro para realizar esta tarea. En la macro adjunta se muestra cómo aplicar rótulos en un gráfico XY (Dispersión) y se asume que los datos y rótulos asociados están ordenados del mismo modo que en las celdas sombreadas situadas por encima. Para aplicar los rótulos de puntos de datos presione el botón "Ubicar rótulos en el gráfico". Para quitar los rótulos y poder iniciar una nueva demostración, presione el botón "Restablecer gráfico". Para ver el código de la macro adjunta a la hoja, presione el botón "Ver código".

Ubicar rótulos en el gráfico

Restablecer gráfico

Ver código

8 7.5 7 6.5 6 5.5

Valores Y

5 4.5 4 3.5 3 2

3

4

5

6

7

8

9

ón) Ver código

Valores Y

9

Repetición de tareas A menudo es necesario repetir un tipo de tarea específica para un grupo de elementos, que pueden ser celdas, celdas en un rango, hojas de cálculo de un libro o libros en una aplicación. A pesar de que una macro no puede grabar bucles, se puede grabar la tarea principal y luego, mediante pequeñas modificaciones en el código, se pueden crear distintos tipos de bucles según las necesidades del proyecto. Los ejemplos siguientes se centran en un escenario donde el programador tiene un rango de celdas con números en la columna A, y dependiendo del valor de la celda, desea cambiar el color de la celda correspondiente en la columna B. Primero grabaremos el proceso de cambiar el color de fondo de la celda: (Para grabar la macro, en el menú Herramientas haga clic en Macro y luego en Grabar nueva macro). Mientras graba, haga clic en Celdas en el menú Formato, a continuación, haga clic en la ficha Tramas y seleccione un color. En este ejemplo se usará amarillo (.ColorIndex=6). A continuación pare la grabación mediante el botón Detener grabación de la barra de herramientas Grabar macro. La celda seleccionada cambiará el color y se grabará la macro siguiente:

Sub Macro_Grabada() ' ' Macro grabada el 18/11/98 ' With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub

Ir al código de ejemplo Macro_Grabada

Nota: si al grabar seleccionó una celda, puede aparecer en la macro el texto Range("A3").Select. Si deja esta línea sin modificar, cada vez que ejecute la macro se seleccionará la celda especificada en el objeto Range. No quite esta línea de la macro si desea seleccionar la celda. A continuación se puede modificar ligeramente el código y agregar alguna estructura de bucle en el código grabado.

Bucle For Each...Next Para usar el bucle For Each…Next se debe conocer el rango de celdas al que deseamos aplicar la acción grabada. Como condición de este ejemplo, sólo será afectada la celda en la columna B si la celda en A es mayor que 20. Se agregará el enunciado If al enunciado With utilizado. El color cambiará sólo si If es verdadero. Por último, debido a que se desea desplazar la celda correspondiente una columna hacia la derecha de la columna A (columna B), se reemplazará la propiedad Selection en el código usando el método Offset en la celda en la que se aplicará el bucle (celda_en_bucle). El código resultante será:

Sub Ejemplo_For_Each_Next() ' ' Macro_grabada modificada (adición de código de bucle) ' For Each celda_en_bucle In Range("A1:A5") If celda_en_bucle.Value > 20 Then With celda_en_bucle.Offset(0, 1).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next End Sub

Ir al código de ejemplo For_Each_Next

Bucle For…Next Si conoce el número de veces que desea realizar un bucle en el código, puede usar el bucle For..Next. En el ejemplo siguiente, si se desea comprobar 10 celdas hacia abajo a partir de la celda seleccionada, el código será: Sub Ejemplo_For_Next() For Contador = 0 To 9 If Selection.Offset(Contador, 0).Value > 20 Then With Selection.Offset(Contador, 1).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next End Sub

Ir al código de ejemplo For_Next

En este ejemplo, el objeto Selection se utiliza para que el código no especifique ningún rango y realice un bucle diez celdas hacia abajo de la celda activa (de 0 a 9). La variable Contador aumenta progresivamente a medida que avanza el bucle y se puede usar internamente en la estructura del mismo. Aquí se utiliza como argumento Offset para indicar el número de filas que se desplaza Selection desde la celda activa. Si al iniciar la macro la celda activa es A1, en la primera vuelta del bucle, la variable Contador será igual a 0 y se desplazará 0 filas a partir de A1, como se muestra en el enunciado Selection.Offset(Contador, 0).Value.

Do…Loop Para establecer cuándo detener un bucle basado en una condición determinada, puede ser apropiado utilizar Do…Loop. Esta estructura de bucle permite comprobar las propiedades o condiciones de la variable antes de ejecutar el bucle. En el ejemplo siguiente, el bucle continúa hasta que el número de fila al que se hace referencia en Selection.Offset(Contador, 0).Row es mayor que 100. Esto puede ser útil si no desea ejecutar el bucle mas allá de la fila 100.

Sub Ejemplo_Do_Loop() Contador = 0 Do Until Selection.Offset(Contador, 0).Row > 100 If Selection.Offset(Contador, 0).Value > 20 Then With Selection.Offset(Contador, 1).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Contador = Contador + 1 Loop End Sub

Ir al código de ejemplo Do_Loop

Nota: existen otros tipos de bucle Do…Loop disponibles, que ofrecen más flexibilidad en determinadas circunstancias. Si desea obtener más información y detalles acerca de otras estructuras de bucle, consulte en la Ayuda del entorno de Visual Basic la palabra bucles.

Trabajar con matrices La manera utilizada con más frecuencia para transferir el contenido de una matriz a una hoja de cálculo es mediante un bucle como, por ejemplo, For...Next. Con For...Next podrá indexar en la matriz y transferir elementos de la matriz a su dirección de destino de una sola vez. El mismo resultado puede obtenerse sin bucles utilizando la propiedad FormulaArray, del objeto Range, de Visual Basic para aplicaciones. En Visual Basic para aplicaciones, la orientación de una matriz unidimensional es horizontal. Como consecuencia, si el rango de celdas en la hoja de cálculo que va a recibir el contenido de la matriz tiene la misma orientación, es decir, una sola fila y varias columnas, es posible transferir su contenido mediante el enunciado FormulaArray. Por ejemplo: Para una matriz unidimensional horizontal Sub Matriz1() 'Declara una matriz de tamaño 10 Dim x(1 To 10) As Double 'Calcula valores For j = 1 To 10 x(j) = j * j Next j

Ir al código de ejemplo Matriz1

'Verifica que la celda de destino inicial está en blanco 'si no lo está, se abre un nuevo libro para insertar 'el resultado del código If Range(Cells(2, 1), Cells(2, 1)).Value <> "" Then Workbooks.Add 'Transfiere el contenido de una matriz a un área horizontal Range(Cells(2, 1), Cells(2, 10)).FormulaArray = x End Sub El ejemplo anterior funciona correctamente cuando en la hoja de cálculo de destino se hace referencia a una sola fila. Sin embargo, este ejemplo no será válido cuando sea necesario transferir el contenido de una matriz a un rango de celdas con orientación vertical, es decir, una columna y varias filas.

En una matriz bidimensional, es necesario cambiar la orientación de la matriz. Para hacerlo, puede declarar la matriz como matriz bidimensional de varias filas y una sola columna. Por ejemplo: Ir al código de ejemplo Sub Matriz2() 'Para una matriz bidimensional horizontal Matriz2 'Declara una matriz de 10 filas y 1 columna Dim x(1 To 10, 1 To 1) As Double 'Calcula valores aleatorios For j = 1 To 10 x(j, 1) = j * j Next j 'Verifica que la celda de destino inicial está en blanco 'si no lo está, se abre un nuevo libro para insertar 'el resultado del código If Range(Cells(1, 2), Cells(1, 2)).Value <> "" Then Workbooks.Add 'Transfiere el contenido de la matriz a un área vertical Range(Cells(1, 2), Cells(10, 2)).FormulaArray = x End Sub La matriz bidimensional presentada en el ejemplo anterior permite que Visual Basic establezca la orientación de la matriz como orientación vertical. De este modo, se puede transferir la matriz a una hoja de cálculo sin utilizar un bucle.

código de ejemplo Matriz1

código de ejemplo Matriz2

Ejemplos de API La interfaz de programación de aplicaciones (Application Programming Interface, API) es una función incluida en un archivo DLL. Estas funciones suelen utilizarlas Windows y las aplicaciones pero también puede utilizarlas el usuario final. Nota: tenga precaución al experimentar con las llamadas API. Guarde su trabajo a menudo para evitar problemas. Debido a que fueron diseñadas para ser llamadas por el sistema o las aplicaciones, no existe un sistema de detección de errores. Esto significa que el usuario puede hacer que el sistema se bloquee o que surgan otros efectos secundarios no deseados al expermientar con llamadas API. En los siguientes ejemplos se muestra cómo utilizar las API básicas. Estos ejemplo se han probado con Windows 98. Hay varias maneras de utilizar estas funciones y depende del programador determinar cuál es la mejor para obtener los resultados buscados.

Comprobar las letras de todas las unidades En el siguiente ejemplo se utiliza la función GetLogicalDriveStrings para examinar todas las unidades lógicas, que pueden ser unidades extraíbles, fijas o de CD y unidades de red compartidas. Option Explicit Private Declare Function GetLogicalDriveStrings _ Lib "kernel32" Alias "GetLogicalDriveStringsA" _ (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Sub Obtener_unidades_logicas() Dim DrvString As String Dim TotDrvs As Long Dim Contador As Integer 'TotDrvs devuelve el número total de caracteres 'en la cadena devuelta TotDrvs = GetLogicalDriveStrings(0&, DrvString) 'DrvString es el búfer creado para mantener la cadena DrvString = String(TotDrvs - 1, " ") 'Si se llama a GetLogicalDriveStrings una segunda vez, 'la cadena se llenará con datos válidos 'ejemplo "a:\ c:\ d:\ e:\ " TotDrvs = GetLogicalDriveStrings(TotDrvs, DrvString) 'Se analizan la cadenas devueltas mostrando cada una 'en cuadro de mensaje For Contador = 1 To TotDrvs Step 4 MsgBox Mid(DrvString, Counter, 3) Next Contador End Sub

Ir al código de ejemplo Obtener_unidades_logicas

Devolución de la resolución de vídeo En el siguiente ejemplo de código se devuelve la resolución de vídeo de la pantalla y se muestra la información de un cuadro de mensaje. Option Explicit Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) _ As Long 'Constantes para GetSystemMetrics Const SM_CXSCREEN = 0 Const SM_CYSCREEN = 1

' Ancho de la pantalla ' Alto de la pantalla

Sub Obtener_resolucion() Ir al código de ejemplo Dim XVal As Long, YVal As Long Obtener_resolución YVal = GetSystemMetrics(SM_CYSCREEN) XVal = GetSystemMetrics(SM_CXSCREEN) MsgBox "La resolución de la pantalla es de " & XVal & " por " & YVal End Sub

Recuperar el nombre de usuario de inicio de sesión Con el siguiente código se obtiene el nombre del usuario que inició la sesión actual. Option Explicit Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _ (ByVal lpBuffer As String, _ nSize As Long) As Long Sub Nombre_de_usuario() Dim lpBuff As String * 25 Dim ret As Long, UserName As String ret = GetUserName(lpBuff, 25) UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) MsgBox UserName

Ir al código de ejemplo Nombre_de_usuario

End Sub

Devolución del nombre corto de un nombre largo de ruta de acceso y de archivo La siguiente función toma un nombre de archivo largo y devuelve el nombre corto mostrando ambos en un cuadro de mensaje. Private Declare Function GetShortPathName Lib "KERNEL32.DLL" _

Alias (ByVal ByVal ByVal

"GetShortPathNameA" _ lpctstrLongName As String, _ lptstrShortName As String, _ bufLen As Long) As Long

Sub Nombre_corto() Dim LongStr As String, ShortStr As String Dim lStrLen As Long, lRet As Long

Ir al código de ejemplo Nombre_corto

'LongStr es cualquier nombre de archivo largo o variable 'que señale a un archivo LongStr = ThisWorkbook.FullName lRet = GetShortPathName(LongStr, ShortStr, lStrLen) 'Esto nos permite crear un búfer del mismo tamaño que 'la cadena devuelta, lo que nos evita la molestia de tener 'que quitar la izquierda del búfer para obtener la cadena ShortStr = String(lRet, " ") lRet = GetShortPathName(LongStr, ShortStr, lRet) MsgBox LongStr & " se convirtió a " & ShortStr End Sub

Mostrar el nombre del equipo En el siguiente ejemplo de código se muestra cómo utilizar la función GetComputerName para mostrar el nombre del equipo. Option Explicit Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Sub Nombre_del_equipo() Dim Comp_Name_B As String * 255 Dim Comp_Name As String GetComputerName Comp_Name_B, Len(Comp_Name_B)

Ir al código de ejemplo Nombre_del_equipo

'pero el final de la cadena siempre era una cadena 'terminada en cero por lo que podemos usar la función 'Chr(0) para buscar el final Comp_Name = Left(Comp_Name_B, InStr(Comp_Name_B, Chr(0))) 'y devolver únicamente el nombre del equipo MsgBox Comp_Name End Sub

Mostrar el espacio libre disponible en un disco duro El siguiente código muestra cómo buscar el espacio libre en un disco duro así como el tamaño total de la unidad. Option Explicit Private Declare Function GetDiskFreeSpace Lib "KERNEL32.DLL" _ Alias "GetDiskFreeSpaceA" (ByVal lpRoot As String, _

dwSectors As Long, _ dwBytes As Long, _ dwFreeClusters As Long, _ dwTotalClusters As Long) As Long Sub Espacio_libre_en_disco() Dim f As Long, iSectors As Long Dim iTotal As Long, rTotal As Long Ir al código de ejemplo Dim iFree As Long, rFree As Long Espacio_libre_en_disco Dim iBytes As Long Dim sName As String, s As String sName = "C:\" f = GetDiskFreeSpace(sName, iSectors, iBytes, iFree, iTotal) rFree = iSectors * iBytes * CDbl(iFree) rTotal = iSectors * iBytes * CDbl(iTotal) If f Then s = sName s = s & " dispone de " & Format(rFree, "#,###,###,##0") s = s & " bytes libres de un total de " & Format(rTotal, "#,###,##0") _ & " bytes" End If MsgBox s End Sub

Mostrar la ubicación de la carpeta del sistema En el siguiente código se muestra cómo obtener la ubicación de la carpeta del sistema. Option Explicit Declare Function GetSystemDirectory Lib "kernel32" _ Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, _ ByVal nSize As Long) As Long Sub Directorio_del_sistema() Dim Sys_Dir As String, Res As Long Res = GetSystemDirectory(Sys_Dir, 0&) Sys_Dir = String(Res - 1, " ") Res = GetSystemDirectory(Sys_Dir, Res) MsgBox Sys_Dir End Sub

Ir al código de ejemplo Directorio_del_sistema

Descubrir si el sistema puede reproducir archivos .WAV En el siguiente ejemplo de código se consulta al sistema para comprobar si existen dispositivos capaces de reproducir archivos WAV. Option Explicit Declare Function waveOutGetNumDevs Lib "winmm" () As Long Sub Num_de_disps() Dim i As Long i = waveOutGetNumDevs() If i > 0 Then ' Al menos hay un dispositivo.

Ir al código de ejemplo Num_de_disps

MsgBox "Pueden reproducirse datos de onda" Else MsgBox "No pueden reproducirse datos de onda" End If End Sub

Establecer que Excel excel esté "siempre visible" En el siguiente ejemplo de código se muestra cómo hacer que Microsoft Excel esté " siempre visible". Así se impide que otras aplicaciones aparezcan en la pantalla por encima de Microsoft Excel. Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, _ ByVal hWndInsertAfter As Long, _ ByVal x As Long, _ ByVal y As Long, _ ByVal cx As Long, _ ByVal cy As Long, _ ByVal wFlags As Long) As Long Global Const HWND_TOPMOST = -1 Ir al código de ejemplo Global Const HWND_NOTOPMOST = -2 Siempre_visible Sub Siempre_visible() Dim WinHnd As Long, SUCCESS As Long WinHnd = FindWindow("xlmain", Application.Caption) SUCCESS = SetWindowPos(WinHnd, HWND_TOPMOST, 0, 0, 0, 0, Flags) 'Esta línea se insertó sólo para que Excel vuelva a funcionar 'normalmente a los 20 segundos Application.OnTime Now + TimeValue("00:00:20"), "No_siempre_visible" End Sub Sub No_siempre_visible() Dim WinHnd As Long, SUCCESS As Long WinHnd = FindWindow("xlmain", Application.Caption) SUCCESS = SetWindowPos(WinHnd, HWND_NOTOPMOST, 0, 0, 0, 0, Flags) End Sub

de reproducir

Programar con eventos Con frecuencia los programadores necesitan que un evento desencadene una macro para ejecutar una tarea. Microsoft Excel 2000 ofrece la posibilidad de realizar esta acción. Los eventos están siempre asociados con objetos, como puede ser el caso de una hoja de cálculo o un libro. En este ejemplo sólo se mencionarán algunos de los eventos disponibles y se usará un cuadro de mensajes como relleno para demostrar dónde puede ir el código. Para obtener más información acerca de eventos, haga clic en el Ayudante desde el Editor de Visual Basic y use el nombre del objeto y la palabra "eventos" como palabras clave (por ejemplo, eventos de hoja de cálculo).

BeforeDoubleClick Un tipo de evento muy utilizado es BeforeDoubleClick en una hoja de cálculo. Un ejemplo sencillo puede ser: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) MsgBox "Hizo doble clic en la celda " & Target.Address Cancel = True End Sub Este evento se desencadenará cada vez que el usuario haga doble clic en una celda de la hoja de cálculo. El parámetro Target se incorpora a la macro para que el programador sepa en qué celda se hizo doble clic. El argumento Cancel tiene un valor predeterminado de False pero se puede cambiar a True en el código. Al establecer Cancel como True se cancelará la acción predeterminada para el evento. En este caso, la acción predeterminada al hacer doble clic en una celda es cambiar al modo de edición para esa celda. Debido a que Cancel está establecido como True, esto no ocurrirá. Si desea obtener la acción predeterminada, quite la línea Cancel=True.

Change Otro evento de gran utilidad en una hoja de cálculo, es Change. Se desencadena cada vez que el usuario escribe un valor nuevo en una celda. Private Sub Worksheet_Change(ByVal Target As Excel.Range) MsgBox "Esta hoja cambió en la celda " & Target.Address End Sub Nota: este evento no ocurrirá si la hoja acaba de ser recalculada. Existe otro evento denominado Calculate que funciona con hojas de calculo recalculadas.

BeforeClose Un evento útil relacionado con libros es BeforeClose. Se puede utilizar este tipo de eventos para realizar tareas de limpieza antes de guardar o cerrar un archivo. Sub Workbook_BeforeClose(Cancel As Boolean) a = MsgBox("¿Desea cerrar el libro?", vbYesNo) If a = vbNo Then Cancel = True End Sub En este ejemplo, se pide al usuario que cierre el archivo independientemente de si lo guardó previamente o no.

Usar eventos con el objeto Application Antes de usar eventos con el objeto Application puede crear un nuevo módulo de clase y declarar un objeto del tipo Application con la palabra clave WithEvents. Por ejemplo, suponga que se crea un nuevo módulo de clase denominado EventClassModule con el siguiente código: Public WithEvents App As Application Después de declarar los eventos del nuevo objeto, aparecerá la lista desplegable de Object, con el objeto incluido en el módulo de clase. A continuación puede escribir los procedimientos para el nuevo objeto. Al seleccionar el nuevo objeto en el cuadro Object, los eventos válidos para ese objeto se presentarán en el cuadro de lista desplegable Procedure. Antes de ejecutar los procedimientos debe conectar el objeto declarado en el módulo de clase con el objeto Application. Puede utilizar el siguiente código con cualquier módulo. Dim X As New EventClassModule Sub InitializeApp() Set X.App = Application End Sub Después de ejecutar el procedimiento IniciarAp, el objeto App en el módulo de clase apuntará al objeto Aplicación de Microsoft Excel y los procedimientos del evento se ejecutarán cuando el evento ocurra. Nota: si desea obtener más información acerca de eventos, consulte la Ayuda.

Automatización La automatización es una opción de COM (Component Object Model), una tecnología que usan las aplicaciones para exponer los objetos a las herramientas de desarrollo, lenguajes de macro y otras aplicaciones que admiten automatización. Por ejemplo, una aplicación de hoja de cálculo puede presentar una hoja de cálculo, un gráfico, una celda o un rango de celdas, cada uno con un tipo diferente de objeto. Un procesador de texto puede presentar objetos tales como una aplicación, un documento, un párrafo, una frase, un marcador o una selección. En los ejemplos siguientes se muestran tareas de automatización entre Microsoft Excel y otras aplicaciones de Microsoft. Para obtener más información acerca de la automatización, consulte Automatización de tareas repetitivas en la Ayuda de VBA. MICROSOFT ACCESS Este ejemplo devuelve la ubicación de las bases de datos de muestra de Microsoft Access. Sub MS_Access() 'Para ejecutar esta macro, haga clic en Referencias, en el menú Herramientas, 'y seleccione las bibliotecas de objetos de Microsoft Access 9.0. Dim AccDir As String Dim acc As Object Ir al código de 'Crea una sesión de Microsoft Access automatización Set acc = CreateObject("access.application") MS_Access 'Devuelve la ruta de acceso para msaccess.exe AccDir = acc.SysCmd(Action:=acSysCmdAccessDir) 'Muestra la ruta de acceso MsgBox "MSAccess.exe está ubicado en " & AccDir 'Libera espacio de disponibilidad variable Set acc = Nothing End Sub MICROSOFT WORD Este ejemplo copia el gráfico desde la hoja Rótulos de gráficos a un documento nuevo de Microsoft Word. Sub MS_Word() Dim wd As Object 'Crea una sesión de Microsoft Word Set wd = CreateObject("word.application") 'Copia el gráfico en la hoja Rótulos de gráficos Worksheets("Rótulos de gráficos").ChartObjects(1).Chart.ChartArea.Copy 'Hace visible el documento wd.Visible = True 'Activa MS Word Ir al código de AppActivate wd.Name automatización With wd MS_Word 'Crea un documento nuevo en Microsoft Word .Documents.Add 'Inserta un párrafo .Selection.TypeParagraph 'Pega el gráfico .Selection.PasteSpecial link:=True, DisplayAsIcon:=False, Placement:=wdInLine End With Set wd = Nothing End Sub

MICROSOFT POWERPOINT Este ejemplo copia el gráfico desde Rótulos de gráficos a una nueva presentación de Microsoft PowerPoint. Sub MS_PowerPoint() 'Para ejecutar esta macro, haga clic en Referencias, en el menú Herramientas, 'y seleccione las bibliotecas de objetos de Microsoft Powerpoint 9.0. Dim ppt As Object, pres As Object 'Crea una sesión Microsoft PowerPoint Ir al código de Set ppt = CreateObject("powerpoint.application") automatización 'Copia el gráfico en la hoja Rótulos de gráficos MS_Powerpoint Worksheets("Rótulos de gráficos").ChartObjects(1).Copy 'Hace visible PowerPoint ppt.Visible = True 'Activa PowerPoint AppActivate ppt.Name 'Abre un documento nuevo en Microsoft PowerPoint Set pres = ppt.Presentations.Add 'Agrega una diapositiva pres.Slides.Add 1, ppLayoutBlank 'Pega el gráfico ppt.ActiveWindow.View.Paste Set ppt = Nothing End Sub MICROSOFT OUTLOOK Este ejemplo crea y agrega información en una tarea nueva de Outlook. Ejecute Outlook y haga clic en Tareas en la barra de Outlook para ver la nueva tarea. NOTA: La tarea puede demorar unos minutos en aparecer. Sub MS_Outlook() 'Para ejecutar esta macro, haga clic en Referencias, en el menú Herramientas, 'y seleccione las bibliotecas de objetos de Microsoft Outlook 9.0. Dim ol As Object, myItem As Object 'Crea una sesión de Microsoft Outlook Ir al código de Set ol = CreateObject("outlook.application") automatización 'Crea una tarea MS_Outlook Set myItem = ol.CreateItem(olTaskItem) Automation Code 'Agrega información a la nueva tarea With myItem .Subject = "Nueva tarea de VBA" .Body = "Esta tarea se creo desde Microsoft Excel mediante la Automatización" .NoAging = True .Close (olSave) End With 'Quita el objeto de la memoria Set ol = Nothing End Sub

Objetos de datos ActiveX (ADO) Los objetos de datos ActiveX (ADO) permiten manipular directamente bases de datos. En este libro se incluyen varios ejemplos que muestran varias de las tareas más comunes que se realizan con bases de datos. Recuperación de datos mediante controladores ISAM En este ejemplo se recuperan datos de un archivo de dBase y se colocan los resultados en una hoja de cálculo nueva. Para utilizar este ejemplo debe tener instalado el controlador de Microsoft dBase. También debe tener instalado el archivo Customer.dbf.

Sub RetrieveISAMdata() '' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente ''' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft Dim Dim Dim Dim Dim

Ir al código de ejemplo Recuperar datos ISAM

conn As ADODB.Connection rst As ADODB.Recordset NewBook As Workbook PathToDataBase As String i As Integer

' Crea el objeto Connection. Set conn = New ADODB.Connection ' Establece la ruta de acceso a la base de datos PathToDatabase = Application.Path & "\" & _ Application.LanguageSettings.LanguageID(msoLanguageIDInstall) With conn 'Asigna la cadena de conexión al objeto de conexión. .ConnectionString = "DRIVER={Microsoft dBase Driver (*.dbf)};" & _ "DBQ=" & PathToDatabase & ";" & _ "DefaultDir=" & PathToDatabase & "\" ' Abre la conexión .Open strConn End With ' Crea un nuevo objeto objeto Recordset. Set rst = New ADODB.Recordset With rst ' Conecta este conjunto de registros a la conexión abierta anteriormente. .ActiveConnection = conn ' Recupera todos los registros de la tabla Customer. .Open "SELECT * FROM customer" End With

' Agrega una nueva hoja de cálculo a este libro Set NewBook = Workbooks.Add ' Examina todos los campos y devuelve sus nombres a la hoja de cálculo. For i = 0 To rst.Fields.Count - 1 NewBook.Sheets(1).Range("a1").Offset(0, i).Value = rst.Fields(i).Name Next i ' Copia el conjunto de registros a la nueva hoja de cálculo. NewBook.Sheets(1).Range("a2").CopyFromRecordset rst ' Cierra el conjunto de registros Set rst = Nothing ' Cierra la conexión. conn.Close End Sub Recuperación de datos desde Microsoft Access En este ejemplo se utiliza ADO para crear una consulta basada en dos tablas de la base de datos neptuno.mdb. Para ello es necesario tener instalado el archivo neptuno.mdb en su equipo. Sub RetrieveAccessData() '' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente '' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft Dim Dim Dim Dim Dim

conn As ADODB.Connection rst As ADODB.Recordset Nsql As String, Njoin As String, Ncriteria As String NewBook As Workbook i As Integer

Ir al código de ejemplo Recuperar datos de Access

' Crea el objeto Connection. Set conn = New ADODB.Connection With conn ' Establece el proveedor OleDB para la conexión. .Provider = "Microsoft.JET.OLEDB.4.0" ' Abre una conexión a Neptuno.mdb. .Open Application.Path & "\samples\neptuno.mdb" End With 'Puede utilizar los símbolos "_" y "&" para dividir una mísma línea de código 'en dos líneas. Ambas líneas se ejecutarán como si fueran una sola. Nsql = "SELECT DISTINCTROW Categorías.NombreCategoría, " _ & "Productos.NombreProducto, Productos.CantidadPorUnidad, Productos.PrecioUnidad " Njoin = "FROM Categorías INNER JOIN Productos ON Categorías.IdCategoría = " _ & "Productos.IdCategoría " Ncriteria = "WHERE ((([Productos].Suspendido)=No)" _ & "AND (([Productos].UnidadesEnExistencia)>20));"

' Crea un nuevo objeto Recordset. Set rst = New ADODB.Recordset With rst ' Conecta este conjunto de registros a la conexión abierta. .ActiveConnection = conn ' Recupera todos los registros de la tabla Customer. .Open Nsql & Njoin & Ncriteria, conn, adOpenDynamic, adLockBatchOptimistic End With ' Agrega una nueva hoja de cálculo a este libro. Set NewBook = Workbooks.Add ' Examina todos los campos y devuelve sus nombres a la hoja de cálculo. For i = 0 To rst.Fields.Count - 1 NewBook.Sheets(1).Range("a1").Offset(0, i).Value = rst.Fields(i).Name Next i ' Copia el conjunto de registros a la nueva hoja de cálculo. NewBook.Sheets(1).Range("a2").CopyFromRecordset rst ' Cierra el conjunto de registros. Set rst = Nothing ' Cierra la conexión. conn.Close End Sub Mostrar la tablas de una base de datos En este ejemplo se muestran las tablas de la base de datos de Access Neptuno.mdb. Para ello es necesario que el archivo Neptuno.mdb esté instalado en el equipo.

Sub ListTables() ''' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente ''' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft Dim conn As ADODB.Connection Dim rst As ADODB.Recordset ' Crea el objeto Connection. Set conn = New ADODB.Connection With conn ' Establece el proveedor OleDB para la conexión. .Provider = "Microsoft.JET.OLEDB.4.0" ' Abre una conexión a Neptuno.mdb. .Open Application.Path & "\samples\neptuno.mdb" End With Set rst = conn.OpenSchema(adSchemaTables)

Ir al código de ejemplo Mostrar tablas

While Not rst.EOF If rst.Fields("TABLE_TYPE") <> "VIEW" Then MsgBox rst.Fields("TABLE_NAME") End If rst.MoveNext Wend Set rst = Nothing conn.Close End Sub Mostrar los campos de una tabla En este ejemplo se muestran las tablas de la base de datos de Access Neptuno.mdb. Para ello es necesario que el archivo Neptuno.mdb esté instalado en el equipo.

Sub ListFields() ''' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente ''' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim fld As ADODB.Field ' Crea el objeto Connection. Set conn = New ADODB.Connection With conn ' Establece el proveedor OleDB para la conexión. .Provider = "Microsoft.JET.OLEDB.4.0" ' Abre una conexión a Neptuno.mdb. .Open Application.Path & "\samples\neptuno.mdb" End With Set rst = New ADODB.Recordset With rst .ActiveConnection = conn .Open "SELECT * FROM customers" End With For Each fld In rst.Fields MsgBox fld.Name Next fld Set rst = Nothing conn.Close End Sub

Ir al código de ejemplo Mostrar campos

Agregar datos a una nueva tabla de una base de datos de Access En este ejemplo se agrega una nueva tabla a la base de datos neptuno.mdb. A continuación, la subrutina rellena la tabla con los datos que se encuentran en las celdas S1:V30 de esta hoja de cálculo. Para ello es necesario que el archivo neptuno.mdb esté instalado en el equipo. Sub CreateTable() ''' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente ''' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft '' Ext. Microsoft ADO 2.5 para DDL y seguridad ' Las primeras cuatro líneas Dim simultáneamente declaran y ' crean nuevos objetos. Puede utilizar esta sintaxis o bien ' utilizar la declaración Set para crear los objetos. Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim conn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim looprange As Range Dim currcell As Range With conn ' Establece el proveedor OleDB para la conexión. .Provider = "Microsoft.JET.OLEDB.4.0" ' Abre una conexión a Neptuno.mdb. .Open Application.Path & "\samples\neptuno.mdb" End With ' Establece la conexión activa para el objeto Catalog. cat.ActiveConnection = conn With tbl ' Asigna un nombre a la nueva tabla. .Name = "Tabla_de_ventas" ' Asigna un nombre a las columnas de la nueva tabla. With .Columns .Append "Nombre" .Append "Región" .Append "Producto" ' La columna Sales debe ser de tipo de datos "Moneda". .Append "Ventas", adCurrency End With End With ' Agrega la tabla a la base de datos. cat.Tables.Append tbl

Ir al código de ejemplo Crear una tabla

With rst .ActiveConnection = conn ' Abre la nueva tabla. El argumento LockType del método ' Open debe establecerse a adLockOptimistic a fin de ' agregar registros a la tabla. .Open "Tabla_de_ventas", LockType:=adLockOptimistic End With ' Establece el rango de la hoja activa que contiene ' los registros que se añadirán a la base de datos. Set looprange = Range("s2", Range("s2").End(xlDown)) ' Examina la información de la hoja de cálculo. For Each currcell In looprange With rst ' Agrega un nuevo registro. .AddNew ' Agrega información a los campos correspondientes. .Fields("Nombre").Value = currcell.Value .Fields("Región").Value = currcell.Offset(0, 1).Value .Fields("Producto").Value = currcell.Offset(0, 2).Value .Fields("Ventas").Value = currcell.Offset(0, 3).Value ' Escribe el nuevo registro a la base de datos. .Update End With Next currcell rst.Close Set tbl = Nothing Set cat = Nothing conn.Close End Sub Captura de errores de ADO En este ejemplo se muestra como capturar y devolver información importante sobre los errores que puedan recibirse al ejecutar código de ADO.

Sub Trap_ADO_Errors() ''' NOTA: Para utilizar esta subrutina, es necesario crear ''' una referencia a la versión más reciente ''' de la siguiente biblioteca: '' '' Biblioteca de objetos de datos ActiveX de Microsoft Dim conn As ADODB.Connection Dim tbar As String, msg As String ' Va a la sección denominada "ErrorHandler:" cuando ' se produce un error.

Ir al código de ejemplo Capturar errores de ADO

On Error GoTo ErrorHandler ' Crea el objeto Connection. Set conn = New ADODB.Connection With conn ' Establece el proveedor OleDB para la conexión. .Provider = "Microsoft.JET.OLEDB.4.0" ' Intenta abrir una conexión con abc.mdb. .Open "C:\abc.mdb" End With ' Sale de la subrutina si no se encuentra ningún error. Exit Sub ErrorHandler: ' Hace referencia al conjunto Errors del objeto ' Connection. With conn.Errors(0) ' La barra de título contendrá el origen del ' error. tbar = .Source ' Este mensaje muestra el número de error y ' el texto del mensaje de error. msg = "Error número: " & .Number & _ WorksheetFunction.Rept(Chr(13), 2) & _ .Description ' Muestra información sobre el error. MsgBox prompt:=msg, Title:=tbar End With End Sub

No modifique esta tabla. Se utiliza para los ejemplos que se muestran

Ir al código de ejemplo Recuperar datos ISAM

teriormente.

r al código de ejemplo cuperar datos de Access

ea de código

os.PrecioUnidad " egoría = " _

tchOptimistic

Ir al código de ejemplo Mostrar tablas

Ir al código de ejemplo Mostrar campos

Ir al código de ejemplo Crear una tabla

Ir al código de ejemplo Capturar errores de ADO

No modifique Se utiliza para los ejemplos que se muestran

Nombre Carlos Carlos Carlos Carlos Juan Juan Juan Ana Ana Ana Ana Carmen Carmen Carmen Carmen Elena Elena Elena Elena Pablo Pablo Pablo Pablo Julia Julia Julia Julia

Región Este Este Este Este Sur Sur Sur Sur Sur Sur Sur Este Este Este Este Sur Sur Sur Sur Norte Norte Norte Norte Norte Norte Norte Norte

Producto Manzanas Bananas Peras Naranjas Bananas Manzanas Peras Manzanas Peras Naranjas Bananas Bananas Manzanas Peras Naranjas Naranjas Bananas Manzanas Peras Peras Naranjas Bananas Manzanas Peras Naranjas Bananas Manzanas

Ventas $759 $707 $188 $121 $689 $244 $962 $732 $908 $755 $30 $979 $123 $21 $799 $244 $516 $562 $190 $300 $704 $785 $527 $513 $800 $138 $430