martes, 27 de septiembre de 2016

VBA: Objeto Dictionary - Resaltar Palabras elegidas dentro de un Rango.

Aprovecharé hoy para emplear el objeto Dictionary, similar a una Collection en cuanto a finalidad o propósito... pero que aporta algún matiz.

Sirva como breve referencia lo siguiente.
1- La opción para especificar un método de comparación para las claves (vbBinaryCompare y vbTextCompare). Esto permitiría distinguir clave entre mayúsculas y minúsculas.
2- Un método (.Exists) para determinar si existe un objeto en un diccionario.
3- Un método (.Keys) para extraer todas las claves en una matriz.
4- Un método (.Items)para la extracción de todos los elementos en una matriz.
5- Un método (.Key(xxx)=yyyy)para cambiar un valor de clave.
6- Un método (.RemoveAll) para quitar todos los elementos del diccionario.

Una diferencia importante entre el objeto de Collection y el objeto Dictionary es el comportamiento de la propiedad Item: Si utilizamos la propiedad Item para hacer referencia a una clave inexistente de una Collection, obtendremos un error, en cambio si utiliza la propiedad Item para hacer referencia a una clave no existente en un dictionary, dicha clave se agregará al diccionario!! (Por ello, deberemos emplear el método .Exists para determinar si es o no una clave en un Dictionary.)


Comenzamos el ejemplo asignando un Nombre definido al rango A2:A6
contenido =Listado!$A$2:$A$6
este rango contiene una serie de palabras que son importantes y queremos resaltar dentro del texto que aparezca en otro rango...

VBA: Objeto Dictionary - Resaltar Palabras elegidas dentro de un Rango.



Insertamos el siguiente procedimiento en un módulo estándar de nuestro proyecto de VB, y posteriormente asignamos la macro a un botón:

Sub MarcarPalabras()
Dim Rng As Range, Dn As Range
Dim Sp As Variant    'Array
Dim pos As Long, n As Long, x As Long
Dim Dic As Object   'objeto Dictionary

'recuperamos el rango a partir del nombre definido creado
Set Rng = ActiveWorkbook.Names("contenido").RefersToRange

'Generamos nuestro propio objeto Dictionary
Set Dic = CreateObject("scripting.dictionary")
'definimos el tipo/propiedad del objeto
'vbBinaryCompare = si necesitamos una comparativa, respecto a mayusculas-minúsculas, exacta
'vbTextCompare = si NO necesitamos una comparativa, respecto a mayusculas-minúsculas, exacta
Dic.CompareMode = vbTextCompare

'recorremos cada celda del rango, i.e., cada palabra a localizar
'añadiendola a nuestro dictionary
x = 1
For Each Dn In Rng
    'si no existe la nueva palabra la añadimos al Diccionario
    'método .Exists
    If Not Dic.Exists(Dn.Value) Then
        'método Add
        Dic.Add Dn.Value, x
        x = x + 1
    End If
Next Dn

'con el diccionario creado, recorremos cada celda del rango seleccionado
'y cada palabra de cada celda
For Each celda In Selection
    c = 0:    pos = 0
    'separamos las frases por palabras
    Sp = Split(celda.Value, " ")
        'y recorremos cada palabra
        For n = 1 To UBound(Sp)
            'si existe la palabra..
            If Dic.Exists(Sp(n)) Then
                '...localizamos su posición y longitud
                pos = InStr(pos + 1, celda.Value, Sp(n), vbTextCompare)
                'para marcarla en rojo
                celda.Characters(pos, Len(Sp(n))).Font.Color = vbRed
            End If
        Next n
Next celda
End Sub



Tras ejecutar nuestra macro este es el resultado, tal como esperábamos:

VBA: Objeto Dictionary - Resaltar Palabras elegidas dentro de un Rango.

jueves, 22 de septiembre de 2016

Explicación de cómo convertir un valor Decimal a Hexadecimal

Obviamente sabemos que existe la función estándar de Excel:
=DEC.A.HEX(número;[posiciones])
que convertirá cualquier número decimal en hexadecimal...
Hoy lo que veremos es la explicación de esta función... en definitiva en qué consiste ese algoritmo de conversión.
El motivo del post es dar respuesta a un lector que planteaba la siguiente proegunta
Hola quisiera saber como pasar un código decimal a hexadecimal sin usar la fórmula de Excel.  Gracias


Lo más importante antes de comenzar es entender que un sistema decimal emplea 10 valores (del 0 al 9), mientras que el sistema hexadecimal emplea 16 (números de 0 al 9 y además las letras A, B, C, D, E y F) según la siguiente equivalencia:

Explicación de cómo convertir un valor Decimal a Hexadecimal



El algoritmo de trabajo se basa en conseguir en primer lugar la parte entera del cociente del número a convertir y posteriores entre 16 (hasta llegar a cero):
=ENTERO(B2/A3)

Explicación de cómo convertir un valor Decimal a Hexadecimal



En un segundo paso, a partir de esos cocientes (parte entera) obtenidos obtenemos el residuo o resto de la división entre 16 de dichos valores:
=RESIDUO(B2;A3)

Explicación de cómo convertir un valor Decimal a Hexadecimal



En un penúltimo paso, empleando la función BUSCARV conseguimos recuperar el valor correspondiente Hexadecimal (según la Tabla de equivalencia mostrada al inicio):

Explicación de cómo convertir un valor Decimal a Hexadecimal



El último paso es simple, basta unir en sentido inverso (ver celda D6) los valores equivalentes hexadecimales obtenidos.
=D5&D4&D3
Como comprobación, en la celda F8 se aplica la función estándar de Excel... obteniendo el mismo dato.

martes, 20 de septiembre de 2016

Fecha Inmediata Anterior a Otra

Un lector preguntaba por una fórmula para obtener la fecha inmediata anterior a otra dada, según una condición:
[...] necesito hallar la fecha de compra de unos consumos, tiene que ser la próxima anterior[...]
Tal y como se muestra en la imagen:

Fecha Inmediata Anterior a Otra



El objetivo es claro, para cada fecha y artículo del segundo rango D3:E8 debemos recuperar la fecha correspondiente anterior más próxima del primer rango A3:B8.

Para ello construimos la siguiente matricial que insertamos en F3:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
(recuerda validarla presionado Ctrl+Mayuscula+Enter!!)

A continuación copiaremos y pegaremos al resto de celdas F4:F8.


¿Cómo funciona esta fórmula matricial?.
Nos basamos en el cálculo de diferencias entre fechas para llegar a la más próxima anterior; esto es, con la parte más profunda de la fórmula, llegamos a conseguir unas fechas para aquellos casos en los que coincide el artículo.. así discriminamos las fechas que no nos interesan:
SI($A$3:$A$8=D3;$B$3:$B$8;HOY())

Fecha Inmediata Anterior a Otra


Sobre esta fecha obtenida aplicamos un nuevo condicional para calcular la diferencia con la fecha de referencia:
=SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY()))

discriminando además aquellas diferencias negativas (solo nos quedamos con diferencias positivas); esto es así por que buscamos sólo fechas anteriores (una diferencia negativa indicaría que la fecha es posterior).!!

Fecha Inmediata Anterior a Otra



Si de esas diferencias obtenidas nos quedamos con el valor mínimo:
MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
y ese valor mínimo lo restamos a la fecha de referencia, obtenemos la Fecha buscada:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))

Es decir, sobre el ejemplo de la imagen (fecha de referencia 15/07/2016), desde la fecha de referencia nos desplazamos 10 días antes... lo que nos lleva a la fecha de origen: 05/07/2016

Tal como se muestra en la imagen:

Fecha Inmediata Anterior a Otra