La importancia de tener un nombre

!!! Hola !!!

El dia de hoy hablaremos sobre como asignarle un nombre a una celda o en general a un rango.

De hecho todas las celdas tienen ya un nombre:
La celda ubicada en la columna G y la fila 8, se llama "G8", el rango de celdas cuyos vertices son A1 y B5, se llama "A1:B5", sin embargo, estos nombres proporcionados por excel, aunque permiten ubicar facilmente el rango dentro de la hoja de trabajo, no son descriptivos respecto a su contenido.

Me explico mejor con la siguiente imagen donde he estractado y simplificado el resumen de un presupuesto:

En un ejemplo tan basico como este, es claro que para calcular el valor total del presupuesto (en la celda F18), debe escribir la formula: =F6+F11-F16.
Si las celdas F6, F11 y F16 se llamasen respectivamente "directos", "otros" y "descuentos", la formula anterior podria escribirse: = directos + otros - descuentos

Esto proporciona una nueva perspectiva al manejo de formulas, una cuya utilidad no se alcanza a apreciar en este ejemplo, por lo simple del mismo. Imagina una formula mucho mas compleja, donde intervienen muchas otras variables y muchos otros operadores matematicos.


Y como se le puede asignar un nombre a una celda (o a un rango de celdas)?
Facil, solo tienes que usar el control de nombres, el control que se señala en la siguiente imagen.
Simplemente selecciona la celda (o rango de celdas) a renombrar y utiliza el control para asignar un nuevo nombre.

La interfaz de Excel incluye tambien un cuadro de dialogo que permite gestionar (editar, crear, eliminar) los nombres de un rango, el "Administrador de nombres" - buscalo en la ficha "Formulas" de la cinta de opciones:

Se muestra a continuacion el calculo del total del presupuesto, luego de haber creado los nombres de las celdas. Se aprecia en la barra de formulas la implementacion de lo explicado en este articulo

Y como se manipulan los nombres de los rangos usando VBA?

El cuadro de dialogo "nombres de rango" y el "control de nombres" que acabo de mostrar, tienen su representacion en la coleccion Names del modelo de objetos de Excel:

Agregar un nuevo nombre de rango al libro de trabajo activo:
ActiveWorkbook.Names.Add nombre, rango
Ejemplo: ActiveWorkbook.Names.Add "directos", Range("F6")

Eliminar un nombre de un rango del libro activo:
ActiveWorkbook.Names(nombre).Delete
Ejemplo: ActiveWorkbook.Names("directos").Delete

Existen muchos otros metodos y propidades de la coleccion Names, pero estos dos son los de mayor uso.


Glosario

Este articulo ha servido como ejemplo para:
1. Mostrar la utilidad de crear nombres descriptivos a los rangos.

2. Aprender a crear y manipular nombres de rangos desde la interfaz de Excel mediante el control "nombres" y el cuadro de dialogo "nombres de rango"

3. Conocer y manipular otro de los objetos del modelo de objetos de Excel: La coleccion Names - que almacena y permite gestionar los nombres de rangos creados por el usuario, ya sea desde la interfaz del programa o mediante el uso de codigo VBA.


Eso es todo amigos.

He invertido mucho tiempo en esto

!!!! Hola !!!

Hoy queria enseñar como mostrar un reloj en la barra de estado de Excel.

Dije, queria, pero decidi no hacerlo, pues que diferencia habria entre mirar la hora del dia en la barra de estado de Excel o verla en la barra de tareas de Windows?

No tiene mucho sentido, sin embargo, no deseche la idea por completo:

Tal vez queramos saber el tiempo que llevamos trabajando en Excel con un archivo:

Ese es el tema de hoy:
Colocar un pequeño cronometro en la barra de estado que se active al momento de abrir el archivo. Esto nos informara el tiempo que llevamos trabajando en el (claro, si no te has dedicado a otras cosas despues de abrirlo).

Manos a la obra.

Paso No 1. - Codigo a escribir en el modulo de clase ThisWorkbook
Option Explicit
Public inicio As Date

Private Sub Workbook_Open()
  inicio = Now()
  cronometro
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Application.OnTime EarliestTime:=Now() + TimeValue("00:01:00"), procedure:="cronometro", Schedule:=False
Application.StatusBar=False

End Sub
Esto hara que al momento de abrir el libro de trabajo, se cree la variable inicio con la hora actual en invocara al cronometro.
Al final, al momento de cerrar el archivo eliminara el cronometro de la barra de estado

Paso No. 2 - Crear el procedimiento Cronometro dentro de un modulo del proyecto
Public Sub cronometro()
  Dim tiempo As Date
  Dim strTiempo As String
  
  tiempo = Now() - ThisWorkbook.inicio
  
  strTiempo = Format(tiempo, "hh:mm")
  Application.StatusBar = strTiempo
  Application.OnTime Earliesttime:=Now() + TimeValue("00:01:00"), procedure:="cronometro"
End Sub
Esto calculara cada minuto la diferencia entre el inicio y la hora actual y mostrara esta diferencia en la barra de estado.

Notas
1. Seria interesante si pudieramos detener el cronometro cuando abrimos otro archivo y reiniciarlo cuando volvemos a el (No es complicado, es cuestion de programar los eventos Activate y deactivate del libro de trabajo).
2. Seria todavia mas interesante si el cronometro pudiera acumular el tiempo de uso del archivo en todas las sesiones (un estimado del tiempo total invertido en el archivo) - Tampoco es complicado, solo basta almacenar el tiempo acumulado al cerrar el archivo y al momento de abrirlo nuevamente iniciar el cronometro con ese valor.
3. Si noto interes en este tema, podria ampliar el codigo con los 2 puntos indicados.


Glosario
Esta entrada ha servido como ejemplo para:
1. Conocer otro de los objetos de Excel: la barra de estado (StatusBar).

2. Conocer dos procedimientos de evento de Excel: Open y BeforeClose (Estos procedimientos se ejecutan automaticamente respectivamente al momento de abrir y cerrar un archivo).

3. Utilizar el metodo OnTime de Excel para ejecutar un procedimiento cada cierto tiempo.

Eso es todo amigos

Ensamblando las piezas

!!! Hola !!!

Una de las dificultades que se presentan en el proceso de aprendisaje de las caracteristicas de Excel es que se aprenden multitud de caracteristicas del programa (ya sea del manejo de la interfaz y del modelo de objetos a traves de VBA), pero no se tiene la capacidad de implementarlas en forma conjunta dentro de un proyecto.

Son como pildoras informaticas sueltas:
Como lograr que Excel "pronuncie" una frase (ver la entrada: "Que fue lo que dijo")

Como obtener numeros aleatorios (ver la entrada: "Los dados de Excel")

Como obtener el dia de la semana a la que corresponde una fecha (ver la entrada "Que dia fue ese dia")

y otras mas que encuentres en este blog u otros.

Esto es asi por que los articulos de los blogs deben ser cortos, si se quiere que sean atractivos.

Por eso me decidi a crear un proyecto que permita aplicar los conocimientos adquiridos en estas pildoras informaticas:

Vamos a crear una pequeña aplicacion que ayude a estudiar ingles - No estoy todavia muy seguro de que caracteristicas se implementaran, pero sera algo como esto:
Excel dira una frase al azar en ingles y tu deberas escribirla correctamente
(para eso crearemos una base de datos con frases)
Excel mostrara una o varias images de objetos al azar y el usuario debera escribir el nombre (o los nombres) en ingles.
Y otras mas que se me vayan ocurriendo (Acepto sugerencias)

Esto no se hace de la noche a la mañana, toma tiempo, primero hay que conseguir las imagenes jpg, crear la base de datos de frases, etc.

Por ahora les recomiendo que repasen estas entradas (revisen continuamente este articulo, por que voy a ir ampliando este listado).

Que fue lo que dijo?
Para que se familiaricen con la capacidad que tiene Excel de pronunciar frases en ingles.

Los dados de Excel
Para poder entender el proceso de generar numeros aleatorios que permitan seleccionar frases o imagenes al azar

Eso es todo, amigos

Que fue lo que dijo?

!!! Hola !!!

Hoy les voy a mostrar una caracteristica de Microsoft Excel poco conocida, al menos para aquellos que no dominan la lengua de Shakespeare.

Excel puede repetir una frase. Vamos a mostrar varios ejemplos:

Paso No 1.

Abre el Ide de VBA y ubicate en la ventana inmediato

Paso No 2.

Escribe lo siguiente: Application.Speech.Speak "Hello" (termina con la tecla ENTER)

Eso es todo, deberias estar escuchando la frase.

vamos un poco mas alla

Hagamos que Excel lea cada una de las frases que escribes en las hojas de trabajo:

Escribe en la ventana Inmediato:

Application.Speech.SpeakCellOnEnter = True

Listo, a partir de ahora, cada frase que escribas en una celda de la hoja de trabajo sera leida por Excel al momento de usar la tecla ENTER para terminarla.

Como es logico, para deshacer esta caracteristica debes escribir en la ventana inmediato:

Application.Speech.SpeakCellOnEnter = False

Para los que esten interesados y deseen ir un poco mas alla, les dire que lo que acabamos de hacer es manipular el objeto Speech de la aplicacion.

Excel esta compuesto por un sin numero de objetos organizados jerarquicamente, los cuales interactuan entre ellos. El usuario puede manipularlos utilizando el lenguaje de programacion VBA.

Eso es todo amigos

Los dados de Excel

!!! Hola !!!


En esta entrada voy a mostrar como es que se generan numeros aleatorios en Excel.

Si no sabes lo que es un numero aleatorio, te dire que son numeros al azar.

Como cuando tu le dices a alguien. "Dime un numero cualquiera entre 1 y 100"


Y para que sirven los numeros aleatorios?
Tal vez estes creando un juego en Excel donde tienes que adivinar un numero generado por el programa.

Quizas desees recrear el juego de bingo, para lo cual es necesario que se muestren numeros al azar.

Es posible que tengas un cuestionario de 100 preguntas de un examen y trates de automatizar el proceso para que a cada estudiante le asignen diez de esas preguntas al azar.

Y si solo quieres aprender un poco mas acerca de las capacidades de Microsoft Excel?, ese seria un buen motivo para leer e implementar los ejemplos que se muestran mas adelante.


Generando numeros aleatorios usando la interfaz de Excel

Microsoft Excel cuenta con dos funciones que permiten generar numeros aleatorios:

ALEATORIO (RAND) - Devuelve un numero aleatorio entre 0 y 1.
Ejemplo - En la celda A1 escribe la siguiente formula
=ALEATORIO()
Te devolvera un numero (no se cual, nadie lo sabe - por eso es aleatorio) entre 0 y 1


ALEATORIO.ENTRE (RANDBETWEEN) - Devuelve un numero entero aleatorio comprendido entre los argumentos
Ejemplo - En la celda A1 escribe la siguiente formula
=ALEATORIO.ENTRE(1, 100)
Te devolvera un numero aleatorio entre 1 y 100

Generando un numero aleatorio comprendido entre 2 rangos no consecutivos.
Excel no incluye funciones que devuelvan valores aleatorios comprendidos entre 2 rangos no consecutivos, sin embargo, con un poco de ingenio esto puede hacerse:

Un numero aleatorio entre [1, 200] o entre [800, 1000]
=SI(ALEATORIO()>0.5, ALEATORIO.ENTRE(1,200), ALEATORIO.ENTRE(800,1000))

Generando numeros aleatorios usando codigo VBA
Los programadores de VBA tienen acceso a las mismas funciones de la hoja de trabajo ya explicadas. desde el objeto Application.WorksheetFunction, usando los nombres en ingles de estas funciones - ver mi entrada Nombre de las funciones de Excel en Ingles dentro de este mismo blog

Application.WorksheetFunction.RAND()
Application.WorksheetFunction.RANDBETWEEN(1,100)

VBA, incluye, ademas la funcion rnd que devuelve un numero aleatorio entre 0 y 1:
rnd().

Tambien podemos emular la funcion ALEATORIA.ENTRE para obtener un numero aleatorio en el rango [a,b): (b-a)*rnd()+a

Eso es todo amigos

En letras

!!! Hola amigos !!!

El dia de hoy quiero compartir con todos una funcion que permite que podamos escribir un numero usando palabras.

algo asi como:

donde extracte una seccion simplificada de lo que puede ser un presupuesto de construccion, que es el campo donde me desempeño mejor.

Cualquier usuario de Excel, por nuevo que sea, entiende que si modifico la cantidad o el costo unitario de cualquiera de las actividades, la actualizacion de los subtotales y el costo final es automatica.
Pero lo es tambien el costo total en letras?

Si observas detalladamente el grafico, podras observar en la barra de formulas que la celda activa es en realidad una funcion y no un valor escrito a mano.
Una funcion definida por el usuario que ahora comparto con todos los seguidores del blog.

Solo tienes que agregarla a uno de tus modulos y listo:

Public Function EnLetras(Valor) As String
  Dim Centavos As Integer
  Dim Fraccion As String
  Dim Moneda As String
  
  If Not IsNumeric(Valor) Then
    EnLetras = "ERROR"
    Exit Function
  End If
  Moneda = IIf(Int(Abs(Valor)) = 1, " peso", " pesos")
  If Right(Letras(Abs(Int(Valor))), 6) = "illon " Or Right(Letras(Abs(Int(Valor))), 8) = "illones " Then Moneda = "de" & Moneda
  Centavos = Application.Round(Abs(Valor) - Int(Abs(Valor)), 2) * 100
  Fraccion = IIf(Centavos = 1, " centavo", " centavos")
  Fraccion = IIf(Centavos = 0, "", " con " & Letras(Centavos) & Fraccion)
  EnLetras = Letras(Int(Abs(Valor))) & Moneda & Fraccion
  EnLetras = UCase(Left(EnLetras, 1)) & Mid(EnLetras, 2)
  If Valor < 0 Then EnLetras = "menos " & EnLetras
End Function

Private Function Letras(Valor) As String
' Funcion Auxiliar de uso exclusivo de la funcion EnLetras
  Select Case Int(Valor)
    Case 0
      Letras = "cero"
    Case 1
      Letras = "un"
    Case 2
      Letras = "dos"
    Case 3
      Letras = "tres"
    Case 4
      Letras = "cuatro"
    Case 5
      Letras = "cinco"
    Case 6
      Letras = "seis"
    Case 7
      Letras = "siete"
    Case 8
      Letras = "ocho"
    Case 9
      Letras = "nueve"
    Case 10
      Letras = "diez"
    Case 11
      Letras = "once"
    Case 12
      Letras = "doce"
    Case 13
      Letras = "trece"
    Case 14
      Letras = "catorce"
    Case 15
      Letras = "quince"
    Case Is < 20
      Letras = "diez y " & Letras(Valor - 10)
    Case 20
      Letras = "veinte"
    Case Is < 30
      Letras = "veinti" & Letras(Valor - 20)
    Case 30
      Letras = "treinta"
    Case 40
      Letras = "cuarenta"
    Case 50
      Letras = "cincuenta"
    Case 60
      Letras = "sesenta"
    Case 70
      Letras = "setenta"
    Case 80
      Letras = "ochenta"
    Case 90
      Letras = "noventa"
    Case Is < 100
      Letras = Letras(Int(Valor \ 10) * 10) & " y " & Letras(Valor Mod 10)
    Case 100
      Letras = "cien"
    Case Is < 200
      Letras = "ciento " & Letras(Valor - 100)
    Case 200, 300, 400, 600, 800
      Letras = Letras(Int(Valor \ 100)) & "cientos"
    Case 500
      Letras = "quinientos"
    Case 700
      Letras = "setecientos"
    Case 900
      Letras = "novecientos"
    Case Is < 1000
      Letras = Letras(Int(Valor \ 100) * 100) & " " & Letras(Valor Mod 100)
    Case 1000
      Letras = "mil"
    Case Is < 2000
      Letras = "mil " & Letras(Valor Mod 1000)
    Case Is < 1000000
      Letras = Letras(Int(Valor \ 1000)) & " mil"
      If Valor Mod 1000 Then Letras = Letras & " " & Letras(Valor Mod 1000)
    Case 1000000
      Letras = "un millon"
    Case Is < 2000000
      Letras = "un millon " & Letras(Valor Mod 1000000)
    Case Is < 1000000000000#
      Letras = Letras(Int(Valor / 1000000)) & " millones "
      If (Valor - Int(Valor / 1000000) * 1000000) Then
        Letras = Letras & Letras(Valor - Int(Valor / 1000000) * 1000000)
      End If
    Case 1000000000000#
      Letras = "un billon"
    Case Is < 2000000000000#
      Letras = "un billon " & Letras(Valor - Int(Valor / 1000000000000#) * 1000000000000#)
    Case Else
      Letras = Letras(Int(Valor / 1000000000000#)) & " billones"
      If (Valor - Int(Valor / 1000000000000#) * 1000000000000#) Then
        Letras = Letras & " " & Letras(Valor - Int(Valor / 1000000000000#) * 1000000000000#)
      End If
  End Select
End Function

Nota. La funcion no es de mi autoria, tampoco se de quien es. Estuvo dando vuelta en los foros hace un tiempo.

Eso es todo amigos.

Abrir archivos de excel al compas de una cancion

!!! Hola !!!

Te gustaria que al abrir un archivo de Excel, sonara tu cancion favorita?

podrias incluso asignar a cada archivo de Excel una cancion diferente, tal vez quieras que al abrir un archivo de Excel se escuche una instruccion pregrabada respecto al uso y/o utilidad del archivo.

Voy a enseñarte como hacerlo, es facil.
Como es logico, necesitas tener un archivo mp3 en tu computadora (no creo que sea dificil).

Paso No 1.
Insertar el  control ActiveX llamado "Windows media Player" en una de las hojas del libro de trabajo en cuestion.

No te preocupes si en vez de la ficha "Programador" tu pantalla muestra "Desarrollador" son nombres diferentes para la misma ficha en diferentes versiones de Excel.
Como? no aparece la ficha programador en tu cinta de opciones? En este caso deberas agregarla. Puedes esperar unos dias a que haga una entrada en el blog explicando como se hace esto o simplemente googlea "Mostrar la ficha programador en Excel", es un procedimiento muy sencillo.

Como se observa en la imagen, no aparece el control ActiveX llamado "Windows media Player", seguramente tambien te pasa lo mismo (lo que sucede es que es un control que no se usa mucho, por eso no esta en la lista. Debes hacer click en la ultima opcion "Mas controles".
Busca en la lista el control "Windows media Player" - es de los ultimos, por que estan en orden alfabetico. Boton aceptar y listo
No es que el control vaya a aparecer en el listado de controles. El puntero del mouse mostrara una pequeña cruz que te permitira hacer la insercion del control con un simple click en la hoja de trabajo:

Hay esta el control. Realmente no importa mucho el tamaño del control, ni el sitio de la hoja donde lo colocaste - pues vamos a hacerlo invisible. (pero mas adelante)

Paso No 2.
El codigo VBA que permitira activar el control al momento de abrir el archivo

Como vez, es un codigo muy corto y sencillo. Lo importante es que se ubique en el procedimiento de evento Open del libro de trabajo, para que sea leido al momento de abrir el archivo.

Paso No 3. (Si quieres)
Vuelve invisible el control para que no estorbe tu trabajo con el archivo (solamente podras manipularlo usando codigo VBA ). Esto puedes hacerlo desde la ficha Programador:
3.1 selecciona el modo diseño si no lo esta.
3.2 selecciona el control Windows media player de la hoja de trabajo y usa el boton propiedades.
3.3 En el cuadro de dialogo propiedades cambia el valor de la propiedad Visible de True a False
Los tres pasos puedes ejecutarlos mas facilmente escribiendo en la ventana inmediato:
Worksheets("Hoja1").WindowsMediaPlayer1 .Visible=False

Paso 4 (y ultimo)
Graba el archivo con formato xlsm (habilitado para macros). Si lo dejas tipo xlsx no va a funcionar.


Eso es todo amigos

Nombres de las funciones de Excel (En Ingles)

!!! Hola amigos !!!

Todos los que usamos Excel con cierta frecuencia, sabemos que se distribuye en muchos idiomas.

Aunque esto del idioma es solamente una mascara. Me explico:

Cuando sumas un rango de datos escribes dentro de una celda (a veces con a ayuda de la biblioteca de funciones de la cinta de herramientas) "=SUMA(....)"

Esto es lo que escribes (y lo que observas en la barra de formulas), pero internamente Excel procesa la funcion "=SUM(....)". - Si no eres observador, fijate que le falta la "A" al final

De igual manera, todas las demas funciones se muestran y se usan con el nombre asignado en el idioma de la instalacion del programa, pero internamente Excel las procesa con su nombre en Ingles,

Todos estos procesos son automaticos, incluso si creas un archivo en un computador que tiene instalado Excel en otro idioma, cuando lo abras en tu PC, el archivo mostrara los nombres de funciones en tu idioma.

Bueno y que?
que importancia tiene esto?
de que me sirve saber que la funcion SUMA, se llama SUM, que LARGO es en realidad LEN?

De nada, si no estas interesado en aprender a programar en Excel.

Cuando programas en Excel, tienes que usar el nombre de las funciones con su nombre real - y digo real, por que como mencione al principio, los demas idiomas son una mascara.

Ahora si entremos de lleno en el tema:
Como saber el nombre real de una funcion de nuestra hoja de calculo

1.  Utiliza la funcion dentro de una hoja de calculo - En tu idioma (como lo haces siempre)
por ejemplo escribe en la celda B1 la formula: =PROMEDIO(A1:A5)
Espero que tengas datos en ese rango de celdas, de lo contrario la funcion devolvera un error.

2. Selecciona la celda B1 nuevamente (que sea la celda activa)

3. Carga el Ide de VBA (si no sabes lo que es esto, deberias leer mi entrada anterior)

4. En la ventana inmediato del ide, escribe lo siguiente: (Si no ves esta ventana deberias usar la combinacion de teclas Ctrl - G) : ? ActiveCell.Formula (no te olvides de dar enter al final).

Eso es todo. Si lo hiciste bien, la ventana inmediato mostrara =AVERAGE(A1:A5)

AVERAGE es el nombre real de la funcion PROMEDIO de Excel.

Eso es todo amigos

El cuarto de maquinas de Excel

Si usas Excel en forma ocacional o eres nuevo en el manejo del programa, lo mas seguro es que no conozcas lo que yo llamo El cuarto de maquinas de Excel:

Este es el cuarto de maquinas de Excel, mejor conocido como "Ide de VBA"

La imagen no dice mucho,  y si no sabes que es un Ide y tampoco sabes que es VBA, te va a decir menos todavia.
Aunque no parezca, estamos entrando en terrenos de la programacion:

Ide es una abreviacion de "Entorno de desarrollo Integrado" - En ingles, claro
VBA es un lenguaje de programacion

Es decir dentro del Ide de VBA pueden desarrollarse programas que permitan automatizar, mejorar, ampliar, simplificar (y cualquier otro ar que se te ocurra) el trabajo que realizas con Excel.

Si desperte tu curiosidad, sigue mi blog, que a partir de hoy voy a mostrarte como es que es todo este asunto.

Quieres acceder al Ide de VBA? Pulsa la combinacion de teclas ALT-F11