INSTRUCCIÓN PARA EJECUTAR UNA MACRO A UNA HORA DETERMINADA

Esta macro nos permitirá crear un libro de excel a una hora predeterminada , el libro llevara por nombre la fecha en formato añomesdia. Para esto usaremos el método OnTime , este método nos permite ejecutar cualquier macro a una hora especifica. Su sintaxis es la siguiente:

Application.OnTime Primera Hora , procedimiento , Ultima Hora , Programar.

Primera Hora: El momento en que se ejecutara la macro.

Procedimiento : Nombre de la macro.

Ultima Hora: La ultima hora en que se puede ejecutar la macro. Si por algún motivo no se puede ejecutar el procedimiento en la hora establecida(primera hora) , se ejecutar en la ultima hora. Si no se establece este parámetro excel esperara hasta que se pueda ejecutar.

Programa: Se estable en verdadero o falso , verdadero para programar otra macro y falso para borrar una macro previamente establecida.

Veamos el ejemplo:

Para empezar en un nuevo libro de excel vamos a la opción programador  y seleccionamos Visual Basic. 

Cuando estemos en el IDE de VBA seleccionamos la pestaña ThisWorKbook.

Escribimos el siguiente código :

Como podemos ver en el codigo anterior la macro que ejecutaremos a las “17:41:00” se llama CrearLibro.

En una entrada anterior explicamos como crear libros de excel usando macros .

A continuación el código de la macro CrearLibro:

De esta manera la proxima vez que se abra este libro de excel a las  “17:41:00” se creara un nuevo libro.

FUNCION EN EXCEL QUE RETORNA LOS NUMEROS DE UNA CADENA DE TEXTO

Excel tiene muchas funciones que podemos usar dependiendo de nuestra necesidad, sin embargo puede ser que  para casos específicos no encontremos la función que requerimos. Pensando en esto se nos ofrece la posibilidad de crear nuestras propias funciones. Vamos a crear una función en Excel que nos permita extraer los campos numéricos dentro de una cadena de texto, por ejemplo si tenemos la cadena de texto “Agosto2018” la función que crearemos nos retornara el valor 2018. Con este ejemplo también aprenderemos el uso de algunas funciones de cadena.

En las macros que hemos creado hasta ahora siempre usamos la palabra Sub para iniciar los procedimientos. Como  lo que estamos creando son funciones usaremos la palabra Function.

La sintaxis es: palabra Function nombre de la función (parámetros de la función). Para el ejemplo el nombre de la función será numbers y recibirá como parámetro un texto.



Definiremos tres variables: longitud de tipo entero para almacenar la longitud del texto que llega por parámetro, un contador  de tipo entero que usaremos para recorrer la cadena de texto recibida por parámetro  y una variable de tipo texto que nos servirá para almacenar los datos que retorna la función.

CALCULAR LA LONGITUD DE UNA CADENA

Lo primero que debemos hacer es calcular la longitud de la cadena, para esto usamos la función Len , que nos retorna el número de elementos que contiene una cadena de texto.

Hasta ahora nuestra función esta así:

RECORRER LA CADENA

Debemos recorrer la cadena y validar cada elemento para saber si es numérico. Para recorrer la cadena usaremos un ciclo for  desde 1 hasta la longitud de la cadena, la cual calculamos en el punto anterior.

Hasta ahora nuestra función está así:

SUBCADENA DE UN TEXTO

Debemos validar si cada elemento de la cadena es numérico, para esto extraemos posición por posición la subcadena  del texto recibido por parámetro, utilizando la función MID.

Con esta instrucción estamos indicando que necesitamos ver el valor que tiene la variable text a partir de la posición i una posición.

VALIDAR SIN UN VALOR ES NUMÉRICO

Para saber si el texto que hemos extraído con la palabra reservada MID es numérico usaremos la función IsNumeric .

La función IsNumeric retorna verdadero o falso, si el substring es numérico retorna verdadero de lo contrario falso.

Nuestra función estaría así:

CONCATENAR UNA CADENA

A medida que recorremos la cadena de texto y encontramos números debemos ir  uniéndolos en una variable.

Para concatenar usaremos (&)

Con esto estamos almacenando en la variable number lo que contiene la misma con el subtring de la cadena.

Finalmente nuestra función debe retornar la variable concatenada, esto lo conseguimos con la siguiente instrucción

Con esto terminamos y este sería el código de nuestra función

Ahora podemos utilizar en la hoja de cálculo la función numbers.

MACRO PARA LEER ARCHIVOS DE TEXTO

Como el titulo lo indica el código que estudiaremos a continuación nos permitirá leer un archivo con extensión .txt usando macros VBA, pero además de esto aprenderemos el uso de la función SPLIT.

La macro hace lo siguiente:

1. Recorre un archivo con extensión .txt separado por comas.
2.  Genera una cadena de texto concatenando la información leída del archivo.
3.  Utiliza la función Split para crear un vector.
4.  Recorre el vector e imprime cada posición en la hoja de cálculo.

He creado un archivo de texto con el nombre de cinco países y sus capitales separado por comas:

1.     Recorrer el archivo .txt separado por comas.

Definimos las siguientes variables:

La variable vector () es un arreglo de tipo string al cual no le asignamos un número fijo de posiciones porque no sabemos cuántos elementos tendrá. La variable WS de tipo Excel.Worksheet , esta variable identifica la hoja de cálculo. La  variable Cadena de tipo string nos servirá para almacenar los valores que leamos del documento .txt. La variable miarchivo de tipo string almacena la ubicación del documento .txt. La variable texto también de tipo string almacena los valores que se van leyendo del documento .txt. La variable f de tipo entero será un contador que usaremos en el programa.

Asignamos a la variable ws la hoja de cálculo de trabajo. Asignamos a la variable miarchivo la ubicación de la hoja de texto. Abrimos el documento .txt e iniciamos la variable contador f en 0. Utilizamos un ciclo while  para recorrer el archivo de texto, para leer cada línea usamos: Line Input #1 , variable, aumentamos en uno el contador hasta que lleguemos al final del archivo.

2.     Genera una cadena de texto concatenando la información leída del archivo.

Dentro del ciclo while se ejecuta la siguiente instrucción cadena = cadena + texto + ",”. Esta instrucción une el valor de la variable cadena con lo leído del archivo .txt. En VBA usamos el símbolo (+) para unir cadenas.

3.     Utiliza la función SPLIT para crear un vector.

La función SPLIT retorna un vector unidimensional formado por un número específico de subcadenas. Para nuestro ejemplo creamos un vector usando la  función SPLIT así: vector = Split(cadena, ",", Len(cadena) - 1), previamente definimos la variable vector que contendrá el resultado de la función SPLIT, esta función recibe como parámetro una expresión de tipo string , para nuestro caso la variable cadena, un delimitador que para el ejemplo es la “,”. Un límite es decir la longitud del primer parámetro – 1. Para conocer la longitud del primer parámetro usamos la función LEN().

4.     Recorre un vector e imprime cada posición en la hoja de cálculo.

Recorremos el vector creado en el punto tres e imprimimos cada posición del vector en la hoja de cálculo:

Asignamos el valor 1 a la variable contador i, entramos en un ciclo while hasta que el vector en la posición i este vacío. Imprimimos el valor en la hoja de cálculo y aumentamos 1 al contador.

Aquí está el código completo:

Si te fue util este material compartelo en tus redes sociales. Puedes encontrar mas ejemplos como este en mi ebook puedes descargarlo haciendo click aquí.

TIPOS DE DATOS DEFINIDOS POR EL USUARIO EN VBA

Un tipo de dato determina que operaciones y valores puede tener una variable, como sabemos en VBA tenemos los siguientes tipos de datos: integer , boolean , byte , doublé , decimal , single , String , currency , Date , object , variant.

VBA como la gran mayoría de lenguajes le permite al programador definir sus propios tipos de dato, para tal fin usaremos la instrucción TYPE   de tipo public , private  o como en el siguiente ejemplo sin tipo.  Como ejemplo definimos un tipo de dato que representa la información de los clientes en un programa VBA:

Como podemos ver el tipo de dato está conformado por variables diferentes (enteros, cadenas, fecha, etc.), podríamos incluir también una matriz e incluso otro tipo de dato definido por nosotros mismos. Cuando ya hemos creado nuestro tipo de datos podemos usarlo como se muestra en la siguiente rutina:

Definimos una variable de tipo Clientes a la que llamamos mycliente, podemos acceder a cada elemento del tipo de datos cliente lo  como se muestra en la rutina: variable. (Variable del tipo de dato).

Un tipo de dato definido por el programador servirá para que nuestros programas sean más legibles, lo cual facilitaría el mantenimiento del programa.

En el caso del ejemplo el tipo de dato que creamos nos servirá para asignarle valor a una instrucción sql:

Si necesitas automatizar tus procesos en Excel escríbeme a macrosymacros1@gmail.com.

Con este curso online aprenderás más sobre VBA.

El curso consta de 7 horas y 28 lecciones en vídeo. Aprenderás a ahorrar tiempo en excel , al automatizar tu trabajo mediante macros evitando pérdida de tiempo en tareas repetitivas. Aprenderás desde cero a programar en VBA.

Para mayor información sobre el curso de VBA sigue este enlace.

Si te gusto la macro descarga mi ebook  con más de veinte macros ejemplificadas y explicadas como esta.

CONECTAR EXCEL Y ACCES CON ADO

El protagonista de hoy es un mecanismo que sirve de interlocutor entre el lenguaje de programación (para nuestro caso VBA) y la base de datos, el nombre de este es ADO. En una entrada anterior vimos como conectar Excel y Acces con eluso del modelo de datos DAO. Aunque parezca un juego de palabras y suene parecido ADO (ActiveX Data Objects) y DAO (Data Access Objects) tienen sus diferencias.

Crearemos una macro que permite capturar la información de un cliente con el uso de un formulario e insertar esta información en una base de datos Acces.

Lo primero que haremos es crear nuestra base de datos en Acces y dentro de ella la tabla clientes con los siguientes campos:

Definimos un formulario por medio del cual capturaremos la información del cliente, este formulario cuenta con varios controles algunos de los cuales ya explicamos en una entrada anterior:

Este es el código para conectarse a la base de datos:

La variable cnn definida de tipo ADODB.Conection , nos servirá para conectarnos a la base de datos, la variable nomdatabase de tipo string contiene la ubicación de la base de datos , la instrucción thisworkbook.path nos retorna la ruta del libro de Excel abierto actualmente como la base de datos está en la misma ruta lo único que debemos hacer es agregar el nombre de la misma. Después de esto abrimos la base de datos.

Para manejar la información del formulario cree un tipo de datos definido por el usuario al cual llame clientes esto lo hice utilizando la palabra reservada type, en próximas entradas trataremos más a fondo su uso.

En la siguiente rutina empezamos a hacer uso del tipo de dato clientes:

Esta rutina recibe como parámetro el formulario creado anteriormente, definimos la variable mycliente de tipo clientes y asignamos a cada campo el valor que contiene el formulario.

Creamos una rutina en la cual generamos la instrucción SQL para insertar el registro en la base de datos.

Esta rutina recibe como parámetro una variable de tipo clientes (tipo de dato que definimos previamente) y crea una instrucción SQL insert.

Finalmente creamos una rutina que lo único que hace es ejecutar la sentencia SQL:

Esta rutina recibe como parámetro la instrucción SQL y la ejecuta con el comando Execute.

Si necesitas automatizar tus procesos en Excel escribe a macrosymacros1@gmail.com.

Con este curso online aprenderás más sobre VBA.

El curso consta de 7 horas y 28 lecciones en vídeo. Aprenderás a ahorrar tiempo en excel , al automatizar tu trabajo mediante macros evitando pérdida de tiempo en tareas repetitivas. Aprenderás desde cero a programar en VBA.

Para mayor información sobre el curso de Excel sigue este enlace.

Si te gusto la macro puedes descargar mi ebook  con más de veinte macros ejemplificadas y explicadas como esta.

MACRO PARA GENERAR NUMEROS ALEATORIOS EN VBA

De acuerdo a la definición lo aleatorio es algo que depende del azar, de la suerte o de un proceso casual. Procede del vocablo latino aleatorius.

Para generar números aleatorios en VBA usamos la función RND, la función recibe como parámetro un valor de tipo single. Dependiendo de este parámetro será el valor retornado por la función RND:

Si el parámetro es menor que cero retorna el mismo número, mayor que cero el siguiente numero aleatorio en la secuencia, igual a cero el número generado por última vez, si se omite el parámetro retorna el siguiente número aleatorio en la secuencia.

Para ejemplificar el uso de la función RND , creamos una macro que genera números aleatorios en un rango de valores.

Tenemos la siguiente hoja de cálculo:

En la celda B4 se ingresa el valor inferior del rango y en la celda C4 el valor superior. En la celda C4 ingresamos la cantidad de números aleatorios que queremos generar.

Definimos una rutina principal

 Definimos una variable de tipo worksheet que nos sirve para identificar la hoja de cálculo, la variable ffinal que nos indicara cual es la última fila con datos en la columna E de la hoja de cálculo.

En esta rutina se valida que exista un valor inicial y otro final, además que el valor inicial sea menor al final. Si esta condición se cumple se borran los datos que tenga la columna E y se invoca la rutina generadora de números aleatorios.

La rutina recibe como parámetro el rango dentro del cual se generaran los números aleatorios. Definimos una variable en la que almacenaremos el número aleatorio encontrado, una variable para contar las filas de la hoja de cálculo y la iniciamos en cuatro.

Creamos un ciclo que inicia en el número cuatro hasta el valor contenido en la celda D4 de la hoja de cálculo.

Inicializamos el generador de números aleatorios con la función Randomize. Para encontrar el número aleatorio escribimos la siguiente instrucción:

aleatorio = CInt(((numfin - numini + 1) * VBA.Rnd()) + numini)

Donde numfin es el número mayor y numini el número menor

Espero que esta macro te sea útil, si tienes dudas o comentarios escríbenos a macrosymacros@gmail.com.

USO FUNCION RGB EN VBA

RGB proviene del inglés, rojo verde y azul. La idea principal del modelo RGB es obtener otros colores usando como base estos tres, este es el modelo más utilizado en imágenes digitales.

La función RGB en VBA nos retorna un entero que representa un elemento del conjunto RGB.

La función recibe tres parámetros cada uno de los cuales representa  la intensidad de un color: Rojo, verde y azul.  Se deben pasar como parámetros valores entre 0 y 255.

RGB(“Rojo as integer” , “Verde as integer” , “Azul as integer”)

Por ejemplo el color rojo será: RGB(255,0,0).

Para ejemplificar el uso de la función creamos una rutina que la utiliza.

Esta rutina pinta las primeras tres filas de una hoja de cálculo con el color producto de la instrucción: color = VBA.RGB(230, 95, 0)

Como resultado de la ejecución de la macro veremos la hoja de cálculo así:

Puede descargar el documento con la macro siguiendo este enlace.

Espero que esta información te sea útil, si tienes dudas escríbenos a macrosymacros1@gmail.com.