Análisis de datos. Los escenarios

A veces es necesario observar los datos, las operaciones y los resultados desde diversos ángulos. Excel cuenta con una herramienta, que nos facilita esta tarea: el administrador de escenarios. Herramienta de análisis que permite evaluar distintas estrategias, entendiendo por escenario  un conjunto de valores que se utilizan para prever los resultados de un modelo de hoja de cálculo.

El funcionamiento en Excel 2010 es similar a la versión 2003. Supongamos los datos de nuestro ejemplo, donde habíamos creado esos escenarios adjuntándole un botón de comando con código VBA. Con la herramienta Escenarios diseñamos los mismos sin necesidad de código.

Supongamos que tenemos los datos iniciales:

Para guardar esta presentación de datos como un escenario posible en 2003,  hacemos clic en Herramientas >Escenarios y desplegamos la caja de dialogo de escenario.

En 2010 en Datos>Análisis Y si > Administrador de escenarios. Con ello desplegamos la caja de diálogo de Escenarios:

Su funcionamiento es muy simple:

  • Clic en Agregar, y nos saldrá otra ventana en donde escogeremos un nombre para nuestro escenario. En esta ventana se muestra el valor actual de la celda cambiante y preguntando qué nuevo valor se desea introducir en la misma. Cuando introduzca un valor podrá pulsar el botón Agregar para continuar creando otros escenarios, o bien  Aceptar para terminar y regresar al cuadro de diálogo Administrador de escenarios.
  • Con las opciones Modificar/Eliminar podemos editar o eliminar el escenario seleccionado.

Introducción a las herramientas de Macros y VBA en Excel 2010

De forma predeterminada, las herramientas de VBA están ocultas. Es necesario completar los pasos siguientes para cambiar la configuración en las opciones de Excel para acceder a la ficha Programador.

  • Abra el menú Archivo y seleccione Opciones en la barra de navegación izquierda.
  • En el cuadro de diálogo Opciones de Excel, de la navegación de la izquierda, seleccione Personalizar cinta de opciones.
  • En el cuadro de lista derecho, seleccione la casilla de verificación junto a la ficha Programador.
  • Para volver a Excel, haga clic en Aceptar.

Ahora debería aparecerle la pestaña Programador en el menú:

Los iconos utilizados para grabar y reproducir macros VBA en el grupo de código en la ficha del desarrollador:

Icono Visual Basic  – Se abre el Editor de Visual Basic.

Icono Macros  – Muestra el cuadro de diálogo Macro, donde se puede optar por ejecutar o editar una macro de la lista de las macros.

Icono Grabar macro  – Comienza el proceso de grabación de una macro.

Con el icono de referencia relativa  intercambiamos  entre el uso de la grabación relativa o absoluta.

Icono de seguridad de macros – se accede al Centro de confianza, donde se puede optar por permitir o no permitir las macros para ejecutar en este equipo.

Herramientas básicas II

Proteger celda, hojas y libros.

Un aspecto importante en Excel es su carácter manipulativo. Los usuarios pueden modificar datos para su análisis posterior, aunque  en ocasiones se plantea la necesidad de que ciertos datos no puedan manipularse. Para ello se establece la posibilidad de bloquear, el libro, determinadas hojas o determinadas celdas.

Realmente por defecto todas las celdas están protegidas o bloqueadas para que no sufran cambios, pero no nos damos cuenta ya que la hoja no está protegida, para que realmente se bloqueen las celdas antes hay que proteger la hoja de cálculo.

Para desbloquear las celdas:

Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones.

  1. Seleccionar la pestaña Inicio.
  2. Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.
  3. Hacer clic sobre la pestaña Proteger.
  4. Aparecerá la ficha:

5. Desmarcar “Bloqueada”.

Desactivar la casilla Bloqueada y Hacer clic sobre el botón Aceptar.
Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas.

Seleccionar la pestaña Revisar.

Hacer clic sobre el botón Proteger hoja que se encuentra en la sección Cambios.

Aparecerá el cuadro de diálogo Proteger.

Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa.

Activar las opciones deseadas de la casilla Permitir a los usuarios de esta hoja de cálculo para que no tenga efecto la protección para la modificación seleccionada y desactivarla para tener en cuenta la protección.

Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña.

Hacer clic sobre el botón Aceptar.

Recordemos como se procedía en 2003:

1. Para proteger toda una hoja de un libro (archivo) de excel, sólo tenemos que ir directamente al menú Herramientas/Proteger/Proteger hoja.

Se abrirá la siguiente ventana:

Las opciones que vienen seleccionadas por defecto son “Seleccionar celdas bloqueadas“, lo que nos permite situarnos en las celdas que protejamos pero no nos permitirá realizar ninguna modificación en ellas, y “seleccionar celdas desbloqueadas“, que nos permitirá situarnos y modificar aquellas celdas que no protejamos. La lista de acciones que podemos realizar a pesar de que protejamos las celdas son, como se puede ver en la ventana de la imagen, muchas más (cambiar formato de celdas, de filas, de columnas, etcétera)..

2. Si deseamos introducir una contraseña para desactivar posteriormente la protección debemos escribirla en el recuadro “Contraseña para desproteger la hoja“.

3. Pulsamos Aceptar tras seleccionar las opciones deseadas y ya está. Si a continuación intentamos escribir o borrar cualquier contenido de cualquier celda, excel no nos dejará y nos presentará un mensaje como el que sigue:

Para proteger celdas concretas de una hoja debemos seguir los siguientes pasos:

A) Seleccionamos las celdas que NO deseamos proteger, es decir, las entradas de datos.

B) Vamos al menú Formato/Celdas y seleccionamos la última pestaña de la ventana:

C) Desactivamos la opción Bloqueada y pulsamos Aceptar. Al volver a la hoja todavía no ha ocurrido nada. Si prueba a escribir en las celdas que, por ejemplo, contienen fórmulas verá que puede hacerlo sin problema.

D) Vamos al menú Herramientas/Proteger/Proteger hoja y pulsamos directamenteAceptar. A partir de este momento sólo podrá modificar las celdas de entradas de datos, mientras que sus fórmulas y resto de celdas vacías no podrán ser modificadas.

Hipervínculos y comentarios

Los hipervínculos no cambian demasiado respecto a la versión 2003. Supongamos que deseamos establecer un hipervínculo a la página de Movistar desde el área del gráfico. Sólo debemos seleccionar el mismo (clic en el borde del mismo) seguidamente Menú>insertar>Hipervínculo o directamente con Ctrl+Alt+k.  Nos saldrá la misma pantalla que en la versión 2003. Sólo insertar la dirección URL y listo.

Con el fin de proporcionar información al usuario se puede agregar en las celdas que deseemos un comentario que aparecerá como texto emergente al pasar el puntero por la celda en cuestión.  Por ejemplo en la celda F2 insertamos el comentario “el IVA puede variar” –> Revisar / Comentarios / Nuevo comentario. Para modificar o eliminar un comentario abrimos el menú contextual desde la celda en cuestión.

Insertar función

Excel dispone de un gran número de funciones, la versión 2003 contaba con 329 y la versión 2010 unas 340 más o menos.

En la primera versión de Microsoft Excel 2.010 se modificaron los nombres de algunas funciones. Luego, mediante el SP1 (Service Pack 1) se volvieron a los nombres originales. Estos cambios no afectan la compatibilidad entre las distintas versiones de Excel  (la conversión de los nombres se hace automáticamente). A continuación mostramos una tabla donde se ven las funciones que cambiaron de nombre:

Para insertar una función se procede igual que en la versión 2003, podemos hacerlo desde la misma barra para insertar fórmulas pinchando en fx con lo que se nos abre el asistente. La segunda forma es a través de la pestaña Fórmulas. Si vamos a trabajar con operaciones comunes como autosuma, promedio, etc también contamos con la herramienta directa en la cinta de opciones dentro del grupo Modificar.

Utilización del condicional

Si deseamos establecer una acción sobre una celda si cumple una condición podemos utilizar las funciones lógicas. Por ejemplo observe la siguiente tabla:

En la celda c29 hemos procedido a insertar la función promedio. Y desde la cinta de opciones se ha seleccionado número desplegando la flechita hacia abajo para colocar sólo dos decimales. En G9 se ha procedido a insertar la siguiente función lógica =SI(F5<0;”RECESION”;””) . Para colocar el formato condicional hemos seleccionado el ítem en el icono estilos. Esos estilos además se pueden editar simplemente desplegando la pestaña:

Buscar objetivo

Sin duda una de las mejores utilidades que tiene Excel, la función Buscar objetivo, nos facilita averiguar el valor de la variable que determina un resultado en una ecuación sencilla. En el siguiente ejemplo analizaremos su aplicación en Excel 2010.

Supongamos que deseamos conocer el importe periódico a satisfacer para amortizar un préstamo de 4500 € al 1,5 mensual. Para ello podemos diseñar una tabla como la siguiente:

En la celda B9 hemos insertado una cantidad aleatoria, en este caso 200, como cuota inicial. Después hemos calculado los intereses en base al saldo pendiente de amortizar, o sea en C9 hemos insertado =E8*$b$5/100 ; y la amortización es la parte de la cuota que resta una vez descontados los intereses es decir en D9–>=$B$9-C9.

Con esto ya tendríamos la segunda línea del cuadro de amortización. Rellenamos hacia abajo hasta alcanzar las 12 mensualidades.

Evidentemente y como podrá comprobar, con esa cuota en el período 12 nos quedaría todavía un saldo pendiente de 2772,04 €. Bien aquí es cuando entra en juego nuestra función Buscar Objetivo.

Nos posicionamos en la celda E20 y seleccionamos Datos>Análisis Y si> Buscar Objetivo. En la ventana emergente insertaremos los datos tal y como aparecen en la imagen:

Es decir definimos la celda en donde se refleja el último saldo pendiente con el valor cero, ya que al final de los 12 períodos deseamos tener amortizado el préstamo, para cambiar la cuota a abonar es decir. Dicho de otra forma a cuánto debe ascender el importe periódico para devolver el préstamo en 12 cuotas.

Evidentemente que podemos utilizar otra función financiera más práctica como puede ser Pago, (por ejemplo en E3 podemos insertar =PAGO(B5/100;B4;B3)) lo que nos daría el mismo resultado.

Buscar objetivo con VBA

En alguna ocasión se nos puede presentar la necesidad de programar la función buscar objetivo con VBA. Para ello debemos utilizar la notación GoalSeek. Al objeto de conocer mejor su uso lo voy a explicar con el siguiente ejemplo, sobre el que además profundizaré un poquito más en el uso del formato condicional.

Imaginemos un envasador de café que presenta los siguientes datos en su cuenta analítica:

  • Precio coste del kg de café –>13.95 €
  • Costes directos por kg——–> 0.75 €
  • Costes indirectos por kg—— >0.25€
  • Margen inicial——————->15 %
  • IVA inicial————————>7 %

Con esos datos de partida planteamos un escenario inicial, y otros tres alternativos. En el escenario inicial haciendo clic en el botón correspondiente nos debe presentar cuáles serían los resultados de aplicar una subida de 1 punto en el IVA (del 7 al 8%). Los datos de las celdas D22, D23, D24 y D26, son iniciales y fijos. En E22 y E24 se ha aplicado un incremento del coste del 1% por el impacto del IVA soportado. En H25 e I25 se presenta el precio final inicial, y el precio final revisado con el IVA al 8%.

Para ello se ha procedido a redondear este último dato con la función correspondiente (celda I25):

=REDONDEAR.MAS(((E22+E23+E24)*(1+E26))*(1+G22);1)

Como podrá observarse en el ejemplo una subida de 1 punto en el IVA llevaría un desplazamiento de incremento del precio en un 8,69%. evidentemente ello supondría un aumento del beneficio marginal potencial ,  que no  real dado el menor nivel de demanda que supondría un encarecimiento del producto. Por ello se plantea en el ejemplo otro escenario denominado Escenario Costes en el que se plantea la posibilidad de mantener los mismos precios, reduciendo los costes directos. Para ello se ha insertado otro botón mediante la Vista Programador. Esta opción es posible que no esté presente en la cinta de opciones, para insertarla en la misma seleccionamos Inicio>Opciones>Personalizar vista de opciones (en la ventana de la derecha marcamos Programador) y aceptamos.

A continuación y una vez que tenemos disponible la vista Programador hacemos clic en ella y desplegamos el botón Insertar Controles. A diferencia de la versión 2003 estos controles vienen recopilados en dos grupos, los de formulario y los Active x. Seleccionaremos un botón de comando de este segundo grupo, y lo insertamos en nuestra hoja. Para cambiar el nombre, con el botón secundario seleccionamos Objeto botón de comando->Modificar o bien a través de Propiedades -> Caption ; para cambiar el color del botón de igual forma que en VBA 2003 Propiedades ->BackColor.

El siguiente paso es insertar el código asociado al evento clic. Para este Escenario Inicial solo queremos que se presenten los valores de partida, lo podemos hacer mediante una matriz o simplemente uno a uno:

Private Sub CommandButton1_Click()
Range(“D22″) = “13.95”
Range(“D23″) = “0.75”
Range(“D24″) = “0.25”
Range(“D26″) = “0.15”

 

Range(“E22″) = “15.07”
Range(“E23″) = “0.75”
Range(“e26″) = “0.15”

 

End Sub

Una vez insertado el código volvemos a Excel e insertamos el siguiente botón Escenario Costes que nos va a permitir replantear los costes directos en función del objetivo de mantener el precio final. El código asociado al evento clic en este caso sería:
Private Sub CommandButton2_Click()
‘buscamos el objetivo de aguantar el precio final
‘para mantener márgenes reducimos costes directos

Range(“E22″) = “15.07”
Range(“e24″) = “0.26”
Range(“e26″) = “0.15”
Range(“I25″).GoalSeek  Goal:=18.4, ChangingCell:=Range(“E23″)
End Sub

 

Las correspondencias de la sintáxis en VBA con la función buscar objetivo es la siguiente:

Range(“I25″).GoalSeek—>”Definir la celda”

Goal:=18.4 –>”Con el valor” (en nuestro caso 18.4)

ChangingCell:Range(“E23″)–>”Para cambiar la celda” (en el ejemplo la correspondiente a los costes directos la E23).

 

Una vez insertado procedemos a configurar los escenarios Margen y Mixto de forma similar al anterior:

Escenario margen

Private Sub CommandButton3_Click()
Range(“E22″) = “15.07”
Range(“e24″) = “0.26”
Range(“E23″) = “0.75”
Range(“e26″) = “0.15”
Range(“I25″).GoalSeek Goal:=18.4, ChangingCell:=Range(“E26″)
End Sub

Escenario Mixto

Private Sub CommandButton4_Click()
Range(“E22″) = “15.07”
Range(“e24″) = “0.26”
Range(“E23″) = “0.70”

 

Range(“I25″).GoalSeek Goal:=19, ChangingCell:=Range(“E26″)
End Sub

Para rematar podemos establecer un conjunto de iconos por ejemplo flechas que nos permitan visualizar la evolución que van teniendo los datos que analizamos respecto a una situación anterior.

Si escogemos el escenario Costes, seleccionamos la celda E23. Vamos a Inicio->Formato condicional –>Nueva regla  nos saldrá el cuadro siguiente:

 

Desplegamos la ventana Estilo de formato y seleccionamos Conjunto de iconos. Para escoger a su vez el tipo de icono abrimos el desplegable Estilo de icono para seleccionar el que deseemos.

El siguiente paso será establecer la regla como tal, en nuestro caso marcamos que el icono sea verde en la celda E23 cuando el valor sea mayor que el de la celda D23, desplegando  como tipo número. Con el resto procedemos de forma similar.

 

 

 

 

Herramientas básicas I

La nueva interfaz es sustancialmente diferente con respecto a 2003. Lo que todo el mundo se pregunta Pero…¿dónde están las cosas?. Todo ha cambiado, tanto los menús como algunos procedimientos.

Empezando por la barra de menú lo primero que nos encontramos es el botón de office en la versión 2007 y la pestaña Archivo en la versión 2010. Dicho botón nos da acceso a las opciones Nuevo,Abrir,Guardar, Guardar como, imprimir etc.

Si deseamos crear un nuevo libro botón office>Nuevo (2007)  o Archivo-Libro blanco-Crear (2010). Por ejemplo crearemos un libro nuevo que lo llamaré PRUEBA1. Este libro los guardaré en el directorio que desee, haciendo notar que en estas versiones existen varias opciones para guardar un libro de Excel. Como libro de Excel con la extensión .xlsx (.xls en versión 2003). O como Libro de Excel habilitado para macros (con extensión .xlsm).

Como recordará Excel 2003 no distinguía, cuando se guardaban, libros con macros de libros sin macros. De hecho cuando intentábamos abrir un libro con macros nos avisaba e incluso nos impedía actuar sobre los objetos VB si no bajábamos el nivel de seguridad. Este tratamiento en cuanto a la seguridad no ha cambiado ni en 2007  ni en  2010. No obstante para poder trabajar con libros de extensión .xlsm debemos habilitar la seguridad de la siguiente manera:

Programador> Seguridad de macros / Habilitar todas las macros-.

En Excel 2010 debemos ir a Archivo>Opciones>Centro de confianza—>Clic en configuración de centro de confianza>Configuración de macros. Marcamos

Habilitar todas las macros

Con un pequeño ejemplo podemos ir tomando contacto con la nueva interfaz, por ejemplo imaginemos que queremos hacer un pequeño gráfico para comparar tarifas de telefonía móvil. Una vez iniciado Excel, introducimos los datos de la misma manera a como lo haríamos en la versión 2003.

La introducción de las fórmulas no varía, como tampoco lo hace las referencias absolutas y relativas. Es decir podemos anclarr las celdas que nos interesa con el signo $.

Una vez rellenada la hoja con los datos, podemos insertar un gráfico. En principio el icono para insertar gráficos no está presente en la cinta de opciones. A diferencia de la versión 2003, en 2007 y 2010 tenemos la posibilidad de personalizar nuestro entorno de trabajo. Es cierto que en la versión 2003 existe la posibilidad de establecer una barra de herramientas personalizada, a la que incluso le podíamos poner nuestros propios iconos con el pequeño editor que incorpora, pero esta función se hace más presente y necesaria en las versiones 2007/10.

Para ello haciendo clic en Archivo/Botón office,  buscamos barra de herramientas de acceso rápido o bien desplegando en el icono siguiente:

Desplegamos “Comandos más utilizados” y escogemos por ejemplo en este caso “Crear gráfico”.

Si no queremos insertar esta herramienta como acceso rápido iremos a Menú>Insertar>Gráficos. Aquí escogemos el tipo que precisemos para representar los datos. En el ejemplo seleccionamos primero el área de datos (en este caso A1:E42), seguidamente Insertar><gráfico dispersión…con líneas suavizadas

Bueno con esto nos habrá insertado un gráfico más o menos como este:

Para comenzar tenemos en la leyenda 4 series cuando sólo tiene que haber 3. Vamos a eliminar la Serie 1, haciendo clic sobre Serie 1 botón derecho y eliminar serie. A continuación necesitamos cambiar los respectivos nombres de las series, hacemos clic en el gráfico y después en seleccionar datos nos saldrá una ventana como esta:

Y tal como se señala en la imagen clic en editar y cambiamos el nombre a las series.

Si se fija bien podrá observar como se activan las diferentes herramientas para los gráficos. Si selecciona la pestaña Presentación accederá a las opciones de Título de gráfico, Etiquetas, etc.

Los minigráficos

Los minigráficos , son una de las grandes novedades visuales con las que nos sorprende Excel 2010. Se tratan de pequeños gráficos incrustados en el fondo de una celda de tal forma que complementan la información contenida en las celdas de al lado. Estos minigráficos suelen utilizarse para ver de un solo vistazo, tendencias o resultados sin tener que estudiar grandes tablas de números.

En el ejemplo de referencia:

  1. Seleccione la celda B12.
  2. Vaya a Insertar>Minigráfico de columna>
  3. Le aparecerá una nueva ventana para seleccionar el rango de datos seleccione C12:E12.
  4. Clic en Aceptar.

Para eliminar un minigráfico, nos posicionamos en la celda donde se encuentra ubicado y con el botón derecho seleccionamos  Minigráfico> Borrar minigráfico.

Añadir barra de errores a los gráficos

Las barras de error son una herramienta que permite mostrar el rango del desvío estándar de una medición experimental. Para ver comprender mejor su uso y forma de aplicación analicemos el siguiente ejemplo. Supongamos que tenemos la siguiente tabla de datos:

 

X Y Error X Error Y
4,80 2,87 0,79 0,80
5,10 4,00 1,09 3,20
2,55 6,01 2,33 2,31
4,00 6,78 0,07 2,01
3,90 1,00 0,08 3,00

Representamos los datos como gráfico de dispersión XY:

 

 

 

Para agregar barras de error a un grafico, seleccionamos una de las series de datos y abrimos el menú de formato de series de datos (clic en cualquier botón azul).

Se nos abrirá un cuadro de  diálogo que cuenta con una pestaña para definir las barras de error de X y otra para definir las de Y.


En el cuadro de definiciones podemos elegir entre la presentación de la barra (ninguna, por exceso, por defecto o ambas) y la cuantía del error.
Por ejemplo si definimos un error del 25% para cada punto en la ventanilla de Porcentaje y elegimos la opción Ambas en Presentar, veremos algo como esto:

 

Excel ha agregado una línea a ambos lados de cada punto, que representan una desviación del 25% de este punto. Pruebe  a modificar el gráfico agregando el mismo error al valor de las Y.

Si desea variar el formato de las barras lo puede hacer desde el menú tramas.

Utilización de caracteres especiales en gráficos y hojas.

La función CARACTER en Excel regresa un carácter específico para un número entero que ha sido proporcionado como argumento. Un PC tiene un juego de caracteres con un número entero asignado y es precisamente ese número el que debemos proporcionar como argumento.

Ejemplos de la función CARACTER

La función CARACTER puede ayudarnos a hacer uso de caracteres especiales o símbolos dentro de alguna celda como pueden ser los saltos de línea o encontrar alguna letra del alfabeto.

Agregar un salto de línea

Cuando necesitamos agregar dos cadenas de texto que incluyan un salto de línea entre ellas podemos utilizar la función CARACTER (10), que representa precisamente el salto de línea. Observa cómo la celda C2 hace la unión de las cadenas de texto de la celda A1 y B1 pero introduce un salto de línea entre ambas.

Para ver reflejado el salto de línea debemos ir al cuadro de diálogo Formato de celdas (CTRL + 1) y seleccionar la opción Ajustar texto que se encuentra en la pestaña Alineación.

Encontrar una letra del alfabeto

Si queremos saber rápidamente cual es la letra 10 del alfabeto podemos utilizar la función CARACTER que nos dará el resultado inmediato sin necesidad de ir letra por letra.

Antes de mostrar el ejemplo debemos saber que la letra A (mayúscula) tiene asociado el código 65 y de esta manera podemos imaginar que la primera letra del alfabeto es lo mismo que 64 +1, la segunda letra del alfabeto será 64 +2, la tercera 64 + 3 y así sucesivamente. Si necesitamos conocer la décima letra del alfabeto utilizaremos la fórmula CARACTER(64 + 10)

Y cómo sería con VBA: Para insertar la letra c por ejemplo en la celda activa–>

ActiveCell.FormulaR1C1 = “=CHAR(64+3)”

 

Reglas de validación

Las reglas de validación nos permiten especificar el formato para un rango de celdas, restringiendo por ejemplo la entrada de datos no deseados, sean textos, campos vacíos o números. La forma de activar dichas reglas, recordemos, es desde la barra de menú, desplegamos datos y posteriormente validación. 

En este artículo se describe cómo configurar la validación de datos, incluidos los tipos de datos que se pueden validar y los mensajes que se pueden mostrar, además de proporcionar un libro que se puede descargar para obtener ejemplos de validación que se pueden modificar y utilizar en hojas de cálculo propias.

Con VBA también es posible crear reglas de validación.  En el siguiente ejemplo vamos a desarrollar una macro que nos impide dejar vacía la celda A1 y ademas sólo nos va a permitir introducir valores comprendidos entre 5 y 10.

Utilizaremos una estructura With _ end with. Y comenzamos seleccionando el rango a validar, en el ejemplo el A1:

With Range(“a1″).Validation

“Validation” es una propiedad con los métodos siguientes:

  • Add:  para añadir una nueva regla de validación a un rango de celdas.
  • Modify: cuando deseamos modificar las mismas
  • Delete: para eliminarlas.

A su vez el evento Add contiene una serie de argumentos:

Type (tipo de validación) es un argumento obligatorio con el que específicamos que tipo de validación vamos a aplicar, pudiendo escoger entre las siguientes:

  • xlValidateCustom (validación personalizada)
  • xlValidateDate(para fechas)
  • xlValidateDecimal (sólo números decimales)
  • xlValidateInputOnly (sólo entradas determinadas)
  • xlValidateList (mediante listas)
  • xlValidateTextLength (texto de un determinado tamaño)
  • xlValidateTime (sólo formatos horarios)
  • xlValidateWholeNumber ( para no admitir celdas vacías)

Otros argumentos son:

AlertStyle     XlDVAlertStyle opcional.  El estilo de alerta de validación.

XlDVAlertStyle puede ser una de estas constantes XlDVAlertStyle.
xlValidAlertInformation
xlValidAlertStopxlValidAlertWarning

Operator     XlFormatConditionOperator opcional.  El operador de validación de datos.

XlFormatConditionOperator puede ser una de estas constantes XlFormatConditionOperator.
xlBetween
xlEqualxlGreaterxlGreaterEqualxlLessxlLessEqual

xlNotBetween

xlNotEqual

Formula1      Variant opcional. La primera parte de la ecuación de la validación de datos.

Formula2      Variant opcional. La segunda parte de la validación de datos cuando Operator es xlBetween o xlNotBetween; de lo contrario, se omite este argumento.

De esta forma, nuestra macro quedaría como sigue:

Sub val()
With Range(“a3″).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=”5″, Formula2:=”10″
.IgnoreBlank = True
.InCellDropdown = True

‘ para que nos salga una ventanita antes de introducir los datos
.InputTitle = “Número entre 5 y 10″

‘el título de la ventanita
.ErrorTitle = “Error en los datos”

‘los mensajes emergentes
.InputMessage = “Sólo se admiten valores entre 5 y 10″
.ErrorMessage = “Sólo números entre 5 y 10″
.ShowInput = True
.ShowError = True
End With
End Sub

Habrá observado que existe una primera sentencia “.Delete”, con ello nos aseguramos de que no existe una regla de validación o condición previa que nos anule la regla en ejecución. De hecho si elimina esa línea de código observará que se origina un error.

Otras funciones estadísticas

Además de las funciones básicas ya vistas anteriormente, Excel cuenta con otras que pueden resultar de gran ayuda al recontar celdas vacías u ocupadas:

  • CONTAR: cuenta el número de celdas que contienen números.
  • CONTARSI: cuenta las celdas en el rango que coinciden con una condición dada. Por ejemplo si quisiéramos saber cuántos valores dentro del rango B2:B12 (al que yo he llamado DATOS) son mayores a 50 escribiría en la barra de fórmulas =CONTAR.SI(DATOS;”>50″)
  • CONTAR.BLANCO: devuelve el número de celdas vacías dentro de un rango de datos.
  • CONTARA:Cuenta el número de celdas que no están vacías y los valores que hay en la lista de argumentos. Ojo a diferencia de la función CONTAR , esta cuenta cualquier cosa sea número, letras o carácteres.

Bien estas funciones nos ayudan a contar celdas con o sin datos, pero qué ocurre si quisiéramos contar celdas con un determinado color de fondo. Veamos:

Seleccionamos menú Edición – Buscar (Ctrl+B)
Hacemos clic en la flecha Formato
Seleccionamos Elegir formato de celda

Seleccionamos la celda con formato. En nuestro caso, la A2 que es la que tiene el fondo amarillo

Hacemos clic en Buscar todo.

En la parte inferior del cuadro de diálogo Buscar y reemplazar, se muestran todas las celdas con dicho formato.

Pulsamos Ctrl +A para seleccionar todas las celdas con dicho formato
Al hacerlo en la barra de estado se mostrará la suma de los valores. En este caso, 56863.

Si lo que queremos es contar el número de celdas en vez de sumarlas, hacemos clic con el botón derecho sobre la barra de estado

Seleccionamos la función Cuenta.

En la barra de estado se mostrará el número de celdas con el formato que indiquemos. En este caso hay dos celdas con color de fondo amarillo. Por tanto, en la barra de estado aparece 2.

A continuación reproduzco un post de Jorge Dun de  cómo se prodría hacer con VBA :

Function contar_por_color(RangoColor As Range, CeldaColor As Range)
 Dim rngCelda As Range

 For Each rngCelda In RangoColor
  If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
   contar_por_color = contar_por_color + 1
  End If
 Next
End Function

Si quisiéramos sumar los datos que contienen las celdas aplicaríamos la función siguiente:

Function sumar_por_color(RangoColor As Range, CeldaColor As Range, RangoSumar As Range)
Dim rngCelda As Range
Dim colOffset As Long

colOffset = RangoSumar.Column – RangoColor.Column

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_por_color = sumar_por_color + rngCelda.Offset(0, colOffset).Value
End If
Next
End Function

 

Cálculos estadísticos básicos: media,mediana, moda, varianza y desviación estándar

Para comenzar repasaremos las funciones básicas que ostenta Excel para cálculos estadísticos. Para ello inicie un nuevo libro Excel y en la primera hoja inserte los datos siguientes:

 

  • Seleccione por ejemplo la celda A13 para realizar cálculos.
  • Vaya a menú>insertar>función.
  • En seleccionar categorías, escoga “Estadísticas” y “Promedio”, que es la función que nos devuelve la media aritmética de los datos escogidos.

Otra forma rápida es seleccionar el rango de celdas A1:A13 y desplegar el signo sumatorio de la barra de menús, seleccionando la misma función promedio.

Para practicar en A14 inserte la función mediana , en A15 la moda y en A16 la desviación estándar.

 

Para la media

Veamos como podría ser con código VBA:

Function Media(k As Long, Arr() As Single)
Dim Sum As Single
Dim i As Integer 

Sum = 0
For i = 1 To k
Sum = Sum + Arr(i)
Next i

Media = Sum / k


End Function 

Y para la desviación estándar:

Function StdDev(k As Long, Arr() As Single)
Dim i As Integer
Dim avg As Single, SumSq As Single

     avg = Media(k, Arr)
For i = 1 To k
SumSq = SumSq + (Arr(i) – avg) ^ 2
Next i

StdDev = Sqr(SumSq / (k – 1))


End Function 

 

Reconociendo los mensajes de error

A veces cuando trabajamos en Excel se producen errores que la aplicación muestra con una serie de mensajes. Los más comunes son los siguientes:

TIPO DE ERROR

DESCRIPCION

#VALUE!

Se ha introducido un tipo de argumento

u operador erróneo.

#NAME!

Excel no reconoce el texto en una fórmula

#DIV/0

Se está intentando dividir entre cero

El primer tipo de error suele ocurrir:

  • Cuando se trata de introducir texto en una fórmula que requiere un número o valores lógicos.
  • Cuando se introduce una referencia a una celda , una fórmula o una función como una array constante.
  • Al ejecutar una macro que en realidad es una función.
El segundo tipo de error surge:
  • Al hacer mención a un nombre inexistente.
  • Usar una fórmula en una etiqueta.
  • Introducir texto en una fórmula sin cerrar las correspondientes etiquetas.
En cuanto al tipo de error div 0 no requiere mayor explicación.

Técnicas de control y depuración de errores (2ª parte)

VBA dispone dentro de su categoria de objetos de uno de especial aplicación para el tratamiento de errores: el objeto Err(). Este permite alamacenar información en tiempo de ejecución sobre los errores que puedan presentarse apareciendo cuando se realice una operación no válida. Los atributos o propiedades se especifican por quien genera el error.  Veamos una descripción de cada uno:

PROPIEDADES

Description (Propiedad) Lectura y escritura. Devuelve o establece una cadena descriptiva asociada a un error.
Erl (Propiedad) Sólo lectura. Devuelve un entero que indica el número de línea de la última instrucción ejecutada.
HelpContext (Propiedad) Lectura y escritura. Devuelve o establece un Integer que contiene el identificador de contexto de un tema de un archivo de Ayuda.
HelpFile (Propiedad) Lectura y escritura. Devuelve o establece una expresión String que contiene la ruta de acceso completa a un archivo de Ayuda.
LastDLLError (Propiedad) Sólo lectura. Devuelve un código de error proveniente del sistema y producido por una llamada a la biblioteca de enlaces dinámicos (DLL).
Number (Propiedad) Lectura y escritura. Devuelve o establece un valor numérico que especifica un error.
Source (Propiedad) Lectura y escritura. Devuelve o establece una expresión String que especifica el nombre del objeto o de la aplicación que generó originalmente el error.

METODOS

Clear (Método) Borra todos los valores de las propiedades del objeto Err.
Raise (Método) Genera un error en tiempo de ejecución. Se puede utilizar en lugar de la instrucción Error.

Veamos un ejemplo del mismo, en él vamos a generar un error por desbordamiento en tiempo de ejecución y después vamos a presentar el mensaje correspondiente:

Sub ejemplo_error()
Dim Msg As String
‘ Si se presenta un error construimos un mensaje informativo del mismo
On Error Resume Next
Err.Clear ‘borramos cualquier objeto error
Err.Raise (6)  ‘ generamos un error por desbordamiento
‘ comprobamos que se ha dado el error y presentamos el mensaje
If Err.Number <> 0 Then
  Msg = “Error” & Str(Err.Number) & ” ha sido generado por ” _
      & Err.Source & Err.Description
  MsgBox Msg, vbCritical, “Error”
End If

End Sub

En el ejemplo ha podido ver el uso del evento Clear se utiliza para borrar explícitamente el objeto Err una vez que se ha controlado un error, como ocurre cuando se usa el control diferido de errores con On Error Resume Next. Yel evento Raise genera un error en tiempo de ejecución.

Como ya dije en la primera parte de este capítulo, se nos pueden presentar diversos tipos de errores. Hemos podido analizar algunas técnicas que nos facilita el manejo de los mismos. Pero a la hora de programar el editor VBA nos aporta otras herramientas que nos ayudan cuando estamos programando a depurar nuestro código. Recordemos que al principio establecimos los cuatro tipos fundamentales de error que se pueden dar:

  • de sintáxis
  • de compilación
  • de ejecución
  • de lógica

Los errores de sintáxis se detectan  directamente a medida que se introduce el código. VBA tiene una herramienta de comprobación de sintáxis que viene activada por defecto. No obstante para activarlo vaya a Herramientas>Opciones>ficha editor>Comprobación de sintáxis automática. Los errores de sintáxis que no se corrigan provocan un error de ompilación.

Los errores de compilación dijimos que se generan cuando se compila el programa, es decir, cuando se traduce a lenguaje máquina el código que hemos escrito.

Cuando Excel trata de ejecutar un código y detecta un procedimiento, una instrucción o una operación inválida se produce un error de ejecución, por ejemplo cuando estamos haciendo una llamada a una Hoja 4 siendo que esta no existe.

Los más complicados de detectar serían los errores de lógica, entre otros aquellos imputables al propio programador. Para entendernos aquellos insertos en nuestro propio algoritmo que da lugar a errores inesperados. Para este tipo de error VBA dispone de una herramienta de  depuración que permiten la ejecución del código paso a paso comprobando los procedimientos y valores de las variables. Para acceder a la misma puede activarla desde la barra de menú>Ver>barra de Herramientas>Depuración.

Una vez que la active le saldrá una barra de herramientas con unos iconos: las cuatro primeras leidas de izquierda a derecha ya las conoce, fijémonos en el icono de una mano “alternar punto de interrupción”. En ocasiones, puede interesar poner en ejecución un programa e interrumpirlo en una línea específica. Esto es posible mediante los llamados puntos de interrupción, que se adjuntan a la línea específica en la cual se desea detener la ejecución. De este modo, cuando el curso de ejecución del programa llegue a la línea, éste se  detendrá, es decir, la línea con punto de interrupción quedará pendiente y sin ejecutar; en ese momento, la aplicación  donde se ha creado el módulo pasa de Modo Ejecución a Modo Interrupción.

Tras la interrupción, se podrá las siguientes acciones:

  • evaluar las variables que están siendo utilizadas a través de la ventana Inspección
  • ver el curso exacto de ejecución del código, es decir, la secuencia de acciones que se realizan

Más a la derecha de la barra encontrá una serie de iconos que activan diversas ventanas, una de ellas es la ventana Locales que nos muestra todas las variables y parámetros involucrados en una función o procedimiento.

La ventana Inmediato se utiliza en tiempo de diseño para depurar y evaluar expresiones, ejecutar instrucciones, imprimir valores de variables, etc. Permite escribir expresiones que el lenguaje de programación evalúa o ejecuta durante la depuración.

 

Técnicas de depuración y de control de errores (1ª parte)

En el presente apartado vamos a abordar un acuestión no trivial, y que nos puede reportar ciertos beneficios al realizar nuestros programas. Los errores son casi inevitables, aun planificando bien nuestro código podemos cometer errores de diseño, de sintáxis, de compilación o lógicos.

Si VBA encuentra un error y no hemos insertado un código de comprobación de error, el programa se detiene y el usuario se encontrará con un mensaje embarazoso: Continuar,Finalizar o Depurar. Si este error se nos presenta a nosotros en el momento en que estamos probando nuestro código, o es un programa realizado por nosotros mismos, al seleccionar la opción de depurar se nos va a presentar la ventana de código con una línea en amarillo en donde se resalta la localización del error. Podemos por tanto detener la ejecución del programa, corregir el error y ejecutarlo de nuevo.

Pero qué ocurriría si estos errores se le presenta a un usuario que no tiene porque saber de programación. Tenemos por tanto que establecer una estrategia para interceptar los errores. Para comenzar vamos a tratar el siguiente ejemplo:

Introduzca el módulo siguiente:

Sub division()
Dim x, y, z As Double
x = InputBox(“Introduzca el dividendo”, “DIVISION”)
y = InputBox(“Introduzca el divisor”, “DIVISION”)
z = x / y
MsfBox x & ” dividido entre ” & y & “es igual a ” & z

End Sub

Verá que he marcado en negrita el mensaje de respuesta, en el que intencionadamente he introducido un primer error de sintáxis. El mensaje que le aparecerá cuando lo intente  ejecutar es ¡ERROR DE COMPILACION! NO SE ENCUENTRA SUB O FUNCTION. Es decir que cuando VBA compila (traduce a lenguaje máquina) el código que le hemos insertado, no lo entiende.

Corríja el error y ejecútelo de nuevo.

Ahora en tiempo de ejecución cuando el programa le solicite introducir el divisor, introduzca un cero. Afortunadamente ese error VBA ha sido capaz de interpretarlo.  Está dentro de la lista de errores interceptables.

Inserte ahora debajo de la declaración de variables la siguiente línea:

On error resume next

Ejecute de nuevo el código y observará que le arrojará la división entre 0, como solución cero. O sea que con esta sentencia le indicamos a VBA que en caso de que encuentre algún error lo ignore y continúe ejecutando el código.

Es fácil pensar que con esta línea se resuelve todos los problemas pero realmente no es así tan sólo se enmascara, con lo que si no se hace nada para corregir la causa del error, se podrán generar futuros errores.

Vamos a crear por tanto unas líneas de código para manejar los errores. Una opción bastante usada es introducir una condición con el bucle Do—While, por ejemplo:

Sub obtenernumeros()
Dim hecho As Boolean
Dim divisor, dividendo, division As Double

hecho = False
Do While Not divisor <> 0

    dividendo = InputBox(“Introduzca el dividendo”, “DIVISION”)
    divisor = InputBox(“Introduzca el divisor”, “DIVISION”)

    Loop
division = Format(dividendo / divisor, “#0.00″)
MsgBox division

End Sub

Con ello forzamos sin más que hasta que  se introduzca una cifra distinta de cero como divisor. Una manera más directa es crear una función personalizada para la operación en sí, y hacer una llamada a la misma desde un procedimiento. De esa forma para la función podríamos escribir el siguiente código:

Function dividir(dividendo, divisor) As Boolean

‘declaramos las variables
Dim msg As String
Dim resultado As Double
‘establecemos la salida en caso de error
On Error GoTo controldivcero

‘si no hay errores dividimos
resultado = Format(dividendo / divisor, “#0.00″)
‘presentamos el resultado
msg = dividendo & ” entre ” & divisor & ” es igual a ” & resultado
MsgBox msg

dividir = True

‘salimos y finalizamos la función dividir
Exit Function

‘aquí el manejador de error
controldivcero:

‘se ha detectado un divisor=0 se le pregunta al usuario si pretende continuar
resultado = MsgBox(“No se puede dividir entre 0 ¿Desea continuar?”, _
vbYesNo + vbQuestion, “Dividir”)

‘si dice que sí rcomenzamos el bucle
If resultado = vbYes Then
    dividir = False
Else
    dividir = True
End If

End Function

Y con el procedimiento siguiente obtenemos los operadores y llamamos a la función:

Sub obtenernumeros_bis()
Dim hecho As Boolean
Dim divisor, dividendo As Variant

hecho = False
Do While Not hecho
    dividendo = InputBox(“Introduzca el dividendo”, “DIVISION”)
    divisor = InputBox(“Introduzca el divisor”, “DIVISION”)
    hecho = dividir(dividendo, divisor)
Loop

End Sub

En este ejemplo preparar un rutina de control de errores no es muy difícil dado lo previsible y obvio del error que se puede presentar. Aunque por desgracia no siempre es así.

A menudo necesitamos ofrecer al usuario la opción de continuar el programa o abandonarlo. Para ello podemos añadir una o varias sentencias Resume al manejador de errores. VBA define tres tipos:

  • Resume: se le dice a VBA que continúe la ejecución del programa desde la misma sentencia donde se produjo el error.
  • Resume Next: a partir de la primera sentencia ejecutable que encuentre después de la línea que provocó el error.
  • Resume Line: a partir de la etiqueta especificada con Line.

En cualquier momento podemos desactivar el manejador con la sentencia: On Error GoTo 0.

Módulos de clases

Excel cuenta con muchos objetos disponibles, aunque puede llegar el momento en que sea más práctico para el usuario crear su propio objeto. Estos se crean usando un módulo de clase asignándoles propiedades y métodos personalizados.

En el siguiente ejemplo que vamos a desarrollar deseamos aplicar un descuento a los clientes que sean de Londres.  Evidentemente que esto se puede hacer de forma mucho más fácil sin necesidad de ninguna línea de código, pero nos resulta útil para explicar de forma clara el concepto que estamos desarrollando.

Desde VBA seleccione Insertar>Módulo de clase. Es importante tener en cuenta:

  • Todo objeto personalizado ha de tener su propio módulo.
  • El módulo de clase es necesario renombrarlo para reflejar el objeto personalizado.

Por lo tanto vamos a renombrar nuestro módulo desde la ventana de propiedades y le llamaremos clsClientes. A continuación vamos a asignar las propiedades:

  • Public localidad as string
  • Public ratio as double
  • Public importeventa as double

Como necesitamos que estas variables sean accesibles para el módulo estándar le hemos declarado Public.

El siguiente código crea un método descuento que permite calcular el descuento aplicable:

Public Function descuento() As Double

descuento = importeventa * ratio

End Function

 

Ahora mediante un módulo estándar procederemos al cálculo de los descuentos de los datos que se han insertado en la hoja 1:

Declaramos una la variable para hacer uso de nuestro objeto personalizado y otra para hacer el recorrido por las filas:

Option Explicit

Dim cliente As clsCliente

Dim fila As Byte

 

A continuación establecemos la variable en un nuevo objeto:

Set cliente = New clsCliente

A partir de aquí recogemos los valores de las diferentes propiedades:

With cliente
    For fila = 2 To 6
    .localidad = Cells(fila, 2).Value
    .ratio = 0.1
    .importeventa = Cells(fila, 3).Value
E introducimos un bucle condicional:

If cliente.localidad = “Londres” Then

    Cells(fila, 5).Value = cliente.descuento
    Cells(fila, 4).Value = “10 %”
    Cells(fila, 6).Value = Cells(fila, 3).Value – Cells(fila, 5).Value

    End If

 

Todo el código del módulo junto con los cierres correspondientes:

Sub descuento_clientes()

Set cliente = New clsCliente

With cliente
    For fila = 2 To 6
    .localidad = Cells(fila, 2).Value
    .ratio = 0.1
    .importeventa = Cells(fila, 3).Value

      If cliente.localidad = “Londres” Then

        Cells(fila, 5).Value = cliente.descuento
        Cells(fila, 4).Value = “10 %”
        Cells(fila, 6).Value = Cells(fila, 3).Value – Cells(fila, 5).Value

    End If

   Next

End With

End Sub

Excel y bases de datos (Oracle)

Como continuación de posts anteriores, en esta ocasión vamos a analizar como establecer una conexión entre Excel y una base de datos Oracle, que nos permita hacer una consulta devolviendo los datos a Excel. Ya comenté que son diversas las tareas que se pueden efectuar entre una base de datos y Excel. Así que para tener un primer contacto y repasar las diferencias que existen entre unos SGBD y otros, he preferido no adentrarme demasiado en otro tipo de operaciones.

Como hacíamos en otras macros, es necesario como paso previo tener activadas las referencias oportunas: Microsoft ActiveX Data Objects 2.x. Este ejemplo ha sido desarrollado con la versión Oracle® Database Express Edition. Para ello he creado una pequeña tabla “EMPLEADOS” con tres campos ID, NOMBRE y EDAD.

Veamos el código:

Sub excel_oracle()
  ‘Definiendo las variables para la conexión, reordset, columnaumnas y filas
  Dim conexion As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim columna As Integer
  Dim fila As Integer

  ‘creando la conexión a la base de datos y al recordset
  Set conexion = New ADODB.Connection
  Set rs = New ADODB.Recordset

   ‘parámetros de la conexión
  conexion.Open ( _
     “User ID=system” & _
    “;Password=root” & _
    “;Data Source=XE” & _
    “;Provider=MSDAORA.1″)

  ‘sentencia SQL sacando todos los datos de la tabla EMPLEADOS
  rs.Open “select * from EMPLEADOS”, conexion

  ‘vamos a presentar los resultados poniendo el contador a cero
  ”””””””””””””””””””””””””””””””’
  ‘Colocamos los datos empezando por os títulos de las columnas”
  ‘                                                            ”
  ”””””””””””””””””””””””””””””””’
  columna = 0

  ‘ En la primera fila:los nombres de las columnas
  Do While columna < rs.Fields.Count
    Cells(1, columna + 1) = rs.Fields(columna).Name
    columna = columna + 1
  Loop

  ‘Ahora los datos obtenidos de la selección
  fila = 1
  Do While Not rs.EOF
    fila = fila + 1
    columna = 0

    Do While columna < rs.Fields.Count
      Cells(fila, columna + 1) = rs.Fields(columna).Value
      columna = columna + 1
    Loop

    rs.MoveNext
  Loop

End Sub

El fragmento de código referente a la conexión, nos marca los parámetros que se precisan para realizar la misma: el usuario (en este caso system) , la contraseña (root) el origen de los datos (la base de datos XE) y el proveedor   OLEDB de Microsoft para Oracle que es MSDAORA.1.

Después lanzamos la orden SQL y presentamos los datos con dos bucles, uno para la cabecera, y otro para los datos. Y aquí el resultado:

 

Excel y bases de datos (MySQL)

Como continuación del post anterior, en este vamos a tratar la conexión de Excel con el gestor de bases de datos MySQL. Las operaciones que se pueden realizar entre una hoja Excel y una base de datos pueden ser variadas,tanto como para trabajar un tutorial aparte. No obstante se pueden abordar las principales:

  • Crear/eliminar una base de datos.
  • Crear/eliminar tablas y campos.
  • Hacer consultas generales o específicas.
  • Insertar datos o borrarlos.

En el post anterior traté de como crear una base de datos en Access, con sus tablas y campos; y el volcado posterior de datos de Excel hacia Access. Más adelante iré tratando el resto de operaciones que podemos efectuar entre los dos programas de Microsoft, mediante VBA.

Por ahora vamos a analizar como hacer operaciones con MySQL.

Si lo analizan bien, casi siempre efectuámos los mismos pasos:

  1. Debemos crear el espacio de trabajo, donde debemos especificar a Excel el motor que se precisa para hacer la conexión al gestor de bases de datos.
  2. Tenemos que marcar las referencias específicas para que Excel la carge y la tenga disponible en su biblioteca.
  3. Preparamos las variables para:
  • la conexión
  • servidor
  • usuario
  • contraseña
  • nombre de la base con la que trabajar
  • las tablas y campos en su caso.

Una vez tratado los datos, es decir ejecutada la petición, cerrarmos la conexión y vaciamos las variables objeto.

Bien en el caso que nos ocupa y, atendiendo al primer punto, debemos cargar la referencia a la librería Microsoft Active X Data Objects 2.x .

Lo más probable es que no tenga descargado los drivers para la conexión con MySQL. Para ello vaya a esta dirección “MySQL ODBC 3.51 Driver”. Le aconsejo que escoga Windows (x86, 32-bit), MSI Installer Connector-ODBC  para 32-bit y en caso de 64 Windows (x86, 64-bit), MSI Installer Connector-ODBC. Una vez descargadodoble clic en el MSI y escoga instalación típica.Una vez terminada la instalación, puede verificar la misma en Panel de Control>Herramientas Administrativas>Orígenes de datos ODBC. Clic en la pestaña Drivers y búsquelo en la lista:

A continuación le expongo comentado el código para volcar los datos de una base de datos de MySQL a Excel.  En el ejemplo la base que se ha empleado se llama “directorio” y la tabla “example”. Se ha utilizado un servidor local, con lo que la dirección del host es la 127.0.0.1. Para poder practicar le aconsejo que inserte una nueva base de datos, sino tiene una ya creada, con una tabla y unos pocos datos.

 
‘Para empezar y como paso previo cargamos la referencia a la librería Microsoft Active X Data Objects 2.x
‘Se presupone instalado el driver MySQL ODBC 3.51

‘Comenzando

Sub excelMySQL()
‘variable para la conexión
Dim conexion As New ADODB.Connection

‘idem para el nombre del servidor en este caso estoy empleando una versión en local
Dim miservidor As String

‘ahora la base de datos en este caso se llama directorio y la tabla example
Dim bd As String

‘variables para usuario y contraseña (no la creo porque no la tengo en el servidor, en otro caso hay que crearla
Dim user As String

‘variables para la tabla que tiene sólo tres campos
Dim i As Long, tabla, nombre, edad, consulta As String

‘el recordset
Dim rs As ADODB.Recordset

‘———————————————————————-

‘Estableciendo la conexión

‘———————————————————————-

miservidor = “127.0.0.1”
bd = “directorio”
user = “root”

Set conexion = New ADODB.Connection ‘ con esto utilizamos la conexión indicada más arriba

conexion.Open “DRIVER={MySQL ODBC 3.51 Driver}” _
& “;SERVER=” & miservidor _
& “;DATABASE=” & bd _
& “;UID=” & user _
& “;OPTION=16427″

‘————————————————————————

‘A partir de aquí si todo ha ido bien podemos realizar varias acciones
‘Crear o eliminar tablas
‘Añadir campos
‘En este caso vamos a extraer datos a Excel
‘————————————————————————

Set rs = New ADODB.Recordset
consulta = “SELECT * FROM example”
rs.Open consulta, conexion, adOpenStatic

With Worksheets(1).Cells
.ClearContents
.CopyFromRecordset rs
End With

‘Cerramos la conexión
On Error Resume Next
rs.Close
Set rs = Nothing
conexion.Close
Set conexion = Nothing
On Error GoTo 0

End Sub

Habrá podido observar una línea :  rs.Open consulta, conexion, adOpenStatic. Quisiera comentarla un poco. El objeto Recordset es el interface entre los datos obtenidos de nuestras consultas sobre las tablas y nuestras páginas excel. Representa una tabla organizada en filas (registros) y columnas (campos). La propiedades y métodos de Recordsets pasan por entender el funcionamiento de otros dos objetos claves:

El  cursor  o puntero que nos permite desplazarnos por los registros del recordset. Dependiendo del tipo elegido determinaremos los desplazamientos y cambios realizables en los datos.

El tipo de cursor lo definiremos mediante la propiedad CursorType, los posibles valores son:

El otro objeto es el cierre o tipo de bloqueo que efectuaremos en la base de datos cuando modifiquemos un recordset, a fin de evitar que dos o mas usuarios accedan a modificar un mismo registro a la vez.

El tipo de cierre lo definiremos mediante la propiedad LockType, los posibles valores son:

Por último habrá podido observar el parámetro : & “;OPTION=16427″. Esto nos asegura que los valores numéricos de tipo large son interpretados correctamente por Excel.

Excel y bases de datos (Access)

En este post voy a intentar explicar como utilizar objetos para crear bases de datos, introducir datos, modificar su contenido o crear consultas, mediante VBA Excel.

Excel ya lleva incorporada una serie de funciones para trabajar con bases de datos, que gestionan múltiples tareas para el usuario. Si queremos, no obstante, automatizar y potenciar estas tareas debemos hacer uso de VBA.

Antes que nada es necesario aclarar algunos conceptos claves.

Las aplicaciones utilizan unos mecanismos para conectarse a bases de datos (en adelante BD). Son los ActiveX Data Objects (ADO),.

Con ADO, un programa puede leer, insertar, editar, o borrar, la información contenida dentro de la base de datos. Además, se puede manipular la propia base para crear nuevas tablas, como también alterar o eliminar las ya existentes, entre otras cosas.

ADO sustituyó a DAO y a RDO (para BD remotas)  que eran los sistemas previos que se usaban.

La última versión de ADO, creada por Microsoft, se llama ADO.NET, y se usa en los entornos de programación de la plataforma .NET, de Microsoft, para manejar bases de datos tanto en Windows como en la Web mediante ASP.NET, que es la nueva versión del ASP para la plataforma.NET.

Comenzaremos para aquellos que trabajan con versiones antiguas de BD, trabajando con DAO.

Para utilizar los objetos de acceso a datos, lo primero que debemos hacer es crear una referencia a estos objetos. Para ello recuerde dentro del editor VBA vamos a  Herramientas-Referencias-Microsoft DAO 3.6  Object Library.

Cuando cargamos esta referencia se crea un primer objeto del que se derivan los demás con los que vamos a trabajar: DBEngine, a partir del mismo obtendremos los espacios de trabajo  (Workspace) donde se encontrarán cada una de las BD (database).

Una vez creada la BD obtendremos las TableDef por cada tabla que creemos con sus respectivos campos (Fields) y el índice de referencia de cada tabla (Index).

Al objeto de poder realizar los ejemplos sería conveniente que cree una carpeta por ejemplo C:\VisualExcel.

Supongamos que tenemos un libro con unos datos del que vamos a crear una base de datos. Podríamos insertar un botón “Crear BD” e insertarle el código siguiente:

 

Private Sub CommandButton1_Click()

Dim bd As Database

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

End Sub

 

Si no tiene activada la referencia comprobará que no en la selección del tipo de variable objeto, no le aparece la opción Database y por lo tanto tendrá un error.

Con el método CreateDatabase escribimos como argumentos la ruta y el nombre del fichero a crear. Si no escribimos nada en la extensión tomará el tipo por defecto que es Acces “mdb”.

 

Si en vez de crear manualmente la carpeta desea automatizarlo debería crear los procedimientos para verificar la ruta / archivo y en su caso crear la BD. Veamos:

 

Procedimiento para crear la carpeta

Sub NuevaCarpeta(ruta As String, nombrecpt As String)

‘Verificamos que el directorio existe

If Dir(ruta, vbDirectory + vbHidden) <> “” Then

 ‘comprobamos que en dicha ruta no existe ya una carpeta con el mismo nombre aunque esté oculta

    If Dir(ruta & “\” & nombrecpt, vbDirectory + vbHidden) = “” Then

‘ si todo es correcto la creamos en la ruta y con el nombre especificados

    MkDir ruta & “\” & nombrecpt

mensaje:     MsgBox (“Archivo creado: ” & ruta & “\” & nombrecpt)

    Else: MsgBox “La carpeta ya existe”

   End If

Else: MsgBox “Directorio no existente”

End If

End Sub

 

Y para crear la BD

Private Sub CommandButton1_Click()

Dim bd As Database

On Error Resume Next

NuevaCarpeta “C:\”, “VisualExcel”

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

End Sub

 

 

Revise las funciones Medir y Dir, que  realmente son comandos de MS-DOS adaptados a VBA.

 

Bien hemos creado la BD pero como comprobará está vacía. Por lo tanto hemos de crear las tablas, los campos y los índices.

 

 

Para las tablas emplearemos el TableDef y para los campos Field como tipos.

Para crear los campos, al igual que en Access debemos indicar el nombre y su tipo.

 

Para crear la tabla emplearemos :

Set  td= bd.CreateTableDef(“Nombre_para_la_tabla”)

 

Y para los campos, algo similar:

Set fldnombre= td.CreateField(“Nombre”,dbTex,8) es decir el nombre del campo, el tipo según Access y su longitud.

 

Por último escribiríamos las sentencias para añadir los campos a la tabla, y la tabla a la base de datos.

 

Veamos como queda todo junto:

 

Private Sub CommandButton1_Click()

Dim bd As Database

Dim td As TableDef

Dim fldalias, fldnombre, fldapellidos, flddireccion, fldpoblacion, fldtelefono, fldnacimiento As Field

 

On Error Resume Next

‘creando la carpeta de ejemplo

NuevaCarpeta “C:\”, “VisualExcel”

‘creando la base de datos

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

 

‘ahora la tabla

Set td = bd.CreateTableDef(“MiTabla”)

 

‘creando los campos

Set fldalias = td.CreateField(“Alias”, dbText, 8)

Set fldnombre = td.CreateField(“Nombre”, dbText, 30)

Set fldapellidos = td.CreateField(“Apellidos”, dbText, 50)

Set flddireccion = td.CreateField(“Direccion”, dbText, 50)

Set fldpoblacion = td.CreateField(“Poblacion”, dbText, 50)

Set fldtelefono = td.CreateField(“Telefono”, dbInteger, 9)

Set fldnacimiento = td.CreateField(“Fecha_nacimiento”, dbDate)

 

‘añadiendo los campos a la tabla

td.Fields.Append fldalias

td.Fields.Append fldnombre

td.Fields.Append fldapellidos

td.Fields.Append flddireccion

td.Fields.Append fldpoblacion

td.Fields.Append fldtelefono

td.Fields.Append fldnacimiento

 

‘añadiendo la tabla

bd.TableDefs.Append td

 

End Sub

 

Si abre ACCESS verá  que se ha creado la base de datos, con la tabla y los campos correspondientes. También comprobará que lo ha hecho en modo Access 2000.

Una forma alternativa y más cómoda podría ser crear los campos con un bucle With…wend. En el ejemplo siguiente podrá ver cómo además creamos un campo autoincrementable:

‘Creando la base de datos
Sub base_datos()
‘creamos las variables objeto para el espacio de trabajo
Dim ws As Workspace

‘la base de datos a la que llamaré bd
Dim bd As Database
‘y la única tabla de la bd que se llamara td
Dim td As TableDef
‘para los campos uno por cada columna
Dim id, apellidos, nombre, direccion, pob, tel As Field
‘creando los objetos
Set ws = DBEngine.Workspaces(0)
Set bd = ws.CreateDatabase(“C:\mi_bd”, dbLangGeneral)
Set td = bd.CreateTableDef(“MiTabla”)

‘necesito crear un campo contador para ello lo creo aparte de los otros
Set id = td.CreateField(“Id”, dbLong)
id.Attributes = dbAutoIncrField
td.Fields.Append id
‘creando y añadiendo  el resto de campos de una sóla vez
With td.Fields

    .Append td.CreateField(“Alias”, dbText, 30)
    .Append td.CreateField(“apellidos”, dbText, 30)
    .Append td.CreateField(“nombre”, dbText, 30)
    .Append td.CreateField(“dirección”, dbText, 50)
    .Append td.CreateField(“población”, dbText, 30)
    .Append td.CreateField(“tel”, dbLong)
End With

‘añadiendo la tabla a la base de datos

bd.TableDefs.Append td

If Error = “” Then
MsgBox “Base de datos creada”

End If

End Sub

Bien hasta ahora lo que hemos hecho es crear la base, las tablas etc Pero todavía no hemos volcado los datos a Access.  Veamos como podriamos hacerlo:

‘Exportando los datos con DAO
Sub exportar_datos()
Dim bd As Database, rs As Recordset, r As Long, x As Long, td As TableDef

‘abriendo la base de datos
Set bd = OpenDatabase(“C:\mi_bd.mdb”)

‘abriendo recordset
Set rs = bd.OpenRecordset(“MiTabla”, dbOpenTable)

‘recogiendo todos los campos en una tabla
r = 2 ‘empiezo en la fila 2 de la hoja 1

Do While Len(Range(“A” & r).Formula) > 0
‘repetir hasta la primera celda vacía de  la columna A

With rs
    .AddNew
    .Fields(“Alias”) = Range(“B” & r).Value
    .Fields(“apellidos”) = Range(“C” & r).Value
    .Fields(“nombre”) = Range(“D” & r).Value
    .Fields(“dirección”) = Range(“E” & r).Value
    .Fields(“población”) = Range(“F” & r).Value
    .Fields(“tel”) = Range(“G” & r).Value

    .Update
End With
r = r + 1

Loop

x = rs.RecordCount

‘cerramos
rs.Close
Set rs = Nothing

bd.Close
Set bd = Nothing

  If Error = “” Then
MsgBox “Exportación correcta se han creado ” & x & ” registros.”

End If

End Sub

Si en algún momento dado quisiéramos eliminar la base usaríamos la función Kill:

‘Para elminarla
Sub eliminar_bd()
Kill (“C:\mi_bd.mdb”)

End Sub

 

La Shell de Windows

La SHELL de Windows es la interfaz gráfica de Microsoft Windows. Es donde se recoge toda la interfaz del usuario: el escritorio, la barra de tareas, explorador, etc.

Cuando usamos el sistema operativo como usuarios finales, trabajamos generalmente con aplicaciones, bien para navergar en internet, para redactar documentos, para hacer cálculos, etc.

Los programadores ,por contra,  tienen necesidades más avanzadas que los usuarios finales . Los programadores necesitan para compilar y ejecutar programas y automatizar tareas repetitivas.

La mayoría de los usuarios estamos familiarizados con la interfaz gráfica de usuario o GUI. La mayoría de los entornos GUI muestran un escritorio que se rellena con ventanas , iconos y menús . Se utiliza un puntero (dirigido por un ratón o trackball) para seleccionar los elementos en el escritorio. Estas interfaces son a veces llamados WIMP interfaces, después de las primeras letras de las palabras W_ indow, i_ con, m_ enu, y p_ ointer.

La otra interfaz de usuario es la shell de comandos . Un intérprete de comandos no es más que una ventana sin ningún tipo de decoración gráfica. Los comandos se muestran como texto. No hay ningún icono, y se puede usar el ratón sólo en circunstancias limitadas.

Uuna consola de comandos no es una buena interfaz de usuario para los usuarios ocasionales. Sin embargo, los usuarios experimentados encuentran que el shell de comandos, es indispensable para muchas tareas.

Vamos a ir poco a poco viendo cómo podemos agilizar nuestras macros implementando comandos de la shell de Windows wn nuestros códigos.

Por ejemplo en la siguiente macro vamos a crear un botón al que le vamos a asociar el código para abrir el bloc de notas:

  1. Abra un libro nuevo.
  2. Inicie el cuadro de controles.(Menú>Ver>barra de herramientas>Cuadro de controles)
  3. Seleccione y dibuje un botón.
  4. En tiempo de diseño haga doble click sobre el mismo e inserte  entre Private Sub CommandButton1_Click()  y End Sub el código

Dim RetVal
RetVal = Shell(“C:\Archivos de programa\Windows NT\Accesorios\Wordpad.exe”, 1)    ‘ Ejecuta bloc de notas
AppActivate RetVal

Salga del modo diseño (clic en el icono escuadra ) vaya a excel y haga clic en el botón. Si lo ha hecho correctamente se le abrirá el bloc de notas.

Analizando el código, lo primero que hemos hecho es declara una variable tipo variant para recoger el número que identifica la tarea del programa que ejecuta la función Shell.

Esta función tiene además los argumentos siguientes:

  • En primer lugar el nombre del programa a ejecutar y su ubicación exacta.
  • En segundo lugar un número asociadoal estilo de la ventana en que se va a ejecutar el programa, que puede presentar los valors siguientes:
vbHide 0 Se oculta Windows y se pasa el foco a la ventana oculta. La constante vbHide no se aplica a plataformas Macintosh.
vbNormalFocus 1 Windows recupera el foco y vuelve a su posición y tamaño original.
vbMinimizedFocus 2 Windows se muestra como un icono con foco.
vbMaximizedFocus 3 Windows se maximiza con foco.
vbNormalNoFocus 4 Windows vuelve al tamaño y posición más recientes. La ventana activa actual permanece activa.
vbMinimizedNoFocus 6 Windows se muestra como un icono. La ventana activa actual permanece activa.

Si quisiéramos mostrar / acceder a la ventana de comandos deberíamos ejecutar la macro siguiente:

Sub muestra_cmd()
Dim RetornaVal
RetornaVal = Shell(“C:\WINDOWS\system32\cmd.exe”, 1)

End Sub

Intente ejecutar un ejemplo similar como el anterior haciendo que se muestre mediante un botón la calculadora de Windows.

 

Excel y WebMail

Como continuación a la interoperatividad de Excel con los servicios de correos, en el presente post trataré de explicar como podemos mandar un libro, una hoja o un pequeño texto escrito en una hoja excel, usando correo webmail.

La primera macro nos va acomprobar el sistema de correo WebMail que tenemos instalado o si no tenemos ninguno:

Sub sistema_decorreo_instalado()
Select Case Application.MailSystem
    Case xlMAPI
        MsgBox “Está utilizando  Microsoft Mail”
    Case xlPowerTalk
        MsgBox “El sistema de correos es PowerTalk”
    Case xlNoMailSystem
        MsgBox “No tiene ningún sistema de correos instalado”
End Select
End Sub

En principio establecemos un flujo de control (Select Case) que va a verificar que clientes de correos tenemos en el equipo, y esto lo hacemos con la propiedad Application.MailSystem, esta es de sólo lectura y soporta tres constantes xlMAPI que verifica si estamos usando un Internet Message Access Protocol (IMAP) , si usamos el sistema de comandoz por voz de Microsoft PowerTalk o no tenemos nada instalado.

A continuación vamos a ver como podríamos enviar una copia de la hoja activa por correo webmail, en mi caso utilizando WebMail de Microsoft 2011:

Sub EnviarHojaPorCorreoElectrónico()
    ActiveSheet.Copy
    ActiveWorkbook.SendMail Recipients:=”fulanito@loquesea.com”, Subject:=”Envío de hoja de Excel”
    ActiveWorkbook.Close savechanges:=False
End Sub

Bueno como podrá ver no tiene mucha complicación, hemos utilizado el método SendMail Recipients (algo así como ” enviar correo al buzón”…) y poco más.

Supongamos que ahora tenemos una hoja con un texto escrito, y desamos enviar ese texto en concreto. Supongamos que en la celda E1 hemos escrito la dirección del destinatario y en la E2 la del remitente. Aquí va el código:

Function EnviarMails_CDO() As Boolean

    ‘ Creo la variable de objeto CDO
    Dim Email As CDO.Message
    Dim Autentificacion As Boolean

    ‘ ahora doy vida al objeto
    Set Email = New CDO.Message

    ‘indicamos los datos del servidor:
    Email.Configuration.Fields(cdoSMTPServer) = “smtp.gmail.com”
    Email.Configuration.Fields(cdoSendUsingMethod) = 2
    ‘indicamos el nro de puerto. por defecto es el 25, pero gmail usa el 465. hay otro

    Email.Configuration.Fields.Item _
        (“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) = CLng(465)
    ‘aqui dejamos en claro si el servidor que usamos requiere o nó autentificación.
    ‘1=requiere, 0=no requiere. Para gmail, entonces, 1
    Email.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/&#8221; & _
                “configuration/smtpauthenticate”) = Abs(1)
    ‘segundos para el tiempo maximo de espera. aconsejo no modificarlo:
    Email.Configuration.Fields.Item _
        (“http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout&#8221;) = 30

    ‘aqui defino como True (verdadera) a la autentificación para el envío de mails.
    Autentificacion = True
    ‘ahora configuramos las opciones de login de gmail:
    If Autentificacion Then
        ‘nombre de usuario
        Email.Configuration.Fields.Item _
            (“http://schemas.microsoft.com/cdo/configuration/sendusername&#8221;) = “fulanito@gmail.com”
        ‘contraseña
        Email.Configuration.Fields.Item _
            (“http://schemas.microsoft.com/cdo/configuration/sendpassword&#8221;) = “micontraseña de correo”
        ‘si el servidor utiliza SSL (secure socket layer). en gmail: True
       Email.Configuration.Fields.Item _
            (“http://schemas.microsoft.com/cdo/configuration/smtpusessl&#8221;) = True
    End If

        ‘a partir de ahora tomaremos los datos incluidos en el la hoja de excel:
        ‘ Dirección del Destinatario
        Email.To = Trim([e1].Value)

        ‘ Dirección del remitente
        Email.From = Trim([e2].Value)

        ‘ Asunto del mensaje
        Email.Subject = Trim([e3].Value)

        ‘ Cuerpo del mensaje
        Email.TextBody = Trim([e4].Value)

        ‘Ruta del archivo adjunto
        If [e5].Value <> vbNullString Then
            Email.AddAttachment (Trim([e5].Value))
        End If

        ‘antes de enviar actualizamos los datos:
        Email.Configuration.Fields.Update
        ‘colocamos un capturador de errores, por las dudas:
        On Error Resume Next
        ‘enviamos el mail
        Email.Send
        ‘si el numero de error es 0 (o sea, no existieron errores en el proceso),
        ‘hago que la función retorne Verdadero
        If Err.Number = 0 Then
           EnviarMails_CDO = True
        Else
          ‘caso contrario, muestro un MsgBox con la descripcion y nro de error
           MsgBox “Se produjo el siguiente error: ” & Err.Description, vbCritical, “Error nro ” & Err.Number
        End If

        ‘destruyo el objeto, para liberar los recursos del sistema
        If Not Email Is Nothing Then
            Set Email = Nothing
        End If
        ‘libero posibles errores
        On Error GoTo 0
    End Function

En principio para que pueda funcionar ha de activar la siguiente referencia: Microsoft (CDO for Windows 2000) . Para ver bien los comentarios y comprender el código debería insertarlo en un módulo y hacer las pruebas.

Quizás lo que cueste un poco más de entender son las líneas referentes a las configuraciones de los campos de correo. Necesitamos decirle al sistema como están configurados los campos referentes al remitente, destinatario etc. Y para ello debemos indicarle donde puede encontrar la información pertinente. Bien pues de la misma manera que a veces precisamos acudir a una biblioteca a buscar información de un tema en concreto, aquí hacemos lo mismo. Por ello en primer lugar debemos abrir la biblioteca de objetos CDO , biblioteca donde se encuentra las referencias que el sistema necesita sobre las interfaces de los programas de correos. Eso es lo que hacemos con la línea:

Email.Configuration.Fields.Item _
            (“http://schemas.microsoft.com/cdo/configuration/sendusername&#8221;)

y siguientes.

En muchos casos no es necesario especificar esas líneas ya que CDO utilizaría por defecto el gestor de correo incluido en la máquina.

Siguiendo con los ejemplos, en la siguiente macro vamos mandar las celdas que seleccionemos de una hoja:

‘Este procedimeinto envía las celdas seleccionadas en el cuerpo del mensaje
‘Seleccione unas cuantas celdas antes de enviar el mensaje

Sub CDO_enviando_un_rango()
    Dim rng As Range
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant

    Set iMsg = CreateObject(“CDO.Message”)
    Set iConf = CreateObject(“CDO.Configuration”)

       iConf.Load -1    ‘ CDO valores por defecto
       Set Flds = iConf.Fields
        With Flds
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl&#8221;) = True
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate&#8221;) = 1
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername&#8221;) = “fulanito@gmail.com”
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword&#8221;) = “contraseñadefulanito”
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;) = “smtp.gmail.com”

        .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;) = 2
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) = 25
        .Update
    End With

    Set rng = Nothing
    On Error Resume Next

    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox “La selección o no es un rango o la hoja está protegida” & _
               vbNewLine & “corrigalo e inténtelo de nuevo”, vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    With iMsg
        Set .Configuration = iConf
        .To = “fulanito@gmail.com”
        .CC = “”
        .BCC = “”
        .From = “””FULANITO”” <fulanito@gmail.com>”
        .Subject = “Esto es una prueba”
        .HTMLBody = RangetoHTML(rng)
        .Send
    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Hay que prestar especial cuidado con los firewalls e incluso con los antivirus. Debe verificar que no le están bloqueando las salidas.

En esta macro utilizamos una función VBA propia de Outlook pero que se traslada mediante CDO a Excel :HTMLBody = RangetoHTML(rng) es decir es una función que convierte a código HTML el rango que hayamos seleccionado.

EXCEL Y OUTLOOK

En el presente post vamos a examinar como trabajar conjuntamente con Outlook y Excel, implementando códigos en VBA. Si bien en la actualidad son cada vez más los usuarios de servicios de clouding y por ende correo web, todavía los clientes mail ocupan un espacio de uso  nada despreciable en muchos ordenadores.

Para automatizar Outlook, primero debe tener acceso a la modelo de objetos de Outlook.   Es decir, para manipular objetos de Outlook, primero debe ser capaz de “verlos”.   En general hay dos maneras de exponer un modelo de objetos para su aplicación Excel, conocido como el enlace anticipado y el enlace OLB.

“Enlace” se refiere a la exposición del modelo de objetos del cliente a la aplicación host.   En nuestro caso, eso significa que la exposición del modelo de objetos de Outlook para nuestra aplicación en VBA para Excel, el   modelo de objetos de Outlook se almacena en un OLB (biblioteca de objetos) de archivos.

Para poder tener acceso a la librería de objetos de Outlook procederemos de la misma forma que con Word. Abra el edito VBA y desde la barra de menús Herramientas>Referencias>Microsoft Outlook x.0 (donde x.0 puede ser desde el 9.0 al 14.0, para los ejmplos de este post utilizaremos 14.0).

Vamos a utilizar una macro para ver si se ha establecido correctamente la referencia al objeto. Si todo va bien, se debe de arrancar Outlook y nos saldrá un MsgBox confirmándolo:

Sub probando_referencia()
Dim outlookApp As Outlook.Application
 Set outlookApp = New Outlook.Application
 MsgBox outlookApp.Name
End Sub

Otra forma:

Sub probando_referencia_bis()
    Dim outlookApp As Object

    Set outlookApp = CreateObject(“Outlook.Application”)

    MsgBox outlookApp.Name
End Sub

Utilizamos CreateObject cuando deseamos crear una nueva instancia de Outlook y GetObject para hacer referencia a una instancia en ejecución de Outlook.  El siguiente código utiliza el enlace para mostrar la carpeta predeterminada de la agenda de la instancia existente de Outlook y crea una instancia si no hay una ya existente:

Sub Mostrar_Calendario()
    ‘declaración de objetos
    Dim outlookApp As Object
    Dim olNs As Object

    ‘tratamiento en caso de error
    On Error Resume Next

    ‘recogemos la referencia al objeto Outlook
    Set outlookApp = GetObject(, “Outlook.Application”)

    ‘tratamiento en caso de error que no se pueda crear el objeto
    If Err.Number = 429 Then
        Set outlookApp = CreateObject(“Outlook.application”)
    End If

    On Error GoTo 0
    ‘obteniendo el calendario de la carpeta por defecto
    Set olNs = outlookApp.GetNamespace(“MAPI”)
    ‘si no aparece activamos el explorador
    If outlookApp.ActiveExplorer Is Nothing Then
        outlookApp.Explorers.Add _
            (olNs.GetDefaultFolder(9), 0).Activate
    Else
    ‘en caso contrario lo mostramos
        Set outlookApp.ActiveExplorer.CurrentFolder = _
            olNs.GetDefaultFolder(9)
        outlookApp.ActiveExplorer.Display
    End If
    ‘vaciamos las variables de objeto
    Set olNs = Nothing
    Set outlookApp = Nothing

End Sub

Aunque en posteriores posts trataré el tema de los errores, a fin de poder tener un listado de  errores “previsibles” en ejecución desde el editor pulse F1 (ayuda) y busque
Errores interceptables, podrá ver y analizar diferentes tipos de errores con el número que VBA le asocia.

Algo más complicado sería automatizar el envío de e-mails desde Excel utilizando Outlook. Bueno para el nivel de este blogg_curso quizás es algo avanzado, pero intente leer los comentarios y comprender el código:

Sub mandar_rango_por_correo()
    ‘declaramos las variables
    ‘el rango de origen Source
    Dim Source As Range
    ‘el libro a enviar como miLibro
    Dim miLibro As Workbook
    Dim wb As Workbook
    ‘nombre de la ruta del fichero temporal
    Dim TempFilePath As String

    ‘nombre del fichero temporal
    Dim TempFileName As String

    ‘variables de apoyo para la extensión y formato de fichero
    Dim FileExtStr As String
    Dim FileFormatNum As Long

    ‘variables para crear los objetos aplicación y correo
    Dim OutApp As Object
    Dim OutMail As Object

    ‘inicializamos las variables
    Set Source = Nothing

    ‘si hay error salta a la linea siguiente
    On Error Resume Next

    ‘seleccionamos el rango
    Set Source = Range(“A1:K50″).SpecialCells(xlCellTypeVisible)

    ‘desactivamos cualquier controlador de errores
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox “El origen no es un rango o la hoja está protegida. ” & _
               “Por favor corrígalo “, vbOKOnly
        Exit Sub
    End If
    ‘aceleramos la ejecución del código
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ‘añadimos un nuevo libro con una sola hoja en el mismo
    ‘de esa forma mandamos una copia del original
    Set wb = ActiveWorkbook
    Set miLibro = Workbooks.Add(xlWorksheet)

    ‘copiamos el origen
    Source.Copy
    With miLibro.Sheets(1) ‘ahora en la hoja 1
        .Cells(1).PasteSpecial Paste:=8 ‘pego el ancho de la columna (a 8)
        .Cells(1).PasteSpecial Paste:=xlPasteValues ‘pego los valores
        .Cells(1).PasteSpecial Paste:=xlPasteFormats ‘pego el formato
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    ‘utilizo la función Environ para recoger una variable de Windows y asigno el nombre
    TempFilePath = Environ$(“temp”) & “\”
    TempFileName = “Selection de ” & wb.Name & ” ” _
                 & Format(Now, “dd-mmm-yy h-mm-ss”)

    ‘según la versión coloco la extensión del fichero
    If Val(Application.Version) < 12 Then
        ‘ Caso de Excel 2000 ó 2003 .xls
        FileExtStr = “.xls”: FileFormatNum = -4143
    Else
        ‘ Caso de  Excel 2007 ó 2010.
        FileExtStr = “.xlsx”: FileFormatNum = 51
    End If

    ‘entrando con Outlook
    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    ‘salvo mi fichero
    With miLibro
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
        ‘rellenando los datos del envío
            .To = “enriquecabell@gmail.com”
            .CC = “”
            .BCC = “”
            .Subject = “Libro de prueba”
            .Body = “Bueno aquí va adjunto el fichero”
            .Attachments.Add miLibro.FullName
            .Send
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    ‘ya como no lo necesitamos nos cargamos los ficheros temporales
    Kill TempFilePath & TempFileName & FileExtStr

    ‘vaciando variables
    Set OutMail = Nothing
    Set OutApp = Nothing
    ‘poniendo la casa en orden
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Excel y Word

Todas las aplicaciones Office de Microsoft utillizan el mismo lenguaje VBA; la diferencia entre ellos es su modelo de objeto. esto facilita que cualquier el acceso entre ellas siempre y cuando estén instaladas.

Para el caso que nos ocupa, cómo acceder a Word mediante excel, precisamos acceder a la biblioteca de objetos de Word:

biblioteca de objetos

Un archivo con una extensión de nombre de archivo .OLB que proporciona a los controladores de Automatización OLE (como Visual Basic) información sobre los objetos de Automatización OLE disponibles. Puede utilizar el Examinador de objetos para examinar el contenido de una biblioteca de objetos y obtener información sobre los objetos que proporciona.

Para acceder a dicha biblioteca podemos hacerlo de dos formas:

  • Mediante vinculación previa: la referencia al objeto se realiza cuando se compila el programa.
  • Mediante vinculación posterior: en tiempo de ejecución.

Vinculación previa

El código se ejecuta más rápido, ya que se realiza una referencia al objeto antes de que se escriba el código. Para ello debemos añadir la biblioteca a la aplicación caso de que no esté disponible. Para ello:

  1. Seleccionar Herramientas>Referencias.
  2. Activar Micrsosoft Word 11.o Object Library.
  3. Clic en Aceptar.

Si no se encuentra la biblioteca es que Word no está instalado. Si encuentra otra versión es que hay otra versión diferente de Word instalada.

Veamos un ejemplo de cómo abrir un documento Word desde Excel denominado testWord.doc y que lo tenemos en C:\ existente :

Sub prueba_dos_word()
‘Cramos una variable para la aplicación y otro para el documento
Dim wdApp As Word.Application
Dim wdDoc As Document

‘invocamos la aplicación y abrimos el documento
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & “\testWord.doc”)
wdApp.Visible = True

‘vaciamos las variables
Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

Si la versión de Word a la que se hacer referencia no existe, aparecerá un error de aplicación.

Vinculación posterior

Con este tipo de vinvulación creamos un objeto que hace referencia a la aplicación Word  antes de vincular con la biblioteca Word.

El siguiente ejemplo abre y hace visible el documento anterior:

Sub prueba_word()
‘debemos declarar una variable de objeto
Dim varDoc As Object

‘creamos el objeto
Set varDoc = CreateObject(“Word.application”)

varDoc.Visible = True ‘hacemos visible el documento
Sheets(“Hoja1″).Range(“A1:B1″).Copy ‘selecionamos y copiamos el rango señalado
varDoc.Documents.Add ‘añadimos hojas al documento
varDoc.Selection.Paste ‘pegamos la selección
varDoc.activedocument.SaveAs ThisWorkbook.Path & “/” & “testWord.doc” ‘guardamos el documento en el directorio raiz como…
varDoc.Documents.Close ‘cerramos  varDoc.Quit
Application.CutCopyMode = False
End Sub

La diferencia si la prueba es notable. Con este método no sabemos lo que está ocurriendo. No aparecen las ayudas visuales cuando se hace referencia a los objetos de Word.

Abrir documentos de Word

Para abrir un documento existente hay que utilizar el método Open. Admite varios parámetros entre ellos Read Only y AddtoRecentFile. Por ejemplo el siguiente código abre un documento existente en forma sólo lectura e impide que el archivo se añada a la lista de archivos recientes, bajo el menú Archivo:

Sub abrir_arc_word()

Documents.Open Filename:=”C:\testWord.doc”, ReadOnly:=True, AddToRecentFiles:=False

End Sub

XML y Excel (Ejemplo práctico)

En el presente post vamos a reproducir un ejemplo práctico de Microsoft de como tratar con datos XML en Excel.

  1. Descargue descargue el archivo de ejemplo XML.
  2. Agregará una asignación a una hoja de cálculo y a continuación la llenará con los datos importados.

Empiece preparando la hoja de cálculo

  1. Inicie Excel y abra el archivo de ejemplo.

Cuando aparezca el cuadro de diálogo Abrir XML, haga clic en Utilizar el panel de tareas Origen XML y en Aceptar.

  1. En la celda A1, escriba Empleado y en la celda A3, escriba Administrador.
  2. En la celda C1, escriba Departamento, y en la celda C3, escriba Fecha de presentación.
  3. Aplique el formato que desee al texto para resaltarlo.

Agregue la asignación

  1. Arrastre el elemento ns1:EmployeeName del panel de tareas Origen XML a la celda B1 y, a continuación, arrastre el elemento ns1:Department a la celda D1.
  2. Arrastre el elemento ns1:ManagerName a la celda B3 y, a continuación, arrastre el elemento ns1:Date a la celda D3.
  3. En ns1:ExpenseItem, seleccione ns1:Description y arrástrelo a la celda B5.
  4. Aún en ns1:ExpenseItem: arrastre el elemento ns1:Account a la celda C5, arrastre ns1:Date a la celda D5 y, a continuación, arrastrens1:Total a la celda E5.
  5. Guarde el archivo. Esto agregará la asignación al archivo y enlazará los datos del archivo de origen a la asignación.

Ahora ya podrá…

Importar datos a la asignación

  1. Seleccionar cualquiera de las celdas asignadas. En el menú Datos, elija XML y, a continuación, haga clic en Importar.

Aparecerá el cuadro de diálogo Importar XML.

  1. Use la lista Buscar en para localizar el archivo de ejemplo y haga clic en Importar.

Excel rellenará los campos asignados. Observe que Excel aplica automáticamente el formato de lista XML a los elementos repetitivos (en este caso, los elementos de gasto) en la asignación.

XML y Excel

Una de las ventajas a partir de la versión 2003 de Excel  es la mejora en la gestión de datos XML.

XML es un sistema estándar de codificación de información. Los programas que utilizan el formato XML pueden intercambiar fácilmente sus datos, ya que responden a una misma lógica interna.

Los documentos XML son ficheros de texto que contienen la información organizada en forma de árbol: cada rama puede tener unos atributos propios y servir de base para otras ramas. Además, los documentos XML se pueden transformar (por ejemplo, a formato HTML, para mostrar la información en una página web), o combinar: un tronco con todas sus ramas puede pasar a ser una rama de otro árbol mayor. Un ejemplo de documento XML abreviado podría ser:

   <juegos>
     <juego nombre="escondite" espacio="exteriores">
       <jugadores min="2" max="16" />
       <edades min="3" max="indefinido" />
       <descripcion>
          Los jugadores se reparten en dos equipos y...
       </descripcion>
     </juego>
     <juego nombre="ajedrez" espacio="interiores">
       .... etc.
     </juego>
   </juegos>

XML tiene unas reglas que hay que preservar y un alfabeto. Vamos a analizar como covertir un libro desde Excel a HTML y vieceversa utilizando XML.

Reglas

  • Todo elemento de datos debe empezar y terminar con una etiqueta idéntica. Los nombres de etiquetas son sensibles a mayúsculas y minúsculas.
  • El archivo XML debe empezar y terminar con una etiqueta raíz. En el archivo sólo puede existir una etiqueta raiz.
  • Es válido tener una etiqueta vacía.
  • Si se anidan etiquetas, la interior debe estar centrada antes de cerrar la exterior.

Alfabeto

Cómo consigue Excel leer correctamente los datos: existen dos tipos adicionales de archivo que mejoran a los archivos XML.

Los archivos XSD son archivos de esquema que definen la relación entre los datos y los requisitos de validación de los mismos. Los archivos XSL se denominan transformaciones o soluciones. Una transformación define cómo se deberían mapear los campos en el archivo XML en su documento.

Convertir HTML a EXCEL y viceversa con XML

Podemos guardar cualquier archivo xls en formato xlm. Los gráficos y los códigos vba no obstante se perderán, no así los formatos.

Para abrir un archivo XML:

Sub Abrir_XML()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(“c:\ejemplo.xml”)

End Sub

 

Como lista podemos usar el siguiente código:

Sub Abrir_XML_FicheroLista()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(Filename:=”c:\ejemplo.xml”, LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

End Sub

 

Trabajando con varias propiedades

Dado que la mayoría de objetos tienen diversas propiedades, a veces, necesitaremos efectuar varias acciones sobre un mismo objeto. Si tenemos un objeto con un nombre demasiado largo, a veces,  resulta engorroso tener que escribir una y otra vez dicho nombre para asociarle el método o propiedad que desamos asignar. A fin de facilitar un poco las cosas VBA nos ofrece utilizar la estructura With…End With.

Por ejemplo imagine que deseamos poner una fila entera en negrita, doble subrayado, fuente grande y color especial, el código prodría ser el siguiente:

Range(“A1:K1″).Select

Selection.Font.Bold=True

Selection.Font.Size=12

Selection.Font.ColorIndex=5

Selection.Font.Underline=xlUnderlineStyleDoubleAccounting

 

Podemos reescribirlo con la sintáxis expuesta teniendo en cuenta que todo lo que hace referencia al objeto en sí empieza con un punto:

With Range (“A1:K1″).Font

.Bold=True

.Size=12

.ColorIndex=5

.Underline=xlUnderlineStyleDoubleAccounting

End With

Formularios: cuadro de lista y cuadro combinado

Un cuadro de lista y un cuadro combinado son controles similares pero con ligeras diferencias y sirven para permitir al usuario elegir entre varios elementos o insertar el valor que desee en una lista.

Por ejemplo:

Cuadro de lista. Muestra una lista de uno o más elementos de texto de entre los cuales puede elegir el usuario.

Cuadro combinado. Combina un cuadro de texto con un cuadro de lista para crear un cuadro de lista desplegable. Un cuadro combinado es más compacto que un cuadro de lista, pero requiere que el usuario haga clic en la flecha abajo para mostrar la lista de elementos. Utilice un cuadro combinado para permitir que el usuario escriba una entrada o elija un solo elemento de una lista. El control muestra el valor actual en el cuadro de texto, independientemente de cómo se haya insertado ese valor.

 

La propiedad predeterminada de un control ListBox es Value.

El evento predeterminado de un control ListBox es Click.

El siguiente ejemplo agrega y elimina el contenido de un control ListBox utilizando los métodos AddItem y RemoveItem y las propiedades ListIndex y ListCount.

Para utilizar este ejemplo, copie este código de ejemplo en la parte Declaraciones de un formulario. Asegúrese de que el formulario contiene:

  • Un control ListBox llamado ListBox1.
  • Dos controles CommandButton llamados CommandButton1 y CommandButton2.

 

Ejemplo:

 

Private Sub Command1_Click()
If Text1 = “” Then
MsgBox “Debe ingresar un nombre para poder agregar un elemento”, vbQuestion + vbOKOnly, “Datos incompletos”
‘Salimos de la rutina ya que no se ha ingresado nada en el control text1
Exit Sub
End If

‘Agregamos el contenido del Text1 en el control List1
List1.AddItem Text1
End Sub

Private Sub Command2_Click()
‘Si la lista no está vacía entonces podemos eliminar
If List1.ListIndex <> -1 Then
‘Eliminamos el elemento que se encuentra seleccionado
List1.RemoveItem List1.ListIndex
End If
End Sub

 

Formularios: Check buttom / Option Buttom

El control CheckBox, o casilla de verificación, permite elegir una opción (activada/desactivada, True/False) que el usuario puede establecer o anular haciendo click.

Si un control CheckBox es dependiente de un origen de datos, al cambiar el valor, éste cambia el valor de su origen. Un control CheckBox deshabilitado muestra el valor actual, pero está atenuado y no permite realizar cambios al valor desde la interfaz de usuario.

La propiedad predeterminada de un control CheckBox es Value.

El evento predeterminado de un control CheckBox es Clic.

Control CheckBox. Ejemplo de las propiedades Enabled y Locked

El siguiente ejemplo demuestra las propiedades Enabled y Locked y cómo se complementan entre sí. Este ejemplo expone cada propiedad independientemente con un control CheckBox, para que observe los valores individuales y combinados. Este ejemplo también incluye un segundo control TextBox para que pueda cortar y pegar información entre los controles TextBox y comprueba las actividades admitidas por los valores de estas propiedades.

Nota Puede copiar la selección al Portapapeles utilizando CTRL+C y pegar utilizando CTRL+V.

Para utilizar este ejemplo, copie este código de ejemplo en la parte Declaraciones de un formulario. Asegúrese de que el formulario contiene:

  • Un control TextBox llamado TextBox1.
  • Dos controles CheckBox llamados CheckBox1 y CheckBox2.
  • Un segundo control TextBox llamado TextBox2.

 

 

 

 

Private Sub CheckBox1_Change()

TextBox2.Text = “TextBox2″

TextBox1.Enabled = CheckBox1.Value

End Sub

 

Private Sub CheckBox2_Change()

TextBox2.Text = “TextBox2″

TextBox1.Locked = CheckBox2.Value

End Sub

 

Private Sub UserForm_Initialize()

TextBox1.Text = “TextBox1″

TextBox1.Enabled = True

TextBox1.Locked = False

CheckBox1.Caption = “Enabled”

CheckBox1.Value = True

CheckBox2.Caption = “Locked”

CheckBox2.Value = False

TextBox2.Text = “TextBox2″

End Sub

 

Formularios (Frame)

Es un contenedor de otros controles. Los agrupa para que resulten facilmente identificables para el usuario.

Para agrupar controles, en primer lugar trace el control Frame y, a continuación, meta los controles dentro de Frame. De este modo podrá mover al mismo tiempo el Frame y los controles que contiene. Si traza un control fuera del Frame y, a continuación, intenta moverlo dentro de éste, el control se colocará sobre el Frame, pero no pertenecerá a el.  Es decir, si es un OptionButton este se comportará como si estuviese fuera del Frame, aunque físicamente esté dentro de el.

El evento predeterminado de un control Frame es Click.

Crear un formulario

Para crear un formulario personalizado abriremos el editor VBA  e iremos a insertar->UserForm. Verá como en la ventana de código aparee ahora un pequeño formulario vacío y aparece el cuadro de herramientas.

A la derecha poedrá ver la ventana de propiedades. No obstante si no la visualiza pulse f4 o vaya a menú->ver->ventana de propiedades. También puede pulsar sobre el icono correspondiente en la barra de menús.

En dicha ventana podrá ver las propiedades inherentes al objeto form, siendo las que más le ineterese las siguientes:

Name->indica el nombre con el que vamos a identificar el objeto. Generalmente suele emplearse cualquier nombre significativo con el prefijo frm. Por ejemplo frmempleados

Caption->el nombre que va a visualizar el objeto en el caso del formulario arriba a la izquierda.

Border/ ->Back Color-> los colores de los bordes y el fondo.

Font->con que tipo de fuente se va a escribir en el formulario.

Enable-> por defecto a true si inicialmente queremos programar que el objeto no se vea debemos cambiarlo a false.

En el formulario podemos mediante arrastrar y soltar colocar diferentes controles desde la caja de herramientas o cuadro de controles. Aquí tenemos una lista de los principales controles:

Para definir la posición de un control, hay que modificar las propiedades left y top que nos indican la distancia entre el control y la esquina superior izquierda del formulario.

Para una explicación multimedia de como crear un formulario en VBA Excel puedes ver este vídeo .

Formularios

Los formularios  en general son documentos impresos o en línea diseñados  con un  formato y estructura estándar que facilita la captura, la organización y la edición de la información.

Los formularios  contienen controles,  objetos que muestran datos o hacen que sea más fácil para los usuarios entrar o editar los datos, realizar una acción o seleccionar una opción. En general, los controles facilitan el uso de los formularios. Algunos ejemplos de controles comunes son los cuadros de lista, los botones de opción y los botones de comando. Los controles también pueden ejecutar macros asignadas y responder a eventos, tales como clics del mouse, mediante la ejecución de código de VBA.

Resumen de controles de formulario
Nombre del botón Ejemplo Descripción
Imagen del botón Etiqueta Ejemplo de un control de etiqueta de formulario Identifica el propósito de una celda o un cuadro de texto, o muestra texto descriptivo (como títulos, leyendas, imágenes) o breves instrucciones.
Imagen del botón Cuadro de grupo Ejemplo de control de cuadro de grupo de la barra de herramientas Formularios Agrupa controles relacionados en una unidad visual en un rectángulo con una etiqueta opcional. Generalmente, se agrupan botones de opción, casillas de verificación o contenido estrechamente relacionado.
Imagen del botón Botón Ejemplo de un control de botón de la barra de herramientas Formularios Ejecuta una macro que realiza una acción cuando un usuario hace clic en él. Los botones también se conocen como botones de comando.
Imagen del botón Casilla de verificación Ejemplo de control de casilla de verificación de la barra de herramientas Formularios Activa o desactiva un valor que representa una selección inequívoca entre opuestos. Puede seleccionar más de una casilla de verificación en una hoja de cálculo o en un cuadro de grupo. Una casilla de verificación puede tener uno de tres estados: activada, desactivada y mixta, lo que significa una combinación de los estados activada y desactivada (como en una selección múltiple).
Imagen del botón Botón de opción Ejemplo de control de botón de opción de la barra de herramientas Formularios Permite una única elección dentro de un conjunto limitado de opciones que se excluyen mutuamente; un botón de opción generalmente está contenido en un cuadro de grupo o un marco. Un botón de opción puede tener uno de tres estados: activado, desactivado y mixto, lo que significa una combinación de los estados activado y desactivado (como en una selección múltiple). Los botones de opción también se conocen como botones de radio.
Imagen del botón Cuadro de lista Ejemplo de un control de cuadro de lista de formulario Muestra una lista de uno o más elementos de texto de entre los cuales puede elegir el usuario. Use un cuadro de lista para mostrar grandes cantidades de opciones que varían en número o contenido. Existen tres tipos de cuadros de lista:

  • Un cuadro de lista de selección única permite solamente una elección. En este caso, un cuadro de lista se asemeja a un grupo de botones de opción, a excepción de que un cuadro de lista puede controlar un gran número de elementos de manera más eficiente.
  • Un cuadro de lista de selección múltiple permite una elección o elecciones contiguas (adyacentes).
  • Un cuadro de lista de selección extendida permite una elección, elecciones y no contiguas, o inconexas.
Imagen del botón Cuadro combinado Ejemplo de un control de cuadro combinado de formulario Combina un cuadro de texto con un cuadro de lista para crear un cuadro de lista desplegable. Un cuadro combinado es más compacto que un cuadro de lista pero requiere que el usuario haga clic en la flecha abajo para mostrar una lista de elementos. Use un cuadro combinado para permitir que un usuario escriba una entrada o elija solamente un elemento de la lista. El control muestra el valor actual en el cuadro de texto, sin importar el modo en que dicho valor se haya proporcionado.
Imagen del botón Barra de desplazamiento Ejemplo de control de barra de desplazamiento de la barra de herramientas Formularios Se desplaza por un intervalo de valores cuando el usuario hace clic en las flechas de desplazamiento o arrastra el cuadro de desplazamiento. Además, se puede mover por una página (en un intervalo preestablecido) de valores haciendo clic en el área entre el cuadro de desplazamiento y cualquiera de las flechas de desplazamiento. Generalmente, el usuario también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados.
Imagen del botón Control de número Ejemplo de control de número de la barra de herramientas Formularios Aumenta o disminuye un valor, como un incremento numérico, una hora o una fecha. Para incrementar el valor, es necesario hacer clic en la flecha arriba; para disminuirlo, se debe hacer clic en la flecha abajo. Generalmente, el usuario también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados.

El objeto Range

Dentro de la coleción de objetos de Excel y tal como había expuesto anteriormente Range es casi la unidad básica con la que podemos trabajar. Y digo casi porque dentro de Range podemos encontrar un objeto como es Cells (celdas). A qué nos referimos cuando hablamos de Range:

  • A un conjunto de celdas.
  • A una celda en concreto.
  • A toda una fila / columna.
  • A celdas de otras hojas.

Como propiedades el objeto Range tiene las siguientes:

  • ActiveCell->representa la primera celda activa de la ventana activa (windowactive) o especificada.
  • Areas->todos los rangos de una seleción múltiple.
  • Cells->representa a una celda o clección de ellas.
  • Columns->representa las columnas de la hoja activa,o especificada, o del rango especificado.
  • Entire column/row->la columna/fila  entera dentro del rango.
  • End->la celda situada al final del rango especificado.
  • Offset->la celda desplazada según un nº específico de fila/columna.

Formas de seleccionar celdas con Range:

Una celda sólo:

Range(“A1″).Select

Un conjunto de celdas contiguas dentro de la misma hoja:
Range(“A1:G5″).Select

Para seleccionar celdas no contiguas:
Range(“A1,A5,B4″).Select

Para seleccionar celdas no contiguas mas un conjunto contiguo:
Range(“A1,A5,B4:B8″).Select

Otro ejemplo:

La siguiente tabla muestra algunas referencias de estilo A1 utilizando el método Range.

Referencia Significado
Range(“A1″) Celda A1
Range(“A1:B5″) Celdas de la A1 a la B5
Range(“C5:D9,G9:H16″) Selección de varias áreas
Range(“A:A”) Columna A
Range(“1:1″) Fila uno
Range(“A:C”) Columnas de la A a la C
Range(“1:5″) Filas de la uno a la cinco
Range(“1:1,3:3,8:8″) Filas uno, tres y ocho
Range(“A:A,C:C,F:F”) Columnas A, C y F

De todas las propiedades descritas tal vez una de las más usadas sea Offset, que nos permite seleccionar celdas contiguas a la celda activa según una determinada posición de filas y clumnas:

expresión.Offset(RowOffset, RowOffset)

RowOffset      Variant opcional. El número de filas (positivo, negativo o cero) que indica el desplazamiento del rango. Los valores positivos desplazan hacia abajo y los negativos, hacia arriba. El valor predeterminado es 0.

ColumnOffset      Variant opcional. El número de columnas (positivo, negativo o cero) que indica el desplazamiento del rango. Los valores positivos desplazan a la derecha y los negativos, hacia la izquierda. El valor predeterminado es 0.

ActiveCell.Offset(0,0)->nos determina la posición actual

Ejemplo:

Sub ejemplo()
‘ejemplos del empleo de range y cells
‘escribo Hola en varias celdas de la hoja 2
Worksheets(“Hoja2″).Range(“A1:B5″) = “Hola”
‘lo mismo pero en celdas discontinuas
Worksheets(1).Range(“F1″, “G5″) = “Hi”
‘ahora en la hoja 2
Worksheets(“Hoja2″).Range(“J1, K3, H5″) = “AAA”
ActiveCell.Offset(2, 2) = “Me desplazo”
End Sub

Si establece una variable de objeto para un objeto Range, puede manipular fácilmente el rango utilizando el nombre de la variable.

El siguiente procedimiento crea la variable de objeto miRango y, a continuación, asigna la variable al rango A1:D5 de la Hoja1 del libro activo. Las instrucciones posteriores modifican las propiedades del rango, sustituyendo el nombre de la variable por el objeto del rango.

Sub Aleatorio()  
Dim miRango As Range
Set miRango = Worksheets(“Hoja1″).Range(“A1:D5″)
miRango.Formula = “=ALEATORIO()”
miRango.Font.Bold = True
End Sub

Utilice la función Array para identificar un grupo de hojas. El siguiente ejemplo selecciona tres hojas del libro activo.

Sub Varias()  
Worksheets(Array(“Hoja1″, “Hoja2″, “Hoja4″)).Select
End Sub

Utilice la propiedad Rows o Columns para trabajar con filas o columnas enteras. Estas propiedades devuelven un objeto Range que representa un rango de celdas. En el siguiente ejemplo, Rows(1) devuelve la fila uno de la Hoja1. A continuación, la propiedad Bold del objeto Font del rango se establece en True.

Sub FilaNegrita()
Worksheets(“Hoja1″).Rows(1).Font.Bold = True
End Sub

La siguiente tabla muestra algunas referencias de fila y columna, utilizando las propiedades Rows y Columns.

Referencia Significado
Rows(1) Fila uno
Rows Todas las filas de la hoja de cálculo
Columns(1) Columna uno
Columns(“A”) Columna uno
Columns Todas las columnas de la hoja de cálculo

Para trabajar con varias filas o columnas al mismo tiempo, cree una variable de objeto y utilice el método Union, combinando varias llamadas a la propiedad Rows o Columns. El siguiente ejemplo cambia a negrita el formato de las filas uno, tres y cinco de la hoja de cálculo uno del libro activo.

Sub VariasFilas()
Worksheets(“Hoja1″).Activate
Dim miUnión As Range
Set miUnión = Union(Rows(1), Rows(3), Rows(5))
miUnión.Font.Bold = True
End Sub
Método abreviado para hacer referencia a rangos


Podemos utilizar también corchetes cuando hacemos referencias a rangos. Utilizando corchetes, así:

 

En vez de Range(“D5″) podemos utilizar [D5]

En vez de Range(“A1:D5″) podemos utilizar [A1:D5]

Range(“A1:D5″,”G6:I17″)   —>[A1:D5,G6:I17]

El objeto WorkSheet(hoja)

El objeto Worksheets representa a las hojas de un libro de Excel. El acceso al mismo puede ser:

  • La hoja activa–> ActiveSheet
  • Llamada a la hoja 1–>WorkSheets(“Hoja1″)
  • Llamada a la primera hoja de la colección–>WorkSheets(1)
  • Llamada a la última hoja de la colección.–>WorkSheets(WorkSheets.Count)
  • Llamada a una hoja de otro libro.–>
Workbooks(“Libro1″).Worksheets(“Hoja1″)
Workbooks(“Libro2″).Worksheets(“Hoja1″)
Propiedades: a excepción de “name” todas son de lectura.
  • .Name–>Nombre de la hoja.
  • .Index–>Numero de la hoja dentro del índice numérico de la colección.
  • .Count–>Devuelve el número de hojas dentro de la colección.
  • .Next/previous–>Devuelve la hoja siguiente/anterior.
  • .Used Range–>Devuelve un objeto range con el rango de la hoja que tiene datos.
Ejemplo: la siguiente macro devuelve el nombre de las hojas del libro.
Sub Muestra_mis_hojas()
    Dim mihoja As Worksheet

    For Each mihoja In Worksheets
        MsgBox mihoja.Name
    Next mihoja

End Sub

Eventos:
  • .Activate–>activa la hoja
  • .calculate–>reclacula la hoja especificada.
  • .Delete–>Borra la hoja.
  • .Protect–>Protege la hoja señalada.
Ejemplo: macro para proteger la hoja activa.
Sub ProtectSheet()

    ActiveSheet.Protect

End Sub

El objeto APPLICATION

El objeto Application representa la aplicación completa Microsoft Excel. Como todo objeto tiene una serie de propiedades :

Propiedad Devuelve
Application. UserName Nombre del usuario
OrganizationName Nombre de la empresa
OperatingSystem Sistema operativo
Version Versión de MS Excel
ProductCode Código de MS Excel
StandardFont Fuente por defecto
StandardFontSize Tamaño fuente por defecto
DecimalSeparator Carácter separador de miles
ActivePrinter Impresora por defecto
DefaultFilePath Ruta de acceso por defecto
UserLibraryPath Ruta a carpeta Add-Ins

Ejemplo de macros del objeto Application: en el siguiente ejemplo listamos algunas de las propiedades descritas.

Sub ejemplo()

Dim i As Byte
Dim primero, ultimo As Variant
Dim uno, dos, tres, cuatro, cinco As String
Dim matriz

uno = Application.UserName
dos = Application.OperatingSystem
tres = Application.ActiveWorkbook.Name
cuatro = Application.Version
cinco = Application.LibraryPath

matriz = Array(uno, dos, tres, cuatro, cinco)

For i = 1 To 5
Cells(i, 1) = matriz(i)
Next
End Sub

Los eventos del objeto Application se suelen dar generlamente al crear o abrir un libro, cuando cambia una hoja, o se crea o modifica una tabla dinámica.

NewWorkbookSheetActivate

SheetBeforeDoubleClick

SheetBeforeRightClick

SheetCalculate

SheetChange

SheetDeactivate

SheetFollowHyperlink

SheetSelectionChange

SheetPivotTableUpdate

WindowActivate

WindowDeactivate

WindowResizeWorkbookActivate

WorkbookAddinInstall

WorkbookAddinUninstall

WorkbookBeforeClose

WorkbookBeforePrint

WorkbookBeforeSave

WorkbookDeactivate

WorkbookNewSheet

WorkbookOpen

WorkbookPivotTableCloseConnection

WorkbookPivotTableOpenConnection

Ejemplo: Crearemos un libor nuevo con ese nombre.
Sub NuevoLibro()

Workbooks.Add
Application.ActiveWorkbook.SaveAs (“Mi_nuevo_libro”)
End Sub

 

Abrir un libro existente: para abrir un libro utilizaremos la siguiente Macro.

      Sub AbrirLibro()

         Workbooks.Open (“C:\Mis documentos\Ejemplo.xls”)

      End Sub

 

Trabajando con objetos

Tal vez haya escuchado hablar de programación estructurada, modular,( algo menos ) lógica, funcional, concurrente, y la que vamos a tratar a continuación la POO (programación orientada a objetos).

La programación estructurada  es un tipo de programación que usa una lógica secuencial en su estructura, con estructuras de control, secuenciales y repetitivas. Es una forma de programar que tiene sus ventajas e inconvenientes, pero que, siendo profusamente utilizado en los años 60 se ha quedado un pco corto a la hora de cubrir las necesidades de aplicaciones gráficas.

No es objeto de este post entrar a describir los otros tipos de programación, ya que VBA es un lenguaje orientado a objetos, y por ello nos vamos a centrar en él.

Un objeto, diccionario en mano, es cualquier cosa perceptible por uno o más sentidos, especialmente algo que se puede ver y sentir.

Para VBA entendamos que un objeto es cualquier cosa dentro de la aplicación que puede manipularse de alguna forma.

Al igual que un vehículo, que como tal es un objeto, dentro del mismo existen otros objetos tales como las ruedas, las puertas, los retrovisores. Tanto el objeto vehículo como sus objetos inherentes tienen una serie de propiedades, como modelo, color, motor…Los métodos del coche definen que podemos hacer con él: frenar, girar,…

Los eventos del vehículo nos dice las cosas que le ocurren, si dejamos las luces encendidas y salimos un pitido nos avisa del hecho, etc.

Si analiza un poco lo descrito observará que existe una organización intrínseca en el modelo orientado a objetos. Es decir se da una jerarquía desde los niveles superiores (más genéricos) hasta los niveles inferiores más específicos.

Algunas propiedades también realizan la función de objetos. Por ejemplo el objeto Application tiene una propiedad ActiveWindow (nombre de la ventana activa) que a su vez en sí misma es un objeto. Esto puede dar lugar a confunsiones en la sintáxis. Por ejemplo:

Application.ActiveWindow.ActiveCell.Font.Italic

Puede abreviarse y quedaría así:

ActiveCell.Font.Italic

Las funciones ImputBox y MsgBox

A lo largo de los diferentes ejemplos que he ido insertando, he ido adelantando en la práctica algunos conceptos que, no  he expuesto en profundidad. En el presente post, trataré de explicar las posibles configuraciones que tiene los cuadros de entrada (inputbox) y de mensajes (message-box/MsgBox).

Los primeros permiten introducir datos por teclado. Su sintáxis es la siguiente:

InputBox([prompt],[Title],[Default],{xpos],[ypos],[help file,context])

‘Prompt : Es el texto mostrado en el cuadro de dialogo

‘Title :(opcional)Texto de titulo para el cuadro de dialogo

‘Default :Valor por defecto mostrado en el cuadro de dialogo

‘Xpos,Ypos :La patalla se ubicar segun las cordenadas

‘Helpfile ,context (opcional).el archivo de ayuda

‘Type :Opcional. Expresión numérica que es el número de contexto de Ayuda asignado por el autor al tema de Ayuda correspondiente.El tipo de dato devuelto como se ve en la siguiente tabla.

codigo –> SIGNIFICADO
‘——
‘0 —–> Una fórmula
‘1 —–> Un numero
‘2 —–> Una cadena(texto)
‘4 —–> Un valor logico(True o False)
‘8 —–> Una celda de referencia ,como un objeto de rango
’16 —–> Un error de valor,como #N/A

El MsgBox es el cuadro de respuesta de la API de Windows. Su sintáxis es la siguiente:

Muestra un mensaje en un cuadro de diálogo, espera a que el usuario haga clic en un botón y devuelve un tipo Integer correspondiente al botón elegido por el usuario.


MsgBox(prompt[, buttons][, title][, helpfile, context])

La sintaxis de la función MsgBox consta de estos argumentos con nombre:

prompt Requerido. Expresión de cadena que representa el prompt en el cuadro de diálogo. La longitud máxima de prompt es de aproximadamente 1024 caracteres, según el ancho de los caracteres utilizados. Si prompt consta de más de una línea, puede separarlos utilizando un carácter de retorno de carro (Chr(13)) o un carácter de avance de línea (Chr(10)), o una combinación de caracteres de retorno de carro – avance de línea (Chr(13) y Chr(10)) entre cada línea y la siguiente.
buttons Opcional. Expresión numérica que corresponde a la suma de los valores que especifican el número y el tipo de los botones que se pretenden mostrar, el estilo de icono que se va a utilizar, la identidad del botón predeterminado y la modalidad del cuadro de mensajes. Si se omite este argumento, el valor predeterminado para buttons es 0.
title Opcional. Expresión de cadena que se muestra en la barra de título del cuadro de diálogo. Si se omite title, en la barra de título se coloca el nombre de la aplicación.
helpfile Opcional. Expresión de cadena que identifica el archivo de Ayuda que se utiliza para proporcionar ayuda interactiva en el cuadro de diálogo. Si se especifica helpfile, también se debe especificar context.
context Opcional. Expresión numérica que es igual al número de contexto de Ayuda asignado por el autor al tema de Ayuda correspondiente. Si se especifica context, también se debe especificar helpfile.

Valores

El argumento buttons tiene estos valores:

VbOKOnly 0 Muestra solamente el botón Aceptar.
VbOKCancel 1 Muestra los botones Aceptar y Cancelar.
VbAbortRetryIgnore 2 Muestra los botones Anular, Reintentar e Ignorar.
VbYesNoCancel 3 Muestra los botones , No y Cancelar.
VbYesNo 4 Muestra los botones y No.
VbRetryCancel 5 Muestra los botones Reintentar y Cancelar.
VbCritical 16 Muestra el icono de mensaje crítico.
VbQuestion 32 Muestra el icono de pregunta de advertencia.
VbExclamation 48 Muestra el icono de mensaje de advertencia.
VbInformation 64 Muestra el icono de mensaje de información.
VbDefaultButton1 0 El primer botón es el predeterminado.
VbDefaultButton2 256 El segundo botón es el predeterminado.
VbDefaultButton3 512 El tercer botón es el predeterminado.
VbDefaultButton4 768 El cuarto botón es el predeterminado.
VbApplicationModal 0 Aplicación modal; el usuario debe responder al cuadro de mensajes antes de poder seguir trabajando en la aplicación actual.
VbSystemModal 4096 Sistema modal; se suspenden todas las aplicaciones hasta que el usuario responda al cuadro de mensajes.
VbMsgBoxHelpButton 16384 Agrega el botón Ayuda al cuadro de mensaje.
VbMsgBoxSetForeground 65536 Especifica la ventana del cuadro de mensaje como la ventana de primer plano.
VbMsgBoxRight 524288 El texto se alínea a la derecha.
VbMsgBoxRtlReading 1048576 Especifica que el texto debe aparecer para ser leído de derecha a izquierda en sistemas hebreo y árabe.

Ejemplo:

Sub ejemplo()
Dim pregunta, res1, res2, res3 As String

pregunta = InputBox(“¿Cómo te llamas?”, “Bienvenido”)
res1 = MsgBox(“Hola ” & pregunta, vbExclamation, “Bienvenido”)
res2 = MsgBox(“Hola ” & pregunta, 64, “Bienvenido”)
res3 = MsgBox(“Hola ” & pregunta, 64 + vbMsgBoxRight, “Bienvenido”)
End Sub

Pruebe con las diferentes combinaciones a la hora de programar sus cuadros. Lo interesante es preparar unos cuantos que fácilmente puede implementar en sus programas.

Funciones de conversión de tipos

Existen una gran variedad de funciones de conversión en VBA aunque en la mayoría de casos se hace de forma automática. Las funciones de conversión se utilizan para cambiar el tipo de dato, de una expresión  a fin de poder operar  con ella.

Función  Tipo  Valor que devuelve la función 
CBool Boolean Cualquier cadena o expresión numérica válida
CByte Byte 0 a 255
CCur Currency 922 337 203 685 477,5808 a 922 337 203 685 477,5807
CDate Date Cualquier expresión de fecha válida
CDbl Double -1.79769313486231E308 a -4,94065645841247E-324 para los valores negativos ; 4,94065645841247E-324 a 1,79769313486232E308 para los valores positivos
CDec Decimal +/-79 228 162 514 264 337 593 543 950 335 para los número sin décimales. El rango de numeros  para  28 décimales +/-7,9228162514264337593543950335. El número más pequeño es diferente de cero 0,0000000000000000000000000001
CInt Integer -32 768 a 32 767 , las fracciones se redondean. Las partes decimales se redondean a 0.5 y el número par más cercano
CLng Long 2 147 483 648 a 2 147 483 647 , las fracciones son redondeadas.  Las partes decimales se redondean a 0.5 y el número par más cercano
CSng Single -3,402823E38 a -1,401298E-45 para los valores negativos,  1,401298E-45 à 3,402823E38 para los valores positivos
CStr String Los valores devueltos por la función Cstr de la expresión dependiente
CVar Variant Mismo rango de valores para los números de tipo doble y el tipo de cadenas no númericas.

En el siguiente ejemplo puede ver una forma de convertir datos de tipo numérico a tipo fecha y viceversa:

Sub convierte()
Dim x As Integer
Dim y As Date
x = Cells(1, 1)
Cells(1, 2) = CDate(x)

y = Date
Cells(3, 1) = y
Cells(3, 2) = CLng(y)
End Sub

Funciones Financieras

VBA tiene una serie de funciones financieras además de las que ya tiene Excel, que nos ofrece la oportunidad de crear potentes aplicaciones para finanzas empresariales o personales.

Si desea repasar las funciones financieras de Excel aquí tiene un documento que tal vez pueda ayudarle.

Para estudiar las propias de VBA volvamos al examinador de objetos. Pulse F2 y en el cuadro de búsqueda inserte FINANCIAL. Inmediatamente pordrá ver los miembros de Financial:

Las más usuales son:

rate: tasa fija de interés.

nper:nº de períodos

pmt: pagos o depósitos periódicos.

pv:valor actual del préstamo

fv: valor futuro del préstamo o inversión.

type:tipo de pago o depósito.

El siguiente ejemplo muestra el cálculo de la cuota para un préstamo francés:

Sub otro()
Dim Fmt, FVal, PVal, APR, TotPmts, Tipopago, Pago
Const PERIODFIN = 0, PERIODINI = 1    ‘ Momento de los pagos.
Fmt = “###,###,##0.00″    ‘ Define el formato.
FVal = 0    ‘ Normalmente 0 (crédito).
PVal = InputBox(“¿Cuánto dinero desea solicitar?”)
APR = InputBox(“¿Cuál es la tasa anual porcentual del crédito?”)
If APR > 1 Then APR = APR / 100    ‘ Asegura la forma correcta.
TotPmts = InputBox(“¿Cuantos pagos mensuales va a realizar?”)
Tipopago = MsgBox(“¿Realiza los pagos al final del mes?”, vbYesNo)
If Tipopago = vbNo Then Tipopago = PERIODINI Else Tipopago = PERIODFIN
Pago = Pmt(APR / 12, TotPmts, -PVal, FVal, Tipopago)
MsgBox “El importe del pago será ” & Format(Pago, Fmt) & ” al mes.”

End Sub

 

En la práctica que aquí se expone, se ha realizado el cálculo de las cuotas de un préstami tipo francés mediante Excel:

En F10 se inserta la fórmula siguiente:=(Va*i/q)/(1-(1+i/q)^-(n*q))

Si qusiéramos hacerlo mediante VBA el código asociado sería el siguiente:

Sub prestamo_dos()
Dim cuantia, cuota, tipo, periodos, i, r
Const k = 0.25 / 100

tipo = (Cells(4, 4).Value) / 100
periodos = Cells(5, 4).Value
cuantia = Cells(3, 4).Value

Cells(9, 2) = Cells(4, 4) / 100
For i = 9 To 15
Cells(i, 2).Value = (Cells(i – 1, 2).Value) + k
r = Cells(i, 2).Value
Cells(i, 6) = Abs(Pmt(r, periodos, cuantia))
Cells(i, 5) = Abs(Pmt(r / 2, periodos * 2, cuantia))
Cells(i, 4) = Abs(Pmt(r / 4, periodos * 4, cuantia))
Cells(i, 3) = Abs(Pmt(r / 12, periodos * 12, cuantia))

Next

End Sub

 

 

Funciones de cadenas

Una expresión de cadena devuelve un valor  de datos de tipo String. A continuación veremos la mayoría de las funciones que trabajan con cadenas, previamente aquí tenemos un cuadro de las funciones de cadenas en Excel.

Asc(string) =Código de carácter ANSI correspondiente a la primera letra de la cadena.

Chr(código char)= Carácter que corresponde al código ASCII

CStr(expresión)=Convierte una expresión a String.

Format(expresión,formato)=La expresión en el formato especificado

Ejemplos:

Sub prueba()
Dim a As String
a = InputBox(“Introduzca una palabra”, “Funciones de texto”)
Cells(1, 1) = Asc(a)
Cells(1, 2) = Chr(Cells(1, 1))
Cells(1, 3) = Format(Date, “dd / mmm /yyyy”)
Cells(1, 5).NumberFormat = “#,##0.00 €”

End Sub

(PD Cuando quiera insertar el símbolo euro presione alt gr+ 5)

También tenemos las siguientes expresiones para formatear expresiones:

FormatCurrency(expresión)=Expresión con formato de moneda.

FormatdateTime(expresión)=Id con formato de fecha y hora.

FormatPercent(expresión)=Id formato de porcentaje.

InStr(comienzo,posición1,posición2) Ejemplo: en la celda 1,1 previamente he insertado mi nombre completo, después ejecuto el siguiente código.

Sub prueba_dos()
Dim c, b As String
Dim i As Byte
b = Cells(1, 1)

c = InputBox(“Inserte la letra a buscar”)
MsgBox “La letra ” & c & ” que Vd busca se encuentra en la posición ” & InStr(1, b, c)

End Sub

LCase(expresión)= Convierte a minúsculas y Ucase a mayúsculas. Para poner la primera letra en mayúsculas y el resto minúsculas usamos StrConv(cadena,vbPoperCase).Ejemplo:

Sub letras()
Dim b As String
b = InputBox(“Inserte su nombre”)
Cells(1, 1) = b
Cells(2, 1) = LCase(b)
Cells(3, 1) = UCase(b)
Cells(4, 1) = StrConv(b, vbProperCase)
End Sub

Otras funciones de cadenas:

Left(texto,Nchart)–>devuelve los primeros caracteres de una cadena

Right(id)–>nos devuelve los últimos

Midt(texto, Init, NChar)–>permite extraer del texto una serie de carácteres a partir de “Init”.

Ejemplo:

Sub funciones_cadenas()
Dim palabra As String
palabra = InputBox(“Introduzca una palabra”, “Funciones de cadenas”)
If Len(palabra) = 0 Then
MsgBox “ERROR”
GoTo salida
Else
[A1] = “La palabra que ha introducido ha sido ” & palabra
[A2] = “La función left a partir de la 2ª letra devuelve el carácter :” & Left(palabra, 2)
[A3] = “La función right a partir de la 2ª letra devuelve el carácter :” & Right(palabra, 2)
[A4] = “La función Mid(palabra, 1, 2) devuelve :” & Mid(palabra, 1, 2)
[A5] = “Su palabra tiene ” & Len(palabra) & ” caracteres.”

End If
salida:

End Sub

Funciones matemáticas de VBA

VBA cuenta con funciones matemáticas propias que podemos utilizar en nuestros procedimientos:

Abs(número)–>Valor absoluto

Atn(número)–>Arcotangente de un número

Cos(número)–>Coseno

Exp(número)–>número e como base de los logaritmos naturales

Fix(número)–>La pasrte entera de un número decimal*

Hex(número)–>El valor hexadecimal de un número

Int(número)–>Parte entera del número*

Oct(número)–>El valor octal de un número

Rnd(número)–>Número aleatorio ente 0 y 1.

Sgn(número)–>El signo del número

Sqr(número)–>Raiz cuadrada

Sin(número)–>Seno

Tan(número)–>Tangente

 

(*) La función Fix(n) nos devuelve la porción entera del número. Si este es negativo, Fix  devuelve el primer entero negativo que es mayor que ese número. La función Int(n) nos devuelve el primer entero negativo que es menor o igual que el número.

La función Rnd(número)  genera un número aleatorio entre 0 y 1 con las opciones siguientes:

si número <0 Repite el mismo número cada vez

si número=0 El número generado más reciente

si número>0 El siguiente número aleatorio en la secuencia.

Le inserto aquí un código para que compruebe los resultados.

Sub prueba()
Dim i As Byte
Dim j As Integer

principio:
j = Int(Val(InputBox(“Escoja un número positivo “, “Función Random”)))
If j < 0 Then
MsgBox “Error debe escoger un número positivo”
GoTo principio
Exit Sub
Else
For i = 1 To 15
Cells(i, 1) = Rnd(-j)
Cells(i, 2) = Rnd(0)
Cells(i, 3) = Rnd(j)
Next

End If
End Sub

La necesidad de trabajar con números aleatorios surge muy a menudo. A veces necesitamos trabajar con un abanico más amplio que de 0 a9. Aquí le expongo la fórmula que ha de emplear en tal caso:

Int((superior-inferior)*Rnd + inferior

Ejemplo número aleatorio entre 25 y 100

Sub prueba2()
Cells(1, 1) = Int((100 – 25) * Rnd + 25)
End Sub

 

FUNCIONES (funciones de comprobación)

Podemos utilizar innumerables funciones dentro de VBA. También se pueden usar las propias de Excel, llamándolas desde un procedimiento o función. Por último podemos crear nuestras propias funciones. Una posible agrupación sería la siguiente:

  • Funciones de comprobación.
  • Funciones de cadenas.
  • Funciones matemáticas.
  • Funciones de conversión de tipos.
  • Funciones MsgBox e InputBox.
  • Definidas por el usuario.

Funciones de comprobación

Las funciones tienen la particularidad de que nos devuelven un valor. Las funciones de comprobación, que son las primeras que voy a abordar son muy útiles al permitirnos validad o comprobar los tipos de datos, bien los insertados en una celda o los introducidos por teclado.

Lista de Funciones de Comprobación.

IsNuméric(Expresión). Comprueba si expresión tiene un valor que se puede interpretar como numérico.

IsDate(Expresión). Comprueba si expresión tiene un valor que se puede interpretar como tipo fecha.

IsEmpty(Expresión). Comprueba que expresión tenga algún valor, que se haya inicializado.

IsError(Expresión). Comprueba si expresión devuelve algún valor de error.

IsArray(Expresión). Comprueba si expresión (una variable) es un array o no.

IsObject(Expresión). Comprueba si expresión (una variable) representa una variable tipo objeto.

IsNull(Expresión). Comprueba si expresión contiene un valor nulo debido a datos no válidos.

Función IsEmpty

Nos comprueba si una celda o rango está vacío, no hay datos.

Sintaxis

IsEmpty(expresión)

El argumento expresión requerido es un tipo de datos Variant que contiene una expresión de cadena o una expresión numérica. Sin embargo, ya que la función IsEmpty se utiliza para determinar si las variables individuales están inicializadas, el argumento expresión es generalmente un único nombre de variable.

Ejemplo:

Comprobamos si en la celda B1 de la hoja activa hay datos:

Sub ejemplo()
Dim mensaje As String

If IsEmpty(ActiveSheet.Range(“B1″)) Then

MsgBox Prompt:=”la casilla B1 está vacía”, Title:=”ERROR”

Else
mensaje = ActiveSheet.Range(“B1″)
MsgBox “En la casila B1 hay este dato :  ” & mensaje
End If

End Sub

Es complicado utilizar utilizar IsEmpty con datos recogidos por teclado mediante la función InputBox. Y ello es así porque esta función devuelve datos de tipo variant o sea cualquier expresión que se pueda evaluar a datos numéricos, de cadena o de fecha. Para entenderlo mejor modifique el código anterior por este otro:

Sub ejemplo2()
Dim mensaje

mensaje = InputBox(“Usuario”)
If IsEmpty(mensaje) Then

MsgBox Prompt:=”usuario?”, Title:=”ERROR”

Else

MsgBox “Hola ” & mensaje
End If
End Sub

Como comprobará la función IsEmpty no evalúa la cadena vacía, ya que InputBox en ese caso devolvería un cero.

Función IsNumeric/Función IsDate

Nos comprueba si una expresión se evalúa como un número, para IsNumeric o fecha para IsDate Por ejemplo evaluamos si en la celda activa hay valores numéricos:

Sub comprueba()
If Not IsNumeric(ActiveCell.Value) Then
MsgBox “Error”
Else
MsgBox “Los valores son numéricos”

End If

End Sub

Función IsObject

La función IsObject es útil solamente para determinar si un tipo de datos Variant es de VarType vbObject. Ejemplo:

Sub Obj()

Dim R As Range

‘ Si la variable R es Nothing es que no ha sido asignada, no se puede trabajar con ella

If R Is Nothing Then

MsgBox Prompt:=”La variable Objeto no ha sido asignada”, Buttons:=vbOK, _
Title:=”Error”

Else

R.Value = “Hola”

End If

End Sub

Aquí un ejemplo resumen:

Sub comprueba()
Dim cadena As Variant
cadena = ActiveCell.Value

If IsEmpty(cadena) Then
MsgBox “La celda no contiene datos”

ElseIf IsNumeric(cadena) Then
MsgBox “La celda contiene datos numéricos”

ElseIf IsDate(cadena) Then
MsgBox “La celda contiene datos tipo fecha”

ElseIf Not IsArray(cadena) Then
MsgBox “La celda no es un array”

Else
MsgBox “No hay nada para evaluar”
End If

End Sub

Condiciones dentro del bucle For…next

Podemos anidar condiciones una dentro de otras. En un bucle if…then podemos insertar otro y, así también podemos hacerlo con For…next. En l siguiente ejemplo vamos a sacar en las primeras 25 filas de forma aleatoria números en tre el 100 y el 999, después vamos a insertar un bucle anidado que nos va a colorear en rojo y negrita la fuente, para aquellos valores que superen el valor 500. Observe el código y vea cómo se generan los números aleatorios:

Sub ejemplo()
‘declaro variables
Dim numaleatorio As Integer
Dim i As Byte
‘desde la fila 1 a la 25 inserto números aleatorios del 100 al 999
For i = 1 To 25
numaleatorio = Int((999 – 100 + 1) * Rnd + 100)
Cells(i, 1) = numaleatorio
Next

‘voy a cambiar a rojo y negrita los valores que pasen de 500

For i = 1 To 25
    If Cells(i, 1) > 500 Then

        Cells(i, 1).Font.ColorIndex = 3
        Cells(i, 1).Font.Bold = True
    End If
Next i

End Sub

He separado el código para hacerlo más comprensible, evidentemente se puede hacer de otra forma. Dentro del bucle For…Next se sitúa la estructura condicional. Si la condición se cumple en una celda, esta cambia de color. El valor colorindex va del 1 al 56 y representa la paleta de colores en VBA. Aprovechando el tema intente hacer la práctica 6 puede serle de utilidad.

Bucles de repetición:Do…Loop

Este bucle comprueba que se ejecute una serie de instrucciones hasta que se cumpla una condición determinada. Por ejemplo en el siguiente código forzamos que se escriban los números desde el 1 al 11, para ello usamos una variable contador que la inicializamos en 0 y a partir de aquí le sumamos 1 hasta que llege al 11.

Sub ejemplo_Do_Loop()
Dim i As Byte
i = 0
Do Until i = 11
i = i + 1
Cells(i, 1) = i
Loop

End Sub

Una variante de este bucle sería Do…while…loop

Sub ejemplo_Do_while_loop()
Dim i As Byte
i = 0
Do While i < 20
i = i + 1
Cells(i, 1) = i
Loop

End Sub

 

Bucles de repetición: For…next

Los bucles de repetición nos permite efectuar varias veces una serie de instrucciones incluidas en un procedimiento.

El primero que vamos a analizar es el bucle For…next. Su sintáxis sería la siguiente:

For variable = inicio To fin salto
Next variable
Ejemplo vamos a realizar un ejercicio que nos presente por pantalla los cuadrados de los números 1 al 5.
Sub cuadrado()
‘Ejemplo de bucle for..next
‘definimos la variable contador
On Error Resume NextDim x, y As Byte

‘Iniciamos el bucle desde 1 hasta 5
For x = 1 To 5
y = x * x
MsgBox “El cuadrado de ” & x & ” es ” & y

Next

End Sub

Si no se dice nada, la variable comienza en el número que le hallamos maracado como inicio e ira recorriendo el bucle de 1 en 1. Si deseamos que lo haga por ejemplo de dos en dos, tenemos que especificarlo con la expresión step. En el ejemplo citado supongamos que deseamos hacer el mismo cálculo pero con los números pares del 1 al 12:
Sub cuadrado_par()
Dim x, y As Byte
For x = 2 To 12 Step 2
y = x * x
MsgBox “El cuadrado de ” & x & ” es ” & yNext
End Sub

Por cierto se habrá percatado de la presencia de una línea de código al comienzo del bucle On Error Resume Next. Con esta sentencia evitamos que el programa se “cuelge” ante la presencia de errores más o menos viene a decir: en presencia de error salta al siguiente paso.
En su momento trabajaremos el tema referente al tratamiento de errores.

Sintaxis reservada

Ya mencioné en otro post que podemos utilizar cualquier nombre para definir nuestras variables o módulos, con la única excepción de no utilizar aquellas que son propias de la sintáxis de VBA.

SINTAXIS_RESERVADA

Estructuras de decisión Select Case anidados

Me ha parecido interesante la explicación y el ejemplo de las estructuras anidadas de Select Case y la reproduzco aquí al objeto de que vd pueda ver la potencialidad de esta estructura con un ejemplo que, a pesar de la presencia de varias condiciones, resulta bastante clara y así además, aborda ejemplos más desarrollados.

Publicado por ExcelForo

El problema planteado es el siguiente:

…en una base de datos de personas, el objetivo es que Excel diga la clasificacion del porcentaje de grasa de una persona, el cual se obtiene por un numero que da una balanza.
El problema es que son muchas condicionales!
Si es mujer de 18-39 años: 0-21 bajo en grasa;22-33 normal;34-39 alto en grasa;más de 39 obesidad.
Si es mujer de 40-59 años:0-23 bajo en grasa;24-34 normal;35-40 alto en grasa; más de 40 obesidad.
Si es mujer de 60-69 años: 0-24 bajo en grasa;25-36 normal;37-42 alto en grasa; más de 42 obesidad.
Si es hombre de 18-39 años: 0-8 bajo en grasa;9-20 normal;21-25 alto en grasa;más de 25 obesidad
Si es hombre de 40-59 años: 0-11 bajo en grasa;12-22 normal;23-28 alto en grasa;màs de 28 obesidad
Si es hombre de 60-99 años: 0-13 bajo en grasa;13-25 saludable;26-30 alto en grasa; más de 30 obesidad.

Si bien podríamos optar por desarrollar la fórmula anidando repetidamente la función SI condicional, es cierto que sólo lo podríamos implementar con versiones superiores a Excel 2003, ya que en esta o anteriores nuesto máximo de anidaciones era de siete…

Recordemos que para crear una funcíon personalizada o UDF debemos acceder al Editor de VBA presionando Alt+F11, y ya dentro del Editor insertar un modulo (Menú Insertar > Modulo), haciendo doble clic en el módulo copiaremos el siguiente código:

‘creamos una función con tres variables – Sexo, Edad y % grasa
Public Function Grasa1(sexo, edad, Porcgra)
Select Case sexo
‘definimos categorías para mujeres
Case Is = “M”
Select Case edad
Case 18 To 39
Select Case Porcgra
Case 0 To 21: Grasa1 = “bajo en grasa”
Case 22 To 33: Grasa1 = “normal en grasa”
Case 34 To 39: Grasa1 = “alto en grasa”
Case Is > 39: Grasa1 = “obesidad”
End Select
Case 40 To 59
Select Case Porcgra
Case 0 To 23: Grasa1 = “bajo en grasa”
Case 24 To 34: Grasa1 = “normal en grasa”
Case 35 To 40: Grasa1 = “alto en grasa”
Case Is > 40: Grasa1 = “obesidad”
End Select
Case 60 To 99
Select Case Porcgra
Case 0 To 24: Grasa1 = “bajo en grasa”
Case 25 To 36: Grasa1 = “normal en grasa”
Case 37 To 42: Grasa1 = “alto en grasa”
Case Is > 42: Grasa1 = “obesidad”
End Select
End Select
‘lo mismo para hombres
Case Is = “H”
Select Case edad
Case 18 To 39
Select Case Porcgra
Case 0 To 8: Grasa1 = “bajo en grasa”
Case 9 To 20: Grasa1 = “normal en grasa”
Case 21 To 25: Grasa1 = “alto en grasa”
Case Is > 25: Grasa1 = “obesidad”
End Select
Case 40 To 59
Select Case Porcgra
Case 0 To 11: Grasa1 = “bajo en grasa”
Case 12 To 22: Grasa1 = “normal en grasa”
Case 23 To 28: Grasa1 = “alto en grasa”
Case Is > 28: Grasa1 = “obesidad”
End Select
Case 60 To 99
Select Case Porcgra
Case 0 To 13: Grasa1 = “bajo en grasa”
Case 14 To 25: Grasa1 = “normal en grasa”
Case 26 To 30: Grasa1 = “alto en grasa”
Case Is > 30: Grasa1 = “obesidad”
End Select
End Select
End Select
End Function

Ya podremos utilizar nuestra función Grasa1 en nuestra Hoja de cálculo, sobre tres celdas
=GRASA1(sexo; edad; %grasa)

Estructuras de decisión Select Case

Cuando tenemos que verificar muchas condiciones la estructura if…else puede ser un tanto pesada. Con Select…Case podemos conseguir una estructura más clara y legible. Su sintáxis es la siguiente:

Select [ Case ] testexpression [ Case expressionlist [ statements ] ] [ Case Else [ elsestatements ] ] End Select

Ejemplo:

Sub usuarios()
Dim usuario As String
usuario = InputBox(“Ingrese su nombre”)

Select Case usuario
Case “Hugo”
MsgBox “Bienvenido Hugo”
Case “Luis”
MsgBox “Bienvenido Luis”
Case Else
MsgBox “Lo siento no estás registrado”
End Select

End Sub

 

Estructuras de decisión if_then_else

Los pequeños programas que hemos escrito hasta ahora, se han limitado a ejecutar una serie de instrucciones de forma secuencial, una detrás de otra.

En este post vamos a abordar el estudio de las estructuras de decisión, las cuales evalúan si se cumplen o no una o varias condiciones antes de ejecutar la sentencia.Comenzamos con if__then__else:

Veamos su estructura:

 

Por ejemplo evaluaremos la edad del usuario para que pueda acceder a la aplicación:

 

El código en VBA sería:

Sub ejemplo7()
Dim edad As Byte
‘solicitamos la edad del usuario por teclado
edad = InputBox(“¿Qué edad tienes?”, “Edad”)

‘si es mayor de edad
If edad >= 18 Then
‘entonces bienvenido
MsgBox “Bienvenido”

‘en caso contrario
Else
‘mensaje y fin
MsgBox “Lo siento no puede acceder a la aplicación”
‘cerramos Excel
Application.Quit
End If

End Sub

 

OPERADORES

Tienen una finalidad básica en cualquier lenguaje de programación y más aún en VBA. Nos van a permitir realizar operaciones aritméticas, lógicas, de concatenación y de comparación. Haz clic para ver la presentación.

OPERADORES

Arrays dinámicos

Ya he hablado anteriormente de los arrays. La mayoría de los lenguajes de programación utilizan los arrays que, recordemos es un conjunto de elementos que tienen en común un nombre aunque entre ellos son diferentes. Por ejemplo el array dígitos estaría compuesto por las cifras 0,1,2,3,4,5,6,7,8 y 9. Cada uno de ellos irán referidos con un índice. Así supongamos el array compuesto por los meses del año:

  • Al tratarse de un número de elementos fijo estaríamos hablando de un array estático que, en este caso consta de 12 elementos.
  • Antes de empezar a programar con ellos necesitamos declararlos

Dim meses(11) as byte

  • Podrá observar que entre paréntesis he colocado 11 y no 12, ya que, por defecto los subíndices de los arrays de VBA empiezan en 0 (que es el límite inferior de la matriz). Si deseamos que comience en 1 en vez de en 0, incluiremos antes del primer array y antes del primer procedimiento las expresión:

Option Base 1

Ahora bien ¿que haríamos si no conociéramos de antemano la dimensión de nuestra array? Para ello podemos declarar un array dinámico dejando el paréntesis en blanco en la sentencia Dim:

Dim miArray( ) as Double

Una vez que conozcamos el número de elementos que necesitamos usaremos la sentencia Redim para señalar el número de subíndices necesarios. Por ejemplo:

Redim mi Array(102)

Veamos un ejemplo sencillo para entender los conceptos mencionados:

Vamos a declarar una matriz unidimensional de 3 elementos compuesto por los tres primeros dias de la semana, y haremos que no los presente por pantalla:

Option Explicit

Sub asignandoarray()
Dim miArray(3) As String
miArray(0) = “lunes”
miArray(1) = “martes”
miArray(2) = “miércoles”

MsgBox miArray(0) & ” ” & miArray(1) & ” ” & miArray(2)
End Sub

Ahora redimensionaremos el array para introducir el jueves y el viernes:

Sub redimarray()
‘Voy a redimensionar la array anterior para incluir el jueves y el viernes
ReDim miArray(5)
miArray(0) = “lunes”
miArray(1) = “martes”
miArray(2) = “miércoles”
miArray(3) = “jueves”
miArray(4) = “viernes”

MsgBox miArray(0) & ” ” & miArray(1) & ” ” & miArray(2) & ” ” & miArray(3) & ” ” & miArray(4)
End Sub

Si deseamos no tener que volver a escribir el mismo procedimiento podemos incluir datos con la expresión Redim preserve(el número siguiente en el índice)

Sub asignandoarray2()
ReDim miArray(3)
miArray(0) = “lunes”
miArray(1) = “martes”
miArray(2) = “miércoles”

ReDim Preserve miArray(3)
miArray(3) = “jueves”
MsgBox miArray(0) & ” ” & miArray(1) & ” ” & miArray(2) & ” ” & miArray(3)
End Sub

Módulos, propiedades y eventos

El código que escriba en un proyecto de Visual Basic aparecerá siempre en un módulo. Un módulo es un archivo de proyecto, pudiendo ser un módulo de formulario, de clase o de carácter general como vimos en el capítulo anterior. En cada módulo, el código se divide en dos secciones: declaraciones y procedimientos.

Los procedimientos son unidades de código como pequeños programas, escritos para realizar funciones determinadas, con un propósito bien definido. En cualquier módulo, el programador dispone de una sección especial llamada general en las que se sitúan las declaraciones y en la que se puede incluir otros procedimientos creados por el programador En el apartado declaraciones se puede introducir las constantes, variables y tipos de datos que necesite en su aplicación. (Todos estos conceptos serán tratados a lo largo del curso).

Los procedimientos pueden tener parámetros, especificados entre parétentesis que le permiten comunicar al procedimiento alguna información que necesite o que sea el propio procedimiento quien devuelva algún valor. En un módulo de formulario el código que se sitúa se refiere tanto a dicho formulario como al resto de objetos que estén dibujados en él. En este tipo de módulos cabe destacar los procedimientos de evento que se encargan de dar una respuesta programada a los eventos que ocurren en la aplicación.

Si has escrito código para algún procedimiento de evento, éste aparece en negrita en la lista de procedimiento de la ventana de código. El código de carácter general o que puede compartir en más de un proyecto, se sitúa en un módulo general. El código que aparece en este tipo de módulos no se relaciona con un objeto determinado, sino que tiene carácter general. En los módulos generales no se pueden incluir procedimientos de evento como en el caso de los módulos de formulario.

Es conveniente añadir comentarios a las líneas de código que escribas, de esta forma podrás entender los programas aunque haga tiempo que los escribiste. Para añadir un comentario en una línea se utiliza el carácter ‘ (apóstrofe).

Objetos en Visual Basic

Un objeto en Visual Basic se caracteriza por tres componentes: propiedades, métodos y eventos. Las propiedades son aquellas características o atributos que permiten establecer el htmlecto de un objeto como el color, tamaño, posición, etc… O el estado del mismo: activo, maximizado, etc…

Existen propiedades que sólo están disponibles en tiempo de diseño y otras que sólo están disponibles en tiempo de ejecución. Los métodos son pequeños programas que actúan sobre un determinado objeto y que establecen su comportamiento. Así un objeto puede moverse, ocultarse, etc… Puedes utilizar cualquier método que forme parte del objeto.

Los eventos son las situaciones que se producen y que nos interesan identificar para establecer algún tipo de de respuesta por parte del objeto. Así, puedes hacer click sobre un determinado objeto, creando de esta forma un evento reconocible por el objeto. Que suceda algo o no como respuesta a este evento dependerá de que hayas programado alguna acción en el correspondiente procedimiento de evento.

Establecer propiedades

Cuando insertas objetos en un formulario tienes que establecer algunas propiedades que presenta. Las propiedades son aquellas características propias del objeto que hacen que se distingan de otro objeto. En una aplicación Windows podemos distinguir diversos tipos de ventanas, aunque lo normal es identificar una ventana inicial que se puede maximizar o minimizar y uno o más cuadros de diálogo cuyo tamaño suele ser fijo. Sin embargo, debes darte cuenta que las barras de herramientas también son ventanas de una aplicación. . Normalmente la ventana inicial o de arranque será la que presente el menú principal, formado por menús desplegables en uno o más de un nivel. El resto de ventanas no deberían contener menús desplegables, aunque puede darse el caso.

Algunas propiedades de los formularios que se pueden establecer en tiempo de diseño son:

  • BorderSytle: Establece el estilo del borde del formulario.
  • Caption: Establece el texto que aparece en la barra de título del formulario.
  • ControlBox: Permite mostrar o no el menú de control de las ventanas de Windows.
  • Enabled: Establece si el formulario puede responder o no a los eventos que generes.
  • Font: Establece las características de los objetos de texto que se sitúen en el formulario.
  • Icon: Cambia el icono que representa el formulario.
  • Left, Top, Height, Width: Establecen la posición del formulario en la pantalla así como las dimensiones.
  • MaxButton y MinButton: Establece si se podrá maximizar o minimizar el formulario.
  • MousePointer: Modificar el puntero del ratón.
  • Visible: Establece si el formulario se mostrará visible en tiempo de ejecución.
  • WindowState: Establece el modo en que se carga inicialmente el formulario, normal, maximizado o minimizado.

Todas esta propiedas están disponibles en tiempo de diseño través de la ventana de propiedades. En tiempo de ejecución se puede cambiar o consultar el valor de algunas propiedades que sólo tienen sentido en el tiempo de desarrollo.

Convención para nombrar objetos en Visual Basic

OBJETO OBJETO ESPAÑOL PREFIJO
Form Formulario frm
CheckBox Casilla de verificación chk
ComboBox Cuadro combinado cbo
Data-bound combobox Cuadro combinado enlazado a datos dbc
Command Button Botón de comando cmd
Data Control de datos dat
Directory list box Cuadro lista de directorios dir
Drive List Box Cuadro lista de unidades drv
File List Box Cuadro lista de archivos fil
Frame Marco frm
Grid Rejilla grd
Data-bound grid Rejilla enlazada a datos dbg
Horizontal scrollbar Barra de desplazamiento horizontal hsb
Image Imagen img
Label Etiqueta lbl
Line Linea lin
OLE Container Contenedor OLE ole
PictureBox Cuadro de imagen pic
Shape Forma shp
Text Box Cuadro de texto txt
Timer Temporizador tmr
Vertical scroll bar Barra de desplazamiento vertical vsb

Utilizar Métodos

Una vez se ha establecido la parte estructural del formulario, se pasa a programar el comportamiento que debe seguir, de forma que cumpla con el objetivo para el que desees crearlo, utilizando los métodos.

Un método es un componente más de del objeto, que puede ser utilizado directamente. Para ello debes llamar al método e indicarle sobre qué objeto deseas aplicarlo, que deberá admitirlo.
La sintaxis en este caso debe ser: NombreObjeto.NombreMetodo[par1,...,parn], donde la lista de parámetros [par1...,parn] es opcional de cada método.

Un ejemplo es:

Si quieres mostrar un formulario llamado frmPrimero la sintaxis será:

frmPrimero.Show 1

El valor 1 nos indica que aparecerá de forma modal y si el valor es 0 aparecerá de forma no modal.

Entender los eventos

El conjunto de eventos está predefinido en Visual Basic por lo cual no puedes crear nuevos eventos para los objetos. El conjunto de evento reconocibles por un objeto está ligado con los procedimientos de evento. Estos procedimientos son unidades de código que le permiten especificar la respuesta que debe dar un objeto ante la ocurrencia de un evento que puede reconocer. La definición del procedimiento de evento tiene la siguiente forma:

Private Sub NombreObjeto_NombreEvento([Lista de parametros])
...instrucciones...
End Sub

Módulos,procedimientos, subrutinas y módulos de clases

Algo que normalmente me suelen  preguntar mis alumnos es la diferencia entre módulos y procedimientos en VBA. Un módulo se utiliza para recopilar bajo un nombre un conjunto de declaraciones, instrucciones y/o procedimientos. Aplicado al ejemplo de nuestra primera macro “Hola Mundo”, el módulo lo podíamos titular

Sub saludo() ‘ Sub de subrutina, el nombre de nuestro módulo
Dim saludo As String ‘ declaración de nuestra variable
saludo = MsgBox(“Hola Mundo”, vbCritical, “Mi saludo”) ‘instrucción
End Sub

Ahora  ¿qué es un módulo de clase? Es una plantilla con la que crear objetos.

Definirá todas las características que quisieramos que nuestro objeto final tuviera. Por ejemplo: podemos tener una clase llamada clsEmpleados en el cual deseamos tener todas las características con respecto a un empleado. Como propiedades podriamos definir: nombre, apellidos, domicilio, edad, casado, sueldo, fechaIngreso, Antiguedad, etc. Como métodos podriamos tener: ActualizarDatos o GuardarCambios (donde cambios a las propiedades anteriores se grabaran fisicamente en una base de datos), ValidarDatos (donde se valida los datos siempre y cuando haya habido cambios en las propiedades segun una variable privada Cambios)…

Un módulo de clase:

 

  • Es parecido a los tipos definidos por el usuario UDT.
  • Cada módulo define un tipo de objeto.
  • En ejecución creas una instancia (o objeto) de la clase.
  • No tiene interfaz de usuario.

Los módulos de clase no presentan una interfaz gráfica al desarrollados tal y como lo hacen las formas o formularios. En los formularios puedes colocar visualmente un botón, una lista, un texto, etc. Los módulos de clase solo contiene código que definen las caracteristicas del objeto.Si se desea presentar una ventana informativa (como un Acerca de) u otro formulario, este puede pertenecer a un proyecto ActiveX DLL y mandarlo llamar mediante un método (como MostraAcercaDe) que expone alguna clase dentro del proyecto.

  • Se pueden crear múltiples instancias en ejecución.

De acuerdo a la configuración de las propiedades de la clase se pueden crear varias instancias u objetos de una misma clase. Esto es, la propiedad Instancing de un módulo de clase indica como se trataran los objetos creados utilizando un módulo de clase determinado. Tenemos la opciónde que sea una clase Privada lo cual significa que no se puede instanciar directamente por otro programa, solo el proyecto ActiveX DLL o ActiveX Exe que lo contiene una copia u objeto de este tipo de clase.

  • Proporciona métodos y propiedades.

Eventos de un módulo de clase: Initialize y Terminate

Los módulos de clase presenta dos eventos:

  • Initialize: ocurre cuando se crea una instancia de una clase. Se usa, generalmente para inicializar cualquier dato usado por la instancia de una clase en el código.
  • Terminate: ocurre cuando toda referencia a una instancia de una clase son removidas de memoria al establecer todas las variables que la refieren a un objeto a Nothing o cuando la ultima referencia al el objeto se encuentra fuera de alcance. Usado generalmente para limpiar la memoria de objetos creados dentro de esta instancia y generar un error si hay alguna anomalía o guardar alguna información del objeto, etc.

Diferencias con los módulos estándar

  • Deben ser creadas explícitamente (crear instancia) antes de usarlas.

No se puede utilizar el metodo o la propiedad de una clase si no se tiene una instancia de la misma o una referencia al componente que la contiene si la clase esta configurada como Instancing = PublicNoCreatable.

  • Puedes crear múltiples instancias de la clase.

 

Veamos un ejemplo:

  1. Inicie VBA y vaya a Menú>Insertar>Módulo de clase.
  2. Dele un nombre por ejemplo ObAplicacion
  3. Inserte el siguiente código con el que crearemos un objeto Aplicacion:

Public WithEvents MiAplicacion as Application

Si pulsa ahora F2 y despliega la lista de objetos encontrará el objeto recién creado. A continuación crearemos dos procedimientos para nuestro objeto recién creado. El primero se encargará de insertar una nueva hoja en el libro:

Private Sub MiAplicacion_NuevaHoja(ByVal wb As Workbook, ByVal sh As Object)
Dim nombrehoja As String
nombrehoja = InputBox(“Introduzca un nombre para la hoja”)
ActiveSheet.Name = nombrehoja
ActiveSheet.Move After:=Sheets(Sheets.Count)

End Sub

Con este código crearemos un nuevo libro:

Private Sub MiAplicacion_NuevoLibro(ByVal wb As Workbook)
Dim numhojas, numactual, diferencia As Integer
‘por cada nuevo libro solicitamos al usuario el número de hojas
‘caso necesario se agregan o eliminan hojas
Do
    numhojas = Application.InputBox(“¿Cuántas hojas va a necesitar?”, Type:=1)
Loop While numhojas = False
numactual = Sheets.Count
diferencia = numactual – numhojas

‘eliminamos las hojas de sobra y desactivamos alertas
Do While diferencia > 0
Application.DisplayAlerts = False
Sheets.Item(diferencia).Select
ActiveWindow.SelectedSheets.Delete
diferencia = diferencia – 1
Loop
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Por último vamos a conectar el módulo de clase con el objeto Aplicación:

Option Explicit

Dim app As New ObjAplicacion
Sub Inicializa_MiAplicacion()
Set app.MiAplicacion = Application

End Sub


Arrays

Supongamos que precisamos referenciar los datos de una cartera de clientes. Tal vez precisemos almacenar información para cada uno de los clientes acerca de su nombre, apellidos, dirección, edad,etc. Esto implicaría la declaración de una variable por cada concepto y por cada cliente. Además de la perdida de tiempo, es por tal motivo que recurrimos a las Matrices o Arrays.

Un array es un conjunto de datos del mismo tipo ordenados en forman líneal uno después de otro. Los componentes de un array se han de referenciar por medio del nombre del array y un índice de desplazamiento para indicar el componente deseado.

Como ejemplo práctico vamos a realizar este sencillo ejercicio que va a consistir en obtener los productos cruzados de los primeros 10 números naturales. Para ello vamos a realizar una macro que nos escriba los 10 primeros números en la fila 1 y en la columna A para después efectúar el producto.

  1. Abra un libro nuevo.
  2. Presione Alt+F11 para ejecutar el Editor de Visual Basic.
  3. En el menú Insertar, haga clic en Módulo.
  4. Escriba el siguiente código en la hoja de módulo:

Sub tabla_multiplicar()
    Dim i, j As Byte

        Dim myarray As Variant
        myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
        Range(“a1:a10″).Value = Application.WorksheetFunction.Transpose(myarray)
        Range(“a1:j1″).Value = myarray
        Range(“a1:a10″).Font.ColorIndex = 5
        Range(“a1:j1″).Font.ColorIndex = 5
        For i = 1 To 10
            For j = 1 To 10
            Cells(i, j) = Cells(1, j) * Cells(i, 1)
            Next j
            Next i

        End Sub
Como se habrá podido observar después de  declarar dos variables byte, la i y la j, hemos declarado nuestra matriz con el nombre de  “myarray” y tipo Variant. En principio todos los elmentos de un array deben ser del mismo tipo, el hecho de declararlos de tipo Variante nos facilita mezclar elementos de diferente tipo e incluso incluir otras arrays. Seguidamente hemos definido los elementos de nuestra matriz con la siguiente declaración:

myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Con la siguiente línea invocamos una función de Excel la transposición de matrices. O sea intercambiar filas por columnas:

Range(“a1:a10″).Value = Application.WorksheetFunction.Transpose(myarray)

Con ello conseguimos escribir en forma de columna nuestra matriz fila. A continuación insertamos los valores de la misma matriz en la fila 1

Range(“a1:j1″).Value = myarray
Formateamos un poco dándole color azul a la fuentes de la 1 fila y columna y después insertamos un bucle para efectúar los productos cruzados. No se preocupe por ese fragmento de código lo entenderá cuando tratemos las estructuroas de decisión.