MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL, Apuntes de Introducción a la Gestión Empresarial

¡Descarga MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL y más Apuntes en PDF de Introducción a la Gestión Empresarial solo en Docsity! MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL Introducción al Visual Basic 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 1 Índice 1. INTRODUCCION (a propósito de Visual Basic) ...................................................................... 4 2. OBJETIVOS ............................................................................................................................. 4 3. DESARROLLO DE LOS EJEMPLOS ........................................................................................... 5 3.1. Creación de un “botón” que al apretarlo escriba HOLA. .............................................. 5 3.2. Acumulación de “HOLA”’s en la misma celda. .............................................................. 6 3.3. Acumulación de texto en varias diagonales sucesivas. ................................................. 7 3.4. Programación de series de Fibonacci. ........................................................................... 8 4. NUEVOS EJEMPLOS: OBTENCIÓN DE NÚMEROS PRIMOS .................................................. 18 5. CONCLUSIONES ...................................................................... ¡Error! Marcador no definido. 6. ANEXO (Sentencias y funciones habituales) ....................................................................... 23 6.1. Problemas con variables (¿por qué no se dejan definir las variables?) ...................... 24 6.1.1. Option Explicit: .................................................................................................... 24 6.1.2. Dim…. As [Integer, Double, String, Boolean…]: ................................................... 24 6.2. Condición If…, etc. (diversas posibilidades): ............................................................... 24 6.2.1. Select Case ........................................................................................................... 25 6.2.2. Ejemplos de utilización ........................................................................................ 25 6.3. Bucles: For... To ... Next/Do While... Loop/Do Loop… Until (Utilización y posibles problemas): ............................................................................................................................. 25 6.3.1. Do... Loop Until .................................................................................................... 25 6.3.2. Do While... Loop .................................................................................................. 26 6.3.3. For... To... Next .................................................................................................... 26 6.3.4. With ..................................................................................................................... 27 6.4. Coordenadas polares: ¿Cómo pasar de coordenadas cartesianas (x,y) a polares (r,α)?: 28 6.4.1. Radio (calculado a partir de las coordenadas x e y de los puntos en cuestión) r = RaizCuadrada(x^2+y^2): ...................................................................................................... 28 6.4.2. Ángulo (calculado a partir de las coordenadas x e y de los puntos en cuestión) α=Arctan (x/y): .................................................................................................................... 28 6.5. Cambiar criterios de ordenación: ................................................................................ 29 6.6. Menús… ....................................................................................................................... 29 6.7. Para Ordenar ............................................................................................................... 30 6.8. Quitar el signo de los números convertidos en string: ............................................... 30 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 4 1. INTRODUCCION (a propósito de Visual Basic) Visual Basic para aplicaciones es una combinación de un entorno de programación integrado denominado Editor de Visual Basic y del lenguaje de programación Visual Basic, permitiendo diseñar y desarrollar con facilidad programas en Visual Basic. El término “para aplicaciones” hace referencia al hecho de que el lenguaje de programación y las herramientas de desarrollo están integrados con las aplicaciones del Microsoft Office (en este caso, el Microsoft Excel), de forma que se puedan desarrollar nuevas funcionalidades y soluciones a medida, con el uso de estas aplicaciones. El Editor de Visual Basic contiene todas las herramientas de programación necesarias para escribir código en Visual Basic y crear soluciones personalizadas. Este Editor, es una ventana independiente de Microsoft Excel, pero tiene el mismo aspecto que cualquier otra ventana de una aplicación Microsoft Office, y funciona igual para todas estas aplicaciones. Cuando se cierre la aplicación, consecuentemente también se cerrará la ventana del Editor de Visual Basic asociada. Este manual ha sido elaborado por José Pedro García Sabater con la colaboración de Gonçal Bravo i Reig y Alberto López Gozalbes a lo largo de diversas versiones de la hoja de cálculo Microsoft Excel. Es posible que a lo largo del mismo se hallen algunas inexactitudes ligadas entre otras razones a la evolución de Excel. Si encuentran errores sería estupendo que nos lo hicieran saber para así corregirlos. 2. OBJETIVOS El documento está inicialmente dirigido a alumnos de ingeniería que con mínimos conocimientos de programación pueden entender cómo funciona el VBA de Excel. No se pretende enseñar a programar, sólo a utilizar el entorno y a sacar partido al mínimo conocimiento en programación que tienen mis alumnos de ingeniería. Lo que se pretende con este manual es presentar de una manera práctica, diferentes utilidades, funciones, sentencias…, en el Editor de Visual Basic, y que con posterioridad serán útiles para el desarrollo del ejercicio concreto de que consta la práctica. Los ejemplos son sencillos e incluso un poco tontos, y desde luego inútiles en sí mismos. En cada ejemplo se presentan una o varias funcionalidades. Tomando ejemplos sencillos, se irán mostrando sucesivamente las diferentes utilidades a realizar o utilizar. Son utilidades básicas cómo definir un botón de ejecución de programa, cómo dar valores a celdas de la página de Microsoft Excel (mediante un programa definido en el Editor de Visual Basic), cómo definir e introducir bucles y condiciones,… 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 5 3. DESARROLLO DE LOS EJEMPLOS 3.1. Creación de un “botón” que al apretarlo escriba HOLA. Vamos a crear un botón, que al hacer clic sobre él, muestre en la celda A1 la expresión “HOLA”. Para ello, en primer lugar, se instalará en el documento de Microsoft Excel, el menú Programador (Menú Archivo -> Opciones -> Personalizar cinta de opciones y se selecciona la casilla Programador). Una vez hecho esto, aparecerá la pestaña Desarrollador desde la que se pueden añadir los botones dentro de la pestaña. En él se tomará el icono que representa a un botón, desplegándose en la Hoja1, por ejemplo, del documento Excel. De los dos botones que hay (tanto en formularios como en ActiveX), se seleccionará el de Controles de ActiveX, ya que de este modo se podrá cambiar el color y otras opciones del propio botón. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 6 Una vez hecho esto, se pulsará dos veces sobre dicho botón para acceder así al Editor de Visual Basic, con el que se realizará el pequeño programa requerido, tal y como sigue: 3.2. Acumulación de “HOLA”’s en la misma celda. Ahora vamos a cambiar el programa anterior, cambiando una de las líneas de programa, para hacer que cada vez que se haga un clic en el botón, se acumule un nuevo “HOLA” (igual que podría ser cualquier otro valor numérico o cadena de caracteres) al anterior. De esta forma, se identificará el contenido de la primera celda como un contador, acumulándose, en cada clic sobre el botón, una nueva cadena de texto en dicha celda contador. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 9 Pero, ¿qué es una macro?, y ¿cómo se graba una macro? En primer lugar, se debería considerar que una macro es un pequeño programa ejecutable desde la Hoja de Cálculo, y que realiza funciones repetitivas o comunes en la normal ejecución de la actividad con la herramienta de cálculo. Así, y en el caso particular de grabar una macro para poder cambiar de color una serie de celdas de la Hoja de Cálculo, se procede de la siguiente forma. En el menú, se toma la opción Desarrollador, y en ésta, Grabar macro. Acto seguido, se realiza la acción a grabar en la macro, en este caso, cambiar de color el color de una columna de la hoja de cálculo. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 10 Abriendo la opción de Visual Basic, la macro grabada quedaría reflejada de la siguiente manera: En el paso anterior se ve, en el código definido por la macro, la opción Range; esto define el rango de aplicabilidad de la opción escogida con el código, en ese caso el cambio de color de las celdas A1 hasta la A10. Además se le puede cambiar el nombre al botón para que deje de “llamarse” CommandButton1 y así poder ponerle el nombre deseado y cambiar otras propiedades como el color del botón. Pero, ¿cómo se consigue cambiar el nombre al botón? Para ello, se selecciona el Modo Diseño del cuadro de controles de la pestaña Desarrollador, una vez ahí, se haría clic con el botón derecho del ratón, sobre el botón al que se le quiere cambiar el nombre. Acto seguido, se selecciona la opción Propiedades y dentro de estas se cambia la opción Caption. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 11 Una vez mostradas las acciones anteriores, se va a pasar a definir el ejemplo concreto. Así, y como ya habíamos dicho, vamos a definir el código de programa necesario para por un lado generar la serie de Fibonacci de términos aleatorios, y por el otro, tomar de los valores de la serie anterior exclusivamente las cifras correspondientes a las unidades. Aquí pueden observarse dos bloques diferenciados de programa, cada uno para un botón diferente (que se pueden ver en la transparencia siguiente). En el primero se crea una serie de 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 14 al número presente en cada celda, pero evitando el negro (correspondiente al 0), y el blanco (correspondiente al 1). Tras lo definido anteriormente, y haciendo clic sobre el cuarto botón, se obtendría el diagrama de Gantt correspondiente a la serie previamente calculada, cambiando cada vez que se ejecutara todo el proceso completo. Botón 1  Botón 2  Botón 3  Botón 4. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 15 Una vez realizado lo anterior, vamos a mostrar como emplear la función de Visual Basic, Call. Con esta función lo que pretendemos, es poder hacer llamadas desde dentro de un programa a otro que puede ser utilizado varias veces, y de esta forma, evitaríamos tener que definir el programa correspondiente cada vez. En este caso, vemos como una vez definida la serie de Fibonacci (de la misma forma que ya se ha visto previamente en varias ocasiones, siguiendo el mismo ejemplo), se introducen tres 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 16 llamadas a otras tantas funciones independientes previamente definidas (como se ha visto en las transparencias precedentes), mediante la función call. Así, una vez calculada mediante el bucle Do While la serie de Fibonacci, se llamaría inicialmente a la función Reducir. Ésta, como ya se ha visto, tomaría el resultado anterior, “reduciéndolo” a la cifra de unidades correspondiente a cada uno de los elementos de la serie anterior. Se vería, de la misma forma que se veía en un punto anterior, como con la utilización de la función resto mod, entre 10, conseguimos tomar o “reducir” la cifra correspondiente a las unidades de los elementos de la serie de Fibonacci previamente calculada. A continuación, se llama a la función Ordenar, que realizará la ordenación de los elementos de la serie numérica previamente calculada, de mayor a menor (siendo este código obtenido, como ya se había explicado, a partir de la grabación de una macro utilizando la función ordenar del menú). Tomando como rango de elementos a ordenar, la primera columna (A), desde la celda 1 a la 15, en este caso. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 19 Como se puede ver en la pantalla anterior del Editor de Visual Basic, el programa previamente descrito se ha dividido en dos partes. En la parte que vemos aquí (declarada a partir del botón) mediante la función InputBox, se declarará un menú que se verá en la página de la Hoja de Cálculo del Microsoft Excel, presentando el texto “DIME UN NUMERO”, identificado con la variable numero definida como string. Esta cadena (que recibe el número que se introduciría desde teclado) mediante la función Val, registrará el valor numérico deseado que se pasaría a la otra función (la que calcularía si dicho número es primo o no). Esto también se podría haber conseguido de una manera un poco más simple, declarando únicamente valor como entero y guardando el número introducido en la InputBox directamente como entero como se puede ver en la siguiente captura: Una vez hecho esto, dentro de una condición if, y utilizando la función MsgBox (esta función, al igual que la previamente definida InputBox, tiene como misión el mostrar en pantalla un mensaje en forma de menú de Windows, pero ahora presentando un resultado determinado y definido desde programa) se mostraría un mensaje sobre la Hoja de Cálculo, diciendo si el número previamente introducido es primo o no. Tal como se ha visto previamente, tomando el valor de la variable valor se llama a la función esprimo (x), donde la variable x equivale al valor enviado valor. Así, definiendo esta función como Boolean, la cual daría como resultado una respuesta verdadera o falsa (true o false), se entraría en un bucle Do While (que utiliza como condiciones que el número introducido es 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 20 inicialmente primo, para entrar en el bucle, y que el último número por el que se dividirá el introducido, para comprobar si es primo o no, deberá ser menor o igual a la raíz cuadrada del introducido). En este bucle, dentro se pondría una condición if, en la que indica que para que un número no sea primo, el resto de dividirlo por otro menor que él debe ser cero. Como se puede comprobar, al trabajar con variables Booleanas, se devuelve o recibe un True o un False, que en función de la definición de la condición if del siguiente programa (el definido por el botón), se dará como resultado lo correspondiente al “si” (if) o al “sino” (else). Ahora se ve cómo quedaría en la pantalla de la hoja de Excel lo expuesto previamente. Se ve, en la página siguiente, como al hacer clic sobre el botón, aparecería el menú pidiendo un número, y acto seguido se diría si éste es primo o no. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 21 Ahora se van a definir los dos programas necesarios para obtener un número determinado de números primos, siguiendo el mismo esquema previamente definido. Primero se ve cómo se define con la función InputBox, un nuevo menú en el que se pide el número de números primos deseado. Además, se incluye una línea de código para poder borrar el listado previo de números primos cada vez que se haga clic sobre el botón (para que salga un nuevo menú). Esta llamada mediante la función Call, se hace a una macro grabada mientras se seleccionaba toda la columna A y se borraba su contenido, como se puede ver. Aquí se puede observar el segundo programa, llamado por el primero, y pasándole el número de números primos a generar (tamaño de la lista) empezando por el 2. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 24 Application.screenupdating=True Application.calculation=xlCalculationAutomatic Application.EnableEvents=True Application.CutCopyMode = False En estos anexos se podrán encontrar instrucciones para Visual Basic y para las hojas de cálculo de Excel. A éstas últimas se les puede reconocer fácilmente dentro de los anexos porque no van introducidas dentro de ningún “Sub” y además las instrucciones referidas a las hojas de cálculo Excel van escritas en mayúsculas. Un ejemplo de una instrucción referida a las hojas de cálculo Excel puede ser: DESREF (C11; 0; SI (C6>$C$3;-$C$3;-C6); 1; 1) 6.1. Problemas con variables (¿por qué no se dejan definir las variables?) 6.1.1. Option Explicit: Con esta aplicación, se avisaría en caso de no tener definida una variable, o en caso de utilizar datos de páginas diferentes a la activa. 6.1.2. Dim…. As [Integer, Double, String, Boolean…]: Con esto queda la variable perfectamente definida, si no se pusiera no ocurriría posiblemente nada, salvo que se utilizaría una mayor cantidad de memoria de la necesaria, al definirse instantáneamente en el momento de utilizarla como de tipo Value. 6.2. Condición If…, etc. (diversas posibilidades): If … Then … / If … Then … Else … / If … Then … ElseIf … Then … ¿Cuándo poner el EndIf?, ¿cuándo no?, ¿cuándo se deberían usar los “:” (dos puntos)? Las instrucciones If...Then...Else se pueden presentar en varios formatos, con unas características determinadas. Normalmente, se presentan anidadas en tantos niveles como sea necesario. Esto, sin embargo, puede hacer menos legible el código, por lo que es aconsejable utilizar una instrucción Select Case en vez de recurrir a múltiples niveles de instrucciones If...Then...Else anidadas (únicamente en caso de que el excesivo número de anidamientos pudiera dar problemas en la legibilidad del programa, o errores en la depuración de éste). Así, si realizamos la condición en varias líneas de código, será necesario cerrar el anidamiento con un End If; instrucción que no se usaría en caso de realizar la condición en un sola línea (If Then, condición cierta). 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 25 6.2.1. Select Case En este caso, esta instrucción será más útil que la Condición If..., cuando se ejecute uno de varios grupos de instrucciones, dependiendo del valor de una expresión condición a cumplir. 6.2.2. Ejemplos de utilización Ahora se presentan una serie de ejemplos prácticos, con los que aclarar y facilitar el uso de las condiciones If en la programación en Visual Basic. Básicamente, en el ejemplo siguiente se observa, como se deberían de utilizar los anidamientos consecutivos de If… Then, ElseIf… Then y Else. Se observa aquí, cómo se utilizaría el End If, siempre en el caso del anidamiento de condiciones, y no en el caso de escribir la condición If (general) en una sola línea. If b > 0 And c > 0 Then Hoja1.Cells(i, 8) = a Else If b > 0 And c < 0 Then Hoja1.Cells(i, 8) = a + 360 Else Hoja1.Cells(i, 8) = a + 180 End If End If 6.3. Bucles: For... To ... Next/Do While... Loop/Do Loop… Until (Utilización y posibles problemas): Las estructuras de bucle también son conocidas por el nombre de estructuras de control. Permitiendo la repetición de determinadas acciones. Uno de los errores más comunes que se producen en la utilización de bucles de este tipo, es la no inicialización de las variables utilizadas como contadores de iteraciones. Así que habrá que prestar una atención especial en este punto. Una opción para evitar este posible error, sería la definición al principio del programa, como primera línea de código de éste, el ya comentado Option Explicit. A continuación se presentan las diferentes opciones que permite el Visual Basic para definir bucles, es decir, repetición y/o acumulación de acciones determinadas, entre unos límites definidos. La no definición de estos límites concretos, sería otro error común y más problemático, al producirse la entrada en bucles infinitos, que bloquean el módulo de cálculo de nuestro ordenador. 6.3.1. Do... Loop Until Esta estructura de control se puede usar para ejecutar un bloque de instrucciones un número indefinido de veces. Las instrucciones se repiten hasta que una condición llegue a ser True. 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 26 Un ejemplo podría ser el siguiente: Sub ComPrimeroUntil () contador = 0 miNum = 20 Do Until miNum = 10 miNum = miNum – 1 contador = contador + 1 Loop MsgBox "El bucle se ha repetido " &contador& " veces." End Sub 6.3.2. Do While... Loop Siguiendo lo explicado en el punto inicial, otro error común sería el no introducir la línea de acumulación del contador (por ejemplo: i = i + 1), con lo que el bucle entraría cada vez en el cálculo, quedándose colgado en este punto. En este caso, las instrucciones se repiten mientras una condición sea True (al contrario que con el Do… Loop Until). Este tipo de bucle se utilizaría normalmente en caso de tener que cumplirse una condición marcada por el While. Así, en este tipo de bucles, se puede dar el caso de que no se entre desde el primer momento, debido al no cumplimiento de esta condición. Ejemplo de utilización de esta función (hay que fijarse en la inicialización previa de la variable contador i): i = 5 Do While Hoja1.Cells(i, 2) <> "" b = Hoja1.Cells(i, 5) c = Hoja1.Cells(i, 6) d = (b ^ 2) + (c ^ 2) a = Sqr(d) Hoja1.Cells(i, 7) = a i = i + 1 Loop 6.3.3. For... To... Next Mediante la palabra clave Step, se puede aumentar o disminuir la variable contador en el valor que se desee (For j = 2 To 10 Step 2). Se pueden anidar bucles For...Next, colocando un bucle For...Next dentro de otro. Para ello, hay que proporcionar a cada bucle un nombre de variable único como su contador. La siguiente construcción es correcta: For i = 1 To 10 For j = 1 To 10 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 29 If b > 0 And c < 0 Then Hoja1.Cells(i, 8) = a + 360 Else Hoja1.Cells(i, 8) = a + 180 End If End If i = i + 1 Loop Hay que darse cuenta de que se utiliza la condición anidada If ... Then ... Else ... End If, porque Excel, da valores de ángulo en el plano de las X positivas (1er y 4º cuadrantes), por lo que para poder tener una visión clara de la posición de cada punto en función de su ángulo (tenerlo marcado de 0o a 360o), se debería sumar 180 a los valores de ángulo obtenidos de los puntos situados en el 2º y 3er cuadrantes, y 360 a aquellos situados en el 4º cuadrante. 6.5. Cambiar criterios de ordenación: Aquí se puede ver cómo se podrían definir criterios de ordenación (ascendente o descendente), en función de la necesidad del programador, y respecto a una columna o rango predefinida. Application.AddCustomList ListArray:=Range("J2:J21") numlista = Application.CustomListCount Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=numlista + 1, MatchCase:=False, Orientation:=xlTopToBottom,_ DataOption1:=xlSortNormal Application.DeleteCustomList ListNum:=numlista 6.6. Menús… Se definen menús específicos tomándolos como variables definidas como barras de comandos de control, o de otros tipos, y dándoles a su vez los nombres correspondientes a estos menús de trabajo. Dim MenuAyuda As CommandBarControl Dim MenuNuevo As CommandBarPopup Dim Plan As CommandBarControl Call BorrarMenu Set MenuAyuda = CommandBars(1).FindControl(ID:=30010) If MenuAyuda Is Nothing Then Set MenuNuevo = CommandBars(1).Controls.Add(Type:=msoControlPopup, Temporary:=True) Else Set MenuNuevo = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=MenuAyuda.Index, Temporary:=True) 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 30 End If MenuNuevo.Caption = "Plan de Recuento" Set Plan = MenuNuevo.Controls.Add(Type:=msoControlButton) Plan.Caption = "Plan de Recuento" Plan.OnAction = "CalculaPlan" 6.7. Para Ordenar Básicamente, se busca lo mismo que cuando se hablaba del cambio de criterios de ordenación. Range ("D18:F23").Select Selection.Sort Key1:=Range("D19"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 6.8. Quitar el signo de los números convertidos en string: Estas líneas de código sirven para poder tomar números positivos siempre, aunque se introdujeran negativos (por error o cálculo), a través de un menú, cálculo,… El resultado sería semejante a la utilización de la función del Editor de Visual Basic, Abs (numero) (que devuelve el valor absoluto de todo número introducido entre paréntesis. nombre = Str(i) nombre = Right(nombre, Len(nombre) - 1) 6.9. Cuando queremos poner referencias relativas a variables en la fórmula: Referencias que corresponderían con los valores de la celda correspondiente de la Hoja de Cálculo con la que se está trabajando. DESREF (C11; 0; SI (C6>$C$3;-$C$3;-C6); 1; 1) 6.10. Temporizador: Función encargada de dar un intervalo de tiempo, previamente a la obtención de un resultado, o por otra razón necesitada por el programador. Dim ppio As Single ppio = Timer Do While ppio + 10 > Timer Loop 6.11. Funciones: Definición de funciones (con la forma que se requiera, sea Integer para entero, o de cualquier otro tipo), dentro del programa, en el Editor de Visual Basic, con la intención de tenerlas 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 31 definidas a parte de la programación del botón en la Hoja de Cálculo, o para una rellamada a posteriori, por ejemplo con la función Call. Function fact(x) As Integer End Function 6.12. Zoom de la ventana: Zoom, agrandará o empequeñecerá la presentación del formulario preseleccionado, en la ventana activa de trabajo. ActiveWindow.Zoom = 25 6.13. Para cancelar el botón: Bastaría con introducir la orden siguiente: End 6.14. Procedimiento que empieza con un formulario: Sub Prevision_Userform() CommandButton1.Caption = "Previsión" End Sub 6.15. Otro modo de cambiar el color: Esta es una de las opciones válidas para el cambio de color, en una celda, o en cualquier otro objeto seleccionado. Hay que considerar, que en este caso se realizaría mediante una graduación de los tres colores básicos disponibles (rojo, verde y azul), aunque también podría hacerse mediante valores numéricos globales, representando las mezclas correspondientes de estos colores básicos. Label10.BackColor = RGB(242, 148, 150) 6.16. Para abrir un formulario: Línea de código que mostraría/abriría un formulario, que en este caso ha sido llamado Prevision. frmPrevision.Show 6.17. Para ocultar un formulario: frmPrevision.Hide Ambas sentencias (la 6.16 y la 6.17), sencillas como se puede comprobar, se refieren a la apertura de formularios referentes a objetos determinados. También estarían aquí relacionados los UserForm. 6.18. Procedimiento que empieza automáticamente: Este procedimiento, abriría… 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 34 6.30. Formato interior de Celda: Con este código de programa, se le daría a la celda activa, o seleccionada (o al rango activo o seleccionado en su interior), unos valores determinados de grado de color, y de formato de texto. Selection.Interior.ColorIndex=34 Selection.Interior.Pattern=xlsolid 6.31. Enteros aleatorios entre límites: Para producir enteros aleatorios en un intervalo dado, usa esta fórmula: Int ((Límite_superior - límite_inferior + 1) * Rnd + límite_inferior) Aquí, límite_superior es el número mayor del intervalo y límite_inferior es el número menor del intervalo. Nota: Para repetir secuencias de números aleatorios, se debe llamar a la función Rnd con un argumento negativo antes de utilizar la función Randomize con un argumento numérico. Al utilizar la instrucción Randomize con el mismo valor de número, no se repite la secuencia anterior. 6.32. Suprimir los cuadraditos en un texto importado: Se ha importado en la columna A un texto desde otro programa pero todo aparece lleno de pequeños cuadraditos que se deberían suprimir. Para ello, se puede utilizar esta macro para conocer los códigos de los caracteres que los generan. Sub acode() For i = 1 To 255 Range("a" & i) = Chr(i) Next End Sub Para reemplazar estos caracteres por un espacio, se puede utilizar esta otra macro: Sub Macro1Cuadrados() Dim c For Each c In Range("A1:" & _ Range("A1").SpecialCells(xlCellTypeLastCell).Address) For i = 1 To 31 Application.StatusBar = c.Address & " " & i On Error Resume Next Range(c.Address) = Application.Substitute(c, Chr(i), " ") 'Err.Clear 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 35 'Resume Next Range(c.Address) = Application.Substitute(c, Chr(127), " ") Range(c.Address) = Application.Substitute(c, Chr(129), " ") Range(c.Address) = Application.Substitute(c, Chr(141), " ") Range(c.Address) = Application.Substitute(c, Chr(143), " ") Range(c.Address) = Application.Substitute(c, Chr(144), " ") Range(c.Address) = Application.Substitute(c, Chr(157), " ") Next Application.StatusBar = False End Sub 6.33. Seleccionar los caracteres en una celda Excel: ¿Cómo elegir por orden alfabético creciente o decreciente una celda Excel que contenga una cadena de caracteres? Para esto, se puede utilizar esta función de T.Shuttleworth con algunas modificaciones: Option Compare Text Function SortString(ByVal iRange, Optional Creciente As Boolean = True) Dim i%, j%, sTemp$ For j = 1 To Len(iRange) – 1 For i = 1 To Len(iRange) – 1 If Mid(iRange, i, 1) > Mid(iRange, i + 1, 1) Then sTemp = Mid(iRange, i, 1) Mid(iRange, i, 1) = Mid(iRange, i + 1, 1) Mid(iRange, i + 1, 1) = sTemp End If Next i Next j If Creciente = False Then For i = Len(iRange) To 1 Step -1 SortString = SortString & Mid(iRange, i, 1) Next Exit Function End If SortString = iRange End Function También se puede utilizar esta solución mediante fórmula MATRICIAL utilizando la XLL (morefun.xll) que se puede cargar en: http://www.freewarefiles.com/downloads_counter.php?programid=14922 =MCONCAT(TRIV(STXT(A1;SIGUIENTE(NBCAR(A1);1);1);;1)) 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 36 6.34. Insertar automáticamente retornos de carro en un texto: Al introducir texto mediante una macro en una celda, se pretende que este texto sea cortado cada 100 caracteres, pero sin cortar las palabras. El texto en cuestión podría ser truncado con la macro siguiente: Function Corte(TxTronque As String, LgMax As Integer) As String Dim i As Integer Dim p As Integer Dim FinLigne As Long p = 1 i = 0 Do While i < Len(TxCorte) FinLigne = InStr(p, TxCorte, Chr(10)) If FinLigne > LgMax Then i = i + LgMax Else: i = FinLigne + LgMax End If Do While Mid(TxCorte, i, 1) <> " " i = i - 1 If i = 0 Then If FinLigne = 0 Then i = p + LgMax: Exit Do i = FinLigne + LgMax: Exit Do End If Loop Mid(TxCorte, i, 1) = vbCr i = i + LgMax p = i + 1 Loop Corte = TxCorte End Function 6.35. Comodines de búsqueda: Existen comodines en Excel para reemplazar los caracteres en una búsqueda. El operador “*” puede reemplazar un grupo de caracteres, y el comodín “?” uno solo. La utilización en la función búsqueda de “*” y de “?” puede causar desórdenes importantes en los ficheros. Así, si se busca por ejemplo la palabra "completándola" y se quieren recuperar todas las posibilidades de escritura con los acentos o no, se utilizarían los comodines “*” y “?” de la siguiente forma "complet?ndola" o "complet*a" 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 39 6.41. Quitar los números de una cadena de caracteres: ¿Es posible, en una celda, eliminar la cifra que sigue a un nombre? Por ejemplo: en una tabla, se tienen los nombres siguientes con un número (sin espacio) ZAZA1, ZAZA2 etc. El objetivo es el de encontrar ZAZA, quitando los números y sabiendo que a veces se pueden encontrar también ZAZA11, ZAZA252 y hasta ZA345ZA. Function SoloTexto(s As String) For a = 1 To Len(s) If Mid(s, a, 1) <= 9 Then Else SoloTexto = SoloTexto + Mid(s, a, 1) End If Next End Function Atención: si una cifra se encuentra en el medio de la palabra igualmente se suprime. O incluso: (aquí se conserva en lugar de quitar) Range("B1")= Left(Range("A1"),4) 6.42. Buscar una cadena de caracteres en otra: Se abre un fichero de texto y se lee línea a línea: debiéndose verificar que cada vez que se pasara de línea (retstring) se tuviera la cadena de caracteres ".htm". ¿Cómo se haría? Por ejemplo, para buscar una “a” en hablar. Position = InStr([inicio], "hablar", "a") Si la cadena buscada se encuentra, el resultado es la posición del primer carácter de la cadena buscada en la cadena comprobada. Como esta función diferencia entre mayúsculas y minúsculas, se debería, o comprobar las dos, o comprobarlo todo en mayúsculas o todo en minúsculas. Pudiéndose obtener algo así: Do While f.AtEndOfStream <> True retstring = f.Readline Position = InStr(UCase(f.Readline), ".HTM") If Position > 0 Then 'Instrucciones en caso de que se cumpla la condición Else 'Instrucciones en caso de que NO se cumpla la condición 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 40 End If Loop 6.43. Trocear una frase sin cortar las palabras: Se querría cortar una frase, sin cortar las palabras, de tal manera que cada trozo de frase, puesto en celdas adyacentes, no comportara más de 20 caracteres. Public Sub Parse20PerCell() Dim bigString As String Dim tempStr As String Dim cell As Range Dim pos As Integer Set cell = Range("A1") bigString = cell.Text Do While bigString <> "" Set cell = cell.Offset(0, 1) If Len(bigString) < 21 Then cell.Value = Trim(bigString) bigString = "" Else tempStr = Right(StrReverse(bigString), 21) pos = InStr(tempStr, " ") If pos = 0 Then MsgBox "More than 20 contiguous characters between spaces." Else cell.Value = Trim(StrReverse(Mid(tempStr, pos + 1, 255))) bigString = Mid(bigString, 22 - pos, 255) End If End If Loop End Sub Otra solución: El número máximo de caracteres de la frase es, en este ejemplo, inferior a 1000 y el número de celdas en las que el texto se tiene que repartir se supone inferior a 100. Sub test1() Set Rng = Sheets(1).Range("A1") iTotal = Mid(Rng, k + 1, 1000) & " " For j = 2 To 100 For i = 21 To 1 Step -1 If Mid(iTotal, i, 1) = " " Then 20 de febrero de 2014 [MANUAL BÁSICO PARA EMPEZAR A TRABAJAR CON MACROS DE VISUAL BASIC PARA EXCEL] 41 k = i Exit For End If Next Rng(1, j).Value = Mid(iTotal, 1, k - 1) iTotal = Mid(iTotal, k + 1, 1000) Next End Sub 6.44. Última palabra de una frase: ¿Cómo conseguir con una fórmula de la hoja extraer la última palabra de una frase? =DERECHA(A1;ENCONTRAR(" ";COINCIDIR(A1;LARGO(A1)- FILA(INDIRECTO(""&LARGO(A1)));1);0)) 6.45. Borrar el carácter de la derecha: ¿Cómo borrar el carácter situado más a la derecha en una celda? Por ejemplo, si el texto se encuentra en B10 =IZQUIERDA(B10;LARGO(B10)-1) 6.46. Comprobar la presencia de una cadena de caracteres: ¿Cómo verificar que una cadena de caracteres se encuentre en una celda o en una variable? =CONTAR.SI(A1;"*texto*")=1 Reenvía TRUE si la cadena de caracteres (texto) se encuentra en la celda A1. 7. Pequeños Ejercicios 1- Activar la pestaña Desarrollador/Programador que habilita el uso de Macros/Visual Basic. 2- Añadir un botón permitiendo que se le cambie el texto, el tipo de letra y el color del botón desde Propiedades. 3- Grabar un vídeo en el que se muestre cómo mostrar la palabra “HOLA” en la celda A1 del Excel al pulsar sobre el botón. 4- Grabar un vídeo en el que se muestre cómo mostrar una acumulación de “HOLA”s seguidos en la celda A1 del Excel al pulsar sobre el botón. 5- Grabar un vídeo en el que se muestre cómo mostrar una acumulación de “HOLA”s en varias diagonales seguidas.