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.

MACRO PARA EXPORTAR GRAFICOS A POWER POINT

La macro de hoy es muy sencilla pero muy útil, nos permite exportar gráficos de Excel a PowerPoint:

En el documento de Excel tengo generado dos gráficos, que exportaremos a power point con la macro.

     Hemos agregado una figura que utilizaremos como botón para ejecutar  la macro:

Creamos una referencia a Microsoft Power Point:

   Este es el código de la macro:

Definimos cuatro variables: WPPT que representa power point , WSLD que representa el slide de la presentación , WS define nuestra hoja de cálculo, GR los gráficos de la hoja de cálculo.

Creamos la presentación en power point con la instrucción WPPT.Presentations.Add, dentro de un ciclo recorremos todos los graficos de la hoja de cálculo y los agregamos a la presentación, antes de esto creamos un nuevo slide en la presentación. Con estas instrucciones agregamos el slide:

WPPT.ActivePresentation.Slides.Add _

            WPPT.ActivePresentation.Slides.Count + 1, ppLayoutBlank

        WPPT.ActiveWindow.View.GotoSlide _

            WPPT.ActivePresentation.Slides.Count

        Set WSLD = WPPT.ActivePresentation.Slides( _

            WPPT.ActivePresentation.Slides.Count)

Con esta instrucción copiamos la imagen de Excel a PowerPoint:

WS.ChartObjects(GR.Index).Chart.CopyPicture

        WSLD.Shapes.Paste.Select

Después centramos la imagen en la presentación:

WPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, msoTrue

        WPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, msoTrue

Si ejecutamos la macro tendremos el siguiente resultado:

Puedes descargar el documento con la macro siguiendo este enlace.

Espero que esta macro te sea de utilidad, si tienes dudas escríbenos a macrosymacros1@gmail.com.

MACRO PARA LLENAR UN COMBOBOX CON MAS DE DOS COLUMNAS

Generalmente un combobox se llena con un dato por columna  que el usuario puede seleccionar como vimos en una entrada anterior, pero en ocasiones se deben agregar más de una columna. Hoy veremos cómo hacer esto:

      En la hoja de cálculo he creado una tabla con la siguiente información: Nombre,   Apellido y país de cuatro personas. El objetivo de la macro será llenar un       combobox con esta información.

Cuando el usuario haga clic en la imagen  mostraremos un formulario con el combobox.

1    Creamos el formulario y agregamos un combobox , además del combobox   

     agregamos un botón que nos servirá para cerrar el formulario.

Dentro de las propiedades del combobox está la propiedad columncount que nos sirve para indicar cuantas columnas tendrá el control, esta propiedad puede establecerse en tiempo de ejecución o simplemente asignándole un valor como lo  hacemos en este ejemplo:

1 En el método UserForm_Activate del formulario realizamos el llamado a una rutina  

   llena el combobox, a esta rutina le pasamos como parámetro el mismo 

   formulario, lo hacemos así para que cada vez que se ejecute el formulario se  

   llene el combobox.

1 Para cerrar el formulario agregamos un botón con el siguiente código:

1 Esta es la rutina con la que llenamos el combobox

a.     Recibe como parámetro el formulario.

b.     Definimos una variable que representa la hoja de cálculo  de tipo Excel.Worksheet.

c.     Definimos la variable f de tipo entero que nos sirve para recorrer fila a fila la hoja de cálculo.

d.     Definimos la variable ffinal que nos permitirá saber cuál es  la última fila con datos en la hoja de cálculo.

e.     Asignamos a la variable WS  la hoja de calculo

f.        Calculamos la fila final y guardamos el valor en ffinal.

g.     Recorremos la hoja de cálculo desde la fila tres hasta la última fila con datos y a medida que se ejecuta este ciclo se llena el combobox.

1   Definimos una rutina que ejecuta el formulario.

Finalmente el resultado que obtenemos al ejecutar la macro es el siguiente:

Espero que te sea de utilidad, si tienes dudas déjanos tus comentarios o escríbenos a macrosymacros@gmail.com.

Puedes descargar el documento con la macro siguiendo este enlace.

MACRO PARA COMBINAR COLUMNAS EN EXCEL

De acuerdo con la definición matemática una combinación es un arreglo donde el orden no tiene importancia, podrías por ejemplo saber todas las combinaciones del baloto, lo cual no implica que lo llegaras a ganar. Siguiendo con los conceptos matemáticos la notación para las combinaciones  es  C(n,r) cuantas veces se pueden combinar n elementos r a la vez.

Pensando en el título de hoy escribí una macro que encuentra todas las combinaciones posibles  entre tres columnas con diferentes valores. Así funciona la macro:

1.    En la hoja de cálculo tenemos una tabla con la información que combinaremos

1    2.   Hemos agregado dos figuras que funcionan como botones

Cuando el usuario haga click en el botón combinar, se ejecutara la macro que describo a continuación:

  a.    Definimos cuatro variables

Ws de tipo Excel.worksheet representa la hoja de cálculo principal, wsformat  también de tipo Excel.worksheet representa la hoja de cálculo con los formatos que daremos a cada celda,  combinación de tipo string nos servirá para ir almacenando de forma temporal cada posible combinación , cuenta de tipo entero nos servirá para contar el número de combinaciones posibles.

a    b. Creamos la rutina principal

A partir de esta rutina invocaremos otras que realizaran la combinación entre columnas, definimos tres variables de tipo entero: lastrow , f  y rowreplace. Asignamos a la variable WS definida en el punto anterior  la hoja de cálculo principal y a la variable wsformat la hoja de cálculo con los formatos. Invocamos la rutina columnaA que combina la columna A de la hoja de cálculo con las columnas B y C, se aumenta en uno el contador de filas  e invocamos la rutina columnaC que combina la columna C con las columnas A y B ,  nuevamente se aumenta el contador de filas en uno y se invoca la rutina columnaB que combina la columna B con las columnas A y C.

1   3. Explicare el funcionamiento de la rutina columnaA , las rutinas columnaC y columnaB obedecen al mismo algoritmo, con diferencias muy leves.

La rutina recibe los siguientes parámetros: lastrow de tipo entero donde almacenaremos el número de la última fila con datos en la columna a, rowreplace de tipo entero será un contador de la celda de las columnas B y C (según corresponda) que estamos combinando con la columna A, ws de tipo Excel.worksheet que representa la hoja de cálculo principal y  wsformat que representa la hoja de cálculo de formatos.

Definimos la variable f que identifica la fila en la que estamos ubicados, la inicializamos en 6 porque desde esta fila empezaremos a recorrer la hoja de cálculo principal. Buscamos cual es la última fila con datos en la hoja de cálculo y guardamos este resultado en la variable lastrow.

Iniciamos un ciclo desde 6 hasta el valor que contenga la variable lastrow y dentro de este ciclo combinamos todas las filas de la columna A, almacenando el resultado en la variable combinación.

Terminado este ciclo copiamos los formatos de la hoja wsformat y los pegamos en la hoja principal, después asignamos el valor de la variable combinación.

Iniciamos otro ciclo desde el valor que contiene la variable rowreplace hasta la última fila con datos – 1, en este ciclo invocaremos una rutina que combina las columnas A y B. Como resultado obtendremos las combinaciones posibles de la columna A y B.

Hacemos lo mismo para las columnas A y C.

La siguiente rutina combina las columnas A y B.

Recibe como parámetros las variables de tipo entero lastrow , rowreplace y f.

Desde 6 hasta la última fila con datos recorre la columna A combinándola con el valor de la columna B en la fila rowreplace.

Si hacemos click en el botón combinar obtendremos como resultado, todas las combinaciones posibles

Cuando se hace click en el botón limpiar se ejecuta una rutina que borra la información de las combinaciones en la hoja de cálculo.

Si quieres recibir la hoja de cálculo con la macro, escríbeme a macrosymacros1@gmail.com o puedes descargarla siguiendo este enlace.

1

MACRO PARA AGREGAR HOJAS A UN LIBRO DE EXCEL

Por lo general agregar una hoja a un libro de Excel resulta bastante sencillo, basta con hacer clic en el botón insertar hoja de calculo. Pero si tuvieras que agregar muchas hojas a un libro con un determinado nombre para cada hoja, entonces te seria muy útil automatizar este proceso.

Hoy quiero mostrarles como podemos hacer esto con una macro sencilla pero muy útil. Cada vez que ejecutemos esta macro se insertara en el libro de de Excel que la contenga una hoja de calculo que llevara como nombre la fecha del sistema.

La macro funciona de la siguiente manera:

1.   Captura la fecha del sistema.

2.   Valida la fecha del sistema, contra el campo fecha de una hoja(X) del libro de Excel.

3.    Si la fecha del sistema es mayor al valor que tiene la hoja de calculo(X) en el campo fecha, se inserta una nueva hoja de calculo que llevara como nombre la fecha del sistema y actualizara el campo fecha de la hoja(X).

Hemos creado un libro de Excel llamado macro25 y en el una hoja de cálculo con el nombre parámetros, esta hoja de calculo tiene un campo que se llama fecha:

La posición A2 de la hoja de cálculo debe estar vacía la primera vez que se ejecute la macro.

Ahora escribimos el código de nuestra macro, definimos las siguientes variables globales: 

La variable ws nos servirá para identificar la hoja de cálculo activa, la variable fecha nos servirá para almacenar la fecha del sistema.

Crearemos una rutina para capturar la fecha del sistema y compararla contra el campo fecha (posición A2) de la hoja parámetros:

Analicemos la rutina fecha del sistema:

Primera línea: se asigna a la variable ws la hoja parámetros, a partir de ahora la hoja  parámetros se identifica como ws.

Segunda línea: Encuentra la fecha del sistema año, mes y día. Los convierte a string usando la función CSTR y los concatena, para asignarlos a la varia fecha.

Tercera línea: Valida si la variable fecha es mayor al campo fecha de la hoja parámetros o si el campo fecha de la hoja parámetros esta vacio.

Cuarta Línea: Si se cumple la condición de la tercera línea se invoca la rutina Crear hoja, la que insertara la hoja de calculo en el libro de Excel.

Quinta Línea: Asigna al campo fecha de la hoja parámetros la fecha del sistema, esto si se cumple la condición de la tercera línea.

La segunda rutina inserta la hoja de cálculo en el libro, veamos su código:

En esta rutina se definen las siguientes variables locales:

Ws_nueva que nos permitirá identificar la hoja de cálculo activa, nombre que almacenara el nombre de la hoja de cálculo activa.

En la primera línea: Después de definir las variables se agrega una hoja de calculo al libro de Excel.

En la segunda línea: se le asigna a la variable ws_nueva la hoja de cálculo activa.

En la tercera línea: Se le asigna a la variable nombre el nombre de la hoja de calculo insertada.

En la cuarta línea: Se agregan comillas al principio y al final del nombre de la hoja de calculo que se acaba de insertar, es necesario insertar la comillas para poder ejecutar la instrucción de la sexta línea.

En la quinta línea: se asigna a la variable nombre fecha la fecha del sistema.

En la sexta línea: Se cambia el nombre la hoja insertada y se asigna como nombre la fecha del sistema.

Para que se ejecute la macro cada vez que abrimos el libro agregamos las siguientes instrucciones:

Ahora hacemos clic en guardar, cerramos nuestro libro de Excel.

Hoy es 11 de septiembre de 2016.

Si abro nuevamente el libro de Excel vemos lo siguiente:

Se inserto una hoja de cálculo con el nombre 20160911, el campo fecha de la hoja de cálculo queda con la fecha del sistema:

Si actualizo la fecha del sistema con el día 12 de septiembre:

Abrimos nuevamente e libro de Excel y vemos que se creo una nueva hoja de cálculo con el nombre 20160912:

El campo fecha de la hoja parámetros se actualiza:

Descarga el archivo con la macro desde este enlace.

Espero que esta macro te sea util , si necesitas automatizar tus procesos en excel escribeme a macrosymacros1@gmail.com.

En este enlace encontraras una colección de macros como esta , completamente explicadas.