viernes, 26 de septiembre de 2014

Separar en varias celdas un texto

Puede descargar el siguiente fichero de Excel con la macro y un ejemplo para practicar su uso.

En Excel se puede escribir un texto en una sola celda que ocupe varios párrafos. Cuando estamos escribiendo y queremos crear un nuevo párrafo dentro de la misma celda no tenemos que pulsar ENTER ya que en ese caso la celda se valida y no nos deja seguir escribiendo el segundo párrafo.

Lo que debemos hacer para pasar al siguiente párrafo es pulsar ALT+ENTER. Esto es, debemos pulsar la tecla ALT y simultáneamente la tecla ENTER, y así pasamos al segundo párrafo y podemos seguir escribiendo.

Supongamos que tenemos una única celda con dos o más párrafos y deseamos que cada párrafo ocupe una celda diferente. Esto se puede hacer de forma manual de la siguiente forma.

  1. Editamos la celda que contiene varios párrafos. Para editarla debemos estar situados en ella y pulsar la tecla de función F2. También se edita pulsando doble click sobre la propia celda.
  2. Seleccionamos todo el contenido de la celda
  3. Pulsamos Control+C para copiar al portapapeles
  4. Nos situamos en cualquier celda vacía y que debajo de ella también tenga algunas celdas vacías tantas. Vamos a necesitar tantas celdas vacías como párrafos contenga nuestro texto.
  5. Pulsamos Control+V que pega el contenido del portapapeles
Con este método hemos conseguido de forma manual separar los diferentes párrafos en y que cada uno de ellos ocupe una celda diferente.

Ahora queremos hacer esto de forma automática mediante una Macro de Excel, creando un código VBA (Visual Basic para Aplicaciones).

La macro es la siguiente.

Sub Separa()
Dim toma As String, linea As String
Dim largo As Integer, i As Integer, s As Byte
Dim caracter As String * 1
Dim fila As Long, columna As Integer
Dim A() As Integer
fila = ActiveCell.Row
columna = ActiveCell.Column
toma = ActiveCell.Value
largo = Len(toma)
ReDim A(largo)
For i = 1 To largo
    caracter = Mid(toma, i, 1)
    If caracter = Chr(10) Then
        s = s + 1
        A(s) = i
    End If
Next i
If s = 0 Then MsgBox ("En la celda activa no se detecta ningún ENTER"): End
A(s + 1) = largo + 1
For i = 0 To s
    linea = Mid(toma, A(i) + 1, A(i + 1) - A(i) - 1)
    Cells(fila + i + 1, columna) = linea
Next i
End Sub

Para que funcione correctamente debemos situarnos previamente en una celda que contenga dos o más párrafos de texto creados pulsando ALT+ENTER para separar cada uno de ellos.

La macro lo que hace es respetar la celda en la que estamos sin cambiarla y justo debajo de ella utiliza las celdas que existen para poner en cada una de ellas uno de los párrafos. Se necesitan tantas celdas vacías debajo de la celda que contiene el texto como párrafos existan.

Por ejemplo, si tenemos una única celda con el texto siguiente:

Linea1
Linea2
Linea3
Linea4

La macro detecta que existen 27 caracteres. Esto se puede medir con la función =LARGO
Los caracteres son:

  • 6 de la primera línea
  • 6 de la segunda líena
  • 6 de la tercera líena
  • 6 de la cuarta línea
  • 3 retornos de carro
Los retornos de carro son las veces que hemos pulsado ALT+ENTER y el código de carácter que llevan en CHR 10.





Al final la macro crea debajo de la celda que contiene ese texto (celda amarilla) cuatro celdas con el texto separado correspondiente a cada uno de los 4 párrafos.

Separar por un carácter


En la Hoja2 hemos creado otro ejemplo donde el usuario decide el carácter separador. Por ejemplo, podemos separar un texto por la coma.

En la celda D2, de color rosa, ponemos el carácter separador, por ejemplo una coma. En la celda D3 se calcula el código que corresponde a ese carácter. La coma tiene el código 44.

En la celda amarilla B5 escribimos el texto que contiene una o varias comas, o el carácter separador que nos interese. Con el cursor situado en B5 pulsamos el botón que lanza la macro y se producirá la separación del texto en varias celdas.


Observamos que Dato1 ha quedado bien, pero que Dato2 y los siguientes tienen un espacio en blanco al inicio. Si deseamos que este espacio inicial no aparezca lo que haremos es usar la otra macro que hemos habilitado que se lanza pulsando en el icono de la goma de borrar.


En esta ocasión no hemos lanzado las macros con los botones habituales, hemos optado por un control ActiveX para la primera y una imagen para la segunda macro.

La macro que se lanza con el botón rotulado como "Separa por carácter" es un botón de comando (control ActiveX). Para editarlo tenemos que ir a Programador y pulsar sobre el icono de escuadra para entrar en modo diseño y luego ir a las propiedades, donde podremos cambiar ligeramente el color de fondo 'BackColor'.

Estando en modo diseño pulsamos con el botón derecho del ratón sobre el botón y elegimos 'Ver código'. La macro es un evento de tipo click.


Private Sub CommandButton1_Click()
  Call SeparaPorCaracter
End Sub


No olvidar pulsar de nuevo sobre la escuadra para salir del modo diseño.

La segunda macro se lanza pulsando sobre el icono de la goma de borrar. Conseguir asociar una macro a una imagen es más sencillo ya que simplemente se ha de pulsar sobre el imagen con el botón derecho del ratón y elegir 'Asignar macro'.

sábado, 20 de septiembre de 2014

Arrays

El archivo utilizado en los vídeos se puede descargar del siguiente enlace.

Un Array es un conjunto de variables.
  • Si son de una dimensión hablamos de vectores
  • Si son de dos dimensiones hablamos de matrices
  • Si son de más de dos dimensiones hablamos de matrices multidimensionales
En general, denominaremos matriz a cualquier Array sea de la dimensión que sea.

Por ejemplo, definimos la matriz A que es un vector de 5 componentes. Se define así:

Dim A(5) as Integer

Con ello hemos definido sus 5 componentes de tipo Integer. Los componentes son:
  • A(1)
  • A(2)
  • A(3)
  • A(4)
  • A(5)
Esto es un vector de cinco componentes, donde cada uno de ellos puede contener un valor de tipo integer.

Una matriz de dos dimensiones, rectangular de 5 filas y 7 columnas se define así:

Dim A(5,7) as integer

En este ejemplo todos los componentes son de tipo integer y serían los siguientes:
  • A(1,1)  A(1,2)  A(1,3)  A(1,4)  A(1,5)  A(1,6)  A(1,7)
  • A(2,1)  A(2,2)  A(2,3)  A(2,4)  A(2,5)  A(2,6)  A(2,7)
  • A(3,1)  A(3,2)  A(3,3)  A(3,4)  A(3,5)  A(3,6)  A(3,7)
  • A(4,1)  A(4,2)  A(4,3)  A(4,4)  A(4,5)  A(4,6)  A(4,7)
  • A(5,1)  A(5,2)  A(5,3)  A(5,4)  A(5,5)  A(5,6)  A(5,7)

Primer Vídeo

  • ¿Qué es un array?
  • Arrays, Vectores, Matrices.


Segundo Vídeo

  • ReDim para redimensionar matrices.
  • Randomize para barajar la semilla del generador de números aleatorios.



Tercer Vídeo

Ordenar con macro.



Cuarto Vídeo

  • Ordenar aleatorios en VBA
  • Uso de matrices
  • Tiempo de cálculo



Quinto Vídeo

  • Usamos la función de VBA Array
  • Dejamos valores de un Array en fila y en columna
  • Capturamos esos valores con otro Array
  • Una macro llama a otra macro con parámetros



Caso práctico

Puede ver un ejemplo del uso de matrices y ReDim en un caso práctico que nos permite separar el texto de una celda que está escrito en varios párrafos y que finalmente quede cada párrafo en una celda separada. Está en el siguiente enlace:

Para saber más

viernes, 19 de septiembre de 2014

Variables Objeto

Los ficheros de Excel utilizados en los vídeos se pueden descargar de los siguiente enlaces.

El lenguaje de programación VBA (Visual Basic for Applications) en Excel es un lenguaje orientado a objetos. Los objetos son:
  • libros (Worksheet)
  • rangos (Range)
  • filas (Row)
  • columnas (Column)
  • tablas dinámicas (PivotTable)
  • gráficos (Chart)
  • entre otros muchos, ya que prácticamente todo es un objeto
  • incluso la propia aplicación Excel (Application)

Primer Vídeo

  • Definir variables objeto
  • Establecer su valor con SET
  • Bucle For Each ... Next


Segundo Vídeo

Creamos una hoja Index al inicio y después 100 hojas más que vamos renombrar de forma automática con una macro que se lanzará al elegir de un desplegable un mes de entre una lista con los doce meses del año.


jueves, 18 de septiembre de 2014

Trabajar con fechas y horas

Archivo de Excel utilizado: excelavanzado_fechas.xlsx

Excel permite trabajar bastante bien con fechas y horas. Veamos algunas funciones que permiten este tratamiento.

Primer Vídeo

Tratamiento de fechas.



Segundo Vídeo

Tratamiento de horas, minutos y segundos.



Tercer Vídeo

Determinación del día de la semana.


Cuarto Vídeo

Calendario perpetuo.


Para saber más

Si quiere ampliar otros aspectos del tratamiento de fechas y horas puede visitar estos enlaces.



martes, 16 de septiembre de 2014

Programación VBA 1

Introducción a la programación en Visual Basic para Aplicaciones (VBA). Variables, Bucles, Condicionales, funciones definidas por el usuario

  1. Mi primera macro
  2. Escribir en una hoja desde una macro
  3. Nuevas formas de escribir con macro
  4. Lanzar macros con iconos y botones
  5. Desplazamiento en una macro con Offset
  6. Macros con With ... End With
  7. Variables en VBA
  8. InputBox y MsgBox
  9. Bucles del tipo FOR...NEXT
  10. Lluvia
  11. Condicionales
  12. Estructuras repetitivas: Do While ... Loop
  13. Función Personalizada
  14. Programación por eventos: Fechador

Condicionales

Archivo de Excel utilizado: excelavanzado_condicionales.xlsm

El condicional IF nos permite redirigir al flujo del programa en función de si se cumple o no una condición.

If Condición Then
   sentencia 1
   sentencia 2
   ... ... ...
   sentencia n
End If
Cuando deseamos establecer unas sentencias para cuando no se cumple usamos ELSE.

If Condición Then
   sentencia 1
   sentencia 2
Else
   sentencia 3
   sentencia 4
End If

Primer Vídeo

  • Condicional IF con ELSE
  • Programación por eventos.
  • TARGET.Address
  • Evento Change

Segundo Vídeo

Operadores lógicos NOT, AND, OR.



Tercer Vídeo

Condicionales con ELSEIF.
Else If se escribe junto Elseif.




Cuarto Vídeo

Condicionales del tipo Select Case.


lunes, 15 de septiembre de 2014

Bucles del tipo FOR...NEXT

Archivo de Excel utilizado: excelavanzado_fornext.xlsm

La estructura repetitiva FOR...NEXT nos permite realizar las instrucciones interiores a ella tantas veces como se indique. Es ideal cuando se sabe el número de ciclos que hemos de realizar. Se utiliza una variable auxiliar que podemos denominar con el nombre que queramos, mientras no sea una palabra reservada, y que inicialmente llamaremos i por costumbre.

El siguiente es un ejemplo de bucle For Next que se repite 10 veces.

For i=1 to 10
   MsgBox i
Next i

En esta macro de ejemplo las líneas de código interiores al For Next se repetirán 10 veces. Para el primer ciclo la variable i tomará el valor 1, para el segundo ciclo tomará el valor 2, y así sucesivamente hasta llegar al último ciclo donde i toma el valor 10.

La línea de código que hemos puesto en este ejemplo es un MsgBox cuya acción es lanzar una ventana emergente que irá mostrándonos los números del 1 al 10 a medida que la variable i va tomando esos valores. Por tanto, tendremos que pulsar 10 veces sobre el botón Aceptar para ir pasando a las siguientes ventanas.

Primer Vídeo

Option Explicit
Sub Lista()
Dim i As Byte
For i = 1 To 10
    Cells(i, 1) = i
Next
End Sub



Segundo Vídeo

  • RND() para generar números aleatorios
  • INT poma la parte entera
  • Inicializar variables
  • Contador
  • Acumulador

Sub Lista2()
Dim i As Long
For i = 1 To 10000
    Cells(i, 2) = i
Next
End Sub
Sub Lista3()
Dim i As Long
For i = 1 To 10
    Cells(i + 4, 3) = i
Next
End Sub
Sub Lista4()
Dim i As Long, fila As Long
fila = 5 'Inicializamos la variable
For i = 1 To 10
    Cells(fila, "D") = i
    fila = fila + 1 'Contador
Next
End Sub
Sub Lista5()
Dim i As Byte, s As Integer, ale As Byte
s = 0 'Inicializamos la variable
For i = 1 To 10
    'generamos un nº aleatorio entre 1 y 100
    ale = Int(Rnd() * 100) + 1
    Cells(i + 4, "E") = ale
    s = s + ale 'Aculador
    Cells(i + 4, "F") = s
Next i
End Sub

Tercer Vídeo

  • Fecha y hora del sistema con NOW
  • NumberFormat para formatear una los valores de una celda
  • Application.ScreenUpdating = False
  • Application.ScreenUpdating = True

Sub Cronometro()
Dim i As Long
Worksheets("Hoja2").Activate
Range("C4") = Now
Application.ScreenUpdating = False
For i = 1 To 10000
    Cells(i, 2) = Int(Rnd() * 50000) + 1
Next i
Application.ScreenUpdating = True
Range("C5") = Now
Range("C6") = Range("C5") - Range("C4")
Range("C6").NumberFormat = "[hh]:mm:ss"
End Sub


Cuarto Vídeo

  • Bucles For...Next anidados
  • Llamar a una macro con CALL

Sub Multiplica()
Dim i As Byte, j As Byte
Worksheets("Hoja3").Activate
'Call Borra
Borra
For i = 1 To 10
    For j = 1 To 10
        Cells(i + 1, j + 1) = i * j
    Next j
Next i
End Sub
Sub Borra()
Range("B2:K11").ClearContents
End Sub


Quinto Vídeo

  • Ejecución de bucles For...Next con un paso distinto de 1 usando STEP
  • Paso negativo
  • Reducción de variables para aligerar el código




Sexto Vídeo

  • Acumular con macro. Tres métodos.
  • Uso de Offset (desplazamiento) para trabajar usando varias celdas de dos formas:
    • sin desplazar el cursor
    • desplazando el cursor

viernes, 12 de septiembre de 2014

InputBox y MsgBox en macros de Excel

El archivo de Excel utilizado en los ejemplos es el siguiente. También se incluye un enlace a un documento con la ayuda de InputBox y MsgBox.

InputBox y MsgBox son dos funciones que permiten interactuar con el usuario al ejecutar una macro en Excel.

Primer Vídeo

Veamos MsgBox.



Segundo Vídeo

Veamos InputBox.



Tercer Vídeo

Podemos utilizar MsgBox para que muestre un mensaje y aparezcan uno o varios botones como los siguientes.
  1. Aceptar  (botón único)
  2. Si, No
  3. Si, No, Cancelar
  4. Anular, Reintentar, Omitir
  5. Aceptar, Cancelar
  6. Reintentar, Cancelar

Iconos que podemos elegir que aparezcan junto al mensaje de texto.
  1. Crítico
  2. Interrogación
  3. Advertencia
  4. Información

miércoles, 10 de septiembre de 2014

Variables en VBA

Archivo de Excel utilizado: excelavanzado_variables.xlsm

En la programación de macros para Excel en VBA (Visual Basic para Aplicaciones) es muy recomendable el uso de variables para trabajar. Normalmente simplifican la compresión del código y en otros casos son imprescindibles.

Primer Vídeo

Veamos los tipos de variables. Disponemos de un documento compartido con las diferentes variables que podemos utilizar al programar macros. Es un documento de Google Drive que se puede ver en el siguiente enlace.



Segundo Vídeo

Veamos una macro que define las variables, asigna valores a esas variables y finalmente usa los valores de las variables para escribir en nuevas celdas. Se ejecuta la macro paso a paso pulsando F8. 
Aprenderemos a usar la "Ventana Locales" donde se muestra el valor de cada variable a medida que se ejecuta la macro paso a paso.



Tercer Vídeo

Definir o dimensionar las variables en Excel con DIM es optativo pero muy recomendable.

Con OPTION EXPLICIT conseguimos auto-obligarnos a definir previamente las variables utilizadas.

Para que automáticamente se añada OPTION EXPLICIT al inicio de cada nuevo Módulo podemos ir a Herramientas, Opciones y marcar la casilla correspondiente a "Requerir declaración de variables".



jueves, 4 de septiembre de 2014

Macros con With ... End With

Archivo de Excel utilizado: excelavanzado_with.xlsm

La estructura With ... End Whit permite resumir varias líneas de programa cuando existen partes repetitivas. Sirve para ejecutar una serie de acciones sobre un mismo objeto, sin tener que repetir toda su jerarquía.


Sub Saluda()
Worksheets("Hoja1").Activate
ActiveSheet.Range("B5").Value = "¿Cómo esta usted?"
ActiveSheet.Range("B5").Font.Bold = True
ActiveSheet.Range("B5").Font.Italic = True
ActiveSheet.Range("B5").Font.Color = RGB(255, 0, 0)
End Sub

Sub Saluda2()
Worksheets("Hoja1").Activate
' Estructura Whith...End With
With ActiveSheet.Range("B5")
    .Value = "¿Cómo esta usted?"
    .Font.Bold = True
    .Font.Italic = True
    .Font.Color = RGB(255, 0, 0)
End With
End Sub

La calculadora de colores se puede encontrar en el siguiente enlace.

Desplazamiento en una macro con Offset

Archivo de Excel utilizado: excelavanzado_offset.xlsm

La programación de macros en Excel se hace en VBA (Visual Basic for Application). Es un lenguaje de programación orientado a objetos. Ejemplos de objetos son los rangos, celdas, filas, columnas, gráficos, tablas dinámicas, entre otros muchos. Ya que prácticamente todo son objetos. Se encuentran jerarquizados siendo el superior Application que hace referencia a la propia aplicación Excel.

Offset significa desplazamiento y hemos de indicar cuantas filas hacia abajo y columnas a la derecha nos tenemos que desplazar. Si las filas son negativas indica que el desplazamiento es hacia arriba y si las columnas son negativas indica que nos desplazamos hacia la izquierda.

Offset(fila,columna)


miércoles, 3 de septiembre de 2014

Creación de Macros

En este módulo aprenderemos a crear macros con grabadora de forma sencilla sin necesidad de programar código. Las macros pueden ocupar siempre el mismo lugar dentro de la hoja de cálculo o bien ejecutarse desde la posición donde tengamos puesto el cursor. En este segunda caso se denominan macros de referencias relativas. También aprenderemos a depurar el código creado mediante la ejecución paso a paso pulsando F8. Veremos que la macro puede crear grabada en el libro en el que estamos trabajando o en el libro Personal. Veremos diferentes formas de lanzar la ejecución de las macros. Finalmente trataremos el tema de la seguridad en macros.

  1. Ficha Programador y ficha Desarrollador
  2. Macros con Grabadora
  3. Lanzar macros con iconos y botones
  4. Libro Personal
  5. Libro Personal en Excel
  6. Macros, iconos, seguridad y depuración paso a paso
  7. Grabación de un Formulario

Macros, iconos, seguridad y depuración paso a paso

Archivo de Excel utilizado: excelavanzado_macro_icono.xlsm

Dentro del tema de la programación de macros con grabadora y antes de introducirnos en la programación en VBA (Visual Basic for Applications) vamos a tratar tres temas de interés.
  • Lanzar macros con iconos
  • Seguridad en macros. Ubicaciones de confianza
  • Depuración de macros paso a paso pulsando F8

Libro Personal

También puede consultar esta otra entrada del blog que habla también sobre este tema.



El libro Personal en Excel es un libro muy especial (PERSONAL.XLSB) ya que todas las macros que se graben en él quedaran disponibles para cualquier libro que abramos.

Al grabar una macro con grabadora nos preguntan si deseamos que se grabe "En este Libro" o en el "Libro de macros Personal". Si se graba "en este libro" la macro únicamente estará disponible en ese libro, pero si se graba en el libro de macros Personal podremos invocar la macro desde cualquier libro.

Esto es posible ya que el libro de macros PERSONAL una vez creado se abre de forma oculta siempre que iniciemos la aplicación Excel. Podemos elegir que se abra no de forma oculta sino que se muestre y en ese caso también podemos aprovechar para convertirle en la platilla de inicio.


El libro PERSONAL.XLSB se graba en la siguiente ruta.
C:\Users\Adolfo\AppData\Roaming\Microsoft\Excel\XLSTART
El usuario en mi caso es Adolfo, siendo necesario que cada uno adapte la ruta a su propio usuario.
Esta es la ruta en Windows 8 y en Excel 2010. La ruta puede cambiar según la versión de Windows y la versión de Excel que se esté usando.

El fichero que se ha generado en mi ordenador es el siguiente.

Nota

Existe una entrada anterior que trata el tema del libro personal en Excel 2007.