Crear un Hipervínculo en nuestro Libro arrastrando y soltando.

Conocemos diferentes formas de generar hipervínculos en nuestras hojas de Excel (con macros, con la herramienta Insertar hipervínculo, con la función HIPERVINCULO...).
Hoy veremos una forma poco conocida: arrastrando y soltando(Drag and Drop)

Supongamos nuestros datos en la Hoja1 de nuestro Libro de trabajo:

El proceso es muy simple, y en pocos pasos tendremos nuestro hipervícunlo creado.
Lo más importante, sin esto no funcionará el método, es que el libro esté guardado.
1-selecciona la celda hacia donde queremos dirigir el futuro hipervícnulo.
2-Sitúate en el borde de dicha celda hasta que aparezca el cursor en su modo Mover (cruz de cuatro flechas)
3-Haz clic derecho con el ratón, y sin soltar arrastra la celda a otro lugar...
TRUCO!!: si quieres llevar el hipervícunlo a otra hoja, presiona la tecla Alt para acceder a la hoja destino.
4-Soltamos donde queramos dejar el hipervínculo.
(si hemos cambiado de hoja, presionando Alt, primero soltamos esta tecla).
5-Al soltar el botón derecho del ratón que manteníamos pulsado se abrirá un menú contextual, donde seleccionaremos la opción:
Crear hipervínculo aquí

6-Listo, ya tenemos nuestro hipervícunlo creado.

Vemos en el siguiente video el proceso completo:

Otra posibilidad, sin emplear la tecla Alt, es sencillamente crear el hipervínculo en la celda de al aldo, en la misma hoja, para luego Cortar y Pegar en el destino deseado...

Seguir leyendo...

Cálculo del PayBack o del Periodo de Recuperación en Excel.

Aprenderemos hoy a obtener el PayBack o Periodo de recuperación de una inversión empleando nuestras hoja de cálculo.

Comenzaremos diciendo que desde luego no es el mejor indicador para medir la rentabilidad de una inversión, existen el VAN o la TIR como métodos más válidos, pero es sin duda un método sencillo y ágil, de fácil interpretación... y como mayor inconveniente, el PayBack no considera el paso del tiempo (y el efecto sobre los flujos de caja).
Lee un poco más en la Wikipedia.

Para explicar cómo realizar este cálculo partiremos del siguiente ejemplo:
Tenemos un desembolso inicial de 20.000 euros y unos flujos de caja (diferencia entre Cobros y Pagos) para los siguientes cinco años de:
5.000,00 € (-) 9.500,00 € (-) 5.000,00 € (-) 5.000,00 € (-) 10.000,00 €

En el rango B9:G9 calculamos el Flujo Acumulado para cada año (como la suma de los diferentes flujos acumulados hasta la fecha).

Por último vemos la fórmula necesaria para el cálculo:
𝑃ay𝐵ack =[Ú𝑙𝑡𝑖𝑚𝑜 𝑝𝑒𝑟𝑖𝑜𝑑𝑜 𝑐𝑜𝑛 𝐹𝑙𝑢𝑗𝑜𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜]+([𝑉𝑎𝑙𝑜𝑟 𝑎𝑏𝑠𝑜𝑙𝑢𝑡𝑜 𝑑𝑒𝑙 ú𝑙𝑡𝑖𝑚𝑜 𝐹𝑙𝑢𝑗𝑜 𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜]/[𝑉𝑎𝑙𝑜𝑟 𝑑𝑒𝑙 𝐹𝑙𝑢𝑗𝑜 𝑑𝑒 𝐶𝑎𝑗𝑎 𝑑𝑒𝑙 𝑠𝑖𝑔𝑢𝑖𝑒𝑛𝑡𝑒 𝑝𝑒𝑟𝑖𝑜𝑑𝑜])

haz clic en la imagen

Para el cálculo del 'Ú𝑙𝑡𝑖𝑚𝑜 𝑝𝑒𝑟𝑖𝑜𝑑𝑜 𝑐𝑜𝑛 𝐹𝑙𝑢𝑗𝑜𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜' empleamos la función CONTAR.SI. En la celda D14:
=CONTAR.SI(C9:G9;"<0")
También, más fiable:
=COINCIDIR(VERDADERO;INDICE($C$9:$G$9>0;1;0);0)-1
ejecutada matricialmente!!! (presionando Ctrl+Mayusc+Enter).

Lo que nos indica es el último año con Flujo Acumulado negativo, es decir, el anterior a la Recuperación.
Es más fiable, y recomendable, la segunda opción (empleando COINCIDIR...), ya que localizamos el primer ejercicio en que se genera un Flujo positivo.. mientras que la opción de CONTAR.SI únicamente sería valida cuando no hay fluctuaciones posteriores, y volviéramos a un Flujo Acumulado Negativo posterior.

El siguiente parámetro de nuestra fórmula '𝑉𝑎𝑙𝑜𝑟 𝑎𝑏𝑠𝑜𝑙𝑢𝑡𝑜 𝑑𝑒𝑙 ú𝑙𝑡𝑖𝑚𝑜 𝐹𝑙𝑢𝑗𝑜 𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜' lo calculamos en D15 con la fórmula INDICE siguiente:
=ABS(INDICE(C9:G9;1;D14))
que nos devuelve en VALOR ABSOLUTO (función ABS) el flujo Acumulado del año anterior a generarse la Recuperación, o dicho de otro modo, del último Flujo Acumulado Negativo.

El último parámetro '𝑉𝑎𝑙𝑜𝑟 𝑑𝑒𝑙 𝐹𝑙𝑢𝑗𝑜 𝑑𝑒 𝐶𝑎𝑗𝑎 𝑑𝑒𝑙 𝑠𝑖𝑔𝑢𝑖𝑒𝑛𝑡𝑒 𝑝𝑒𝑟𝑖𝑜𝑑𝑜' montamos la fórmula en D16:
=INDICE(C7:G7;1;D14+1)

para recuperar el flujo anual (NO los acumulados!!) correspondiente al año de la Recuperación de la Inversión.

Estamos listos para el cálculo final. Para nuestro ejemplo:
PayBack= 3 + (500 / 5000) = 3,10 años
El Plazo de recuperación de la inversión, según los flujos de tesorería definidos, es de 3,10 años.

Seguir leyendo...

VBA: La Paleta de Colores en VBA para Excel (.Color vs .ColorIndex).

Frecuentemente me preguntan en mis formaciones por la paleta de colores, en especial cuando hablamos de formatos...
Veremos hoy un sencillo ejemplo de cómo obtener las equivalencias entre las propiedades (.Color y .ColorIndex) y la función RGB para trabajar con colores en Excel.

En el siguiente código listamos los 57 colores permitidos por la propiedad .ColorIndex (de 0 a 56), entre los 16.777.216 de colores posibles de .Color, y cómo recuperarlos con la función RGB (Red, Green, Blue - valores entre 0 y 255-) estandarizada...
Igualmente veremos cuál es la forma de expresarlo en formato HTML.

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Sub PaletaColores()
'Paleta básica de 57 colores (0 to 56)
Dim c As Long
Dim strIni As String, strHEX As String

'Añadimos cabecera...
With Range("A1:H1")
    .Value = Array("Color:", "Interior", "Font", "HTML (Hexadecimal)", "Rojo (R)", "Verde (G)", "Azul (B)", "ColorIndex")
    .Font.Bold = True
End With

'recorremos los 57 colores...
For c = 0 To 56
    'Indicamos Número de Color....
    Cells(c + 2, 1).Value = "Color " & c & ":"
    
    'damos color al Relleno de la celda, empleando la propiedad ColoIndex
    Cells(c + 2, 2).Interior.ColorIndex = c
    
    'damos color a la Fuente de la celda
    'escribimos el texto [Color...]
    With Cells(c + 2, 3)
        .Font.ColorIndex = c
        .Value = "[Color " & c & "]"
    End With
    
    'a partir del valor numérico recuperado de la propiedad Color de la celda
    'conseguimos un identificador de seis caracteres....
    strIni = Right("000000" & Hex(Cells(c + 2, 2).Interior.Color), 6)
    Rojo = Right(strIni, 2)     'dos caracteres por la derecha para el Rojo
    Verde = Mid(strIni, 3, 2)   'dos caracteres enmedio para el Verde
    Azul = Left(strIni, 2)      'dos caracteres por la izquierda para el Azul
    
    'componemos el código Hexadecimal
    strHEX = Rojo & Verde & Azul
    'Añadimos la almohadilla necesara para obtener el código de color en HTML/Hexadecimal
    Cells(c + 2, 4).Value = "#" & strHEX
    
    'Obtenemos en número decimal el valor del color
    'empleamos la función HEX.A.DEC
    'y descomponemos el valroes decimales para conseguir el código de color en formato RGB
    Cells(c + 2, 5).Formula = Application.WorksheetFunction.Hex2Dec(Rojo)
    Cells(c + 2, 6).Formula = Application.WorksheetFunction.Hex2Dec(Verde)
    Cells(c + 2, 7).Formula = Application.WorksheetFunction.Hex2Dec(Azul)
    
    'Acabamos añadiendo el código de color para ColorIndex
    Cells(c + 2, 8).Value = Cells(c + 2, 2).Interior.Color
Next c

'autoajustamos ancho de columnas
Range("A1:H1").EntireColumn.AutoFit

End Sub

El resultado lo vemos en la siguiente imagen:

Seguir leyendo...

VBA: El método PopUp en programación de Excel.

Hoy veremos un método de VBA Excel que nos permite controlar el tiempo de exposición de un MsgBox, veremos el método .PopUp.
Este método tiene la siguiente sintaxis:
objeto.PopUp: .Popup(Texto,[Tiempo en segundos],[Titulo MsgBox],[Tipo])

Siendo relevantes las posibles constantes a emplear en el cuarto parámetro: Tipo.
Desde este controlamos el aspecto del cuadro diálogo. Veamos cuales son las constantes a nuestra disposición.
Nota: Son similares a las que podemos emplear directamente en un MsgBox, pero con alguna adicional.

En este primer cuadro describimos el número y tipo de botones a mostrar en el cuadro de diálogo:

Miembro	Valor	Valor Hexadecimal	Descripción
OKOnly	0	0x0	Muestra sólo el botón Aceptar.
OKCancel	1	0x1	Muestra los botones Aceptar y Cancelar.
AbortRetryIgnore	2	0x2	Muestra los botones Anular, Reintentar y Omitir.
YesNoCancel	3	0x3	Muestra los botones Sí, No y Cancelar.
YesNo	4	0x4	Muestra los botones Sí y No.
RetryCancel	5	0x5	Muestra los botones Reintentar y Cancelar.
	6	0x6	Muestra los botones Reintentar, Cancelar y Continuar.

Para describir el estilo del icono.

Miembro	Valor	Valor Hexadecimal	Descripción
Critical	16	0x10	Muestra el icono Mensaje crítico.
Question	32	0x20	Muestra el icono Consulta de advertencia.
Exclamation	48	0x30	Muestra el icono Mensaje de advertencia.
Information	64	0x40	Muestra el icono Mensaje de información.

El tercer grupo determina qué botón es el valor predeterminado.

Miembro	Valor	Valor Hexadecimal	Descripción
DefaultButton1	0	0x000	El primer botón es el predeterminado.
DefaultButton2	256	0x100	El segundo botón es el predeterminado.
DefaultButton3	512	0x200	El tercer botón es el predeterminado.
DefaultButton4	768	0x300	El cuarto botón es el predeterminado.

El cuarto grupo determina la modalidad del cuadro de mensaje.

Miembro	Valor	Valor Hexadecimal	Descripción
ApplicationModal	0	0x0000	Aplicación modal: el usuario debe responder al cuadro de mensaje antes de continuar trabajando en la aplicación actual.
SystemModal	4096	0x1000	Sistema modal: se suspenden todas las aplicaciones hasta que el usuario responda al cuadro de mensaje.

Y el quinto grupo especifica si el cuadro de mensaje es la ventana de primer plano, junto con la alineación y la dirección del texto.

Miembro	Valor	Valor Hexadecimal	Descripción
VbMsgBoxHelpButton	16384	0x4000	Agrega el botón Ayuda al cuadro de mensaje.
MsgBoxSetForeground	65536	0x10000	Especifica la ventana del cuadro de mensaje como ventana de primer plano.
MsgBoxRight	524288	0x80000	Texto alineado a la derecha.
MsgBoxRtlReading	1048576	0x100000	Especifica que el texto debe aparecer para ser leído de derecha a izquierda en los sistemas árabe y hebreo.

Recordemos que a la hora de sumar números para crear el valor final del argumento Tipo (cuarto argumento del método .PopUp) , se debe utilizar únicamente un número de cada grupo!!!.

Para mostrar un ejemplo práctico, insertamos el siguiente código dentro de la ventana de código de un módulo estándar desde el editor de VB:

Option Explicit
Const Tempus = 2 'Segundos
  
Sub MsgBox_con_Temporizador()
Dim objShell As Object
Dim respuesta1 As Integer, respuesta2 As Integer, respuesta3 As Integer

Set objShell = CreateObject("WScript.Shell")
'Sintáxis Método PopUp:  .Popup(Texto,[Tiempo en segundos],[Titulo MsgBox],[Tipo Botón])
respuesta1 = objShell.Popup("Mensaje 1 Retrasado en 2 Segundos.", Tempus, "excelforo.com", 6 + vbQuestion)
respuesta2 = objShell.Popup("Mensaje 2 Retrasado en 2 Segundos.", Tempus, "excelforo.com", vbYesNo + vbExclamation)
respuesta3 = objShell.Popup("Mensaje 3 Retrasado en 2 Segundos.", Tempus, "excelforo.com", vbRetryCancel + vbInformation)

Set objShell = Nothing
  
End Sub

Para poder trabajar con las posibles respuestas tendremos presente el siguiente cuadro de valores de respuesta del método:

Miembro	Valor decimal	Descripción
	-1	Valor cuando el usuario no hace clic en ningún botón después de transcurridos los N segundos.
vbOK	1	Aceptar
vbCancel	2	Cancelar
vbAbort	3	Anular
vbRetry	4	Reintentar
vbIgnore	5	Ignorar
vbYes	6	Sí
vbNo	7	No
	11	Continuar

En el ejemplo observamos tres ejemplos del método .PopUp, aplicando un retardo de dos segundos entre cada MsgBox, y mostrando diferentes combinaciones de botones, iconos o estilos...
Notemos que la cuestión temporal en VBA es siempre muy aproximada y que dependiendo de los procesos abiertos en nuestro equipo, el tiempo estipulado será más o menos preciso (normalmente 'menos').

Seguir leyendo...

VBA: Declarar el tipo de dato de las variables con signos.

Todos conocemos que al crear nuestros procedimientos es recomendable (en algunos casos obligatorio) definir nuestras variables y asignarle su Tipo de dato...
A modo de recapitulación comenzaremos repasando brevemente cuáles son los tipos de datos estándar en VBA (más habituales!):
Tipo Dato: Boolean
Memoria: En función de la plataforma de implementación
Valores: True o False

Tipo Dato: Date
Memoria: 8 bytes
Valores: 0:00:00 (medianoche) del 1 de enero de 0001 a 11:59:59 p.m. del 31 de diciembre de 9999.

Tipo Dato: Integer
Memoria: 4 bytes
Valores: -2.147.483.648 a 2.147.483.647 (con signo)

Tipo Dato: Long (entero largo)
Memoria: 8 bytes
Valores: -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 (9,2...E+18 †) (con signo)

Tipo Dato: String (longitud variable)
Memoria: En función de la plataforma de implementación
Valores: 0 a 2.000 millones de caracteres Unicode aprox.

Tipo Dato: Double (punto flotante de precisión doble)
Memoria: 8 bytes
Valores: -1,79769313486231570E+308 a -4,94065645841246544E-324 † para los valores negativos; 4,94065645841246544E-324 a 1,79769313486231570E+308 † para los valores positivos


Tipo Dato: Single (coma flotante/ precisión simple)
Memoria: 4 bytes
Valores: -3,402823E38 a –1,401298E-45 para valores negativos; 1,401298E-45 a 3,402823E38 para valores positivos.

y otros como: Byte, Decimal, Double (punto flotante de precisión doble), Object, Variant, Currency...

Es de esta forma como generamos nuestras variable:
Dim texto As String
Dim entero As Integer
Dim Enterolargo As Long
Dim fecha As Date
etc..

Los motivos recomendados de emplear y definir variables, entre otras, son:

1-Podríamos utilizar esa variable posteriormente en varias macros: En este caso, la forma de declarar la variable no es exactamente igual, sino que la línea Dim Variable As Tipo_Dato se debe colocar al principio de todo el módulo para que el ámbito de la variable sea precisamente de módulo y no a nivel de una macro concreta (ámbito local a nivel procedimiento)
2-Ocuparán menos espacio en memoria, que si no las declaramos, ya que por defecto, VB asignara el tipo Variant -que ocupa bastante memoria: 22 bits-.

En el post de hoy veremos una forma distinta (si bien, yo no recomendaría), heredada de los primeros pasos de la programación, donde se empleaban ciertos signos/símbolos para definir qué tipo de dato asociábamos a nuestra variable con el siguiente significado:

String $ Dim texto$
Integer % Dim entero%
Long & Dim EnteroLargo&
Double # Dim Doble#
Decimal @ Dim decimal@
Single ! Dim simple!

Recuerda mi recomendación, evita esta forma de definir las variables...

Seguir leyendo...

Tabla de datos con textos.

Estamos muy habituados a emplear la herramienta de análisis de hipótesis Tabla de datos con valores numéricos.. ya que realmente es cuando más provecho sacamos de ella.

Veremos hoy un uso diferente, aprovechándonos de esta herramienta, pero recuperando datos tipo texto.. y como a partir de ella obtenemos una Tabla de referencia cruzada imposible de otras formas...

haz clic en la imagen

Como recordaremos todos, la Tabla de datos es una herramienta matricial que analiza la variabilidad de un resultado sobre dos variables.
La encontramos en la Ficha Datos > grupo Herramientas de datos > botón Análisis Y Si/Análisis de Hipótesis

La idea del ejemplo es, a partir de un rango de datos por columnas, con registros únicos combinados sobre el campo Producto y Comercial, recuperar el dato de la columna Cliente correspondiente, esto es, construir una tabla de referencias cruzadas de acuerdo a las variables: Producto y Comercial.

Para ello emplearemos una función BUSCARV en su modo matricial (ver).
Incluimos en la celda F3 la siguiente función:
=SI.ERROR(BUSCARV(G2&F2;ELEGIR({1\2};$B$2:$B$13&$C$2:$C$13;$D$2:$D$13);2;0);"-")
ejecutada matricialmente!! (presionando Ctrl+Mayusc+Enter)

Completamos el rango de fila superior G3:K3 con los nombres de los productos, y en el rango de columna izquierda F4:F7 con los nombres de los comerciales.
A continuación seleccionamos el rango completo F3:K7 y accedemos a la herramienta Tabla, configurándola como sigue:

El resultado es el esperado, conseguimos una tabla de referencia cruzada con elementos tipo texto en el cruce!!... algo que no conseguiríamos con tablas dinámicas, por ejemplo.

Seguir leyendo...

VBA: Mostrar los sumandos.

Hoy veremos una UDF (función personalizada en VBA) muy simple como respuesta a la cuestión planteada de un usuario, quien pedía obtener en forma de lista los sumandos de un suma dada.

Tenemos un rango de celdas en B2:B4 y en B5 hemos realizado la suma de éstos:
=SUMA(B2:B4)
En C5 insertamos nuestras UDF:
=sumandos(B2:B4)
donde obtenemos el listado de elementos sumados.

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de un móodulo estándar del explorador de proyectos del Editor de VB:

Function sumandos(rango) As String
'determinamos número de elementos
n = rango.Count
'recorremos todos los elementos del rango indicado
For i = 1 To n
    'unimos los distintos elementos con un separador +
    If i = 1 Then
        lista = rango.Item(i)
    Else
        lista = lista & " + " & rango.Item(i)
    End If
Next i

'devolvemos a la celda la lista de elementos
sumandos = lista

End Function

Una variante sería recuperar la lista a partir de una celda con fórmula:

Function sumandos(CeldaFx) As String
Dim funcion As String, Fx As String
'verificamos la celda seleecionada tenga fórmula
If CeldaFx.HasFormula Then
    'recuperamos los rangos de trabajo...
    funcion = CeldaFx.Formula
    Fx = Mid(funcion, InStr(1, funcion, "(") + 1, Len(funcion) - InStr(1, funcion, "(") - 1)
    'determinamos número de elementos
    n = Range(Fx).Count
    'recorremos todos los elementos del rango indicado
    For i = 1 To n
        'unimos los distintos elementos con un separador +
        If i = 1 Then
            lista = Range(Fx).Item(i)
        Else
            lista = lista & " + " & Range(Fx).Item(i)
        End If
    Next i
    
    'devolvemos a la celda la lista de elementos
    sumandos = lista
Else
    sumandos = "Celda sin fórmula/función"
End If

End Function

Seguir leyendo...

VBA: Buscar un valor aproximado en un rango en Excel.

En un comentario de esta entrada del blog (ver) un lector preguntaba por la forma de realizar una búsqueda aproximada (por exceso) de un valor dado.

Si partimos de estos datos en el rango B2:M10

Deberemos retocar la función personalizada (UDF) del post anterior.. incorporándole un bucle DO...LOOP para localizar el siguiente valor superior al buscado más próximo.
Al no saber cuál es la diferencia de esa aproximación, he optado por incluir una salido del bucle cuando la diferencia llegue al valor buscado +10.
El bucle comenzaría con
DO
'la búsqueda por fila y columna..
LOOP UNTIL x=10

Así añadiremos nuestro código en un módulo estándar de nuestro explorador de proyectos:

Function busqueda(valor)
'función que busca un valor APROXIMADO dado en el rango B2:M10.
'iniciamos un contador que usaremos como diferencia a partir del valor dado
x = 1

Do
'c recorre las columnas de la matriz, de la 2ª a la 13ª.
For c = 2 To 13
    'f recorre las filas de la matriz, de la 2ª a la 10ª.
    For f = 2 To 10
        'si encontramos el valor aproximado nos detenemos
        'y salimos de la función
        If Cells(f, c).Value = valor + x Then
            busqueda = Cells(f, 1).Value
            Exit Function
        End If
    Next f
Next c
x = x + 1
'detenemos el bucle en una aproximación de +10
Loop Until x = 10
End Function

OJO, devuelve la primera coincidencia!!

Seguir leyendo...